Smartsheet to SQL Server
Hi Team,
只是好奇有可能链接内容SQL Server to fetch data?Thanks
Comments
-
Andrée Starå ✭✭✭✭✭✭
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Nathan Lloyd Employee
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
-
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 ✭✭✭
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?
-
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.
-
Has someone looked at 'automate.io' to handle this?
Categories
It depends on your automation settings. If you set it to unrestricted, you can send to an email address in a cell without giving them access to the sheet. You can also specify which cells are provided in the update request within the automation builder.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":[]},{"discussionID":106840,"type":"question","name":"Report not showing all rows that match the filter parameters","excerpt":"Hello! I have a sheet that has 3424 rows with a specific Due Date. When I look at that filter option in the report, I see that it sees 3424 for that field. However, when I add that parameter, only 2500 rows display. I can't find a row limit to reports that would cause this. Is there a row limit to reports that I'm unaware…","categoryID":321,"dateInserted":"2023-06-23T16:50:36+00:00","dateUpdated":null,"dateLastComment":"2023-06-23T17:36:10+00:00","insertUserID":74136,"insertUser":{"userID":74136,"name":"asimcock","url":"https:\/\/community.smartsheet.com\/profile\/asimcock","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-23T17:45:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-23T19:11:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":40,"score":null,"hot":3375083206,"url":"https:\/\/community.smartsheet.com\/discussion\/106840\/report-not-showing-all-rows-that-match-the-filter-parameters","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106840\/report-not-showing-all-rows-that-match-the-filter-parameters","format":"Rich","tagIDs":[265],"lastPost":{"discussionID":106840,"commentID":382043,"name":"Re: Report not showing all rows that match the filter parameters","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382043#Comment_382043","dateInserted":"2023-06-23T17:36:10+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-23T19:11:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-23T17:47:12+00:00","dateAnswered":"2023-06-23T17:02:03+00:00","acceptedAnswers":[{"commentID":382031,"body":"
See this article. There is a 2500 row limit for reports.<\/p>