Bulk POST operation syntax

Scott_B

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.

Jeff Reisman

@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.

Scott_B

Thanks, Jeff. That's exactly what I was looking for. I really appreciate it.

Jeff Reisman

@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.

Scott_B

Thanks, Jeff. That's exactly what I was looking for. I really appreciate it.

RamPS

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?

Jeff Reisman

@RamPS

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!

RamPS

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.

https://community.smartsheet.com/discussions/tagged/api-and-developers

Jeff Reisman

@RamPS

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.

RamPS

@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.