Speeding up an onEdit() function in Google Apps Script

Question

We're managing all changes from multiple sheets (one for each of 29 different locations) to be reflected in one main_db. The main_db is then hooked up to Data Studio for Analytics.

We have implemented a "updates_to_db" record sheet for the changes being made, and then running a triggered service every 10 mins that does the actual getting and setting of data in the main_db.

I faced issues with onEdit() functions writing data over each other when getLastRow() was returning the same value for onEdit()'s that were fired off in quick succession. I've implemented a PropertyKey to now hold this value. onEdit() first updates this value, and then continues. This way all subsequent onEdits() are correctly being written to the updated lastRow.

onOpen() function This sets the PropertyKey value for real last row in the updates records sheet accurately.

function onOpen() {
  
  // Welcome message
  var message = 'The current time is ' + new Date().toString();
  var title = 'Welcome to xxxxxx DASHBOARD';
  SpreadsheetApp.getActiveSpreadsheet().toast(message, title);

  var last_row = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(push_to_db_sheet).getLastRow();
  PropertiesService.getScriptProperties().setProperty('last_row_updates_sheet', last_row + 1);

}

onEdit() function

function onEdit(e){

  var activeSheet = e.source.getActiveSheet();
  var date_stamp = new Date();
  var tabs = ["Customer Requests","Indent Status","Tracking Acknowledgements","Actual Costs","Balance Payments","Payment Updates"];
  
  if(tabs.indexOf(activeSheet.getName()) !== -1){

    var row_to_edit = PropertiesService.getScriptProperties().getProperty('last_row_updates_sheet');
    row_to_edit= +row_to_edit; // converting string to int
    PropertiesService.getScriptProperties().setProperty('last_row_updates_sheet', row_to_edit + 1);

    var cell = e.range;
    var col = cell.getColumn();
    var changes_sheet = e.source.getSheetByName(push_to_db_sheet);
  //  var last_row = changes_sheet.getLastRow() + 1;
    
    changes_sheet.getRange(row_to_edit, 1).setValue(date_stamp);
    changes_sheet.getRange(row_to_edit, 2).setValue(activeSheet.getName());
    changes_sheet.getRange(row_to_edit, 3).setValue(activeSheet.getRange(cell.getRow(),3,1,1).getValue());
    changes_sheet.getRange(row_to_edit, 4).setValue(cell.getColumn())
    changes_sheet.getRange(row_to_edit, 5).setValue(e.value);
      
    cell.setNote('Last modified: ' + date_stamp); // helps the user know when the last edit was made
    cell.setBackground("#FBF7EC"); // helps the user visually see when the update has been captured. I can move this up to make it "seem" faster...
  } // IF tab name matches

}

Any ideas on how I can speed up onEdit()? It is currently taking between 4 - 13 seconds to run. Eventually, I will be calling email and SMS trigger functions based on specific cells which were edited. I'd like a pop-up with the email/sms to be shown, and then a confirmation button will trigger the email/sms.

I'm worried that with a long onEdit() time the user will be several columns ahead when the email/sms pop-up dialogue is displayed.

Is there any reason I shouldn't close this as a duplicate of https://stackoverflow.com/a/29800759/ or https://stackoverflow.com/a/57836700? — TheMaster, Sep 06 '20 at 14:27
@TheMaster thank you for the reference links. Once I am done with the entire function improvements, including email/sms dialogue triggers, I'll come back here and post my improvement methods that actually worked, with some performance results. — Tanveer Chandok, Sep 06 '20 at 14:37
I'm closing it. If you'd like to add generalised answers, You can add it to any of the duplicate questions linked above, So that there will be a single thread of data instead of split up data. Alternatively, If you'd like a specific answer, you can edit your existing answer. Also read up on DRY principle. Avoid repeating entire global calls: `PropertiesService.getScriptProperties()` and `activeSheet.getName()` .. preferably store them in a variable. — TheMaster, Sep 06 '20 at 15:08
Ok - got it! This was my first Q on the site, thanks for showing me the ropes! Will keep this in mind next time @TheMaster — Tanveer Chandok, Sep 06 '20 at 16:57

score 1 · Answer 1 · answered Sep 06 '20 at 14:51

I've updated this list of read/writes:

changes_sheet.getRange(row_to_edit, 1).setValue(date_stamp);
    changes_sheet.getRange(row_to_edit, 2).setValue(activeSheet.getName());
    changes_sheet.getRange(row_to_edit, 3).setValue(activeSheet.getRange(cell.getRow(),3,1,1).getValue());
    changes_sheet.getRange(row_to_edit, 4).setValue(cell.getColumn())
    changes_sheet.getRange(row_to_edit, 5).setValue(e.value);

To this:

    changes_sheet.getRange(row_to_edit, 1, 1, 5).setValues([[date_stamp , activeSheet.getName() , activeSheet.getRange(cell.getRow(),3,1,1).getValue() , cell.getColumn() , e.value]]);

Notes:

setValues() needs a 2D array. Make sure to wrap the data in the appropriate [] brackets to achieve this when setting data like I am.
The previous method had an average onEdit completion time of 7.6988 s
The updated method has an average onEdit completion time of 3.6741 s -> 52% decrease

Is this a reasonable execution time to expect for an onEdit() function?

Speeding up an onEdit() function in Google Apps Script

1 Answers1