I have created an API and set its scope to just the BAQ I want to return. In the REST helper, I can paste the API in the header for REST v2. I get the data back as expected in the REST helper. However, I want to use this data in Excel to make some charts. I have tried a few different ways to pull it in, including power query editor, and the =webservice formula. Nothing has worked so far. I couldn’t get the power query editor to return anything, and the webservice formula always returned #Value.
Help me understand the differences here. I think I want to use web API as the data source, not OData. But sometimes it looks like web API and odata are the same thing. I think both methods return data in JSON format. Am I getting these acronyms correct? I want to connect to the datasource using only my API key, so I don’t have to enter my username and credentials. Is that how this works?
Thanks for your time!
Nate
I am trying to get away from using the odata v1 with username/pw authentication. Thats the whole reason the API key exists in the first place. Right?
I tried adding this to the connection but still get the same errors. Either anon connection is not allowed, or I have to enter an API key, which keeps saying I need an API key name. Also tried the whole website string with the api key in Excel’s -webservice() formula, but still returns nothing (value).
The only way I have ever gotten this to work is to get data “From Web” instead of “From oData Feed” in Excel. Using the oData Feed always gave me an error.
I get the same kind of error using the web data source in excel. What do you paste in there as a connection string? Do you include your API in the address? Do you include the API key name in the address?
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. I thought the whole point of API key and v2 REST was to avoid the username requirement? If I choose WebAPI, I am back to where I started.
In the couple of worksheets I have done, once a user opens the file and refreshes the data, they enter their credentials for the first time it no longer asks them to authenticate. Depending on what you’re trying to do that might be a deal breaker, but unfortunately it’s the only way I know how to get this to work in the first place.
