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
Ed
Best Answer
-
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
Answers
-
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
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
-
I'm expereince the same issue and wondering if anyone had any success connecting to Excel using
the API Key
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 430 Global Discussions
- 150 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 500 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives