时间戳记脚本不适用于粘贴/拖动单元格值 [英] Time Stamp Script not working on Pasting/Dragging Cell Values

问题描述

我有一个脚本，用于记录在特定单元格中进行输入的时间戳.它使用onEdit触发器.

问题陈述:

当我在一系列相邻单元格(在同一列)中拖动/复制粘贴数据时，只有第一个条目会产生时间戳输出.

代码:

  var s = SpreadsheetApp.getActiveSheet();var sName = s.getName();var r = s.getActiveCell();if(r.getColumn()== 8& sName =='Processing'){//在哪张纸上观看哪一列var row = r.getRow();var time = new Date();SpreadsheetApp.getActiveSheet().getRange('CU'+ row.toString()).setValue(time);//将时间戳记放在哪一列};}; 

解决方案

您正在使用onEdit在编辑字段时插入时间戳.但是，当通过复制/粘贴或拖动单元格/范围进行编辑时，时间戳仅应用于新目标范围中的第一个单元格.

发生这种情况的原因是您的脚本输出只能识别 activecell ，而不能识别其余的 activerange

var r = s.getActiveCell();var row = r.getRow();

有几种解决问题的方法.

ActiveRange :启用脚本以处理活动范围内的行数.

  function onEdit(){var s = SpreadsheetApp.getActiveSheet();var sName = s.getName();var r = s.getActiveCell();var row = r.getRow();var ar = s.getActiveRange();var arRows = ar.getNumRows()//Logger.log("DEBUG:活动范围=" + ar.getA1Notation()+，行数=" + ar.getNumRows());var time = new Date();if(r.getColumn()== 8& sName =='Processing'){//在哪张纸上观看哪一列//遍历行数对于(var i = 0; i< arRows; i ++){var rowstamp = row + i;SpreadsheetApp.getActiveSheet().getRange('CU'+ rowstamp.toString()).setValue(time);//将时间戳记放在哪一列}}} 

---

事件对象:启用脚本以利用OnEdit生成的事件对象.

在以下脚本中，编辑范围，列，工作表名称，开始和结束行号都是通过使用 onEdit可用的事件对象来获取/确定的.

  function onEdit(event){var s = SpreadsheetApp.getActiveSheet();//Logger.log(JSON.stringify(event));//调试var ecolumnStart = event.range.columnStart;var erowStart = event.range.rowStart;var erowEnd = event.range.rowEnd;var ecolumnEnd = event.range.columnEnd;//Logger.log("DEBUG:范围详细信息-列开始:" + ecolumnStart +，列结束:" + ecolumnEnd +，行开始:" + erowStart +，行结束:" + erowEnd)；//Logger.log("DEBUG:工作表为" + event.source.getName()+，范围=" + event.range.getA1Notation());var sName = event.range.getSheet().getName();//Logger.log("DEBUG:工作表名称为" + sName)var time = new Date();var numRows = event.range.rowEnd -event.range.rowStart + 1;if(event.range.columnStart == 8&& sName =='处理'){//在哪张纸上观看哪一列//循环行数对于(var i = 0; i< numRows; i ++){var row = event.range.rowStart + i;SpreadsheetApp.getActiveSheet().getRange('B'+ row.toString()).setValue(time);//将时间戳记放在哪一列}}} 

I have a script which records the time stamp on which an entry is made in a specific cell. It uses a onEdit trigger.

PROBLEM STATEMENT:

When I'm dragging/copy-pasting the data over a range of adjacent cells(in the same column), only the first entry is producing a time-stamp output.

CODE:

  var s = SpreadsheetApp.getActiveSheet();
  var sName = s.getName();
  var r = s.getActiveCell();
  if( r.getColumn() == 8 && sName == 'Processing') { //which column to watch on which sheet
    var row = r.getRow();
    var time = new Date();
    SpreadsheetApp.getActiveSheet().getRange('CU' + row.toString()).setValue(time); //which column to put timestamp in
  };
 };

解决方案

You are using onEdit to insert a timestamp when a field is edited. But when an edit is done by copy/paste or dragging a cell/range, the timestamp is applied only for the first cell in the new target range.

The reason that this is happening is that your script output recognises only the activecell and does not recognise the rest of the activerange

var r = s.getActiveCell(); var row = r.getRow();

There are several solutions to your problem.

ActiveRange: Enable the script to process the number of rows in the active range.

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  var sName = s.getName();

  var r = s.getActiveCell();
  var row = r.getRow();
  var ar = s.getActiveRange();
  var arRows = ar.getNumRows()
  // Logger.log("DEBUG: the active range = "+ar.getA1Notation()+", the number of rows = "+ar.getNumRows());
  var time = new Date();

  if( r.getColumn() == 8 && sName == 'Processing') { //which column to watch on which sheet
   // loop through the number of rows
    for (var i = 0;i<arRows;i++){
      var rowstamp = row+i;
      SpreadsheetApp.getActiveSheet().getRange('CU' + rowstamp.toString()).setValue(time); //which column to put timestamp in
    }
  }
 }

---

Event Objects: Enable the script to take advantage of the event Objects generated by OnEdit.

In the following script, the edited range, the column, sheet name, the starting and ending row numbers are all obtained/determined by using Event Objects available to onEdit.

function onEdit(event) {
  var s = SpreadsheetApp.getActiveSheet();

  // Logger.log(JSON.stringify(event)); //DEBUG

  var ecolumnStart = event.range.columnStart;
  var erowStart = event.range.rowStart;
  var erowEnd = event.range.rowEnd;
  var ecolumnEnd = event.range.columnEnd;
  // Logger.log("DEBUG: Range details - Column Start:"+ecolumnStart+", Column End:"+ecolumnEnd+", Row start:"+erowStart+", and Row End:"+erowEnd);
  // Logger.log("DEBUG: the sheet is "+event.source.getName()+", the range = "+event.range.getA1Notation());
  var sName = event.range.getSheet().getName();
  // Logger.log("DEBUG: the sheet name is "+sName)

  var time = new Date();
  var numRows = event.range.rowEnd -event.range.rowStart+1;

  if( event.range.columnStart == 8 && sName == 'Processing') { //which column to watch on which sheet
    // loop though the number of rows
    for (var i = 0;i<numRows;i++){
      var row = event.range.rowStart+i;
      SpreadsheetApp.getActiveSheet().getRange('B' + row.toString()).setValue(time); //which column to put timestamp in
    }
  }
}

这篇关于时间戳记脚本不适用于粘贴/拖动单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！