如何使自动时间戳添加到特定列? [英] How To Make Automatic Timestamp To Be Added Into Specific Column?

问题描述

(这是针对以下问题的后续问题-

• 当人们在Apple选项卡中的状态"列(即A列)中编辑时，时间戳将添加到时间戳"列(即B列)中.更具体地说，当人们编辑单元格A2时，时间戳记将添加到B2中.
• 当人们在香蕉"标签中编辑名称"列(即D列)时，时间戳将添加到时间戳"列(即F列)中人们有时会一次编辑多个单元格(拖动或复制并粘贴).更具体地说，当人们编辑单元格D34时，时间戳记将添加到F34中

无论如何，我现在使用的代码不是"timestamp"列，而是总是添加到"status"或"name"列旁边的列中.

  function onEdit(e){addTimestamp(e);}函数addTimestamp(e){var obj = {"Apple":1，"Banana":4}；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);}} 

解决方案

我相信您的目标如下.

• 您要在编辑其他特定列的单元格时将 time 的值添加到特定列.
例如，当列"A"的单元格为编辑后，您要将值放在"C"列中.

在这种情况下，我想提出以下修改.

修改后的脚本:

请根据您的实际情况修改 obj .

  function addTimestamp(e){var obj = {"Apple":{"checkColumn":1，"putColumn":3}，"Banana":{"checkColumn":4，"putColumn":6}}；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] .checkColumn&& columnStart == columnEnd&& columnStart == obj [sheetName] .checkColumn&&row; rowStart> == 2){var time = Utilities.formatDate(new Date()，'Asia/Tokyo'，'yyyy/MM/dd');sheet.getRange(rowStart，obj [sheetName] .putColumn，rowEnd-rowStart + 1).setValue(time);}} 

• 在此脚本中，当列"A"苹果"编辑工作表，将 time 的值放在"C"列中.当列"D"为香蕉"的意思是编辑工作表，将 time 的值放在"F"列中.

(This is a follow up question for - onEdit Not Working When I Copy&Paste Values in Multiple Cells at Once)

What I am doing with the script: Using google app script to add an automatic timestamp to 'timestamp' column on each tabs when people edit specific column in the spreadsheet.

Couple things you may want to know before answering the question:

• Total 6 tabs in this spreadsheet
• The specific column that people will edit and the 'timestamp' column in each sheets are in the different columns
• People sometimes edit multiple cells (in the same column) at once by copy&pasting or dragging

My question: When anyone edit specific column, how to make timestamp to be added into the column I want?

For example:

• When people edit 'status' column in Apple tab (which is column A), the timestamp will be added into 'timestamp' column (which is column B). More specifically, when people edit cell A2, the timestamp will be added in B2
• When people edit 'name' column in Banana tab (which is column D), the timestamp will be added into 'timestamp' column (which is column F) And people sometimes edit multiple cells at once (dragging or copy&pasting). More specifically, when people edit cell D34, the timestamp will be added in F34

However with the code I am using right now, instead of the 'timestamp' column, the timestamp will always being added into the column that right next to 'status' or 'name' column.

function onEdit(e) {
addTimestamp(e);
}
function addTimestamp(e) {
  var obj = {"Apple": 1, "Banana": 4}; 
  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);
  }
}

解决方案

I believe your goal as follows.

• You want to put the value of time to the specific column when the cell of the other specific column is edited.
• For example, when the cell of column "A" is edited, you want to put the value to the column "C".

In this case, I would like to propose the following modification.

Modified script:

Please modify obj for your actual situation.

function addTimestamp(e) {
  var obj = { "Apple": { "checkColumn": 1, "putColumn": 3 }, "Banana": { "checkColumn": 4, "putColumn": 6 } };

  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].checkColumn && columnStart == columnEnd && columnStart == obj[sheetName].checkColumn && rowStart >= 2) {
    var time = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd');
    sheet.getRange(rowStart, obj[sheetName].putColumn, rowEnd - rowStart + 1).setValue(time);
  }
}

• In this script, when the column "A" of "Apple" sheet is edited, the value of time is put to the column "C". When the column "D" of "Banana" sheet is edited, the value of time is put to the column "F".

这篇关于如何使自动时间戳添加到特定列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！