CODEWITHSUNDEEP
google-sheetsgoogle-sheets-api
Alexander Shyronosov
Alexander Shyronosov

Reputation: 363

Concurrent rows appending in Google Sheets API v4

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": "*"

Upvotes: 7

Views: 5133

Answers (2)

Sam Berlin
Sam Berlin

Reputation: 3773

Consider using the values.append API with InsertDataOption.INSERT_ROWS. Each request should go to its own new row.

Upvotes: 10

Ed Nelson
Ed Nelson

Reputation: 10259

Try google apps script Lock Service.

function appendRow_(row) {
  // The try / catch statement can be uncommented

  //try {
    var lock = LockService.getPublicLock();
    lock.waitLock(10000);

    Do something

    // Post form results with empty responses in order of item index
    s.appendRow(row);
    lock.releaseLock();
  //} 

  //catch (error) { 
     //Insert your own error handling
     //Logger.log(error);
  //}

  //finally { 
    //lock.releaseLock();
  //}
}

Upvotes: 0

Related Questions