Smartsheet ODBC connector not working - but only for Excel

CycleBagEd

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

Ed

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

Ed