// Sheets · Calendar · Apps Script

Create a calendar event from a spreadsheet row in Google Calendar.

Write an Apps Script that reads event details from a Google Sheet and creates Calendar events, storing the returned event ID back in the sheet so re-runs update the existing event instead of duplicating it.

“I want to turn rows in a Google Sheet into Calendar events without ending up with duplicate events every time the script runs.”

The script

copy · paste · trigger

sheetToCalendar.gs

Apps Script

// sheetToCalendar.gs — reads rows from 'Events' sheet, creates or updates Calendar events
// Columns: A=Title, B=Date (YYYY-MM-DD), C=Start time (HH:MM), D=End time (HH:MM), E=Event ID (written by script)

function syncEventsToCalendar() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Events');
  var data = sheet.getDataRange().getValues();
  var calendar = CalendarApp.getDefaultCalendar();

  for (var i = 1; i < data.length; i++) {
    var title = data[i][0];
    var start = parseDateTime(data[i][1], data[i][2]);
    var end   = parseDateTime(data[i][1], data[i][3]);
    var eventId = data[i][4];

    if (eventId) {
      var existing = calendar.getEventById(eventId);
      if (existing) {
        existing.setTitle(title);
        existing.setTime(start, end);
        continue;
      }
    }

    var newEvent = calendar.createEvent(title, start, end);
    sheet.getRange(i + 1, 5).setValue(newEvent.getId());
  }
}

function parseDateTime(datePart, timePart) {
  var d = new Date(datePart);
  var parts = timePart.toString().split(':');
  d.setHours(parseInt(parts[0]), parseInt(parts[1]), 0, 0);
  return d;
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

The idempotency key problem, and why column E is your fix

Every time your script runs — whether triggered by a time-based trigger, an `onEdit` hook, or a manual run — CalendarApp.createEvent will happily create a brand-new event with no memory of the last one. Run it ten times, get ten identical entries. The fix is simple in principle: write the event ID that Calendar returns back into the row, then check for that ID before creating anything new.

CalendarApp.createEvent returns a CalendarEvent object. Call .getId() on it immediately and store the result in a dedicated column (column E in this script). On the next run, that cell is non-empty, so the script calls calendar.getEventById(eventId) instead. If the event still exists, it updates title and time in place. If it was deleted from Calendar manually, getEventById returns null, and the script falls through to create a fresh one and write the new ID back.

The first time I wired this up I forgot the null-check on getEventById, which threw a TypeError on rows where someone had deleted the Calendar event by hand. The double guard — check the cell, then check that the event actually exists — covers both cases cleanly.

Sheet layout and the parseDateTime helper

The script expects a sheet named exactly 'Events' with a header row you supply (row 1 is skipped by starting the loop at i = 1). Columns are: A for title, B for date as a plain YYYY-MM-DD string or a Date-formatted cell, C and D for start and end times in HH:MM format, and E left blank initially for the script to fill.

The parseDateTime helper exists because Sheets can hand you dates as Date objects, strings, or serial numbers depending on the cell format. Passing the value through new Date(datePart) normalizes all three cases. Then setHours pins the hours and minutes from the time column, zeroing seconds and milliseconds so Calendar does not show a stray offset.

If your sheet stores date and time in a single combined cell instead of two separate columns, skip the helper entirely and pass that cell's value directly to createEvent. Apps Script accepts a JavaScript Date as the start and end arguments, so a combined Sheets datetime cell works without any parsing.

Wiring a trigger without drowning in quota errors

CalendarApp calls count against a daily quota of roughly 5,000 Calendar event operations for a standard Google account (Workspace Business raises this, but free accounts hit the lower cap fast on large sheets). If your sheet has hundreds of rows and you attach a trigger that fires every minute, you will burn through the quota in under an hour and every subsequent call will throw a 'Service invoked too many times' error.

A time-based trigger firing every 15 or 30 minutes is fine for most scheduling use-cases. For lower latency, scope an onEdit trigger to only fire when column B, C, or D on the Events sheet changes, and pass the edited row index directly so the loop only processes that one row instead of scanning the whole sheet.

One practical guard worth adding: check that the title cell is non-empty before doing anything. An accidental blank row at the bottom of your data range will attempt to create a nameless event, which Calendar accepts — leaving you with phantom entries that are annoying to clean up.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Why does getEventById return null even though I can see the event in Calendar?: getEventById only finds events on the calendar the script has access to via getDefaultCalendar() or getCalendarById(). If the event was created on a secondary calendar — or if getDefaultCalendar() resolves to a different calendar than the one you are looking at — the lookup will return null. Check which calendar getId() actually wrote to by logging calendar.getName() during the first run.
The script keeps creating duplicates even though column E has an ID in it.: Sheets sometimes formats a long numeric string as a number and strips trailing digits, corrupting the ID. Format column E as Plain Text (Format > Number > Plain text) before the first run. CalendarApp event IDs are opaque strings and must be stored verbatim to round-trip correctly.
Can I sync to a specific calendar instead of the default one?: Replace CalendarApp.getDefaultCalendar() with CalendarApp.getCalendarById('your-calendar-id@group.calendar.google.com'). Find the calendar ID in Google Calendar under Settings > Settings for my calendars > [calendar name] > Calendar ID.
How do I handle all-day events instead of timed ones?: Use calendar.createAllDayEvent(title, date) and existing.setAllDayDate(date) for updates. Pass a single Date object with the time portion zeroed out. The setTime method you use for timed events will turn an all-day event back into a timed one, so keep the two branches separate if your sheet mixes both types.