How To Make Automatic Timestamp To Be Added Into Specific Column?

Question

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

Answer 1

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