2

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.

P.Seymour
  • 53
  • 3
  • 9
  • 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 Mar 15 '17 at 18:27

1 Answers1

3

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...
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
  • The formula I'm using is =IF(AND(G28<=999,G28>0),"Approved",""). If the value of G is more than 1000, then someone manually enters a comment into column L. Doing this does allow the script to run, but obviously not if the formula completes the cell. – P.Seymour Mar 17 '17 at 11:17
  • You're right, trigger can run only when you change G28. The formula wont affect the trigger. That was my answer. – Max Makhrov Mar 17 '17 at 12:02
  • Thanks for the detailed answer, the answer is helpful, but it's unfortunate that a change in a computed cell cannot trigger a script :( Now I will have to reconstruct the whole script and accommodate the formula in a script instead! – Raj Pawan Gumdal Oct 06 '21 at 12:50