CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Christina
Christina

Reputation: 1

Google Sheet triggers is running slow, how to fix it?

My second triggers (OnEdit) seem to run a little slow compare to the first trigger. Is there a way to make sure the (OnEdit) triggers run more effienctly?

    function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.alert(
    'V2025 Strategic Plan for Advancing DEI',
    'Please use CTRL+F and type your last name to locate your task in the SUMMARY tab.',
     ui.ButtonSet.OK);
}

function onEdit () {

  var sheet = SpreadsheetApp.getActiveSheet();
  var editRange = sheet.getActiveRange();
  var editRow = editRange.getRow();
  var editCol = editRange.getColumn();
  var range = sheet.getRange("C2:C");
  var rangeRowStart = range.getRow();
  var rangeRowEnd = rangeRowStart + range.getHeight()-1;
  var rangeColStart = range.getColumn();
  var rangeColEnd = rangeColStart + range.getWidth()-1;
  if (editRow >= rangeRowStart && editRow <= rangeRowEnd && editCol >= rangeColStart && editCol <= rangeColEnd) {

    showAlert();
  }

}

function showAlert() {
  var ui = SpreadsheetApp.getUi();

  var result = ui.alert(
     'Instruction',
     'Please make sure to input the following information: What is the current progress of your KAP? Please explain the steps, numbers or scores used to measure your progress or completion to date?',
      ui.ButtonSet.OK);

}

Thank you.

Upvotes: 0

Views: 1115

Answers (1)

doubleunary
doubleunary

Reputation: 18794

Your onEdit() simple trigger is running slowly because it uses nine API calls before it decides whether to show an alert.

You can make it run much more efficiently by using the event object to let you make the same decision with no API calls, like this:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  const toWatch = {
    range: { // C2:C
      rowStart: 2,
      columnStart: 3,
      rowEnd: +Infinity,
      columnEnd: 3,
    }
  };
  if (!e) {
    throw new Error('Please do not run the onEdit(e) function in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet.');
  }
  if (e.range.rowStart > toWatch.range.rowEnd
    || e.range.rowEnd < toWatch.range.rowStart
    || e.range.columnStart > toWatch.range.columnEnd
    || e.range.columnEnd < toWatch.range.columnStart) {
    return;
  }
  showAlert();
}

The code above is purposefully verbose to illustrate some important concepts. When you run it, this is the gist of what gets done:

function onEdit(e) {
  if (e.range.rowEnd < 2
    || e.range.columnStart > 3
    || e.range.columnEnd < 3) {
    return;
  }
  showAlert();
}

Apps Script performance is vastly dominated by the number of API calls, so the verbose version and the brief version will have the same runtime performance. The benefit of the verbose version is that it makes it easy to add things like treating different sheets in a different way.

In a "real" application, I would probably use the getRangeIntersection_ function, like this:

function onEdit(e) {
  const columnToWatch = e.source.getRange('Sheet1!C2:C');
  const editedCellsInColumnToWatch = getRangeIntersection_(e.range, columnToWatch);
  if (!editedCellsInColumnToWatch) {
    return;
  }
  const newValues = editedCellsInColumnToWatch.range.getValues();
  // ... do something with newValues
}

Upvotes: 1

Related Questions