Here are instructions to set up excel to connect with your Kinetic API V2 when you are using a Azure AD authentication (Organizational Account) and an API key. This will effectively allow a path forward to disable v1 for all of your users.
The API key will be passed using the query string. Keep in mind that this is generally considered a poor security practice, because the query string could be logged on the server. But other than reverting back to v1, it’s our only option currently. If you scope your API keys to specific endpoints for usage in excel, I see little risk here.
If you haven’t connected using an organization account yet, please follow my instructions in this linked post before proceeding.
Kinetic API V1 from Excel with Azure AD (Organizational Account)
-
In Excel’s Data tab, select “From Web”
-
Paste your endpoint with the API key in the query string
https://YOURSERVER/production/api/v2/odata/YOURCOMPANY/Erp.BO.ABCCodeSvc/List?API-KEY=YOURAPIKEY for example -
You should see something like this (assuming you have already connected to Kinetic with your Organizational Account). Click on the “List” value to navigate to the result list
-
Click on the “To Table” button to convert result to a table. Press Ok on dialog.
-
Expand “Column 1”. Select all columns. Make sure to uncheck “Use original column name as prefix”.
-
Click on “Close and Load” to save power query editor. Your results will now load.
