Automating new dates in google sheets / scripts

1
votes

I'm trying to automate creating a calendar with google script. I am taking data from a sheet where I have to enter events weekly. In my sheet, it is formatted to enter into google calendar. That script works well, but I want to automate my week of date. I have it set to take that Monday date, and add 7 days for a new week. It worked for 10/26 to 11/2, but when it goes to the following week, it reverts to 10/9 instead of 11/9 and the rest of the dates are all October and not November. I can't figure out the issue at all. I'm not the best at coding, but have found and manipulated most of the codes found online

https://docs.google.com/spreadsheets/d/14JQ-qqZ4eIl2xKuBjzSHSyC-4fV_yVxih9qUIwLGfNA/edit?usp=sharing

function changeDate() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
 
  var startDate = new Date(sheet.getRange("A2").getValues());
  var secondDate = new Date();
   
  secondDate.setDate(startDate.getDate()+7);
  
  Logger.log(startDate);
  sheet.getRange("A2").setValue(secondDate);
  
}
1
google-apps-scriptgoogle-sheets

1 Answers

1
votes

getDate() returns you only the day of the month, not the full date

you will be better of converting your date into ms with getTime() and add the equivalent of 7 days in ms:

  var startDate = new Date(sheet.getRange("A2").getValues());
  var secondDate = new Date(startDate.getTime()+7*24*3600*1000);
  sheet.getRange("A2").setValue(secondDate);