How can I access the Smartsheet API from Excel power query?

CycleBagEd

Hi,

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/

Credentials: Anonymous

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?

thanks

Genevieve P.

Hi @CycleBagEd

I have a couple of questions about this... can you clarify why you aren't using the ODBC connector? Are you looking to add content to the report or get data?

It would also be helpful to know what you mean when you note that you want it to "be run by anyone with access to the Excel document."

You'll want to add a Header name = Authorization, and the value is Bearer <API Token>. The Smartsheet API Documentation on Raw Token Requests will have more information: https://smartsheet.redoc.ly/#section/API-Basics/Raw-Token-Requests

I've also found an old thread on StackOverflow that goes through setting up PowerQuery and Smartsheet, if that helps.

Cheers,

Genevieve

Genevieve P.

Hi @CycleBagEd

I have a couple of questions about this... can you clarify why you aren't using the ODBC connector? Are you looking to add content to the report or get data?

It would also be helpful to know what you mean when you note that you want it to "be run by anyone with access to the Excel document."

You'll want to add a Header name = Authorization, and the value is Bearer <API Token>. The Smartsheet API Documentation on Raw Token Requests will have more information: https://smartsheet.redoc.ly/#section/API-Basics/Raw-Token-Requests

I've also found an old thread on StackOverflow that goes through setting up PowerQuery and Smartsheet, if that helps.

Cheers,

Genevieve

CycleBagEd

Hi @Genevieve P.

The missing piece of the jigsaw in terms of PQ access was use of the text "Bearer" in front of the API Key.

For future browsers of this topic, the information here is useful in getting started on extracting the contents of your data as opposed to the metadata retrieved initially when you connect to the API

https://www.mssqltips.com/sqlservertip/4621/using-power-bi-with-json-data-sources-and-files/