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.
请让我知道f you have an idea, a tool, some advice, or if I need to elaborate.
Thank you!
Answers
-
Genevieve P. Employee AdminHi@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 usethe Live Data Connectorto 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 listedon 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 theAPI DocumentationandDeveloper Portal.
Cheers,
Genevieve
-
Kelly Moore ✭✭✭✭✭✭
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
Categories
=IFERROR(INDEX(DISTINCT(LOWER(<\/strong>COLLECT(.....................))<\/strong>), 1), \"//www.santa-greenland.com/community/discussion/96683/\")<\/p> I haven't tested it yet, but I feel like it should work.<\/p>"},{"commentID":386435,"body":" Right. There are already a number of posts here in the community detailing how to pull a distinct or unique list from another sheet via formula. It usually involves the formula above without the LOWER function. The number 1 would output the first distinct value. Changing that to a 2 will output the second distinct value, so on and so forth. I usually use a helper column with the numbers manually entered so I can apply it as a column formula.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":320,"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":369,"urlcode":"bridge-by-smartsheet","name":"Bridge"}]},{"discussionID":107674,"type":"question","name":"API PUT and Body help for Cell link from a cell in sheet \"a\" to sheet \"b\"","excerpt":"I am having trouble linking a cell from sheet a to b. I am new to using API and possibly just getting the call wrong? or the body. Any help would be appreciated. PUT: https:\/\/api.smartsheet.com\/2.0\/sheets\/4098048888098692\/rows\/1370574824361860\/cells\/259016080 Body: { \"cells\": [ { \"linkinFromCell\": {…","snippet":"I am having trouble linking a cell from sheet a to b. I am new to using API and possibly just getting the call wrong? or the body. Any help would be appreciated. PUT:…","categoryID":320,"dateInserted":"2023-07-17T16:23:24+00:00","dateUpdated":"2023-07-18T10:24:44+00:00","dateLastComment":"2023-07-18T16:45:05+00:00","insertUserID":135530,"insertUser":{"userID":135530,"name":"Carlo Rodriguez","url":"https:\/\/community.smartsheet.com\/profile\/Carlo%20Rodriguez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-21T15:50:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":91566,"lastUserID":135530,"lastUser":{"userID":135530,"name":"Carlo Rodriguez","url":"https:\/\/community.smartsheet.com\/profile\/Carlo%20Rodriguez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-21T15:50:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":51,"score":null,"hot":3379310909,"url":"https:\/\/community.smartsheet.com\/discussion\/107674\/api-put-and-body-help-for-cell-link-from-a-cell-in-sheet-a-to-sheet-b","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107674\/api-put-and-body-help-for-cell-link-from-a-cell-in-sheet-a-to-sheet-b","format":"Rich","tagIDs":[472,563],"lastPost":{"discussionID":107674,"commentID":385785,"name":"Re: API PUT and Body help for Cell link from a cell in sheet \"a\" to sheet \"b\"","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385785#Comment_385785","dateInserted":"2023-07-18T16:45:05+00:00","insertUserID":135530,"insertUser":{"userID":135530,"name":"Carlo Rodriguez","url":"https:\/\/community.smartsheet.com\/profile\/Carlo%20Rodriguez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-21T15:50:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-18T16:45:08+00:00","dateAnswered":"2023-07-18T11:38:00+00:00","acceptedAnswers":[{"commentID":385713,"body":" Hey @Carlo Rodriguez<\/a> <\/p> This is how I've successfully added a cell-link using the API:<\/p> PUT (with the destination sheet data): <\/p>
<\/p>
<\/p>
![\"CF](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/C9LJSZDFAE96\/cf-1-png.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n![\"CF](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/4GOLOWA0NNC7\/cf-2-png.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n![\"CF](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/CGX9RWPP81B0\/cf-3-png.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n![\"CF](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/BEG81B5HTCF7\/cf-4-png.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n![\"CF](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/Q74IPCBDM40K\/cf-5-png.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
