google-sheetsgoogle-apps-script

How do I add days to a timestamp script?


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

Solution

  • Add days to Javascript's Date object

    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?