onEdit Not Working When I Copy&Paste Values in Multiple Cells at Once

Question

(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.

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.

For example:

• tab "Apple", the 'status' = column Y, the 'timestamp' = column Z
• tab "Banana", the 'status' = column A, the 'timestamp' = column B

Background of the spreadsheet I am working on:

1. total 6 tabs in this sheet
2. every 'status' and 'timestamp' in each tabs are not in the same column
3. in some tabs, 'status' may have a different title (e.g. 'Name')

What I found on stackoverflow so far:

• Time Stamp Script not working on Pasting/Dragging Cell Values
• onEdit not triggering when pasting data

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

The code I am using:

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);}

Answer 1

I believe your goal as follows.

• 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.

Modification points:

• 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.

Modified script:

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);
  }
}

• When you want to add more sheet condition, please modify var obj = {"Apple": 23, "Banana": 55};.

Reference:

• Event Objects