Hi I have a tab with all the tab names, I want to include the date/time the row in that sheet was last updated. E.g:

Is it possible to timestamp multiple sheets into one separate sheet?

CodePudding user response：

If you need to protect the timestamp sheet against unintended modifications, a simple onEdit(e) trigger will not work. Instead, run the timestamp function on an installable trigger, like this:

/** @OnlyCurrentDoc */
'use strict';


/**
* Inserts a table in timestamp.sheet that lists the latest modification
* date recorded for each sheet in the spreadsheet.
*
* Timestamps are only recorded when the spreadsheet is hand modified.
*
* To install a trigger to run this function, click the clock icon
* in the left in Extensions > Apps Script, then click Add trigger > 
* sheetModicationTimestamps > Head > From spreadsheet > on edit > Save.
*
* @param {Object} e The 'on edit' event object.
*/
function sheetModicationTimestamps(e) {
  // version 1.0, written by --Hyde, 16 May 2022
  //  - see https://stackoverflow.com/q/72259914/13045193
  'use strict';
  if (!e) {
    throw new Error('Install a trigger to run this function.');
  }
  try {
    const timestamp = {
      sheetName: 'Sheet modification dates',
      rangeA1: 'C1:D',
      format: 'yyyy-MM-dd HH:mm',
    };
    const sheet = e.range.getSheet();
    const sheetName = sheet.getName();
    if (sheetName === timestamp.sheetName) {
      return;
    }
    const now = new Date();
    timestamp.sheet = e.source.getSheetByName(timestamp.sheetName);
    if (!timestamp.sheet) {
      timestamp.sheet = e.source.insertSheet(timestamp.sheetName);
      timestamp.sheet.getRange(timestamp.rangeA1)
        .offset(0, 0, 1, 2)
        .setValues([['Sheet name', 'Updated on']]);
    }
    timestamp.range = timestamp.sheet.getRange(timestamp.rangeA1);
    timestamp.values = timestamp.range.getValues().filter(row => String(row[0]));
    const rowIndex = timestamp.values.map(row => row[0]).indexOf(sheetName);
    if (rowIndex === -1) {
      timestamp.values = timestamp.values.concat([[sheetName, now]]);
    } else {
      timestamp.values[rowIndex][1] = now;
    }
    timestamp.range
      .clearContent()
      .offset(0, 0, timestamp.values.length, 2)
      .setValues(timestamp.values)
      .setNumberFormat(timestamp.format);
  } catch (error) {
    e.source.toast(error.message, 'sheetModicationTimestamps', 30);
  }
}

CodePudding user response：

It can be done this way:

function onEdit(e) {
  var ss = e.source;
  var sheet = e.range.getSheet();
  var sheet_name = sheet.getName();
  if (sheet_name == 'Timestamps') return; // do nothing if it's 'Timestamps' sheet

  var date = new Date();

  var timestamps_sheet = ss.getSheetByName('Timestamps');
  var sheets_names = timestamps_sheet.getRange('A:A').getValues().flat();  
  var row = sheets_names.indexOf(sheet_name)   1;

  if (row > 0) {
    timestamps_sheet.getRange(row,2).setValue(date);
    ss.toast('Row '   row   ' was updated');
  } else {
    timestamps_sheet.appendRow([sheet_name, date]);
    ss.toast('A new row was added');
  }
}

The script adds or modifies the timestamps on the sheet 'Timestamps' every time you make changes on other sheets of the spreadsheet.

Here is my spreadsheet.