Smartsheet to SQL Server

Jimmy · 01/01/19

Hi Team,

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

Andrée Starå · 01/03/19

Hi Jimmy,

It might be possible with the premium add-on called Data Uploader.

//www.santa-greenland.com/datauploader

Would that work?

Happy New Year!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Nathan Lloyd · 01/04/19

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:

Smartsheet + SQL Server:https://zapier.com/apps/smartsheet/integrations/sql-server

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:

Getting Started:https://github.com/smartsheet-platform/getting-started
API Documentation://www.santa-greenland.com/developers/api-documentation
Developer Portal://www.santa-greenland.com/developers
SDKs://www.santa-greenland.com/developers/sdks
Sample Apps:https://github.com/smartsheet-samples

Hope that helps!

Kind regards,

Nathan L.

Smartsheet Support

Mark Panarusky · 03/22/19

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 RowandUpdate Row. Note that删除行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 youcell values(from sample row data) instead ofcolumn 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 doesnotclear (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.

David_Um · 03/26/19

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?

Mark Panarusky · 03/27/19

I have not yet explored any alternatives to Zapier for pulling Smartsheet data into SQL Server. The options I have seen are both significantly more complex and expensive.

SSIS, using something like CData:https://www.cdata.com/kb/tech/smartsheet-ssis-task-import-2008.rst
Roll your own using Smartsheet's API:http://smartsheet-platform.github.io/api-docs/?_ga=2.157676473.285875843.1553030000-763321198.1522423933

David_Um · 03/27/19

Thanks for sharing. It seems like Smartsheet and a SQL Database do not work well together...hopefully it's on their roadmap to improve this relationship.

MarkSchlaudraff · 03/18/22

Has someone looked at 'automate.io' to handle this?

Smartsheet to SQL Server

Comments

Categories