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

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


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

Answers