How to sync Google Sheets with Google Calendar

The short answer

Google Sheets and Google Calendar don’t sync with each other out of the box. Sheets has a Timeline view that lays out rows with dates, but it only draws your sheet; it doesn’t create or change anything in Google Calendar. To keep the two in step you need a small piece of automation, and which one depends on the direction:

• Sheet to Calendar (each row becomes an event, and edits to the row update the event): a free Apps Script on a timer, or CalendarRows when you want to click and preview.
• Calendar to Sheet (a copy of your events as rows): a short free script on a timer, or a one-off import with CalendarRows.
• Both ways at once: no free tool does this safely, for reasons we explain below. The setup that works is one side in charge, plus a read-only copy of the other.

This guide gives you two complete scripts, both free, and both tested for the cases that usually break a sync: running twice, editing a row, deleting an event, a bad date and an event someone removed from the calendar by hand. Paste one in, switch on a trigger, and it runs without you.

We make CalendarRows, so we say plainly where it is the wrong tool. It is not a background sync. It runs when you click, which is exactly what you want for a one-off schedule and exactly wrong for a sheet that people update all week.

Which kind of sync do you need?

Most people who search for a Sheets and Calendar sync want one of three things. Picking the right one first saves an afternoon.

If you only need events in your calendar once, you don’t need a sync at all. A CSV import does it, and our guide on importing a spreadsheet into Google Calendar lists the exact headers. Syncing starts to matter when the sheet keeps changing after the events exist.

Set up the sheet

A sync works by remembering which event belongs to which row. The script stores that link in the row itself, in an event ID column, so you can sort, filter and hide rows without losing it. Create a tab called Events with these columns in row 1:

Three details decide whether the sync behaves:

• Start and End must be real dates, not text. Select both columns and choose Format → Number → Date time. A real date is right-aligned by default; text is left-aligned. The script marks a row it can’t read as SKIPPED instead of guessing.
• Leave End empty for a one-hour event. Only add it when the length matters.
• Don’t put formulas in G, H or I. The script writes values there. Formulas in A to E are fine: the script reads their results.

Times you type are in the spreadsheet’s time zone (File → Settings). The event is stored as an exact moment, so it shows at the right local time in every calendar, including for people in other time zones.

Make a test calendar first. In Google Calendar, add Other calendars → Create new calendar, call it “Sync test”, then open its settings and copy the Calendar ID from Integrate calendar. Put that ID in the script’s CALENDAR_ID setting while you try it. Deleting a test calendar later removes every event the test made in one step. When you are happy, switch the setting to 'primary' or to your real calendar’s ID.

Sheet to Calendar: the free script

In your spreadsheet, open Extensions → Apps Script, delete the sample code, paste in the script below and click Save. Choose syncSheetToCalendar in the function menu and click Run. The first time, Google asks you to allow the script to edit your spreadsheet and your calendars. That permission is for your own script in your own account; nobody else gets access.

// Tab "Events", header in row 1:
// A Title | B Start | C End | D Location | E Description | F Action | G Event ID | H Status | I Fingerprint
// G, H and I are filled in by the script. Leave them empty for new rows.
// Start and End must be real date-time cells (Format > Number > Date time), not text.
const CALENDAR_ID = 'primary';   // or a Calendar ID from Settings and sharing > Integrate calendar
const SHEET_NAME = 'Events';
const MAX_MS = 4.5 * 60 * 1000;  // stop before Google's 6-minute limit; the next run carries on

function syncSheetToCalendar() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(10000)) return;            // another run is still going
  try {
    const started = Date.now();
    const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
    const last = sheet.getLastRow();
    if (last < 2) return;
    const cal = CALENDAR_ID === 'primary' ? CalendarApp.getDefaultCalendar() : CalendarApp.getCalendarById(CALENDAR_ID);
    const rows = sheet.getRange(2, 1, last - 1, 9).getValues();
    const isDate = (v) => v && typeof v.getTime === 'function' && !isNaN(v.getTime());
    const out = rows.map((r) => [r[6], r[7], r[8]]);
    let created = 0, updated = 0, deleted = 0, errors = 0;
    for (let i = 0; i < rows.length; i++) {
      if (Date.now() - started > MAX_MS) break;
      const [title, start, end, location, description, action, id, , oldPrint] = rows[i];
      const wantDelete = /^(delete|remove|cancel)$/i.test(String(action).trim());
      try {
        if (wantDelete) {
          if (id) {
            const gone = cal.getEventById(String(id));
            if (gone) { gone.deleteEvent(); deleted++; }
            out[i] = ['', 'DELETED', ''];
          }
          continue;
        }
        if (!title || !isDate(start)) { out[i] = [id, 'SKIPPED: needs a title and a real start date', '']; continue; }
        const stop = isDate(end) ? end : new Date(start.getTime() + 60 * 60 * 1000);   // no end: 1 hour
        const print = Utilities.base64Encode(Utilities.computeDigest(Utilities.DigestAlgorithm.MD5,
          [title, start.getTime(), stop.getTime(), location, description].join('|')));
        if (id && print === oldPrint) continue;                // unchanged: nothing to do
        let ev = id ? cal.getEventById(String(id)) : null;
        if (ev) {
          ev.setTitle(title).setTime(start, stop).setLocation(location).setDescription(description);
          out[i] = [ev.getId(), 'UPDATED', print]; updated++;
        } else {
          ev = cal.createEvent(title, start, stop, { location: location, description: description });
          out[i] = [ev.getId(), id ? 'RE-CREATED' : 'CREATED', print]; created++;
        }
      } catch (e) {
        out[i] = [id, 'ERROR: ' + e.message, oldPrint]; errors++;
      }
    }
    sheet.getRange(2, 7, out.length, 3).setValues(out);
    console.log(created + ' created, ' + updated + ' updated, ' + deleted + ' deleted, ' + errors + ' errors');
  } finally {
    lock.releaseLock();
  }
}

// Run this once. It makes the sync run every hour, in the background.
function installHourlySync() {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === 'syncSheetToCalendar') ScriptApp.deleteTrigger(t);
  });
  ScriptApp.newTrigger('syncSheetToCalendar').timeBased().everyHours(1).create();
}

What each run does, row by row:

1. New row (no event ID): creates the event and writes its ID, CREATED and a fingerprint into G, H and I.
2. Unchanged row: does nothing. The fingerprint is a short code built from the title, times, location and description, so the script can tell without asking Calendar. This is what keeps hourly runs fast and stops it from touching events you adjusted by hand in Calendar.
3. Changed row: updates the same event and sets UPDATED. It never creates a duplicate.
4. Row whose event vanished (someone deleted it in Calendar): if you then edit the row, it makes a new event and sets RE-CREATED.
5. Row with delete in Action (also remove or cancel): deletes the event, clears the ID and sets DELETED.
6. Row it can’t use: leaves the calendar alone and says why in Status, for example SKIPPED: needs a title and a real start date. Calendar errors appear as ERROR: with Google’s message, and the row is retried on the next run.

A lock stops two runs from overlapping, so an hourly trigger and a manual run can’t create the same event twice. If a very long sheet would run past Google’s six-minute limit, the script stops at four and a half minutes and the next run carries on with the rows it didn’t reach.

We tested the logic with stand-ins for the Calendar and Sheets services: creating, running twice with nothing to do, editing a title, re-creating an event removed from the calendar, deleting, rows with no title or a text date, and the trigger installer. Stand-ins aren’t Google itself, which is why the test calendar above is worth two minutes.

Sorting and filtering. Sort the whole range, columns A to I together, so each event ID stays with its row. If you sort only A to F, the IDs end up on the wrong rows and the next edit changes the wrong event. Filter views and hidden rows are safe.

Guests, colours and repeats. This script keeps to the fields most sheets need. It doesn’t add guests, because an invitation email goes out the moment a guest is added and we don’t want that to happen by accident. Guests, colours and repeating events are handled by our longer script, by the recurring-events script and by CalendarRows.

Make it run by itself

A script that only runs when you click it isn’t a sync. You can switch on the timer in two ways.

The quick way. Select installHourlySync (it is at the bottom of the script above) in the function menu and click Run once. It removes any older timer for this script and adds one that runs syncSheetToCalendar every hour.

By hand. In the Apps Script editor click the clock icon (Triggers) on the left, then Add Trigger. Choose syncSheetToCalendar, event source Time-driven, type Hour timer, then Every hour, and Save. Use Day timer if a daily refresh is enough.

Check that it works from Executions on the left of the editor: each run lists when it started, whether it finished, and the one-line summary the script logs, such as 1 created, 2 updated, 0 deleted, 0 errors. Google runs time-driven triggers somewhere in the chosen window, not on the dot, so hourly means about once an hour.

Want it faster? A time-driven trigger can run every minute, but be careful before you do. Your trigger runtime is limited each day (see limits), and most sheets don’t change every minute. If edits should reach the calendar within seconds, add an installable From spreadsheet → On edit trigger for syncSheetToCalendar as well as the timer. The fingerprints make each run cheap, because only changed rows reach Calendar, and the lock stops runs from overlapping: a run that finds another still going waits ten seconds and then gives up, and the next trigger picks up anything it missed. Start with the hourly timer, and add On edit only if you need it.

Calendar to Sheet: a live copy of your calendar

The other direction is simpler, because the calendar is the source and the sheet only displays it. This script writes the next 60 days of events into a tab called Calendar and refreshes it on a timer. Paste it into the same Apps Script project, either below the first script or in a new file (the + next to Files → Script). The settings in the two scripts have different names, so they can live side by side.

// Writes your next 60 days of Google Calendar events into a tab named "Calendar".
// The tab is rewritten each run, so don't type in it. Copy rows elsewhere to keep them.
const COPY_CALENDAR_ID = 'primary';   // or a Calendar ID from Settings and sharing > Integrate calendar
const COPY_DAYS_AHEAD = 60;
const COPY_TAB_NAME = 'Calendar';

function syncCalendarToSheet() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName(COPY_TAB_NAME) || ss.insertSheet(COPY_TAB_NAME);
  const cal = COPY_CALENDAR_ID === 'primary' ? CalendarApp.getDefaultCalendar() : CalendarApp.getCalendarById(COPY_CALENDAR_ID);
  const from = new Date();
  from.setHours(0, 0, 0, 0);
  const to = new Date(from.getTime() + COPY_DAYS_AHEAD * 24 * 60 * 60 * 1000);
  const rows = cal.getEvents(from, to).map(function (e) {
    return [e.getTitle(), e.getStartTime(), e.getEndTime(), e.isAllDayEvent(),
            e.getLocation(), e.getDescription(), e.getId()];
  });
  const header = [['Title', 'Start', 'End', 'All day', 'Location', 'Description', 'Event ID']];
  sheet.clearContents();
  sheet.getRange(1, 1, 1, 7).setValues(header);
  if (rows.length) sheet.getRange(2, 1, rows.length, 7).setValues(rows);
  sheet.getRange(1, 9).setValue('Last synced: ' + new Date().toString());
}

// Run this once. It refreshes the tab every hour, in the background.
function installHourlyRefresh() {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === 'syncCalendarToSheet') ScriptApp.deleteTrigger(t);
  });
  ScriptApp.newTrigger('syncCalendarToSheet').timeBased().everyHours(1).create();
}

Run syncCalendarToSheet once to authorise it and see the tab appear, then run installHourlyRefresh once to put it on an hourly timer. The tab has the title, start, end, an All day flag, location, description and the event ID, plus a “Last synced” time in column I.

Three things to know:

• It is rewritten every run. Anything you type in that tab is wiped. Build your formulas, pivot tables and notes in other tabs and point them at Calendar!A:G.
• Repeating events appear once per occurrence inside the 60-day window, each with its own row.
• All-day events have a start at midnight and an All day value of TRUE. If you add up hours from this tab, leave them out. Our guides on exporting Google Calendar to Google Sheets and making a timesheet from Google Calendar show the formulas.

To change how far ahead it looks, change COPY_DAYS_AHEAD. To copy another calendar, such as a team calendar you can read, put its ID in COPY_CALENDAR_ID. Reading a calendar shared as “free/busy only” gives you titles of “Busy” and nothing else; ask the owner for “See all event details” if you need more.

Two-way sync: what is realistic

“Sync” usually means “a change on either side shows up on the other”. We don’t offer that, and we’re wary of any free script that claims it. To do it safely a tool needs to know three things that a spreadsheet and a calendar don’t record on their own:

1. What each side looked like at the last sync. Without that, it can’t tell whether a difference is a new edit in the sheet or a new edit in Calendar.
2. Which side wins a conflict. If the time is changed in both places between two syncs, someone’s change has to lose. A script can pick a rule, but you then have to trust the rule.
3. What a missing item means. A deleted row and a deleted event look the same as “never existed”. Guess wrong and you either lose an event or bring one back from the dead.

Recurring events add a fourth problem, because one change can mean one occurrence or the whole series. Full two-way tools exist, but they are products with their own databases, and we haven’t built one into a free script because a wrong guess deletes real appointments.

What works well in practice is giving each piece of information one owner:

• The sheet owns the plan. Run the sheet-to-calendar script. Edit rows, never the events. People who need to move something change the sheet, or ask whoever owns it.
• The calendar owns what happened. Keep the calendar-to-sheet copy in another tab so you can see everything on the calendar, including meetings people added by hand, next to your plan.
• A change made in Calendar is not lost at once. The sheet script only writes an event when its row changes. A hand edit stays until the next time that row is edited, and then the sheet’s version replaces it. If you need to keep a hand edit, copy it into the row.

For a team that wants to change events in Calendar and have the sheet follow, import the calendar into a tab, edit the rows, and send them back, as described next. That is a manual round trip, and it is safe because you see what will change before it does.

Doing it with CalendarRows

CalendarRows is our add-on for the same job, and it covers the parts a script leaves out. It is a good fit for a big batch with guests and colours, when you want to check the result before anything changes, and for round trips.

1. Install CalendarRows from the Google Workspace Marketplace (the button below), then in your sheet choose Extensions → CalendarRows → Open CalendarRows.
2. On the To Calendar tab pick the sheet, the header row and the calendar. Only Title and Start date are required. Location, Description, Guests, Color, All day and Action are optional, and start and end can be in one cell or in separate date and time columns.
3. Click Preview. It shows how many events will be created, updated, deleted or left unchanged, and lists rows with problems. Nothing changes yet.
4. Click Send to Calendar. Each row keeps its event ID and a status. Edit a row and send again, and only that row is updated. Type delete in the Action column to remove its event.
5. To go the other way, use the From Calendar tab: pick a calendar and a date range, and the events arrive in a new tab with their IDs. Edit that tab and send it back with To Calendar to update the events.

The honest limit: CalendarRows does not run in the background. It works when you open it and click, which is why the free script is the better answer for a sheet that is edited all week. The two fit together: use the script for the always-on part, and CalendarRows for big one-off loads, or when someone wants to check a change first.

Limits to plan around

Google limits what scripts can do each day. These are the figures in Google’s Apps Script quota table at the time of writing; check Apps Script → Quotas in Google’s documentation for current numbers.

For a normal sheet none of these matter. They matter for three cases. A first run on thousands of rows can hit the 6-minute limit, which is why the script stops early and carries on next time. An every-minute trigger spends 90 minutes of a personal account’s allowance quickly if each run takes a few seconds. And a very large initial load can reach the daily creation limit, so some rows show ERROR: until the next day, when the script finishes them.

Google Calendar can also tell a script to slow down if it creates many events quickly. That shows up as an ERROR: status on the rows it affected, and the next run retries them.

Common problems

Every row says SKIPPED. The Start cell is text. Select the column, choose Format → Number → Date time, and re-enter one date. If you pasted from a CSV, wrap the value in =DATEVALUE(…)+TIMEVALUE(…) or use a helper column.

Events are an hour off. The sheet and the calendar are using different time zones. Check File → Settings → Time zone in Sheets against the calendar’s time zone in Calendar settings. The event moment is right; only the display differs. A daylight-saving change between two dates is handled by the dates themselves.

Duplicates appeared. A row was copied with its event ID, so two rows point to one event, or the ID cell was cleared and the row ran again. Clear G, H and I on the copy. If you copy a whole row on purpose, clear those three cells first.

An event came back after I deleted it in Calendar. You edited its row afterwards, so the script saw a changed row with no event and made a new one (RE-CREATED). To drop the event for good, put delete in Action first, or delete the row.

An error like “Cannot read properties of null”. With a custom CALENDAR_ID this usually means the Calendar ID is wrong, or your account can’t see that calendar. Copy the ID again from Settings → Integrate calendar, and check you have “Make changes to events” access for it.

The timer never ran. Open Triggers and check it is listed. If it is, open Executions for the error. The most common cause is a permission that was never granted. Run the function once by hand, accept the permission screens, and wait for the next hour.

The Calendar tab is empty. There are no events in the next COPY_DAYS_AHEAD days on that calendar, or the calendar is the wrong one. Run the function by hand and read the log.

I changed a title in Calendar and the sheet didn’t change. Expected: the sheet script only reads the sheet. See two-way sync.

Frequently asked questions

Related

• How to create Google Calendar events from Google Sheets
• How to export Google Calendar to Google Sheets
• How to import a spreadsheet or CSV into Google Calendar
• How to create recurring Google Calendar events from Google Sheets
• How to send Google Form responses to Google Calendar
• CalendarRows documentation
• All guides

Disclosure: we make CalendarRows, the Google Sheets add-on mentioned in this guide. Cellbrook has built Google Sheets add-ons since 2022. Both scripts here work without CalendarRows, and we say where it is the wrong tool.

Google Sheets, Google Calendar and Google Workspace are trademarks of Google LLC. CalendarRows and Cellbrook are not affiliated with or endorsed by Google.