When automating calendar events using Google Apps Script and Google Sheets, a common roadblock is dealing with date objects. When you pull a standard date value from a spreadsheet cell (for example, 10/06/2026), Google Sheets converts it into a JavaScript Date object representing midnight (00:00:00) on that day.

If you want to schedule a Google Calendar event for a specific time—say, starting at 10:00 AM and ending at 10:30 AM—passing that raw date creates an all-day or midnight event. In this guide, we will look at the cleanest and most reliable ways to set custom start and end times on a date retrieved from Google Sheets.

Understanding Why This Happens

When you use getValue() on a cell containing a formatted date, Apps Script treats it as a JavaScript Date object:

var startTime = spreadsheet.getRange("I2").getValue();
// Logs: Tue Oct 06 2026 00:00:00 GMT+0100

Because startTime is already an instance of Date, trying to concatenate strings (like startTime + " 10:00") can lead to parsing errors or timezone mismatches when passed into new Date(...).

Method 1: Use Native JavaScript setHours() (Recommended)

The cleanest, most readable approach is to use the native JavaScript Date methods setHours() and setMinutes(). Since getValue() already returns a date object, you can directly modify its time values.

Here is an updated version of your script:

function addMedReminder() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var calendarId = spreadsheet.getRange("A20").getValue();
  var eventCal = CalendarApp.getCalendarById(calendarId);
  
  var medName = spreadsheet.getRange("A2").getValue();
  var baseDate = spreadsheet.getRange("I2").getValue();

  // Clone the date for the start time
  var startTime = new Date(baseDate.getTime());
  startTime.setHours(10, 0, 0, 0); // Sets time to 10:00:00.000

  // Clone the date for the end time
  var endTime = new Date(baseDate.getTime());
  endTime.setHours(10, 30, 0, 0); // Sets time to 10:30:00.000

  // Create event with a popup reminder (10 minutes before)
  var event = eventCal.createEvent(medName, startTime, endTime);
  event.addPopupReminder(10);

  Logger.log("Created reminder for: " + medName);
  Logger.log("Starts: " + startTime);
  Logger.log("Ends: " + endTime);
}

Why Clone the Date Object?

Notice the use of new Date(baseDate.getTime()). In JavaScript, objects are assigned by reference. If you modify baseDate directly for startTime and then adjust it again for endTime, both variables could end up pointing to the exact same modified timestamp. Cloning avoids accidental side effects.

Method 2: Read Time Dynamically From Other Cells

If you prefer setting the time dynamically inside Google Sheets (for instance, holding 10:00 in cell J2 and 10:30 in cell K2), you can combine the date components using JavaScript:

function addDynamicTimeReminder() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var eventDate = sheet.getRange("I2").getValue(); // e.g., 2026-10-06
  var startCell = sheet.getRange("J2").getValue(); // e.g., 10:00:00
  var endCell = sheet.getRange("K2").getValue();   // e.g., 10:30:00

  var startTime = new Date(eventDate.getTime());
  startTime.setHours(startCell.getHours(), startCell.getMinutes(), 0);

  var endTime = new Date(eventDate.getTime());
  endTime.setHours(endCell.getHours(), endCell.getMinutes(), 0);

  var calendar = CalendarApp.getDefaultCalendar();
  calendar.createEvent("Medication Reorder", startTime, endTime);
}

Method 3: Calculating End Time Relative to Start Time

If your appointments or reminders always last a set duration (like 30 minutes), you can add minutes mathematically without manually hardcoding both values:

var startTime = new Date(baseDate.getTime());
startTime.setHours(10, 0, 0, 0);

// Add 30 minutes (30 * 60 * 1000 milliseconds)
var durationMs = 30 * 60 * 1000;
var endTime = new Date(startTime.getTime() + durationMs);

Key Best Practices

  • Avoid Global Execution: Notice that in the improved code, variables accessing the sheet (spreadsheet.getRange(...)) are moved inside the function. Declaring them globally runs API calls every time any function is triggered, slowing down your script and increasing execution quotas.
  • Timezone Awareness: Make sure your Google Spreadsheet timezone (File > Settings > Time zone) matches your Google Calendar timezone to prevent your events from drifting forward or backward by hours.