How can I Speed up an onEdit script so that it captures all subsequent edits?

Question

I have a bit of script to capture changes made to a table of information, pulling the new value as well as the person's ID, a category, and the reference of the item being changed. Altogether the script collects 4 snippets of data, plus a timestamp. Once collected, this array is appended to the bottom of a table on another sheet to act as a change log. Once done, the changed cell is formatted yellow to show it's been changed.

It works, but if you change another cell too quickly afterwards, the script trips over and one or more subsequent changes are not captured. How can I "bank" the changes, and write them to the second sheet once all the changes are made? (eg by clicking a "save" button) I am self taught so have big holes in my knowledge, so any helpful suggestions (with examples) gratefully received.

code:

function onEdit(e){

const ss = SpreadsheetApp.getActiveSpreadsheet();
const ViewId = ss.getSheetByName('VIEW').getSheetId(); // Sheet ID of the only sheet this script applies to
const ThisSheetId = ss.getActiveSheet().getSheetId(); // Sheet ID of whichever sheet is active
const SignedOff = ss.getSheetByName('SignedOff'); // Sheet on which the collected data is to be appended


if(ViewId == ThisSheetId){
  
// Exit if we're on the wrong page
 
  var ThisRow = e.range.getRow();
  var ThisCol = e.range.getColumn();
  
  if (ThisRow >= 4 && ThisCol >= 3){

// Exit if we're out of range so only changes within the table are registered

    var NewValue = e.range.getValue();
    var IndividualID = VIEW.getRange(ThisRow,1,1,1).getValue(); // get value from column A of the edited line
    var ItemName = VIEW.getRange(2,ThisCol,1,1).getValue(); // get header value from the edited column
    var Category = VIEW.getRange('J1').getValue(); // get value showing which category the item is from
    var Timestamp = new(Date);

    SignedOff.appendRow([IndividualID,Category,ItemName,NewValue,Timestamp]); // add the collected data to the list on the SignedOff sheet
    e.range.setBackground('#ffff00'); // turn the edited cell yellow to show it's been changed
} return
}return
}

Answer 1

onEdit(e) performance is dominated by the number of API calls it makes. The code in the question is using many SpreadsheetApp calls which make it run slowly:

• 6 API calls every time, regardless of which sheet the edit took place in
• 8 API calls when edit took place in 'VIEW'
• 15 API calls when edit took place in A1:C2 in 'VIEW'
• 17 API calls when data actually gets appended and edited cell marked in yellow

You can optimize the code to use considerably fewer API calls:

• 0 API calls when edit took place in A1:C2
• 2 API calls when edit took place outside A1:C2 in 'VIEW'
• 7 API calls when data actually gets appended and edited cell marked in yellow

...like this:

function onEdit(e) {
  let sheet;
  if (e.range.rowStart < 4
    || e.range.columnStart < 3
    || (sheet = e.range.getSheet()).getName() !== 'VIEW') {
    return;
  }
  const data = sheet.getDataRange().getValues();
  const header = data[1];
  const row = data[e.range.rowStart - 1];
  const values = [row[0], header[9], header[e.range.columnStart - 1], e.value, new Date()];
  e.source.getSheetByName('SignedOff').appendRow(values);
  e.range.setBackground('yellow');
}

With these changes, the onEdit(e) may run fast enough to keep pace with edits.

See these onEdit(e) optimization tips.

How can I "bank" the changes, and write them to the second sheet once all the changes are made? (eg by clicking a "save" button)

Insert a helper sheet and use the filter(isna(match())) pattern to get rows that appear in the data but not in the log. Then use a normal function instead of a simple trigger to append the values in the new sheet to the log. See appendRowsToArchiveSheet for sample code.

When a script runs slowly, the culprit is often found in the spreadsheet rather than in the code. See these spreadsheet optimization tips.