Google Aps Script - Trigger function when Column is "START"

The Denster 0
Viewed 39

I need function to simply trigger another function if a specific Google Sheet column's value (or values) is "START".

Something like this: If column AUTO!N2:N is "START", then trigger the function letsStart()" If column AUTO!N2:N is not "START", then do nothing.

Simple as that :)

Is this possible?

The letsStart() function already ends with:

  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("AUTO")

  .getRange('N2:N')
  .createTextFinder('START')
  .replaceAllWith('STOP')

...and working fine.

This is to avoid unintended looping.

google-sheets google-apps-script javascript
1 Answers

I believe your goal is as follows.

  • You want to run a function of letsStart() when the cells AUTO!N2:N includes a value of "START".

In this case, how about the following 2 patterns?

Pattern 1:

In this pattern, when a cell in AUTO!N2:N is edited and the cell value is "START", letsStart() is run.

function onEdit(e) {
  const { range, source } = e;
  const sheet = range.getSheet();
  const value = range.getValue();
  if (sheet.getSheetName() != "AUTO" || range.columnStart != 14 || range.rowStart == 1 || value != "START") return;
  letsStart();
  // or range.setValue("STOP");
}
  • In this sample, a simple trigger of OnEdit is used. So, when you use this script, please edit a cell of AUTO!N2:N. By this, the script is run. When you put "START" to AUTO!N2:N, letsStart() is run.
    • Please be careful about this.
  • In this case, I thought that range.setValue("STOP"); might be able to be also used instead of letsStart();.

Pattern 2:

In this pattern, when the cells AUTO!N2:N includes a value of "START", letsStart() is run.

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("AUTO");
  const lastRow = sheet.getLastRow();
  if (lastRow == 0) return;
  const textFinder = sheet.getRange("N2:N" + lastRow).createTextFinder("START").matchEntireCell(true);

  if (!textFinder.findNext()) return;
  letsStart();
  // or textFinder.replaceAllWith("STOP");
}

  • When this script is run, when the cells AUTO!N2:N includes a value of "START", letsStart() is run. Althought I'm not sure your whole script of letsStart(), when I saw your script of letsStart(), I thoug that in this case, I thought that letsStart(); might be able to be replaced with textFinder.replaceAllWith("STOP");.

  • For example, when myFunction is modified to onEdit, this script can be also used with the simple trigger of OnEdit.

References:

Tanaike0
Related