Automated Data Export
Hey All,
I have a problem that I have been struggling to find an efficient solution for regarding how to routinely get form data out of smartsheets. I am hoping for ideas, advice, and/or secure tools that you all may know of to help with that. For context, below is my current issue/goal and what I think is the best solution. Please critique:
Goal: I need to get my form data out of smartsheets and into our local network. (Specifically, we are trying to get it onto our SQL Server.) For example, when an object is stolen, we want a user to fill out a form to get all the information for the police. We want the information that was added the smartsheet to then automatically be sent to our SQL Server where it will be used to create a nice looking report.
We have explored some options, we are ruling out Data Shuttle as a tool because it is too expensive for our use case. We are ruling out Zapier because of firewall concerns.
Current Idea: We are looking for a SFTP location for smartsheets, or some secure location where we can grab information for our SQL server. I know there's likely a lot of work that goes into this solution, but security is vital for this project.
Please let me know if you have an idea, a tool, some advice, or if I need to elaborate.
Thank you!
Answers
-
Hi @Schwill
I would have suggested Data Shuttle in this instance, but I see you've already looked into this.
Depending on where you want the data to be stored, 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. I will note that the Live Data Connector is designed to work specifically with the applications listed on the requirements page.
If this isn't an option, then another possibility would be to use the Smartsheet API to develop a custom integration. See the API Documentation and Developer Portal.
Cheers,
Genevieve
-
Hey @Schwill
If the Genevieve's solution above doesn't work (and it would be the cleanest solution), could you create a report and then schedule the report to an email address (See File, Send As Attachment -probably choosing the excel format). Microsoft's PowerAutomate might then be able to move to your network drive location. This is a clunky solution but it might be cost effective. Hopefully the report would be empty most days - if it's capturing items reported stolen! 😲
Kelly
