0

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?

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

  • 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 Jun 19 at 15:18
  • @Fred Yes, that could work. – Rubén Jun 19 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.