6

Hi Team,

Just curious Is it possible to link Smartsheet to SQL Server to fetch data?Thanks

 

Comments

Hi Jimmy,

Depending on your use case, you may be able to use the Live Data Connector to connect your SQL Server with Smartsheet. The Smartsheet Live Data Connector is built on ODBC standards and can be installed as a driver in other ODBC environments. But, it should be noted that the Live Data Connector is designed to work specifically with the applications listed on the requirements page (https://smartsheet-platform.github.io/odbc-docs/#requirements). With the Live Data Connector, we only support using it in those listed applications. It's also worth noting that the Live Data Connector provides a read-only experience, so if you need to write data to Smartsheet, this may not be a suitable solution.


If you would like to request added support for SQL Server with the Live Data Connector, please feel free to submit an Enhancement Request using the link below:

Submit Product Enhancement Request

I did find an available solution through Zapier which you may find useful also:

You could also consider using the available Smartsheet API to develop a custom integration. It is designed to be simple and intuitive, and is based on widely accepted standards and conventions, including REST, JSON, and HTTP success and error codes. Here are some resources for learning more about the Smartsheet API:

Hope that helps!

Kind regards,

Nathan L.

Smartsheet Support

 

I just performed an evaluation of Smartsheet-to-SQL Server using Zapier.  Short answer: FAIL.

  • Initial setup was not too complex, once we established the correct firewall rule to allow access to on-premise SQL Server.
  • Two apps are available:  New Row and Update Row.  Note that Delete Row does not exist, so if you remove a row from Smartsheet, it remains in the database table.  This may be a deal breaker for your application.
  • Setup of New Row is simple enough and basic functionality is OK. 
  • Update Row setup is more complicated because of the Search function required to find which row in the SQL table to update.  Setup gets really wonky if your SQL table has a primary key or required fields.  I ended up removing these constraints to get the Zap configured, then later re-adding table constraints.  After numerous tries I got the Update function to work.
  • There is little help available if you have a problem.  Google turned up basically nothing, and you cannot contact Zapier.
  • If there are any schema changes (new columns) the behavior is really weird.  I disabled the Zaps, added columns Smartsheet and the database, and went through the Zap setups to map the new columns.  The problem is that it does not pick up the schema changes that simply.  It won't recognize new columns unless there are also new rows (?!).  You must add a fake row Smartsheet, with data, and then ask Zapier to find the new columns (as sample data). 
  • Column mapping in Zapier is made unnecessarily opaque because it wants to show you cell values (from sample row data) instead of column names. You can eventually figure it out but this makes the learning curve steeper.
  • As a practical matter, you need a primary key in Smartsheet if you are using the Update Row app.  The only suitable candidate is Row ID (auto-number system column), and this will work.
  • Testing and logging work OK, with sufficient info given to understand what has happened.
  • The Update Row app contains what was for me a fatal flaw:  when you clear a cell in Smartsheet, it does not clear (null out) that field in the SQL table.  The Zap picks up the change, but whatever it passes to the database does nothing.

Overall I found Zapier's connection from Smartsheet to SQL Server to be an incomplete and unsupported piece of middleware.  In its current form it is not robust enough to be used in a production application.

In reply to by Mark Panarusky

Thank you, Mark! This was really helpful/informational. Could you please let me know what you're exploring as an alternative since SQL to Smartsheet using Zapier does not work for you?