Assigned
Status Update
Comments
ko...@gmail.com <ko...@gmail.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.
sh...@gmail.com <sh...@gmail.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
ko...@gmail.com <ko...@gmail.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.
sh...@gmail.com <sh...@gmail.com> #5
Eric, thanks for your reply!
For option #2:
a) Some sheets have more than 200 000 rows, thus getting all rows will impact the performance.
b) Also performance issue as in a).
For option #3: I've filed new issue:https://code.google.com/a/google.com/p/apps-api-issues/issues/detail?id=4828
Thanks,
Alex
For option #2:
a) Some sheets have more than 200 000 rows, thus getting all rows will impact the performance.
b) Also performance issue as in a).
For option #3: I've filed new issue:
Thanks,
Alex
sh...@gmail.com <sh...@gmail.com> #6
Thank you, Eric! Could you please point out ETA of the feature? So that I would be able to decide whether I need to make some workaround or just wait for the feature in prod.
ko...@gmail.com <ko...@gmail.com> #7
Unfortunately I don't have an ETA to provide. While I've raised the issue with the engineering team, they have not yet committed to adding this feature.
ek...@google.com <ek...@google.com>
fo...@zapier.com <fo...@zapier.com> #8
Hi, Zapier here!
We're seeing the same issue when usinghttps://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append with A:A as range to workaround https://code.google.com/a/google.com/p/apps-api-issues/issues/detail?id=4828 and insertDataOption:INSERT_ROWS (https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append#InsertDataOption ). So it seems that workaround #3 doesn't seem to work.
Workaround #2 isn't an option for us either since we don't know the row ID and many sheets are too big to query for it.
Workaround #1 might help, but is not practical since a user might have multiple Zaps working on the same sheet and even have other third parties doing the same.
I think what we need here is a "simple append" that locks the sheet on your end, looks for the first empty row (resolving the table detection issue) and appends the row sent.
Any ETA on progressing this issue or anything we can do to help you troubleshoot this?
Much appreciated!
Fokke @ Zapier
We're seeing the same issue when using
Workaround #2 isn't an option for us either since we don't know the row ID and many sheets are too big to query for it.
Workaround #1 might help, but is not practical since a user might have multiple Zaps working on the same sheet and even have other third parties doing the same.
I think what we need here is a "simple append" that locks the sheet on your end, looks for the first empty row (resolving the table detection issue) and appends the row sent.
Any ETA on progressing this issue or anything we can do to help you troubleshoot this?
Much appreciated!
Fokke @ Zapier
1u...@gmail.com <1u...@gmail.com> #9
I am experiencing the same issue
ol...@gmail.com <ol...@gmail.com> #10
Fokke, do you think the fact you're using A:A as a range as a workaround has anything to do with your issue?
For my application, multiple people can insert data into the same table at the same time from different machines, I'm using spreadsheet.values.append and insertDataOption:INSERT_ROWS, and I'm worried now that I'm going to be losing data if two people append at the same time, is this the case? I'm not using the workaround, my table always has data in column A.
Also I don't understand how using A:A as a range is a workaround forhttps://code.google.com/a/google.com/p/apps-api-issues/issues/detail?id=4828 , if there's no data in column A won't it try writing data in A1?
Thanks,
For my application, multiple people can insert data into the same table at the same time from different machines, I'm using spreadsheet.values.append and insertDataOption:INSERT_ROWS, and I'm worried now that I'm going to be losing data if two people append at the same time, is this the case? I'm not using the workaround, my table always has data in column A.
Also I don't understand how using A:A as a range is a workaround for
Thanks,
fo...@zapier.com <fo...@zapier.com> #11
Apparently the Issue Tracker doesn't come with email notifications :)
I don't see how this range (which is related to columns) would affect the returned row. I think this bug is "just" a matter of not locking the sheet properly. IMHO it should lock, insert the row, get the full inserted row and then unlock. Other writes should wait for the sheet to unlock in the order in which the requests were received.
We've ended up using A:A by trial and error and have found it does force the API successfully to always start at row A when inserting new rows.
I wonder if Google over-engineered these APIs a bit since all we really need is insert a new row at the end of the sheet, with no detection of tables in that sheet or whatsoever.
I don't see how this range (which is related to columns) would affect the returned row. I think this bug is "just" a matter of not locking the sheet properly. IMHO it should lock, insert the row, get the full inserted row and then unlock. Other writes should wait for the sheet to unlock in the order in which the requests were received.
We've ended up using A:A by trial and error and have found it does force the API successfully to always start at row A when inserting new rows.
I wonder if Google over-engineered these APIs a bit since all we really need is insert a new row at the end of the sheet, with no detection of tables in that sheet or whatsoever.
ek...@google.com <ek...@google.com>
[Deleted User] <[Deleted User]> #12
Hi Google, Typeform here.
Well, almost 4 years ... any update about this issue?
Well, almost 4 years ... any update about this issue?
ge...@acidlabs.com <ge...@acidlabs.com> #13
Hi there!! same problem here
I am testing the use of SpreadsheetApp.flush(); after each appendRow. Performance sucks but it seems to work.
I am testing the use of SpreadsheetApp.flush(); after each appendRow. Performance sucks but it seems to work.
[Deleted User] <[Deleted User]> #14
Hi there,
SpreadsheetApp.flush(); after each appendRow() operation does not solve the issue for many concurrent instances of the same function that is appending new rows to the sheet.
A small sample that reproduces the problem very drastically (because appendRow() is wrapped in a simple for() loop) I have posted here for discussion:
https://stackoverflow.com/questions/68642977/google-apps-script-sheets-appendrow-regularly-overwrites-the-last-row-inste/68649225#68649225
SpreadsheetApp.flush(); after each appendRow() operation does not solve the issue for many concurrent instances of the same function that is appending new rows to the sheet.
A small sample that reproduces the problem very drastically (because appendRow() is wrapped in a simple for() loop) I have posted here for discussion:
th...@qualtrics.com <th...@qualtrics.com> #15
Hi Google, Qualtrics here!
We have the same issue, trying to insert rows from concurrent calls. We do local batching but for some of our most heavy users we have parallel workers performing insertions. We are experiencing the issue mentioned with batchUpdate
, with some insertions being lost.
Using the append
API isn't a real solution as its behaviour can be unpredictable with sheets having an empty first column for instance. It is not a real Insert a new row to the sheet.
Any update on this?
Thanks
de...@cirtru.com <de...@cirtru.com> #16
Hi Google, Cirtru here,
We are facing the same issue. We are adding a row at the top using the insertDimension option, but everytime 2 requests are received nearly at the same time, it will add an empty row at the top and only insert data from one of the 2 requests. Is there any update on this? Won't lock slow down the API response time.
We are facing the same issue. We are adding a row at the top using the insertDimension option, but everytime 2 requests are received nearly at the same time, it will add an empty row at the top and only insert data from one of the 2 requests. Is there any update on this? Won't lock slow down the API response time.
[Deleted User] <[Deleted User]> #17
Commenting to keep a eye on this, Vantek here havving the same issue
ro...@venturepact.com <ro...@venturepact.com> #18
Hi Google, Outgrow here,
We are also facing the same issue, any update on this?
We are also facing the same issue, any update on this?
Description
For sending an append request we are using batch update request endpoint:
POST
The body of batch update request has "appendCells" property with appropriate append cells request data:
1. "sheetId": appropriate sheet ID;
2. "rows": only one row;
3. "fields": "*"
Thanks,
Alex