Smartsheet to SQL Server

Jimmy

Hi Team,

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

 

Andrée Starå

Hi Jimmy,

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

https://www.smartsheet.com/datauploader

Would that work?

Happy New Year!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Nathan Lloyd

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: http://www.smartsheet.com/developers/api-documentation
• Developer Portal: http://www.smartsheet.com/developers
• SDKs: https://www.smartsheet.com/developers/sdks
• Sample Apps: https://github.com/smartsheet-samples

Hope that helps!

Kind regards,

Nathan L.

Smartsheet Support

 

Mark Panarusky

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.

David_Um

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

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

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

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

Graham K

Hey, I was having similar difficulties integrating with 3rd party tools so I developed a little web browser plugin to run SQL against my Smartsheet workspace. I just enter my API key into the connections tab, hit refresh, and it creates SQL tables for all of my sheets. I included screenshots to this post!

I currently just use this tool internally, but if your interested I'd be happy to set up a demo environment for you to try it out! If there is enough interest from the community I will publish the tool on the Smartsheet Solution Center!

soooo please upvote!

Thanks!

Graham😄

Rita_Patel

Hi Graham,

Interesting. I am looking for something like this for my project. It would be great if you can help me out. I have some Smartsheet sheets and reports. I want to load them on my SQL server DB. I tried using Smartsheet Live Data Connector without much success.

Thanks,

Rita

heyjay

https://community.smartsheet.com/discussion/comment/398656#Comment_398656

Hello Graham, Im interested on this project.

I am exploring MS Fabric, but could not find a way to load smartsheet to a lakehouse.

Graham K

I'm not familiar with MS Fabric or Lakehouse. I would recommend using the API to replicate your schema, then you can query the tables directly. I can help you out if you want to schedule some time, let me know

Bo Lewis

I've successfully used CozyRoc to both push and pull data between Smartsheet and SQL Server in the past. You install it as an extension on SQL Server and it allows some pretty strong bidirectional activity via SSIS. I haven't looked into it much in the last year, but it worked well for me at the time. I'm honestly a bit surprised no one else mentioned it above... so maybe there is some reason others aren't using it?

chris.comer

https://community.smartsheet.com/discussion/comment/398656#Comment_398656

Very cool, Grant!