It looks like we are having the same conversation on the issue tracker: https://code.google.com/a/google.com/p/apps-api-issues/issues/detail?id=4827
Would you mind copying your reply there, and I'll respond? - Eric On Thu, Oct 6, 2016 at 3:13 PM, Alexander Shyronosov <[email protected]> wrote: > 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 > > > > On Thursday, October 6, 2016 at 8:44:52 PM UTC+3, Eric Koleda wrote: >> >> Note, that the spreadsheet.values.append method has a parameter >> called insertDataOption that allows you to specify that new rows should be >> inserted, which also works around the concurrency issues. I'll see if we >> can add that same option to the appendCells request. >> >> - Eric >> >> On Thursday, October 6, 2016 at 1:40:10 PM UTC-4, Eric Koleda wrote: >>> >>> Hi Alex, >>> >>> 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 append 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. >>> >>> Best, >>> - Eric >>> >>> On Thursday, October 6, 2016 at 10:38:05 AM UTC-4, Alexander Shyronosov >>> wrote: >>>> >>>> We are using Google Sheets API v4 for getting and appending rows in a >>>> sheet. There is an issue when several requests for rows appending are sent >>>> simultaneously, e.g. 4 concurrent requests. Sometimes such requests are >>>> intersected and some of the appended rows are overwritten by another >>>> requests. For instance if each request has only 1 row for append and 4 such >>>> requests are sent at the same moment, only 3 rows are created. This issue >>>> doesn't appear in case of sequential requests. Is it known issue? Is there >>>> any workaround how we can achieve that rows are not overwritten by >>>> concurrent append requests? >>>> >>>> For sending an append request we are using batch update request >>>> endpoint: >>>> POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId >>>> }:batchUpdate >>>> >>>> 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 >>>> >>> -- > You received this message because you are subscribed to a topic in the > Google Groups "Google Spreadsheets API" group. > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/google-spreadsheets-api/G0sUsBHlaZg/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "Google Spreadsheets API" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
