using ODBC and sql server to consolidate smartsheet tables

Chris Arndt

Hello community. Day 1 for me. My company is looking to bring the smartsheet data back in-house for consolidated reporting purposes. MSSQL is our shop. I am looking at the ODBC connector and it looks like the ODBC shows each sheet as a separate table. I'm looking consolidate these sheets into 1 sql table.

Has anyone done this with the ODBC connector? I'm thinking of creating a linked server with this ODBC connector and then writing sql to consolidate all of this. Thanks for your time in advance.

MichaelTCA

Hello @Chris Arndt

It depends on what ODBC you are using.

Smartsheet Live Data Connector is read only, so that won't work if you're writing to files.

You would need a trigger to export the data and consolidate it through a different piece of software, like Data Shuttle or a linked server, then you can read the data out of a single table through the ODBC (If you are using Smartsheet's).

CData is a third party Smartsheet works with that provides an ODBC that might do what you are looking for.

Chris Arndt

Thanks for the feedback. I'm looking to pull data down to store into sql, so I'm not going to write any files to smartsheet. My thought is if I used this odbc with a linked server in SSMS, I could query the data out of smartsheets and consolidate it all into 1 table in MSSQL. I was looking to see if anyone has done this or am I just swimming upstream. I've been involved with this project all of a couple of days.

Your response reads like it is worth trying out, yes? I'm also looking at their API.

MichaelTCA

@Chris Arndt

To clarify, you're trying to export the data out of Smartsheet, consolidate it, and then use the ODBC to reference that table in Smartsheet?

Sounds like Smartsheet's live data connector would work for that (read only), but you would have a separate procedure for getting the data to the database to be "referenceable".

You can't use Smartsheet's ODBC to transfer or consolidate data, only view. You would have to use a 3rd party app if you want multiple functions out of it.

I had to find a different route to go, but maybe it will work for you!

I haven't done much research into their API, you might get a different impression about it than I did. Everything about Smartsheet is designed to be simplified through low code / no code.

Chris Arndt

Which route did you go? I'm also looking at their API.

I am looking to consolidate all of this data into 1 master table that can then be reported on by the tools we have in-house. We are a MSSQL shop, so this consolidation would happen in MSSQL.

MichaelTCA

@Chris Arndt

We decided to manually enter the information where required outside of Smartsheet and use Data Shuttle to import it. Then consolidate all the data into a single log in Smartsheet and present it that way.

So maybe I'm not 100% understanding. If you don't mind me asking, what ODBC are you using?

Yes, the consolidation would happen in MSSQL, but how will you transfer the data? If it is Smartsheet's, you will not be able to transfer the data.

Chris Arndt

I am using the ODBC driver provided by smartsheet. It is their 'smart connector'. The documentation says you can write sql against the smartsheet data through this odbc connector, so I want to see if a linked server can be set up in MSSQL using this ODBC connector to transfer data from smartsheet into a mssql table using sql statements. We have another cloud product that has a custom ODBC driver and we can write sql against it as if the db was in-house, so I'm hoping to be able to do the same.

I'm trying to avoid data shuttle (and the money it costs to get it).

Smartsheet Live Data Connector (smartsheet-platform.github.io)