Trigger a script when a formula changes a cell value

Question

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.

Does your script run via an onEdit trigger? If so, is it checking to see which cell is edited? If it is, then you may need to check on the cell that is manually changed and causing the formula to change values. Can you include the code in your post so it can be looked at as well? — Karl_S

Max Makhrov Max Makhrov · Accepted Answer · 2017-03-16T07:12:14

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

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

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

Trigger a script when a formula changes a cell value

1 Answers