Verified
Status Update
Comments
cc...@google.com <cc...@google.com> #2
I responded on a different channel (https://groups.google.com/d/topic/google-spreadsheets-api/G0sUsBHlaZg/discussion ) but will include it here for posterity's sake:
Unfortunately this is a know limitation of the append requests, and we are working to add that detail to the documentation. The core issue is that the appendCells requests place data into existing cells, rather than create new cells. Because of this, two requests can target the same existing cells and overwrite each other.
There are a few workarounds:
1) Implement locking in your application to ensure that only one process is writing to the spreadsheet at a given moment.
2) Instead of using the appendCells request, send an insertDimension and updateCells request in a single batch. This will add a new row for the new data and write to it in one atomic unit.
3) Use the spreadsheet.values.append() with insertDataOption=INSERT_ROWS, which does the same things as option #2.
I've changed this issue to a feature request to add the insertDataOption parameter to the appendCells request, so that the same behavior is available there.
Unfortunately this is a know limitation of the append requests, and we are working to add that detail to the documentation. The core issue is that the appendCells requests place data into existing cells, rather than create new cells. Because of this, two requests can target the same existing cells and overwrite each other.
There are a few workarounds:
1) Implement locking in your application to ensure that only one process is writing to the spreadsheet at a given moment.
2) Instead of using the appendCells request, send an insertDimension and updateCells request in a single batch. This will add a new row for the new data and write to it in one atomic unit.
3) Use the spreadsheet.values.append() with insertDataOption=INSERT_ROWS, which does the same things as option #2.
I've changed this issue to a feature request to add the insertDataOption parameter to the appendCells request, so that the same behavior is available there.
cc...@google.com <cc...@google.com> #3
Eric, thanks a lot for your reply and suggestions! Regarding the possible workarounds:
1. Locking in my application. Unfortunately it cannot be implemented since the application is widespread and requests can be sent to the Google Sheets API from different machines.
2. insertDimension + updateCells. Actually we are using appendCells because this request does not require the number of a row where new row should be inserted. It's unknown where is the last cells infilled in a sheet. Please let me know if there a way to find out the last row number containing infilled cells.
3. spreadsheet.values.append. We also tried to use this request, however there is some challenges. For instance if the first column doesn't contain any values and the spreadsheet.values.append request is sent with A1:D1 range, it starts to insert items from the second column (B). But if the first column has only one infilled cell, it starts to insert items from the first column (A). Please let me know if I'm doing something wrong.
Actually after a lot of tests, the appendCells request was chosen, so it would be really great if insertDataOption was added to appendCells request.
Thanks,
Alex
1. Locking in my application. Unfortunately it cannot be implemented since the application is widespread and requests can be sent to the Google Sheets API from different machines.
2. insertDimension + updateCells. Actually we are using appendCells because this request does not require the number of a row where new row should be inserted. It's unknown where is the last cells infilled in a sheet. Please let me know if there a way to find out the last row number containing infilled cells.
3. spreadsheet.values.append. We also tried to use this request, however there is some challenges. For instance if the first column doesn't contain any values and the spreadsheet.values.append request is sent with A1:D1 range, it starts to insert items from the second column (B). But if the first column has only one infilled cell, it starts to insert items from the first column (A). Please let me know if I'm doing something wrong.
Actually after a lot of tests, the appendCells request was chosen, so it would be really great if insertDataOption was added to appendCells request.
Thanks,
Alex
cc...@google.com <cc...@google.com> #4
As for option #2, that is a problem. There are two solutions I can think of:
a) First make a .get(fields=sheets.data.rowData.values.userEnteredValue) or value.get() and count the number of rows returned to determine where to insert.
b) If you have a field you can sort by (timestamp, etc) then insert at the top and sort after. This might have performance impacts as the sheet gets larger.
For option #3, that does seem to be a problem if you want to ensure that rows start in column A. Please file a new issue to track that and I'll raise it with the engineering team.
a) First make a .get(fields=sheets.data.rowData.values.userEnteredValue) or value.get() and count the number of rows returned to determine where to insert.
b) If you have a field you can sort by (timestamp, etc) then insert at the top and sort after. This might have performance impacts as the sheet gets larger.
For option #3, that does seem to be a problem if you want to ensure that rows start in column A. Please file a new issue to track that and I'll raise it with the engineering team.
Description