Use Case:
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.
*Here is the summary article: https://www.smartsheet.com/apps/smartsheet-odbc
*Here is the detailed documentation: http://smartsheet-platform.github.io/odbc-docs/#getting-started
*Here are the installation files: http://smartsheet-platform.github.io/odbc-docs/#download
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 justin.winter@cfacorp.com.Audit Schedule__8141418171524996s_(Home)'
*Remove the "justin.winter@cfacorp.com/" 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)" '
Enjoy!
