# Pulling BAQ into Excel - Web API, OData, JSON? **URL:** \ **Category:** Kinetic ERP\ **Tags:** e10\ **Created:** [April 24, 2023, 1:37pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015 "2023-04-24T13:37:14Z")\ **Posts on this page:** 20\ **Page:** 2
**Author:** ![utaylor](https://www.epiusers.help/user_avatar/www.epiusers.help/utaylor/32/64664_2.png) [@utaylor](https://www.epiusers.help/u/utaylor)\ **Post date:** [April 24, 2023, 3:15pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/21 "2023-04-24T15:15:45Z")
It’s even making me think you can put the API Key in the password field: ![image](https://www.epiusers.help/uploads/default/original/3X/4/2/424aeefc265807ff60d0179df9adabf01dc2a208.png) ---
**Author:** ![utaylor](https://www.epiusers.help/user_avatar/www.epiusers.help/utaylor/32/64664_2.png) [@utaylor](https://www.epiusers.help/u/utaylor)\ **Post date:** [April 24, 2023, 3:18pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/22 "2023-04-24T15:18:03Z")
I am not sure, if you want to do a teams we can play around with it. In all transparency I have never tried v2 in excel so I am not coming at this with any experience. ---
**Author:** ![Mark\_Wonsil](https://www.epiusers.help/user_avatar/www.epiusers.help/mark_wonsil/32/51594_2.png) [@Mark\_Wonsil](https://www.epiusers.help/u/Mark_Wonsil)\ **Post date:** [April 24, 2023, 3:18pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/23 "2023-04-24T15:18:12Z")
> [@NateS](#): > > After you enter that, the next window asks for a way to authenticate. It won’t accept anonymous and using basic forces you to enter a username and pw. No, no, no. You’re SaaS. Do you really want anonymous access to your system? 🤔 > [@NateS](#): > > I thought the whole point of API key and v2 REST was to avoid the username requirement? Usernames and Password - or better an ID Token - identify who is calling. The API-Key provides finer control on what you can do (authorization) after authenticating. Passing the API Key in on a URL “works” but is not ideal since the call is generally logged somewhere. Do you use Active Directory? That might be able to solve your authentication problem if the user already has a session token for Kinetic. I think you would then use Organization authentication…but I’ve never tried it. 🤔 ---
**Author:** ![utaylor](https://www.epiusers.help/user_avatar/www.epiusers.help/utaylor/32/64664_2.png) [@utaylor](https://www.epiusers.help/u/utaylor)\ **Post date:** [April 24, 2023, 3:19pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/24 "2023-04-24T15:19:17Z")
Love it Mark, I will look at ways to use AD and also how to use a key vault ---
**Author:** ![Mark\_Wonsil](https://www.epiusers.help/user_avatar/www.epiusers.help/mark_wonsil/32/51594_2.png) [@Mark\_Wonsil](https://www.epiusers.help/u/Mark_Wonsil)\ **Post date:** [April 24, 2023, 3:22pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/25 "2023-04-24T15:22:23Z")
> [@utaylor](#): > > and also how to use a key vault I assume you mean for the API-Key? Don’t get me wrong, Key Vault is good for holding API-Keys and other secrets but if the client can just grab it, then… Right now, the only solution I know of is to write an API which then calls Epicor. The API-Key is handled by the backend and you passthough authentication from the front end. ---
**Author:** ![klincecum](https://www.epiusers.help/user_avatar/www.epiusers.help/klincecum/32/100137_2.png) [@klincecum](https://www.epiusers.help/u/klincecum)\ **Post date:** [April 24, 2023, 3:23pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/26 "2023-04-24T15:23:12Z")
> [@andrew.johnson](#): > > ?api-key it’s `x-api-key` _ **Correction:** _ > [@andrew.johnson](#): > > It’s x-api-key when provided in the header. In the query URL it is api-key. ---
**Author:** ![Olga](https://www.epiusers.help/letter_avatar_proxy/v4/letter/o/57b2e6/32.png) [@Olga](https://www.epiusers.help/u/Olga)\ **Post date:** [April 24, 2023, 3:24pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/27 "2023-04-24T15:24:12Z")
Organization authentication does not work yet. So only basic authentication only. ---
**Author:** ![NateS](https://www.epiusers.help/user_avatar/www.epiusers.help/nates/32/18519_2.png) [@NateS](https://www.epiusers.help/u/NateS)\ **Post date:** [April 24, 2023, 3:25pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/28 "2023-04-24T15:25:01Z")
> [@Mark\_Wonsil](#): > > Do you use Active Directory? We use AD but not connected to Epicor. I think I get it now. I really don’t want anon access, but some post said to try setting that first then modifying the code. I kind of thought that the API was security enough, but I see what you mean, the API doesn’t say anything about who is asking for the data. I thought that was the point, but I guess I am wrong. The goal of using an API only datasource to pull data is to avoid using up a license or seat for a user that is only pulling data down for display purposes. In the past I had setup an odata connection and provided my username and password. This returns data just fine, but there is an issue with automation. I left that datasource setup in the background, and linked it to something that would pull the data every day. The problem is that the day my password expires, I will get a message that I am already locked out. This is because the odata source was trying to pull data over an over with my (now expired) credentials. This forced epicor to lock me out and I had to get someone to reset my PW for me. What should I be doing instead? ---
**Author:** ![klincecum](https://www.epiusers.help/user_avatar/www.epiusers.help/klincecum/32/100137_2.png) [@klincecum](https://www.epiusers.help/u/klincecum)\ **Post date:** [April 24, 2023, 3:25pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/29 "2023-04-24T15:25:32Z")
> [@Olga](#): > > You have to use REST v1. I got it working with Rest v2, but I don’t remember what I did. ---
**Author:** ![andrew.johnson](https://www.epiusers.help/user_avatar/www.epiusers.help/andrew.johnson/32/61226_2.png) [@andrew.johnson](https://www.epiusers.help/u/andrew.johnson)\ **Post date:** [April 24, 2023, 3:26pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/30 "2023-04-24T15:26:07Z")
It’s x-api-key when provided in the header. In the query URL it is api-key. ---
**Author:** ![NateS](https://www.epiusers.help/user_avatar/www.epiusers.help/nates/32/18519_2.png) [@NateS](https://www.epiusers.help/u/NateS)\ **Post date:** [April 24, 2023, 3:26pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/31 "2023-04-24T15:26:26Z")
> [@klincecum](#): > > I got it working with Rest v2, but I don’t remember what I did. Pray tell!? ---
**Author:** ![Olga](https://www.epiusers.help/letter_avatar_proxy/v4/letter/o/57b2e6/32.png) [@Olga](https://www.epiusers.help/u/Olga)\ **Post date:** [April 24, 2023, 3:27pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/32 "2023-04-24T15:27:21Z")
I only know the way to do this is to switch off api keys. So it is interesting how you hacked it. May be it should be fixed to not allow it 😈 ---
**Author:** ![utaylor](https://www.epiusers.help/user_avatar/www.epiusers.help/utaylor/32/64664_2.png) [@utaylor](https://www.epiusers.help/u/utaylor)\ **Post date:** [April 24, 2023, 3:29pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/33 "2023-04-24T15:29:08Z")
I see, yeah I was thinking so they didn’t have to use the API key value itself in the excel connection you could put it in a vault. But that doesn’t make much sense cause you would be authenticating to get to the vault using AD so at that point why wouldn’t you just use AD to authenticate with the epicor rest call. ---
**Author:** ![Mark\_Wonsil](https://www.epiusers.help/user_avatar/www.epiusers.help/mark_wonsil/32/51594_2.png) [@Mark\_Wonsil](https://www.epiusers.help/u/Mark_Wonsil)\ **Post date:** [April 24, 2023, 3:38pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/34 "2023-04-24T15:38:45Z")
> [@NateS](#): > > The goal of using an API only datasource to pull data is to avoid using up a license or seat for a user that is only pulling data down for display purposes. Epicor has a license type for that called, unironically, a Web Service license. There are some limits on the frequency you call it though. Otherwise, people would buy just one license and run all of Kinetic through it. 🤷 > [@NateS](#): > > The problem is that the day my password expires, I will get a message that I am already locked out. This is because the odata source was trying to pull data over an over with my (now expired) credentials. This forced epicor to lock me out and I had to get someone to reset my PW for me. > > What should I be doing instead? In User Account entry, you can create an Integration Account for this. Create a Scope and associate it with an API-KEY and the Integration Account checkbox gets checked (or you have to check it, don’t recall). Play around with that instead of using a person’s credentials - which is not good for…reasons. ---
**Author:** ![NateS](https://www.epiusers.help/user_avatar/www.epiusers.help/nates/32/18519_2.png) [@NateS](https://www.epiusers.help/u/NateS)\ **Post date:** [April 24, 2023, 3:39pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/35 "2023-04-24T15:39:46Z")
Thank you Mark! I think this sounds like the right way. I will work on it and let everyone know! ---
**Author:** ![klincecum](https://www.epiusers.help/user_avatar/www.epiusers.help/klincecum/32/100137_2.png) [@klincecum](https://www.epiusers.help/u/klincecum)\ **Post date:** [April 24, 2023, 3:42pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/36 "2023-04-24T15:42:43Z")
> [@NateS](#): > > Pray tell! > [@klincecum](#): > > I don’t remember what I did … ---
**Author:** ![klincecum](https://www.epiusers.help/user_avatar/www.epiusers.help/klincecum/32/100137_2.png) [@klincecum](https://www.epiusers.help/u/klincecum)\ **Post date:** [April 24, 2023, 3:46pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/37 "2023-04-24T15:46:18Z")
> [@Olga](#): > > So it is interesting how you hacked it I don’t think I did anything weird. Some setting in excel. ---
**Author:** ![klincecum](https://www.epiusers.help/user_avatar/www.epiusers.help/klincecum/32/100137_2.png) [@klincecum](https://www.epiusers.help/u/klincecum)\ **Post date:** [April 24, 2023, 3:47pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/38 "2023-04-24T15:47:11Z")
I’m in the middle of a bunch of fiber and wireless install, I’ll futz with Excel when I can pay attention. ---
**Author:** ![Randy](https://www.epiusers.help/user_avatar/www.epiusers.help/randy/32/51539_2.png) [@Randy](https://www.epiusers.help/u/Randy)\ **Post date:** [April 24, 2023, 4:34pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/39 "2023-04-24T16:34:54Z")
Yes, inquiring minds would like to know. We have a request here to use Excel to pull BAQ data but stuck since we can’t use REST v2 in Excel. ---
**Author:** ![NateS](https://www.epiusers.help/user_avatar/www.epiusers.help/nates/32/18519_2.png) [@NateS](https://www.epiusers.help/u/NateS)\ **Post date:** [April 24, 2023, 4:50pm UTC](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015/40 "2023-04-24T16:50:41Z")
I followed Marks ideas and I setup a new user called OJEXWebService. Once I set the access scope to the BAQ that I want, it automatically setup the Integration Account stuff for me. Next I had to activate that user, which sent the password to my email. Finally, I went to excel, and added a webdata source. I pasted my v2 REST string with the api included, and choose basic authentication. In the username I put the PJEXWebService username, and the temp password that was emailed to me. This worked! I can now see some data in Excel! I will have to toy with it a bit to get it in the right format, but I think this is working correctly. @Olga am I breaking things this way? [Previous page](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015.md?page=1) [Next page](https://www.epiusers.help/t/pulling-baq-into-excel-web-api-odata-json/102015.md?page=3)