1

I currently have a column, e.g. ColB, containing SUMIF functions with its data range from other sheets and a column, e.g. ColC, with dates that I want to automatically update to the last date ColB's value were changed. Here's a picture to illustrate what I mean:

From searching around, I found the following Google Apps Scripts but they only seem to work if the change to ColB's cells is a direct edit as opposed to its value changing from the SUMIF function recalculating new data:

Example Script 1: 

function onEdit(e) {
  if ([2, 5, 8, 11].indexOf(e.range.columnStart) != -1) {
    e.range.offset(0, 1).setValue(new Date());
  }
}

Example Script 2: 

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
   var r = s.getActiveCell();
   if( r.getColumn() == 13 ) { //checks the column
     var nextCell = r.offset(0, 1);
     if( nextCell.getValue() === '' ) //is empty?
       var time = new Date();
       time = Utilities.formatDate(time, "GMT", "HH:mm:ss");
       nextCell.setValue(time);
   };
 };
}

Is there a Google Apps script that can execute what I want?

Improve this question

1 Answer 1

Reset to default
0

Google Apps Script hasn't any trigger that is triggered by formula results changes, so you have to rethink how to get the result what you are looking for from a broader perspective. Maybe instead of looking for a formula change you should think about what makes the formula to be recalculated and which of those recalculations are relevant.

By the way, formulas are recalculated every time that a spreadsheet is opened. Built-in functions are recalculted when a change is made to the spreadsheet, some volatile functions like NOW could be recalculated on change and every certain time. Import functions like IMPORTRANGE officially are recalculated every certain number of minutes (30 minutes for IMPORTRANGE) but the practice tell us that under certain conditions they are recalculated as soon as the source range change. Custom functions are recalculated on spreadsheet opening and are recalculated when one of their arguments changes but they can't use some volatile built-in functions, like NOW, as arguments.

A some sort of "general solution" could be to use a time-driven trigger to poll the spreadsheet or a specific cell for changes and if there is any then add the timestamp but this should be done (very?) carefully to avoid to exceed the Google Apps Script quotas.

References

Related

Improve this answer
2
  • Would the following work? Instead of having the onEdit apply to Sheet1 in my example, it will instead check Sheet2 for changes since that is the sheet where direct, manual edits occur and, if so, the script will then set the updated dates in Sheet1?
    – Fred
    Commented Jun 19, 2019 at 15:18
  • @Fred Yes, that could work.
    – Rubén - Volunteer Moderator - ♦
    Commented Jun 19, 2019 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.