Google-sheets – Put the timestamp of last edit on master sheet, next to edited sheet’s name

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

I'm trying to come up with a script that will print a timestamp of the last edit made to a specific tab within a google sheet. Imagine you have 17 retail stores, each one having its own tab with a set of data. Each time someone makes an edit to that sheet, I want it to print the date of most recent edit on a master tab that has a row with each store name.

enter image description here

Here's what I have, but it's not quite doing what I want it to do:

function onEdit(e) {
var sheetName = "Ponce City Market";
var d = new Date()
e.source.getActiveSheet()
.getRange('C5')
.setValue('Last Modified:\n' +d
    .toLocaleDateString() + '\n' + d.getHours() + ':' + d.getMinutes())
}

The current issue is that it will print the timestamp any time an edit is made anywhere on the file. It is not localized within the Ponce City tab (for example). In the subsequent lines, the tab name and print cell (C5 in this case) will change depending on which tab its pulling from. This way, the last edit made to the store's tab will appear next to the stores name on the master sheet.

Best Answer

I think this is the logic you were trying to express. 

function onEdit(e) {
  var d = new Date();
  var editedSheetName = e.source.getActiveSheet().getSheetName();
  var masterSheet = e.source.getSheetByName("master");
  var names = masterSheet.getRange("A:A").getValues().map(function(row) {
    return row[0];
  });
  var matchingRow = names.indexOf(editedSheetName);
  if (matchingRow != -1) {
    masterSheet.getRange(matchingRow + 1, 2)
               .setValue('Last Modified:\n' + d.toLocaleDateString() + '\n' + d.getHours() + ':' + d.getMinutes());
  }
}

Key points:

  • The script gets the sheet names from column A of the master sheet (I assume it's named "master"). It needs to be flattened with map(...) for indexOf to work on it, so [[a], [b], [c]] becomes [a, b, c].

  • The matching row is identified with indexOf(editedSheetName). One has to remember that Google Sheet row index begins with 1, while JavaScript array index begins with 0. This is why there is +1 in .getRange(matchingRow + 1, 2). The 2 here is column B, where the timestamp should appear.

Related Topic