Loading data from excel into Smartsheet API to an existing sheet
I have data in Excel which I have loaded and cleaned in Python and I want to load the same into Smartsheet through API. This data goes into the existing sheet in Smartsheet, which is typically adding new data into the sheet. I have gone through few articles about "Add Rows" but it doesn't explain about loading data with multiple rows.
Answers
-
The "Add Rows" operation supports both single and bulk
POSToperations. You can specify multiple rows in the request, however all rows must be inserted at the same location (for example, the above, parentId, siblingId, toBottom, or toTop attributes must be the same).Here are the links to the documentation that specifies this:
You may also want to take a look at the Smartsheet Samples in GitHub. I found this sample which may help: python-read-write-sheet
Cheers!
Genevieve
-
I can't be the only one with a use case of mirroring an Excel spreadsheet (or CSV or whatever) into a SmartSheet grid on a regular basis.
The API supports extracting a SS grid in excel format. It should support updating an existing grid with an uploaded excel spreadsheet.
This is very possible via the raw REST API and the SS supported Python package. I wrote something up in Python in a couple of hours that pulls a grid via the API, reads an excel file and sends the necessary update_rows and add_rows API calls. It's similar to the python-read-write-sheet example mentioned above, but far more generic (the column names are readily available on both sides of the equation)
But it seems like a use case that should be supported natively by SmartSheet's API offerings.
I'll clean up the code, get the okey-dokey from my employer and share it on GitHub soon.
-
Hi @Tony Rice I'm looking to solve same problem. have you been successful with the code? if you could share it on GitHub I think it would be great.
-
The problem with the python-read-write-sheet is that it returns a random newly created Smarthsheet id. Using this snippet as an example, how to tell the API to write data to a specific Smartsheet?
-
@Genevieve P.
Hello, in all of your comments where there is a link to your API doc, they all expired and landed on the same page. I think it is because you guys updated the API doc page. Can you do something about it?
-
Hi @OptimusPrime
Thank you for the tag! Yes you're right, the API documentation has been updated. If you search for "Add Rows" in the search bar on the left it should take you to the correct place:
https://smartsheet.redoc.ly/tag/rows#operation/rows-addToSheet
Or if you scroll down, the API documents are sectioned out. Here's the Bulk Operations link: https://smartsheet.redoc.ly/#section/Work-at-Scale/Bulk-Operations
Cheers,
Genevieve
