Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Connecting Alteryx to Smartsheet

justin_winter
8 - Asteroid

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!

16 REPLIES 16
danielbrun2
ACE Emeritus
ACE Emeritus

Great input. Thanks for sharing.

 

Daniel

wale_ilori
9 - Comet

Thanks Justin!!

KatieM
5 - Atom

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.

 

Any suggestions?

 

Thank you,

Katie 

 

wale_ilori
9 - Comet

Hi Katie,

 

So what you're saying is that your syntax is just what shows below?

Select * From "XXXXXXXXXX__9999999999999999s_(XXXXXXXXXXXXXXXXXX - XXXXXXX XXXXX)"

 

mix_pix
10 - Fireball

Hi Justin,

 

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?

 

smartsheet results.png

jglim79
5 - Atom

Hi,

I have followed the step and it's showed me below error message:

Connection Error:

ERROR [28000] Missing connection string parameter(s)

ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR [28000] Missing connection string parameter(s)

 

Attached with log file

 

I have tried with excel and it's successfully connected but not for Alteryx. Please help.

mmorningstar17
5 - Atom

@mix_pix I'm running into the same problem. Did you ever find the fix for this?

mheinze
Alteryx
Alteryx

@jglim79 @mmorningstar17 Try checking this community article that addressed smartsheet connections using API Tokens.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Connecting-Alteryx-to-Smartsheet-using...

 

JamesHa
Alteryx Alumni (Retired)

I think it just needs a browse tool after to show the data.  Not sure why it won't give a preview in the record window.

James Dolan-Hall | Principal Technical Account Manager
support@alteryx.com | community.alteryx.com
Labels