3

I am using the New Google Sheets, and I have created a "Master Document" that contains a multitude of sheets, with each expressing different kinds of data. I want a "Last modified" time stamp to appear on a designated cell on each sheet, but I do not want them to all automatically update together when there is a change in a single sheet. I want each of the time stamp entries to function independently from each other, and update only when their respective sheet is modified. The end result I am looking for is being able to see when each of the sheets of this "Master Document" was last modified.

The =NOW() function has issues with its output being synchronized across all sheets, which is what I do not want.

I have tried using multiple onEdit() script commands, with each referring to an individual sheet, but only one of the sheets ends up displaying time stamp values. My recurring code is replicated below, shortened only to include 2 sheets:

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  //check if we're on the correct sheet
  if( s.getName() == "General Meeting Attendance Roster" ) { 
    s.getRange('S6').setValue("Roster last updated: " + (new Date()));
  };
}

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  //check if we're on the correct sheet
  if( s.getName() == "Master Chapter Roster" ) {
    s.getRange('K1').setValue("Chapter Roster last updated: " + (new Date()));
  };
}

The only values that are different are the sheet names and the cell range. Only the second onEdit() function seems to have an effect, as if the second onEdit() trumped the first. The time stamp does not appear for the sheet listed first in the code.

What can I do so that I could have time stamps on multiple sheets that each function independently? Am I at least on the right track with the code that I got? Or, is it impossible to have multiple onEdit() triggers, even though each one would function in their own sheets (i.e. one onEdit() per sheet)?

1

Don't use several onEdit-functions. Places place both IF-statements in the same tag.

Also, you shouldn't have semicolons after the IF-statements brackets.

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "General Meeting Attendance Roster" ) { //checks that we're on the correct sheet
       s.getRange('S6').setValue("Roster last updated: " + (new Date()));
  }

 if( s.getName() == "Master Chapter Roster" ) { //checks that we're on the correct sheet
       s.getRange('K1').setValue("Chapter Roster last updated: " + (new Date()));
  }
}
1

If you were to place the timestamps in the same cell for each sheet and standardize the text you could use a much simpler function that wouldn't need to be updated if you added a sheet.

function onEdit(e){
  e.range.getSheet().getRange("a1")
    .setValue(e.range.getSheet().getName()+"last updated: " + (new Date()));
}

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.