我正在Google Shays上工作,以定期访问和编辑文档。我想要的是在该列中使用标有"最后编辑日期"的列,以反映每个单元一行的最新编辑日期。例如,如果我在单元格C3中编辑信息,则我希望"最后一个编辑"第3列中的单元格在"最后一个编辑日期"中反映我进行了编辑的日期。我发现了一些由列运行的,但它们仅适用于单个列。我需要它连续覆盖"上次编辑日期"前面的所有单元格。
如果需要的话,将此脚本添加到您的工作表中,并更改格式和时区("GMT+1", "dd.MM.yyyy"
):
function onEdit(event)
{
var sheet = event.source.getActiveSheet();
// note: actRng = the cell being updated
var actRng = event.source.getActiveRange();
var index = actRng.getRowIndex();
var cindex = actRng.getColumnIndex();
var dateCol = sheet.getLastColumn();
var lastCell = sheet.getRange(index,dateCol);
var date = Utilities.formatDate(new Date(), "GMT+1", "dd.MM.yyyy");
lastCell.setValue(date);
}
此代码将使用用户的电子邮件地址更新c:c,如果在数据输入选项卡上编辑了a:b,则使用同一行的当前时间D:D。您需要设置一个onChange()触发器才能使其正常工作。
function userUpdate() {
/*
Changed the updated by/on to make it faster
Put in handling to make sure that if they delete a cell, that it only clears out the submitted by/on if ALL the values in A:B are blank
*/
// Main sheet details
var s = SpreadsheetApp.getActiveSheet();
var spreadsheetTimeZone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
var lastUpdatedString = Utilities.formatDate(new Date(), spreadsheetTimeZone, "MM/dd/yyyy' 'HH:mm:ss");
var sessionEmail = Session.getActiveUser().getEmail();
// Check Sheet
if (s.getName() == "Data Entry") { //checks that we're on the correct sheet
var r = s.getActiveCell();
var row = r.getRowIndex();
var column = r.getColumn();
var activeRange = s.getRange("A" + row + ":B" + row);
//var activeRangeValues =
var activeRangeValuesLength = activeRange.getValues().toString().length;
if (row > 1) {
// Declare variables
var cellValue = r.getValue().toString();
// If the cellValue IS blank
if (activeRangeValuesLength < 5) {
sessionEmail = "";
lastUpdatedString = "";
}
// Update Submitted By / Submitted On
if (column < 5) {
var lastUpdatedBy = s.getRange("C" + row)
lastUpdatedBy.setValue(sessionEmail); // update column C
var lastUpdated = s.getRange("D" + row)
lastUpdated.setValue(lastUpdatedString); // update column D
}
}
}