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

Question

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:

• See it in action → Screen Recording
• Code / Google Apps Script project file → included in the google spreadsheet

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! ????

Answer 1

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.

Answer 2

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?