Google Calendar event from sheets script ends a day early

Question

I have a sheets file that creates a calendar event when a button is clicked. The calendar event is based off of a user entered due date, and script created start date, a entered location, and an entered description all pulled from certain cells in the spreadsheet. The problem is that when I go to the calendar the event ends a day before the due date (or beginning of the day of the due date) and I want it to show for the entire due date. For example if I enter a due date of july 17th into the spreadsheet, when I create the calendar event it only runs through the 16th. Is there a way to get the events to schedule to the end of the day or an easy way to just offset the end date by one day in the script.

  var Description = sheetTemplate.getRange('B12').getValue(); //gets discription from Cell B12
  var Location = sheetTemplate.getRange('C5') .getValue();  //gets location of work order from cell C5
  var StartDate = sheetTemplate.getRange('C4').getValue();  //Start Date from Cell C4
  var DueDate = sheetTemplate.getRange('E5').getValue();    //Gets Due Date From Cell E5


     //This portion of the code schedules the WO on the calendar//

var eventCal = CalendarApp.getCalendarById("XXXXX") ;//Get the Maintenance Calendar
var options = { 'location': Location, 
             'description':Description
            } //sets event details

var WOevent = eventCal.createAllDayEvent(WOname,StartDate,DueDate,options);//creates a calendar event

WOevent.setColor('10') // sets the color of the calendar event to green

ziganotschka ziganotschka · Accepted Answer · 2020-07-15T10:05:40

You have two options

Either you manually add a day to the dueDate

This can be done with new Date(ms):

var DueDate = sheetTemplate.getRange('E5').getValue(); 
DueDate = new Date(DueDate.getTime() + 24*60*60*1000);

Or you use the method createEvent(title, startTime, endTime, options) instead of createAllDayEvent()

This allows you to specify the end time on the DueDate:

var DueDate = sheetTemplate.getRange('E5').getValue();
DueDate = new Date(DueDate);
DueDate.setHours(23);
DueDate.setMinutes(59);
var WOevent = eventCal.createEvent(WOname,StartDate,DueDate,options);

Google Calendar event from sheets script ends a day early

1 Answers

You have two options