如何在单个新选项卡中为多个选项卡创建时间戳。 Google App脚本

Question

嗨，我有一个带有所有选项卡名称的选项卡，我想在该表中的行中包括最后更新的日期/时间。 例如：

表格名称	在表1 16/05/2022 11:20更新的
表1	16/05/2022 14:20
表2	15/05/2022 11:20

吗？

Answer 1

如果您需要保护时间戳板免受意外修改，则简单的OneDit（e）触发器将无法使用。取而代之的是，在可安装触发器的情况下，运行时间戳函数。

/** @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);
  }
}

Answer 2

可以这样做：

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

脚本每次更改电子表格的其他表格时都会添加或修改“时间戳”表上的时间戳。