一次在多行上的 OnEdit 時間戳 (OnEdit timestamp on several rows at one time)

問題描述

一次在多行上的 OnEdit 時間戳 (OnEdit timestamp on several rows at one time)

我已經設置了一個有效的時間戳 onEdit 代碼。我使用了 Youtube 視頻中的代碼並根據我的工作表進行了編輯，可以肯定地說我不是腳本編寫專家。然而，代碼正在處理的工作表有 1000 行數據，我們可以一次更新 100 行。我需要時間戳腳本一次更新幾行，而不僅僅是編輯的第一行。

我的腳本如下。

function addTimestamp(e){
  //variables
  var startRow = 2;
  var targetColumn = 33;
  var ws = "Database";

  //getmodofied row and column

  var row = e.range.getRow();
  var col = e.range.getColumn();

  if(col === targetColumn && row >= startRow && e.source.getActiveSheet().getName() === ws){

    var currentDate = new Date();
    e.source.getActiveSheet().getRange(row,50).setValue(currentDate);
    if(e.source.getActiveSheet().getRange(row,49).getValue() == ""){
      e.source.getActiveSheet().getRange(row,49).setValue(currentDate);
    } // END IF check if date created exists
  } // END IF check column, row, worksheet
} //

參考解法

方法 1:

Things that you should have in mind about the script in the question

• e holds an event object
• e.range holds the Range object that was edited
• e.source.getActiveSheet().getRange(row,50), e.source.getActiveSheet().getRange(row,49) return a single cell Range

Quick and dirty fix

Replace

e.source.getActiveSheet().getRange(row,50)

by

e.source.getActiveSheet().getRange(row,50,e.range.getRows(),1)

The above fix will return a Range having the same number of rows than the edited range but only one column

Replace

if(e.source.getActiveSheet().getRange(row,49).getValue() == ""){
  e.source.getActiveSheet().getRange(row,49).setValue(currentDate);
}

by

 var values = e.source.getActiveSheet().getRange(row,49,e.range.getRows(),1).getValues(); 
 var newValues == values.map(row => {
   return row[0] === '' ? currentDate : row[0];
 }
e.source.getActiveSheet().getRange(row,49,e.range.getRows(),1)e.source.getActiveSheet().getRange(row,49,e.range.getRows(),1).setValues(newValues);

values will hold the values of column 49, starting and ending on the same rows than the edited range but having only 1 column. Array.prototype.map will iterate over all the values. Instead of using a if statement it is using a conditional (ternary) operator, and instead of setValue, it's using setValues to pass all the values at once.

方法 2:

Try this:

function addTimestamp(e){
  const sh=e.range.getSheet();
  if(e.range.columnStart == 33 && e.range.rowStart > 32 && sh.getName() == "Database"){
    let cd=new Date();
    let vs=sh.getRange(e.range.rowStart,49,e.range.rowEnd‑e.range.rowStart+1,2).getValues();
    vs.forEach((r,i)=>{r[1]=cd;if(r[0]==''){r[0]=cd;}});
    sh.getRange(e.range.rowStart,49,vs.length,2).setValues(vs);
  }
} 

I tested this on my account. It works

(by Joel Whitmore、Rubén、Cooper)

參考文件

1. OnEdit timestamp on several rows at one time (CC BY‑SA 2.5/3.0/4.0)