I have this script that moves a row when a cell gets edited and adds a timestamp.
So how this works is that when any cell in Col 12 gets the number 2 it then timestamps the row.
I'm trying to edit this script so it stamps the current date and also add 2 days to that date stamp.
I have tried to just do it like this setValue(new Date()+2
but it does not work.
So I would appreciate some help and to understand of what I'm doing wrong.
Here is the script:
function Cal1 () {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var r = SpreadsheetApp.getActiveSpreadsheet().getActiveRange();
if(s.getName() == "Pipe" && r.getColumn() == 12 && r.getValue() == "2") {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Called");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 2, 1, numColumns).moveTo(target);
target.offset(0,numColumns).setValue(new Date());
s.clearContent(row);
}
}
I took your code as is and incorporated a function that does the adding of days to the timestamp and used it in the line that sets value on the sheet.
Code
function addDays(date, days) {
const newDate = new Date(date);
newDate.setDate(date.getDate() + days);
return newDate;
}
function Cal1 () {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var r = SpreadsheetApp.getActiveSpreadsheet().getActiveRange();
if(s.getName() == "Pipe" && r.getColumn() == 12 && r.getValue() == "2") {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Called");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 2, 1, numColumns).moveTo(target);
target.offset(0,numColumns).setValue(addDays(new Date(), 2));
s.clearContent(row);
}
}
Output
Input | TimeStamps | |
---|---|---|
2 | 1/12/2025 |
References: How to Add Days to Date in JavaScript?