I had a schedule for audits that was maintained in Smartsheet and wanted to do an automated check against our databases to make sure that those audits were conducted.
In order to do so, I need to be able to connect Alteryx to Smartsheet and then link that schedule up to the audit data that was already loaded into our databases. In doing so, I realized there were a few tricks to connecting to Smartsheet via Alteryx make it work that weren't perfectly clear [to me] the first time through. So I decided to write up the step-by-step that made it work for me! As always, there are probably better ways to do this!
1) On your desktop/server ensure that the Smartsheet "Live Data Connector" or ODBC Connector is installed. You have to connect to it as an ODBC Data Source.
2) After installation, an ODBC data source needs to be setup for each user account. All sheets/reports will be setup as tables via the ODBC connection.
*Open your ODBC Administrator console (Search "ODBC" if needed), Go to the "System DSN" tab, click "New", Choose "Smartsheet Live Data Connector", then click "Finish"
*You can enter your credentials here, or enter them each time you connect via that ODBC connection. In my experience it didn't seem to remember the API Key or Password, so I had to do it in Alteryx anyway.
3) Once the connection is setup, now we just need to drop a "Data Input" tool onto our canvas in Alteryx and pull the data:
*Drag the "Data Input" tool to the canvas > Click the dropdown arrow next to "Connect a file or Database" > "Database Connection" > "New ODBC Connection"
*Choose your newly created ODBC connection > enter your username/password or API Key > Click "OK"
4) Now we have the Alteryx Connection, and finally we need to pull the data. The "Tables" tab on the Data Input tool will show the list of eligible data sources to pull from Smartsheet.
*Double click the table that you want then click out of the tool. It will throw an error.
*Manually go back into the Data Input Tool > Open the "Table or Query" > Click on the "SQL Editor" tab
*You will have a query that looks something like this: 'SELECT * FROM email@example.com.Audit Schedule__8141418171524996s_(Home)'
*Remove the "firstname.lastname@example.org/" part from the front of the table name, then place the rest of the table name in double quotes. It should now return your data. My query looked like this: 'SELECT * FROM "Audit Schedule__8141418171524996s_(Home)" '
Hello - have you ever run into an issue where only some tables from Smartsheet are able to be loaded into Alteryx? I am unable to load a few of my Smartsheet tables, though some work with the methodology mentioned above.
The error I am receiving is: Error SQLPrepare: There was an error executing an SQL query: 'near "(": syntax error' (1).
I have eliminated my email from the name of the file and this worked for another table in Smartsheets, but no longer works for any other tables.
Thanks for the detailed instructions...I was able to get my connection set up using the API key. But I get some weird results, as you can see below. The sample shows that there is data there (and the same API key allows me to pull back data in Tableau), but the Input tool doesn't bring in any rows. Any idea what would cause this?