Trigger a script when a formula changes a cell val

问题:

I'm using a Google script then sends out an email when a certain column in a Google sheet is changed. The information in the cell is either inputted manually, or completes using a formula based on information in other cells.

The script works fine when information is manually entered, but not when the formula runs. I've read up on it and realise that a formula calculation doesn't count as an edit, so how do I get the script to run?

It's currently set up to trigger from the spreadsheet when there's an edit.

Below is the part of my script that covers the column/cell in question.

function sendEmail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var row = sheet.getActiveRange().getRow();
  var cellvalue = ss.getActiveCell().getValue().toString();

   if(sheet.getActiveRange().getColumn() == 12)

There's a lot more included in the script so I haven't copied everything onto here. Many thanks in advance.

回答1:

There is no trigger that can run when a formula changes.

Try figure out, what conditions is your formula depends on:

1. if it is another cell, entered manually, then use those cell to trigger it's changes with onEdit
2. if the formula imports data from external source, use random or time functions, you'd better use onTime trigger.
3. if the formula uses importrange then go to the range you import and see the original range, return to step 1 → 2...