当我一次复制并粘贴多个单元格中的值时，onEdit无法正常工作 [英] onEdit Not Working When I Copy&Paste Values in Multiple Cells at Once

问题描述

(这是一个后续问题-

(This is a follow up question of - How to Run Script on Multiple Spreadsheet Tabs)

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

My question: 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.

我正在使用脚本做什么:当有人编辑状态"列(大多数情况下为状态"列)时，我使用onEdit帮助我在时间戳"列中添加时间戳.而且这些列并不总是并排的.

What I am doing with the script: I am using onEdit to help me add a timestamp in 'timestamp' column, when someone edit 'status' column (most of the time, 'status' column). And these columns are not always side by side.

例如:

• 标签"Apple"，状态" = Y列，时间戳" = Z列
• 标签香蕉"，状态" =列A，时间戳" = B列

我正在处理的电子表格的背景:

1. 此工作表中总共6个标签
2. 每个标签中的每个状态"和时间戳"都不在同一列中
3. 在某些标签中，状态"可能具有不同的标题(例如名称")

到目前为止我在stackoverflow上发现的内容:

• 时间戳脚本不起作用粘贴/拖动单元格值
• 粘贴数据时未触发onEdit

不幸的是，我太新了，无法编写脚本，并且在阅读了以上文章之后还不知道如何修复我的代码.

Unfortunately I am too new to script and have no ideal how to fix my code after reading articles above.

我正在使用的代码:

function onEdit(e) {
addTimestamp(e);
}
    
  function addTimestamp(e){

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

 if(e.source.getActiveSheet().getName() === "Apple" && col === 22 && row >= 2){
    
    var time = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd');
    
    e.source.getActiveSheet().getRange(row,25).setValue(time);}
    
    if(e.source.getActiveSheet().getName() === "Banana" && col === 55 && row >= 2){
    
    var time = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd');
    
    e.source.getActiveSheet().getRange(row,56).setValue(time);}

推荐答案

我相信您的目标如下.

• 您要将 time 的值放在已编辑单元格的右侧.
• 即使复制和粘贴值，您也要实现这一目标.
• 您要在多个选项卡中反映出来，并且需要每个选项卡才能将值设置到每一列.每列都不相同.

• You want to put the value of time to the one side right of the edited cell.
• Even when the values are copied and pasted, you want to achieve this.
• You want to reflect this for the multiple tabs, and each tab is required to set the value to each column. Each column is different.

• 在这种情况下，我想建议使用事件对象的 range .事件对象的 range 的值类似于"range":{"columnEnd":#，"columnStart":#，"rowEnd":#，"rowStart".:#} .我以为可以用.
• 为了管理多个选项卡的条件，我想建议使用一个对象.键和值分别是工作表名称和检查列号.

• In this case, I would like to propose to use range of the event object. The value of range of event object is like "range":{"columnEnd":#,"columnStart":#,"rowEnd":#,"rowStart":#}. I thought that this might be used.
• In order to manage the conditions for the multiple tabs, I would like to propose to use an object. The key and value are the sheet name and the cheking column number, respectively.

当以上几点反映到您的脚本中时，它如下所示.

When above points are reflected to your script, it becomes as follows.

请如下修改 addTimestamp 的功能.请复制并粘贴以下脚本并保存.使用此脚本时，请编辑单元格，然后将值复制并粘贴到工作表的检查列中.这样，脚本将运行.

Please modify your function of addTimestamp as follows. Please copy and paste the following script and save it. When you use this script, please edit the cell and copy and paste the values to the checking column of the sheet. By this, the script is run.

function addTimestamp(e) {
  var obj = {"Apple": 23, "Banana": 55}; // Please set your condition. The key and value are the sheet name and the cheking column number, respectively.

  var range = e.range;
  var sheet = range.getSheet();
  var sheetName = sheet.getName();
  var rowStart = range.rowStart;
  var rowEnd = range.rowEnd;
  var columnStart = range.columnStart;
  var columnEnd = range.columnEnd;
  if (obj[sheetName] && columnStart == columnEnd && columnStart == obj[sheetName] && rowStart >= 2) {
    var time = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd');
    sheet.getRange(rowStart, obj[sheetName] + 1, rowEnd - rowStart + 1).setValue(time);
  }
}

• 如果要添加更多工作表条件，请修改 var obj = {"Apple":23，"Banana":55}; .
• 事件对象

这篇关于当我一次复制并粘贴多个单元格中的值时，onEdit无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！