Bulk POST operation syntax
Hi. I have been able to stumble my way to successfully add new rows to my Smartsheet with the POST API request. However, I can't figure out the correct format to send more than one row at a time. I see reference to Bulk Operations in the documentation but I can't find any examples of exactly what that looks like. Would someone be able to provide a generic example of how to concatenate and format the payload for a request of two or more rows? Thank you.
Best Answers
-
@Scott_B This is from the API Documentation:
curl https://api.smartsheet.com/2.0/sheets/{sheetId}/rows \
-H "Authorization: Bearer ll352u9jujauoqz4gstvsae05" \
-H "Content-Type: application/json" \
-X POST \
-d '[{"toTop":true, "cells": [ {"columnId": 7960873114331012, "value": true}, {"columnId": 642523719853956, "value": "New status", "strict": false} ] }, {"toTop":true, "cells": [ {"columnId": 7960873114331012, "value": true}, {"columnId": 642523719853956, "value": "New status", "strict": false} ] }]'
I put the data for the first of the two rows in BOLD so you can see where one ends and the next begins.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks, Jeff. That's exactly what I was looking for. I really appreciate it.
Answers
-
@Scott_B This is from the API Documentation:
curl https://api.smartsheet.com/2.0/sheets/{sheetId}/rows \
-H "Authorization: Bearer ll352u9jujauoqz4gstvsae05" \
-H "Content-Type: application/json" \
-X POST \
-d '[{"toTop":true, "cells": [ {"columnId": 7960873114331012, "value": true}, {"columnId": 642523719853956, "value": "New status", "strict": false} ] }, {"toTop":true, "cells": [ {"columnId": 7960873114331012, "value": true}, {"columnId": 642523719853956, "value": "New status", "strict": false} ] }]'
I put the data for the first of the two rows in BOLD so you can see where one ends and the next begins.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks, Jeff. That's exactly what I was looking for. I really appreciate it.
-
Hi @Jeff Reisman
I found your post after similarly stumbling along and successfully adding 1 row but that's where it ended.
In your above response I note that you have added 2 rows and have individually coded the key value pairs for both rows.
When I tried to add 2 rows I got a 400 bad request error.
[{"cells":[{"columnId":2651513631672196,"value":"ThingThing"}
,{"columnId":7155113259042692,"value":"Name2Name2"}
,{"columnId":4903313445357444,"value":"Andrew"}
,{"columnId":399713817986948,"value":"PlacePlace"}
,{"columnId":1525613724829572,"value":"Colette"}
,{"columnId":7155113259042692,"value":"Ram"}
,{"columnId":399713817986948,"value":"Steve"}
,{"columnId":7225200682862468,"value":"Ishan"}
,{"columnId":1525613724829572,"value":"Place2Place2"}
,{"columnId":8562488142595972,"value":"NameName"}
,{"columnId":8562488142595972,"value":"Craig"}
,{"columnId":4903313445357444,"value":"AnimalAnimal"}
,{"columnId":2651513631672196,"value":"Snehal"}
,{"columnId":7225200682862468,"value":"Animal2Animal2"}
],"toBottom": true}]
How can I iteratively add multiple rows? I am reading in from an excel spreadsheet.
Any suggestions? -
You're missing the square brackets within reach row, and each row needs to be fully closed off, before closing off the entire json string with another square bracket.
'[{"toTop":true, "cells": [ {"columnId": 7960873114331012, "value": true}, {"columnId": 642523719853956, "value": "New status", "strict": false} ] }, {"toTop":true, "cells": [ {"c…
The text in bold is for one row. The statement starts with '[ which will surround the entire string, and the individual row values start with {. Within the first row, we have after "cells": a square bracket, then a space, then a curly bracket. So we know that each row's data must end with } ] }, before you can start the next row's data. At the end of all the row data, the json needs to be closed off with ]' to finish it up.
Hope this helps!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi @Jeff Reisman
Thank you. I am making some headway but haven't reached the solution yet. I am doing this using Alteryx so spinning out the json is proving to be an issue ATM. I'll let you know how I go in the next little while.
If someone has done something similar using Alteryx please reach out. -
I used Alteryx for this too. Unfortunately I'm not at my former company any longer so I don't have access to see how exactly I did it. But I ended up with the whole payload in one cell. If I needed to iterate through say hundreds of records, I think I set it up to mark records as done after they were written to json, and then have the flow keep running starting with the first unmarked row and collecting the next 100 or 200 rows or whatever.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman
Awesome! Thank you again for your help.
I am going to come back to this after today (have a few bigger problems to solve for the day) and will let you know how I go.Thanks again, I appreciate it.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives