Create calendar events from sheet rows

Getting a calendar

const cal = CalendarApp.getDefaultCalendar();              // your own calendar
// const cal = CalendarApp.getCalendarById('team@group.calendar.google.com'); // a shared one

Find a shared calendar's ID in Google Calendar → Settings and sharing → Integrate calendar → Calendar ID.

Creating events

const start = new Date('2026-10-15T11:00:00');
const end = new Date('2026-10-15T11:30:00');

cal.createEvent('Customer visit: Rahul', start, end, {
  description: 'Ring resizing, bring old ring',
  location: 'Karol Bagh store',
  guests: 'rahul@example.com',
  sendInvites: false
});

cal.createAllDayEvent('Annual stock audit', new Date(2026, 10, 6)); // months start at 0 → 6 Nov 2026

The sheet

Tab Appointments:

Combining date and time cells

Sheets stores a time-only cell (e.g. 11:30) as a date on 30-Dec-1899 with that time. Combine the two cells like this:

function combine(dateCell, timeCell) {
  const d = new Date(dateCell);
  const t = new Date(timeCell);
  d.setHours(t.getHours(), t.getMinutes(), 0, 0);
  return d;
}

The script

function syncAppointments() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Appointments');
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return;

  const cal = CalendarApp.getDefaultCalendar();
  const rows = sheet.getRange(2, 1, lastRow - 1, 7).getValues();
  const ids = [];

  rows.forEach(([customer, email, date, time, minutes, purpose, eventId]) => {
    if (!customer || !date || !time) { ids.push([eventId]); return; }

    const start = combine(date, time);
    const end = new Date(start.getTime() + (Number(minutes) || 30) * 60 * 1000);
    const title = `${purpose || 'Appointment'}: ${customer}`;

    let event = eventId ? cal.getEventById(eventId) : null;

    if (event) {
      // Update the existing event instead of creating a duplicate
      event.setTitle(title);
      event.setTime(start, end);
    } else {
      event = cal.createEvent(title, start, end, {
        description: `Customer email: ${email}`,
        location: 'Store'
      });
    }
    ids.push([event.getId()]);
  });

  sheet.getRange(2, 7, ids.length, 1).setValues(ids);
}

Each run updates existing events and creates only the new ones, because the Event ID column remembers which row belongs to which event.

Reminders

event.removeAllReminders();
event.addPopupReminder(60);   // 60 minutes before
event.addEmailReminder(24 * 60); // 1 day before

Time zones

Dates use the script's time zone (Project Settings). If events appear at the wrong hour, check that the script and the calendar both use India Standard Time (or your zone).

Key points

• createEvent(title, start, end, options) and createAllDayEvent(title, date).
• Combine separate date and time cells before creating events.
• Save event IDs to update, not duplicate.