E10.2.400 + REST + Excel 2016 - Not working for me

Epicor ERP 10
, ,
17

disable https -
< add key=“AllowInsecureRestApi” value=“True” />
disable app key -
< add key=“EnforceApiKeyForRestApiV2” value=“false” />

By natively supporting OData, Epicor ERP web services allow for easy consumption from tools that leverage the
protocol. You can have a refreshable spreadsheet against a live data source. Build pivot tables, charts against the
16 Epicor ERP | 10.2.400
Display Data in OData Applications Epicor REST Services v.2
feed for quick reports. Do the following to use REST services to pull Epicor data within an Excel spreadsheet. You
can then format this live data display as you need.
Note OData v4 is supported by MS Excel® 2016 or higher.
Important To be able to get data from a Service/BAQ feed into Excel using REST API v.2, you must:
• exclude the api-key parameter from your requests, and
• disable the default API Key requirement for REST requests by setting the value of the
EnforceApiKeyForRestApiV2 tag in the appSettings section of the system configuration file to false.

  1. Launch Microsoft® Excel®.
  2. In the Main menu, select the Data tab.
  3. From the Menu Ribbon, select the Get Data option.
  4. From the Get Data list, select the From Other Sources > From OData Feed.
    The OData Feed dialog displays.
    Epicor ERP | 10.2.400 17
    Epicor REST Services v.2 Display Data in OData Applications
  5. For the URL, enter the resource you wish to display in this Excel spreadsheet. For example:
    https://EpicorServer/ERP102400/api/v2/odata/EPIC06/Erp.Bo.CustomerSvc/Custo
    mers
    The following URL can be used to display data from the Customer Contacts Business Activity Query:
    https://EpicorServer/ERP102400/api/v2/odata/EPIC06/BaqSvc/COM-CustContacts/
    Data
    Note If you connect to the Epicor application server for the first time, you will be prompted to define
    the Authentication settings. Select the Basic tab. Enter your Epicor user credentials, select which level
    to apply these settings to, and click Connect.
  6. Click OK. The query results dialog box displays.
    18 Epicor ERP | 10.2.400
    Display Data in OData Applications Epicor REST Services v.2
  7. Click Load.
    The resource data is loaded into the current Excel spreadsheet.
    You can then use table design tools to format how this data displays; review your Microsoft® Excel®
    documentation for more information.
18

tried that mate, v2 still give me errors, if you could apply it in your environment with some screenshots, i will be very appreciated

19

On the Epicor side i changed added the 2 tags in web.config

EnforceApiKeyForRestApiV2 = false
AllowInsecureRestApi = true

On the Excel side, for the query I edited the credentials after the initial request threw an error.

For example I have tried the sample from the REST setup guide with my own site settings substituted in.

http://epicorapp01/E102400LIVE/api/v2/odata/NCPLTD/BaqSvc/COM-CustContacts/Data

20

for me it is loading data.

https://erp102/erp10/api/v2/odata/epic06/BaqSvc/COM-CustContacts/Data

21

this is the bit i am struggling with, i have tried all of it with no lock

image
image705×320 8.03 KB

22

Yes. This is the place I added the credentials. I just used one of our system manager accounts.

23

image

24

where exactly on the web.config did you change this, could you please screenshot it

25

image
image.png518×565 34.3 KB

1 Like
26

can you clear existing setting

image
image.png544×535 18.8 KB

image
image.png792×576 10.6 KB

then do setting again as below

image
image.png547×640 24.4 KB

image
image.png700×222 4.92 KB

image
image.png703×308 11 KB

1 Like
27

change this on the server web.config?

28

yes

29

many thanks mate, working beautifly :+1::+1::+1::+1::+1:, thanks for both of you

1 Like
30

@richardh working for you?

31

Seems like an idea that would work for on prem by as SaaS I doubt I can get that changed.

32

We use Excel 2016 and REST v1 securely. Every. Single. Day.

33

Mark are you SaaS public client or on prem as SaaS is not working (did last Monday and that’s it) but Epicor is working on it.

34

Public Cloud SaaS. v1 Rest and O365 (2016).

v2 is not working because of the locked-down security (Access Tokens/Scope).

2 Likes
35

I got version 1 and 2 working from Excel by using our SSO app server instead.

36

Hi All,
This is how it is done in my Excel 2016 version, Assuming Epicor certificate has been installed on client PC, Steps are as follow:

  1. launch Microsoft Excel App.
    image

  2. Go to [Data] Tab.

  3. Click on [From Other Sources]

  4. Select [from OData Data Feed] from the dropdown list

    image
    image693×512 29.1 KB

  5. Copy-Paste the provided Epicor BAQ REST API URL Web address link, you can always double check at in any browser to make share it is working OK.

  6. Enter the relevant Epicor Username and Password then click Next.
    image

  7. Tick the box of the appeared relevant Epicor BAQ ID, thin click next.
    image

  8. Click finish on the next dialog box.
    image

  9. Select where you need the data table to be, then click OK.

    image
    image573×620 27.3 KB

  10. Now you have a live data linked Epicor and all what you need to do next time is right click on any place on this data then select Refresh.
    Note: you will need to Enter your Epicor username and password per each link you create.

    image
    image398×635 16.8 KB

    image