Google Sheets API: Append cells and add developer metadata atomically?

0
votes

I want to append a row of cells to a google sheet and also attach some developer metadata to that row.

In the Google Sheets v4 API, I know you can use batchUpdate to append a row with the appendCells request, and you can add developer metadata using the createDeveloperMetadata request.

My issue is that I wanna set some developer metadata to specifically the newly appended cells atomically. There's not really a way to specifically ensure the range of the newly added row in createDeveloperMetadata, and if I use two different requests, someone else may insert a row between those requests which could shift all the rows, causing the appended cell's range to be pointing to an incorrect row.

Is there a way to attach developer metadata to a newly added cell atomically?

2
google-sheetsgoogle-apigoogle-sheets-api
From your question, I cannot understand about your current issue and your goal. I apologize for this. In order to correctly understand about them, can I ask you about the detail of them? And also, can you provide your current script? - Tanaike

2 Answers

1
votes

Answer:

There is not currently way of ensuring that the sheet structure hasn't changed between requests.

More Information:

Your The best option, I think, is to make two sequential requests in the same batch. Though this isn't foolproof, in very unlucky circumstances. Even inserting the row directly using an UpdateCellsRequest isn't foolproof either, as simply knowing which row you inserted the data doesn't exclude the possibility that someone else may insert/delete a row before it between the two requests.

Feature Request:

You can however let Google know that this is a feature that is important for the Sheets API and that you would like to request they implement it. Google's Issue Tracker is a place for developers to report issues and make feature requests for their development services.

The page to file a Feature Request for the Google Sheets API is here.

I hope this is helpful to you!

References:

0
votes

The solution I figured out was essentially:

  • Fetch the dimensions of the sheet

  • Perform a batch update with insertDimension, updateCells, and createDeveloperMetadata all performed for the same sheet dimension index at the end of the sheet

This basically ensures that the dimension index will point to the same row for all 3 operations in the batch update, and if the index points out of bounds, all 3 operations will fail