Order of items in response to API Add Rows

sgolux · 08/04/22

When using the API to add rows to a smartsheet, namely POST https://api.smartsheet.com/2.0/sheets/{sheetId}/rows - you can supply an array of row descriptors in the request to ask Smartsheet to create those rows.

The response is an array of newly-created Smartsheet rows.

Am I guaranteed that the order of the response array or rows is the same as the request array of rows? The documentation does not specify. But if I can't be guaranteed of the order, how can I relate the response array elements to the request array elements, because I will need to store away the new row IDs with my source system source entities?

Genevieve P. · 08/26/22

Hi @sgolux

Thank you for responding and clarifying your question, I appreciate the time you took to detail your process.

Instead of adding an indexer to the end of the text in the column, I would suggest adding a new column and write a unique ID to that column with your add row request. That way you can leave the data alone without adding and removing extra information.

For example:

Add another column to the destination sheet called "UUID".
Loop over each new row entry you're about to add to the sheet, and add one more cell to the list of cells for each row with that UUID value (if you're using python, there's a native library to generate a unique id called uuid).
Send the rows into Smartsheet and make sure to simply capture the result of the add_rows method, because it will have the new rows in the response.
The result will have all of the new rows, with Smartsheet row numbers, and a way to correlate the source data with unique ID to the newly existing rows in Smartsheet.

You can hide the UUID column on the sheet so nobody will know it's there, and leave the unique identifiers on the row. This way there's no need to clean it or edit the sheet again after you add new rows.

I hope that helps!

Genevieve

Genevieve P. · 08/17/22

Hi @sgolux

The answer to this and your other post is the same so I'll duplicate my response in both threads:

No, there isn't a guarantee for the order of any lists returned from the API. While it may sometimes return in the proper order, this cannot (and should not) be relied upon.

The only way to handle the response is mapping by column ID. To make it easier to program, you can look at the columns array and make a mapping of column ID to column title - then you can refer to that mapping with the human readable title and tease out the ID.

A best practice method is in this example: https://github.com/smartsheet-samples/python-read-write-sheet/blob/master/python-read-write-sheet.py#L13

Cheers,

Genevieve

sgolux · 08/17/22

Hi @Genevieve P. , I see how you may have thought I was asking the same question in two ways, and the same answer would work for both, but there is a subtle difference, and the question in this post really is different.

In the other post, I was asking about the order of columns and row cells in response to the GET SHEET API. And although I was sad about your answer, I do know a way to create the mapping I need per row in that sheet. It is inefficient, but possible.

THIS question, on the other hand, is about the ADD ROWS API, when you add rows to a sheet.

When invoking this API, the response contains an array of the actual rows that were added.

My question is how to map the order of the response to the order of the request, because as far as I can see, there is no way to ensure any particular uniqueness on a row that is added.

In my code, I am building up an array of rows I need to add and then using the bulk ADD ROWS operation to add them, but I need (in my integration) to store away the ID of each created row in my source system to relate my source entity to the newly created Smartsheet Row. But if I can't rely on those rows being returned in the order that I submitted them, how can I relate the row ID to the underlying source entity? I don't see a way that I can definitely rely on any other aspect of the row that might be returned to be unique so that I can parse out the returned IDs and relate them to the source entities.

What I have done so far is a really ugly hack, which is that in one of the row columns I am adding an indexer to the end of the text in the column, and then parsing out that indexer in the response, but then I have to subsequently update each of those rows in order to remove the indexer from the sheet. This is is a terrible hack with a terrible code smell.

Can you offer some other way (if order won't work) that I can definitively and without hacking it associate row IDs with source rows in the ADD ROWS API?

Genevieve P. · 08/26/22

Hi @sgolux

Thank you for responding and clarifying your question, I appreciate the time you took to detail your process.

Instead of adding an indexer to the end of the text in the column, I would suggest adding a new column and write a unique ID to that column with your add row request. That way you can leave the data alone without adding and removing extra information.

For example:

Add another column to the destination sheet called "UUID".
Loop over each new row entry you're about to add to the sheet, and add one more cell to the list of cells for each row with that UUID value (if you're using python, there's a native library to generate a unique id called uuid).
Send the rows into Smartsheet and make sure to simply capture the result of the add_rows method, because it will have the new rows in the response.
The result will have all of the new rows, with Smartsheet row numbers, and a way to correlate the source data with unique ID to the newly existing rows in Smartsheet.

You can hide the UUID column on the sheet so nobody will know it's there, and leave the unique identifiers on the row. This way there's no need to clean it or edit the sheet again after you add new rows.

I hope that helps!

Genevieve

Order of items in response to API Add Rows

Best Answer

Answers

Categories