Script places the dates correctly on my goog cal, then misplaces them all one day early, then places the last one correctly again

Question

The script places the dates correctly on my Google Calendar, then misplaces them all one day early, then places the last one correctly again.

input from google sheet, displayed in Google Calendar

1/4/23  ->  1/4/23 put in goog cal correctly 
1/8/23 ->  1/7/23  - one day off
2/5/23 -> 2/4/23 - one day off
6/18/23 -> 6/17/23 - one day off
10/15/23 -> 10/15/23 - correct

function addEvents(){
  var ss = 
 SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var lr = ss.getLastRow();
 var cal = 
 CalendarApp.getCalendarById("@group.calendar.google.com");

  var data = ss.getRange("A1:C" + lr).getValues();


  for(var i = 0; i<data.length;i++){


    cal.createAllDayEvent(data[i][0], new Date(data[i][1]), 
   {description:'PRO: ' + data[i][2]});

    }
   } 

   three columns in the google sheet
   title, date, and description

score 0 · Accepted Answer · answered Feb 09 '23 at 20:14

Try GetDisplayValues()

var data = ss.getRange("A1:C" + lr).GetDisplayValues();

and potentially format the column as yyyy-MM-dd.

This will ensure the data the script is using is the same as you see on the sheet. Formatting the column will ensure new Date() converts the the data correctly.

If its still off add a couple hours to your Date object to ensure the time is firmly in the middle of the day.

let today = new Date();
today.setHours(today.getHours() + 4);

Script places the dates correctly on my goog cal, then misplaces them all one day early, then places the last one correctly again

1 Answers1