CODEWITHSUNDEEP
javascriptdategoogle-apps-script
JakeW
JakeW

Reputation: 1

When I create an event from a start date and end date in a google sheet, the end date in the calendar is one day earlier

I've written a simple script to take a start date and end date from 2 cells in a spreadsheet and create a calendar event. The cells are formatted as a date and will show the calendar dropdown to select a date. Time zones on both the sheet and the calendar are the same. The function takes in the onEdit event from the spreadsheet. The function itself is working and successfully creates the event in the calendar, but with the end date being one day earlier than the date specified in the spreadsheet.

gets the start date based on the edited row and the column with the start dates
var onSiteStartDate = ss.getRange(editedRow, onSiteStartDateRangeCol).getValue();

gets the end date in the same fashion
var onSiteEndDate = ss.getRange(editedRow, onSiteEndDateRangeCol).getValue();

Creates the event based on those dates with a title concatenated from other cells in the row
onSiteCal.createAllDayEvent(onSiteEventTitle, onSiteStartDate, onSiteEndDate);

logging start and end date in the console returns Wed Oct 09 2019 00:00:00 GMT-0500 (CDT)Thu Oct 17 2019 00:00:00 GMT-0500 (CDT)

However in the calendar, the event starts on the 9th, and the final day of the event is the 16th.

I've fiddled with the time zones in both the spreadsheet and the calendar with no luck.

What's going on here?

Upvotes: 0

Views: 1477

Answers (1)

Jescanellas
Jescanellas

Reputation: 2608

It looks like there is an issue with createAllDayEvent(), as it is not working as expected. I used the same date format as specified in the documentation:

CalendarApp.createAllDayEvent("All day", new Date('October 3, 2019'), new Date('October 6, 2019'));

The event in the Calendar is created from October 3 to October 5, when it should be October 6.

However, as you are working with dates and time, I recommend you to use createEvent(), which uses the format 'July 20, 1969 20:00:00 UTC' and works as expected.

Regarding the bug, you can click on the star next to the issue number to receive updates, and I also will report it.

Upvotes: 1

Related Questions