我有这两个脚本如果结构相同,唯一不同的是:
创建日期:仅当修改了第1列(A)时才插入日期
上次更新:仅当有人修改第5列或第6列时才插入日期。
我认为添加一些其他内容或null很简单,但我没有专业知识……如果有人能帮忙,我将不胜感激。
脚本1:
function CreationDate(event){
//Script Creation Date Timming
var actSht = event.source.getActiveSheet();
var activeCell = actSht.getActiveCell(); //Detec the ActiveCell
var column = activeCell.getColumn(); // Detect the Column of the ActiveCell
var colNums = [1]; //Coulmns, whose edit is considered
if(colNums.indexOf(column) == -1) return; //If column other than considered then return
var row = activeCell.getRow(); //Detect the ActiveRow
if(row < 2) return; //If header row then return
var date = Utilities.formatDate(new Date(), "GMT-3", "dd/MM/yyyy HH:mm"); // Function Date + Format
var r1 = event.source.getActiveRange().getRow();
//Note: Insert the Date in Create Date Column
actSht.getRange(r1, 7).setValue(date)
}
脚本2:
function LastUpdate(e){
//Script LastUpdate Timming
var actSht = e.source.getActiveSheet();
var activeCell = actSht.getActiveCell(); //Detec the ActiveCell
var column = activeCell.getColumn(); // Detect the Column of the ActiveCell
var colNums = [5,6]; //Coulmns, whose edit is considered
if(colNums.indexOf(column) == -1) return; //If column other than considered then return
var row = activeCell.getRow(); //Detect the ActiveRow
if(row < 2) return; //If header row then return
var date = Utilities.formatDate(new Date(), "GMT-3", "dd/MM/yyyy HH:mm"); // Function Date + Format
var r2 = e.source.getActiveRange().getRow();
//Note: Insert the Date in the Column 8
actSht.getRange(r2, 8).setValue(date);
}
这两个函数完全相同,但有两个例外:
- 第一个用一个元素初始化数组colNums,第二个用两个元素初始化(第九行)
- 它们将值输出到不同的列(最后一行,7对8)
一个单一的功能可以很容易地同时做到这两点:
function setTimeStamp(e){
// no magic numbers!
var createdColNums = [1]; //Columns whose edit is considered a new row
var updatedColNums = [5,6]; //Columns whose edit is considered an updated row
var createdStampColumn = 7;
var updatedStampColumn = 8;
var headerRow = 1;
var actSht = e.source.getActiveSheet();
var activeCell = actSht.getActiveCell(); //Detect the ActiveCell
var row = activeCell.getRow(); //Detect the active row
if(row == headerRow)
return; //If header row then return
var column = activeCell.getColumn(); // Detect the active column
var date = Utilities.formatDate(new Date(), "GMT-3", "dd/MM/yyyy HH:mm");
var columnToStamp;
if(createdColNums.indexOf(column) > -1)
columnToStamp = createdStampColumn;
else if(updatedColNums.indexOf(column) > -1)
columnToStamp = updatedStampColumn;
actSht.getRange(row, columnToStamp).setValue(date);
}
注意:如果将函数命名为onEdit(),并将其放在电子表格的脚本中,它将自动作为触发器调用,并且不需要授权。在这种情况下,使用
SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
代替
e.source.getActiveSheet()
话虽如此,imho一个更好的方法是检查创建的时间戳所在的值,如果存在值,则设置更新的时间戳。然后,任何时候编辑任何列都会得到正确的输出:
function onEdit() {
// no magic numbers!
var createdStampColumn = 7;
var updatedStampColumn = 8;
var headerRow = 1;
var actSht = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var activeCell = actSht.getActiveCell(); //Detect the ActiveCell
var row = activeCell.getRow(); //Detect the active row
if(row == headerRow)
return; //If header row then return
var column = activeCell.getColumn(); // Detect the active column
var date = Utilities.formatDate(new Date(), "GMT-3", "dd/MM/yyyy HH:mm");
var columnToStamp;
if (actSht.getRange(row, createdStampColumn).getValue() == "")
columnToStamp = createdStampColumn;
else
columnToStamp = updatedStampColumn;
actSht.getRange(row, columnToStamp).setValue(date);
}
在不真正关注脚本实际在做什么的情况下,这里是两个脚本的"直译/组合"。请注意,我颠倒了条件,以避免从脚本中"返回",从而能够转到第二个条件。
function combined_function(event){
var actSht = event.source.getActiveSheet();
var activeCell = actSht.getActiveCell(); //Detec the ActiveCell
var column = activeCell.getColumn(); // Detect the Column of the ActiveCell
var colNums = [1]; //Coulmns, whose edit is considered
if(colNums.indexOf(column) > -1) { //If column is the one we want then execute
var row = activeCell.getRow(); //Detect the ActiveRow
if(row >= 2){
var date = Utilities.formatDate(new Date(), "GMT-3", "dd/MM/yyyy HH:mm"); // Function Date + Format
var r1 = event.source.getActiveRange().getRow();
//Note: Insert the Date in Create Date Column
actSht.getRange(r1, 7).setValue(date)
}
};// end of first original function
colNums = [5,6]; //Coulmns, whose edit is considered
if(colNums.indexOf(column) > -1){ // same comment, condition inverted
var row = activeCell.getRow(); //Detect the ActiveRow
if(row >= 2){
var date = Utilities.formatDate(new Date(), "GMT-3", "dd/MM/yyyy HH:mm"); // Function Date + Format
var r2 = e.source.getActiveRange().getRow();
//Note: Insert the Date in the Column 8
actSht.getRange(r2, 8).setValue(date);
}
}
}