How can I access the Smartsheet API from Excel power query?
I have ODBC connector for Excel but need to use the API in this instance.
I'd like to create an API connection that can be run by anyone with access to the Excel document.
I have a secure token but am unable to configure the parameters in Power Query so that the Smartsheet API is happy and provides access.
I currently have:
Data source settings: https://api.smartsheet.com/
Privacy Level: Organizational
I then try to configure my query as a web source.
In URL parts I have tried https://api.smartsheet.com/2.0/sheets/[sheetid]
I actually want to access a report which I assume is https://api.smartsheet.com/2.0/reports/
I have tried "ApiKeyName" and "Value" as request headers with the appropriate values.
I have also tried inserting the API key in the global settings.
Can someone please advise how I can configure these settings to access the API to extract report data into Excel via Power Query?