How do I increment dates in Google Sheets with Apps Scripts

Question

I'm working on a scheduling app using Google Sheets and Apps Scripts. I'm trying to figure out what code to use to increment the date one day at a time starting with a certain date. What I've tried so far is...

var startdate = "1/13/19";
var dt = new Date(startdate);
newSheet.getRange("C1").setValue(dt);

// increment date by 1 day
dt = new Date(dt + 86400);
newSheet.getRange("D1").setValue(dt);

// increment date by 1 day
dt = new Date(dt + 86400);
newSheet.getRange("E1").setValue(dt);

The values I get in C1, D1, E1 are 1/13, 1/13, 1/13. If I use 86400000 milliseconds as an increment value I get 12/31/1969 as a result.

Google's Spreadsheet service does not seem to support much in the way of date functions. There's got to be a way to do this. Thanks for any guidance.

Manipulate the date using standard Date instance methods. You can review these in your preferred javascript language reference. As a start, make sure your input string actually parses to a valid Date. — tehhowch

pnuts pnuts · Accepted Answer · 2019-01-24T17:40:40

I'd recommend using four digit years. For dates the unit is a day so +1 is sufficient. The way the following runs dt increments as cells are stepped across to the right (normally!) so for successive dates just keep adding 1 (or 2 if you do want to skip a day):

function one() {
  var startdate = "1/13/2019";
  var newSheet = SpreadsheetApp.getActiveSheet();
var dt = new Date(startdate);
dt.setDate(dt.getDate());
newSheet.getRange("C1").setValue(dt);

// increment date by 1 day
dt.setDate(dt.getDate()+1);
newSheet.getRange("D1").setValue(dt);

// increment date by another 1 day
dt.setDate(dt.getDate()+1);
newSheet.getRange("E1").setValue(dt);
}

How do I increment dates in Google Sheets with Apps Scripts

1 Answers