CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggerstimestampcopy-paste
Luuu
Luuu

Reputation: 27

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

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

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

For example: enter image description here

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

Upvotes: 1

Views: 448

Answers (1)

Tanaike
Tanaike

Reputation: 201378

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

Upvotes: 1

Related Questions