Google-sheets – How to trigger a script in Google sheets when a value changes

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I try to fire a script when a value in a certain cell or range of cells changes.
(it's not changed by the user, so I can't use onEdit)

I've tried to use onChange, but this doesn't fire my script at all

function onChange() {
//  verwijder verwerkte 'klaar' regels

  var actieSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Actie");
  var asRange = actieSheet.getRange("F3:F25") 
  var asValues = asRange.getValues();
    for (var i = 0; i < 22; i++) {
         x1='false';
         asValues[i][0]= false;
      }
      actieSheet.getRange("F3:F25" ) .setValue (asValues);
}

Is there a way to trigger a script if a Cell changes or even if there is any change in a sheet, it will do for me?

Best Answer

onChange isn't a reserved word for a simple trigger on Google Apps Script but there is an "on change" installable trigger that could execute a specified function regardless of it's name. Unfortunately for this case, this installable trigger, the same way that onEdit works, only is triggered by changes made by the user.

If the update is made by a script, make that script run the desired function or "merge" them. If that is not possible, the "workaround" is to use a time driven trigger or the HTML Service and setTimeout to poll the spreadsheet for changes.

The maximum frequency that a time driven trigger could be triggered is every minute (30 minutes for add-ons)

The maximum frecuency for setTimout is every 4ms or every 10ms depending on the web browser.

Be aware that there are quotas and limits that could be or not relevant for your case.

References

Run a Script When Tab Programmatically added to a Google Spreadsheet

Related Solutions

Google Apps Script – Passing Variables to Deployed Web Application

You can modify the function params to accept the argument it's given, and use it in forming the string that it returns. Like so:

function params(row) {                                   // added row here
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssid = ss.getId();
  var sheet = ss.getActiveSheet();
  var sheetName = sheet.getName();
  return '?ssid='+ssid+'&name='+sheetName+'&row='+row;   // added row here
}

Now when you use ...exec" & params(1)) the value of 1 will be correctly appended to the URL used to invoke the web app: the URL will have &row=1 and consequently, the doGet function of the web app will receive this value as e.parameters.row.

However it's easier to just append 1 yourself: in the hyperlink function, put

...exec" & params() & "&row=1" )

...exec" & params() & "&row=" & row() )

There is no need to run a number through a custom function when you can just append it to the URL in the spreadsheet. The reason the custom function params is needed is to pass the values that spreadsheet functions cannot access: spreadsheet id and sheet name.

Google-sheets – Trigger function when IMPORTRANGE() is updated

NOTE: Answer to revision 4

Google Apps Script on edit events are triggered by user made changes only, they aren't triggered on automatic changes like does that occur to automatic recalculation of import functions and functions like NOW. By the other hand, in order to make a change on an external sheet, you should use an installable trigger.

For further details see Installable triggers and search about this both on this site and on Stack Overflow.

NOTE: Answer to revision 1

Besides the use of the run command from the Google Apps Script editor, to run a script on demand you could use a custom menu. There are several threads that include examples of uses of custom menus on this site. Here are few examples:

References

Best Answer

Related Solutions

Google Apps Script – Passing Variables to Deployed Web Application

Google-sheets – Trigger function when IMPORTRANGE() is updated

Related Topic