Google Apps Script & Sheets: appendRow() regularly overwrites the last row instead of appending a new row below

user_16586534 0
Viewed 1214

This is my first question on stackoverflow .

Background: From the reference for appendRow(rowContents) I understand that this operation is atomic, meaning that concurrent access to the last row of a sheet is handled and potential issues are prevented.

Issue / Unexpected Behavior: However, I recently realised that with many concurrent instances of a function that is appending rows to the very same sheet, the last row regularly is overwritten with the fresh data from appendRow(rowContents), instead of rowContents being appended as a new row below the last row.

In the past, I did not experience issues with appendRow() overwriting content on a sheet. It seems to be a recent phenomenon. However, this is my first GAS project that has so many concurrent instances logging their progress to the same spreadsheet/sheet.

Goal to be achieved: Independent from the number of concurrent instances, the script should append one row for each appendRow() operation rather than overwriting previous rows.

Small sample that reproduces the problem:

What I have already tried:

  1. Instead of logging to the spreadsheet, I've sent an email for each appendRow() operation. I can confirm that this is working as expected (I received an email for every appendRow() operation)
  2. Make use of Lock Service: As long as the timeout for trying to acquire a lock is not reached, each appendRow() operation generates a new row. As soon as the timeout is reached, appendRow() starts overwriting the last row again.

Call for help:

  1. Is anybody facing the same issue?
  2. What workarounds do you suggest?
  3. Am I missing something completely?

Any thoughts are highly appreciated!

google-apps-script google-sheets concurrency triggers multithreading
3 Answers

Using appendRow() within a loop is not recommended as you can also read here (Best Practices).

Instead of that:

  for (var i = 0; i < N; i++) {
    var row = [new Date(), task, i]
    s.appendRow(row);
    SpreadsheetApp.flush();
    console.log('row appended: \n',JSON.stringify(row))
  }

you should do that:

  var tb = [];
  for (var i = 0; i < N; i++) {
    var row = [new Date(), task, i];
    tb.push(row);
  }
  s.getRange(s.getLastRow()+1,1,tb.length,tb[0].length).setValues(tb);

In the first case you interact with the spreadsheet file N times, whereas in the second case only one.

user_112252910

Thanks for your suggestion, soMario!

Sure, I am aware that transactions against a spreadsheet should be reduced to a minimum. The actual script is more complex. The presented loop is for demo only.

Unfortunately, I struggle implementing your approach in the actual script because the concurrent tasks need to be able to flush their data to spreadsheet timely. While an iteration takes only some milliseconds in the demo script, iterations in the actual script may take up several minutes.

However, without making use of the Lock Service, your approach is also likely to overwrite existing data as s.getLastRow() might already be outdated because a different instance appended a new row in the meanwhile

I therefore suggest to lock the transaction:

  var out = [];
  for (var i = 0; i < N; i++) {
    var row = [new Date(), task, i];
    out.push(row);
  }
  var lock = LockService.getScriptLock();
  try {
    lock.waitLock(30 * 1000);
  } catch (e) {
    Logger.log('Could not obtain lock after 30 seconds.');
  }
  s.getRange(s.getLastRow() + 1, 1, out.length, out[0].length).setValues(out);
  lock.releaseLock();

Nevertheless, appendRow() is stated to overcome these issues. Why does it not behave as expected?

user_165865340

The issue is that the actual live state of the spreadsheet is only fetched when you create the sheet object on which you will later call appendRow or getLastRow. These 2 calls only account for changes made to the sheet in the current execution thread of the script and will ignore changes made in other execution threads.

The solution to this problem is either to:

  • Refrain from opening the sheet you'd like to act on until you have acquired the script lock.
  • Call SpreadsheetApp.flush() right before the call that matters, after acquiring the script lock.

This sample script demonstrates the issue:

function demoTrigger() {
  const sheet = SpreadsheetApp.openByUrl(ScriptProperties.getProperty('sheetUrl')).getActiveSheet();
  sheet.appendRow(['Demo line!']);
}


function demonstrateGetLastRowBehavior() {
  const ss = SpreadsheetApp.create('demo-delete-me'); 
  const sheetUrl = ss.getUrl();
  ScriptProperties.setProperty('sheetUrl',sheetUrl);
  
  /**
  * You can open the sheet and make sure that the trigger does run and
  * add a line before the second getLastRow call.
  * Otherwise increase the sleep duration.
  */
  console.log(sheetUrl);

  const sheet = ss.getActiveSheet();
  
  // getLastRow output: 0
  console.log(`getLastRow output: ${sheet.getLastRow()}`);

  const trigger = ScriptApp.newTrigger('demoTrigger').timeBased().after(100).create();
  Utilities.sleep(60000); // Sleep for 1 minute to let the trigger execute
  ScriptApp.deleteTrigger(trigger);

  // getLastRow output: 0
  console.log(`getLastRow output: ${sheet.getLastRow()}`);

  SpreadsheetApp.flush();
  // getLastRow output: 1
  console.log(`getLastRow output: ${sheet.getLastRow()}`);
  
}

demonstrateGetLastRowBehavior();

From my testing, calling SpreadsheetApp.flush() syncs up the local cache with the live data.

user_39165260
Related