当我一次复制并粘贴多个单元格的值时，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列
我正在处理的电子表格的背景:
- 此工作表中总共6个标签
- 每个标签中的每个状态"和时间戳"都不在同一列中
- 在某些标签中，状态"可能具有不同的标题(例如名称")
到目前为止我在stackoverflow上发现的内容:
不幸的是，我太新了，无法编写脚本，并且在阅读了以上文章之后还不知道如何修复我的代码.
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
timeto 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
rangeof the event object. The value ofrangeof 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};.
这篇好文章是转载于：学新通技术网
