Google Sheets to Google Calendar: create events from a sheet without duplicates or the day-early bug

The roster lives in a Google Sheet. The team lives in Google Calendar. Somebody has been retyping one into the other every Friday for two years, and the one week they were on holiday, two shifts were missed. In our analysis of r/GoogleAppsScript this is one of the two most asked automation questions (about 250 threads in the sample), and roughly a third of those threads never got a working answer. The reason is not that it is hard. It is that four separate things go wrong, and every tutorial covers one of them.

Below: a script that can be run again safely, the timezone problem that makes events land a day early, how duplicates happen and how to prevent most of them, and when you should not be doing this with a script at all. Checked against Google’s Calendar and Apps Script documentation on 9 September 2026. Test it on a copy of your sheet and a test calendar before pointing it at the real ones.

What “properly” means

A sheet-to-calendar script is only useful if you can run it again. That means it must not create the same event twice, it must update an event when the row changes, it must not silently shift times, and it must fail loudly rather than half-way. Most copied-from-a-forum scripts fail all four. The design that works is simple: one column in the sheet stores the calendar event ID, written the moment the event is created, not at the end. No ID means create; an ID means update. A lock stops two runs overlapping, and a status column handles cancellations.

The sheet layout

Start and End must be real date-time values, not text that looks like a date. Formatting a column as Date time does not convert existing text, so check a cell with =ISDATE(B2): it must return TRUE. The example uses the day.month.year order of a Latvian or most European locales; if your spreadsheet’s locale is US, type 09/14/2026 08:00 instead. The script below skips rows whose dates are not real dates and reports them, rather than failing silently.

The script

Open Extensions, Apps Script. If the project already contains code, do not overwrite it: add a new script file (the plus sign next to Files) and paste the following there. Fill in the three constants at the top: the spreadsheet ID (from the sheet’s URL), the sheet tab name, and the team calendar’s ID (Calendar settings, Integrate calendar). The script names its sheet and calendar explicitly rather than using “whatever is active”, because a scheduled run has no active sheet.

const SPREADSHEET_ID = 'paste-the-id-from-the-sheet-url';
const SHEET_NAME     = 'Roster';
const CALENDAR_ID    = 'team@yourdomain.com';

function syncSheetToCalendar() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(30000)) { Logger.log('Another run is still going; skipping.'); return; }
  try {
    const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);
    const cal   = CalendarApp.getCalendarById(CALENDAR_ID);
    if (!sheet) throw new Error('Sheet "' + SHEET_NAME + '" not found');
    if (!cal)   throw new Error('Calendar not found or no access: ' + CALENDAR_ID);

    const lastRow = sheet.getLastRow();
    if (lastRow < 2) return;                                  // header only
    const rows = sheet.getRange(2, 1, lastRow - 1, 6).getValues(); // A:F, one read
    const problems = [];

    rows.forEach((row, i) => {
      const r = i + 2;                                          // sheet row number
      const [title, start, end, desc, id, status] = row;

      if (String(status).trim().toLowerCase() === 'cancelled') {
        if (id) {
          try { const ev = cal.getEventById(id); if (ev) ev.deleteEvent(); }
          catch (e) { problems.push('Row ' + r + ': could not delete: ' + e.message); return; }
          sheet.getRange(r, 5).setValue('');                    // clear ID, row stays cancelled
        }
        return;
      }
      if (!title) return;                                       // blank row
      if (!(start instanceof Date) || !(end instanceof Date) || end <= start) {
        problems.push('Row ' + r + ': start/end is not a valid date range'); return;
      }
      try {
        let ev = id ? cal.getEventById(id) : null;
        if (ev) {
          ev.setTitle(title); ev.setTime(start, end); ev.setDescription(desc || '');
        } else {
          ev = cal.createEvent(title, start, end, { description: desc || '' });
          sheet.getRange(r, 5).setValue(ev.getId());            // write the ID immediately
        }
      } catch (e) {
        problems.push('Row ' + r + ': ' + e.message);
      }
    });

    if (problems.length) {
      Logger.log(problems.join('\n'));
      // Optional: MailApp.sendEmail('it@yourdomain.com', 'Roster sync problems', problems.join('\n'));
    }
  } finally {
    lock.releaseLock();
  }
}

Run it once from the editor (the play button next to syncSheetToCalendar) on a copy of the sheet and a test calendar first. Google will ask you to authorise access to Sheets and Calendar. Because you are running a script you have just pasted into your own project, this authorisation is for your own code; still, read the list of permissions it asks for, and if you ever see the “unverified app” warning for a script you did not write, stop and find out who owns it. Check the calendar, then check that column E has filled with IDs. Run it again: nothing should be duplicated. Then test the unhappy path: put a text value in a Start cell and confirm the run finishes, logs the problem row and leaves the other events untouched.

Why the ID is written per row

Many forum scripts collect the IDs in an array and write them all at the end. If the run fails half-way (a quota, a permissions hiccup, the 6-minute limit), the events created so far exist in the calendar but their IDs never reach the sheet, and the next run creates them again. Writing each ID immediately after createEvent costs one extra call per new row and closes most of that window. It does not make Sheets and Calendar a single transaction; a failure between createEvent and setValue is still possible, just rare. If a duplicate ever appears, the ID column tells you which event the sheet knows about, and the other one is the orphan.

The timezone bug: events one day or one hour off

This is the part that costs people an afternoon. Three time zone settings are involved, and unless you know what each one does, they will disagree:

1. The spreadsheet’s time zone. File, Settings, Time zone. Dates typed into cells are interpreted here.
2. The script project’s time zone. In the Apps Script editor, Project Settings, and tick Show appsscript.json; the timeZone field. New projects sometimes default to America/New_York regardless of where you are.
3. The calendar’s time zone. Calendar settings for that calendar.

A date-time cell in Sheets is stored as a moment in time, interpreted in the spreadsheet’s zone. “14.09.2026 08:00” in a Europe/Riga sheet is 05:00 UTC. Calendar stores the same moment and shows it in each viewer’s own zone, so a colleague in New York correctly sees 01:00; that is not a bug. The bugs appear when text is converted to dates by a script using the script’s zone, when a date-only value (midnight in the sheet’s zone) is created as a timed event and then displayed in another zone as the previous evening, or when the calendar’s own zone differs and all-day events shift by a day. In practice: set the spreadsheet and the script project to the company’s home zone, keep dates as real date values in the sheet, and test one event across a daylight-saving boundary before trusting it.

For all-day events (a date with no time), use cal.createAllDayEvent(title, date) instead of createEvent. It takes the calendar date of the value, so make sure the value really is that date in the sheet’s zone. Updating an all-day event later needs setAllDayDate, not setTime; the script above handles timed events only.

Running it automatically

In the editor, click Triggers (the clock icon), Add trigger, choose syncSheetToCalendar, event source Time-driven, for example every hour or a daily window such as 6:00 to 7:00 (Google schedules within the hour, not to the minute). Do not use a simple onEdit trigger for this: simple triggers cannot access Calendar because they run without authorisation. An installable “on edit” trigger works, but a timed sync is calmer and easier to debug, and the lock in the script makes overlapping runs harmless.

Two housekeeping rules. Create the trigger from an account that will still exist next year, ideally a dedicated automation user owned by IT, because installable triggers stop when their owner’s account is suspended and nobody else can see them. And set the trigger’s failure notifications to “immediately” for an address people read, so you hear about a broken sync before the team does.

Deleting and moving events

If a row is simply deleted from the sheet, the event stays in the calendar and nothing knows about it any more, so do not delete rows: set column F to Cancelled instead. The script then deletes the event, clears the ID and keeps skipping the row on later runs. To move an event to another calendar, mark it cancelled, let a run remove it, then change the row back and point the script at the new calendar (or extend the script with a calendar column if you sync several).

When not to do this with a script

• Bookings by customers. Use Calendar’s built-in booking pages; the customer picks a slot and no sheet is needed.
• Shift planning where people swap, request and approve. A rota tool with swaps, approvals and notifications exists for a reason. A sheet plus a script becomes the thing everyone blames.
• Anything where the calendar is the source of truth. The script above is one-directional. If people edit events in Calendar and expect the sheet to follow, you need the reverse sync as well, and now you have a synchronisation problem, which is a real problem.

Frequently asked questions

Why are my Google Calendar events created a day early from Google Sheets?

Usually a date-only value (midnight in the spreadsheet’s zone) was created as a timed event and is being displayed in a different zone, or text dates were parsed in the script project’s zone. Set the spreadsheet and script to the company’s zone, keep real date values in the sheet, and use createAllDayEvent for date-only rows.

How do I stop the script creating duplicate events every time it runs?

Store the event ID in a column immediately after each event is created, update existing events on later runs instead of creating new ones, and use a lock so two runs cannot overlap. The script above does all three. It cannot make duplicates impossible in every failure case, but it makes them rare and easy to identify.

Can the script run automatically when the sheet changes?

Yes, with an installable “on edit” trigger, but a time-driven trigger (hourly or daily) is more predictable. Simple onEdit triggers cannot access Calendar.

Related articles

• Your Google Sheets automation stopped working: the 6-minute limit, permission errors and when to move off Apps Script
• Google Calendar booking pages: let customers pick a time themselves
• Managed IT services for Google Workspace companies

If you would rather have this set up and looked after, a simple one-calendar sync like the one above is a short job for us, and keeping it running is part of managed IT services.

Automation failed and nobody owns it? We assess the workflow, its triggers and execution accounts, then scope a repair and a maintenance plan with a named owner. Book a free 30-minute audit or call +371 22 30 50 90.

Assess an automation