Google-sheets – Individual time stamps for individual sheets in a Google Spreadsheet containing multiple sheets

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

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)?

Best Answer

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()));
  }
}

Related Solutions

Google-sheets – onEdit only updates top row after pasting (editing) multiple rows

onEdit can be written to take an event parameter that among other things contains the range that was edited. Changing the function declaration to take a parameter 'event' and changing r to be event.range instead of the activecell gets the exact behavior I believe you want.

function onEdit(event) {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Master Sheet"" ) { //checks that we're on the correct sheet
    var r = event.range;
    if( r.getColumn() == 10 ) { //checks the column
    if( r.getValue() === '' ) {//don't update the timestamp if the cell is edited to a blank vaue
    }
     else
     {
      var nextCell = r.offset(0, 1);
      if( nextCell.getValue() === '' ) {// only update the timestamp if it has not already been updated
     //Format the current date into datetime format (adjust to display how you want)
      var dateTime = Utilities.formatDate(new Date(), "EST-8", "MM-dd-yyyy");
      // Set the cell
      nextCell.setValue(dateTime);}
    }
  }
}
}

Google-sheets – Use the order of sheets in which they appear in a Google spreadsheet

This is possible with a custom function or a script. Here's a custom function =sheets() which returns a column of sheet names, ordered as they are currently in the spreadsheet. You can use the output of this function as an input elsewhere, e.g., in =indirect, thus forming the master sheet according to the order of the other sheets.

function sheets() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var names = [];
  for (var i=0; i<sheets.length; i++) {
    names.push(sheets[i].getName());
  } 
  return names;
}

Note that the function is not automatically recalculated as the sheets are rearranged. If this is a problem, consider a script that is invoked either from a menu, or onOpen, or onEdit, etc. It could look like this:

function sheets(time) {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var names = [];
  for (var i=0; i<sheets.length; i++) {
    if (sheets[i].getName() !== 'master') {
      names.push([sheets[i].getName()]);
    }
  } 
  var master = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('master');
  master.getRange(1, 1, names.length, 1).setValues(names);
}

Here 'master' is the name of the master sheet; note that this name is omitted from the list of sheet names. The list is placed in the first column of master sheet.

Best Answer

Related Solutions

Google-sheets – onEdit only updates top row after pasting (editing) multiple rows

Google-sheets – Use the order of sheets in which they appear in a Google spreadsheet

Related Topic