Smartsheet ODBC connector not working - but only for Excel

I have ODBC connector installed. It was working fine with Excel until we switched to single sign on. Then it broke.

I have followed instructions on using an API key for connectivity. These have not worked. I assumed this was an issue at the connector level.

HOWEVER: I have just tried to connect using MS Access. The ODBC connection asked for an API key, then worked immediately, so the connector is working.

In Excel though, I get the message below. I cannot make it work. What am I doing wrong please?

thanks

Find more posts tagged with

Accepted answers

CycleBagEd

Yes.

Use queries and connections function in Excel.

The data is in JSON and you need to create separate queries for the headers and the data itself and then combine.

Also be aware that sheets, reports and summary reports all seem to require separate syntax for the query and the example here is for a row report. There's also a 10,000 row limit so if there's a lot of data you need to use the page function and include another table in your combination query.

My Advanced Query starts like this for the headers:

let
Query1 = Json.Document(Web.Contents("https://api.smartsheet.com/2.0/reports/YOURREPORTID", [Headers=[Authorization="Bearer YOURAPIKEY"]])),
columns = Query1[columns],

And you break it down in Power Query from there.

I think the info you need is on this thread - see the link at the bottom.

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

All comments

Genevieve P.

Hi @CycleBagEd

Thanks for posting screen captures! From this, it looks like you are entering the API token to the “Credential connection string properties” section rather than in the password field.

Try adding the API key in the “Password” field as it is used to authenticate you the same way as a password would.

Here's the ODBC documentation on establishing the connection with Excel specifically: (http://smartsheet-platform.github.io/odbc-docs/#excel).

Let me know if you're still seeing an error after following the steps in the documentation above!

Cheers,

Genevieve

CycleBagEd

Thanks @Genevieve P.

I can't get it to work from that screen but I have been able to use the (legacy) connection wizard to actually pull the data into Excel finally.

Adding the API key "as is" into the password field does not work
Entering the API key into the Credentials field does not work
Using existing credentials does not work
Using the legacy query wizard and entering the API key DOES work but the screen for selecting and ordering data columns is not present, whereas it WAS present prior to single sign on
Using Microsoft query also works and does present the screen to select a subset of columns from the report/sheet

So it looks like the ODBC option is no longer available to use with SSO, or at least I can't get it to work, and I seem to recall it working prior. There is no mention of using the ODBC route on the instructions you have linked to, but this had worked previously.

The sheet which I want to update has the following query:

let

Source = Odbc.DataSource("dsn=My Smartsheet DSN", [HierarchicalNavigation=true]),

followed by details of the sheet/report and which columns are required. I can't find a way of editing or replacing this query at all - I will have to rebuild the report using one of the other connection methods.

thanks for your support

CharlesB

I'm expereince the same issue and wondering if anyone had any success connecting to Excel using

the API Key

CycleBagEd

Yes.

Use queries and connections function in Excel.

The data is in JSON and you need to create separate queries for the headers and the data itself and then combine.

My Advanced Query starts like this for the headers:

let
Query1 = Json.Document(Web.Contents("https://api.smartsheet.com/2.0/reports/YOURREPORTID", [Headers=[Authorization="Bearer YOURAPIKEY"]])),
columns = Query1[columns],

And you break it down in Power Query from there.

I think the info you need is on this thread - see the link at the bottom.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.