A Bookings-style sheet that only ever creates events but never updates them drifts out of sync the moment someone changes an appointment's time, which is exactly the gap syncSheetRowsToCalendar closes by checking for an existing event ID and updating it in place.
Manually keeping a calendar aligned with a spreadsheet that several people edit throughout the day means someone has to notice every change and go update the matching calendar entry by hand, a task that inevitably falls behind during a busy week.
This example runs on a schedule rather than in response to a specific edit, looping over every appointment row and either updating the title, time, and location of an existing linked event or creating a brand new one if no event ID is present yet.
By the end you'll have an Appointments sheet and a shared team calendar that stay reliably aligned, with a Last Synced column showing exactly when each row was last reconciled.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Column | Field | Purpose |
|---|---|---|
| A | Title | Event title, kept in sync on both creation and update |
| B | Start | Event start time - drives both new events and updates to existing ones |
| C | End | Event end time |
| D | Location | Optional location, updated on existing events too |
| E | Event Id | Links the row to its calendar event; blank means not yet created |
| F | Last Synced | Timestamp written after every successful sync pass on that row |
What it does
On every scheduled run, the function reads all appointment rows and, for each one with an event ID, attempts to fetch and update that existing event's title, time, and location. If the fetch fails - for example the event was deleted directly on the calendar - it falls back to treating the row as needing a fresh event. Rows without an event ID at all go straight to creation.
- Existing events are updated in place rather than deleted and recreated, preserving their calendar history and any guest RSVPs
- A failed getEventById lookup is treated as a signal to recreate rather than crash the whole sync
- Every row gets a Last Synced stamp regardless of whether it was created or updated
Prerequisites
getCalendarById requires the script's account to have at least edit access to the target shared calendar, which for a truly shared team calendar usually means adding the script owner as a manager or editor on that calendar's sharing settings.
- A sheet named 'Appointments' with the six columns described in the setup table
- A shared calendar ID the script account can write to
- A time-driven trigger calling syncSheetRowsToCalendar on a regular interval
- Consistent real date values in the Start and End columns
Walkthrough
The try/catch around the existing-event update is the key piece that makes this resilient - calendar.getEventById throws if the event was deleted outside the script, and clearing eventId to an empty string inside the catch block deliberately falls through to the creation branch just below.
Because both the update and the create branches ultimately write to sheet.getRange(row, 6) at the very end of the loop iteration, Last Synced always reflects the most recent successful pass, whether that pass created or updated the event.
Edge cases
Deleting an event directly from the calendar rather than through the sheet leaves the row's Event Id pointing at a now-nonexistent event, which this function correctly detects via the caught exception and recovers from by creating a replacement.
- Two consecutive sync runs in quick succession are safe, since updating an event with the same values twice is a no-op from the calendar's perspective
- A row with a blank Title but a valid Event Id will still update the event's title to blank, since there's no guard preventing an empty title update
- Manually editing an event's time directly on the calendar will be silently overwritten on the next sync back to whatever the sheet says
Testing
Change the Start time on a row that already has an event ID, run the sync manually, and confirm the calendar event's time updates in place rather than a second event being created alongside it.
- Delete an event directly from the calendar and re-run sync to confirm a replacement event gets created and a new ID stored
- Add a brand new appointment row with no Event Id and confirm a fresh event appears after the next sync
- Check the Last Synced column updates on every row after each run, not just the ones that changed
Hardening
Right now the sheet always wins in any conflict, silently overwriting manual calendar edits - if calendar-side edits should sometimes take precedence, the sync needs a way to detect and flag genuine conflicts instead of blindly pushing sheet values.
- Compare the event's last modified time against Last Synced to detect a manual calendar edit and flag it for review instead of overwriting silently
- Add a Sync Error column to record which rows failed and why, rather than only logging create/update success paths
- Batch the calendar API calls where possible, since calling getEventById and setTime individually for hundreds of rows can be slow on a large Appointments sheet
Variations
The same reconcile-by-ID pattern generalizes to two-way sync with more work - reading recent calendar changes back into the sheet - though that requires storing a last-modified watermark rather than only ever pushing sheet changes outward.
- Extend to two-way sync by also pulling recent calendar changes back into the sheet using CalendarApp's event search by modification time
- Add cancellation handling - if a row's Title becomes 'CANCELLED', delete the linked event instead of updating it
- Sync to multiple calendars based on a Calendar column, rather than one fixed shared calendar for every row
Full code: syncSheetRowsToCalendar()
The function treats the stored Event Id as a hint rather than a guarantee, always verifying the event still exists before deciding whether to update it or fall back to creating a fresh one.
function syncSheetRowsToCalendar() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Appointments');
var lastRow = sheet.getLastRow();
if (lastRow < 2) return;
var calendar = CalendarApp.getCalendarById('team-calendar@group.calendar.google.com');
var data = sheet.getRange(2, 1, lastRow - 1, 6).getValues();
for (var i = 0; i < data.length; i++) {
var row = i + 2;
var title = data[i][0];
var start = data[i][1];
var end = data[i][2];
var location = data[i][3];
var eventId = data[i][4];
if (!title || !(start instanceof Date)) continue;
if (eventId) {
try {
var existingEvent = calendar.getEventById(eventId);
existingEvent.setTitle(title);
existingEvent.setTime(start, end);
existingEvent.setLocation(location || '');
} catch (err) {
eventId = '';
}
}
if (!eventId) {
var newEvent = calendar.createEvent(title, start, end, { location: location });
sheet.getRange(row, 5).setValue(newEvent.getId());
}
sheet.getRange(row, 6).setValue(new Date());
}
}- Line 6: Targets a specific shared calendar by ID rather than the script owner's personal default calendar.
- Line 15: Reads the previously stored event ID, which determines whether this row goes through the update or create path.
- Line 17: Skips rows that are missing a title or a valid start date entirely, whether or not they have an event ID.
- Line 19: Only attempts an update if a previous event ID was actually stored for this row.
- Line 21: Fetches the existing calendar event by its stored ID to update it in place.
- Line 31: Creates a brand new event whenever no valid existing event ID was found or the previous one no longer resolves.
- Line 35: Stamps Last Synced on every row processed in this run, regardless of whether it was created or updated.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before scheduling the calendar sync
- 1Shared calendar ID confirmed and script account granted edit access
- 2Appointments sheet columns match the six described in the setup table
- 3Time-driven trigger installed at an appropriate interval for how often appointments change
- 4Tested the recovery path for a manually deleted calendar event
- 5Considered how manual calendar-side edits should be handled
- 6Last Synced column reviewed to confirm every row updates on each run
Frequently asked questions
Deleting and recreating an event loses its calendar history and, more importantly, resets any guest RSVPs and notification settings tied to that event; updating the existing event in place via setTitle and setTime preserves all of that.
getCalendarById will throw immediately at the top of the function, before any row is processed, so the entire sync run fails cleanly rather than partially updating some rows and not others - check the Apps Script execution log for that specific error.
It depends on how quickly changes need to reflect on the calendar - hourly is a reasonable default for most appointment-scheduling scenarios, though a busy booking desk might justify running it every five or ten minutes instead.
Yes - the function itself doesn't care how it's invoked, so you can call it manually from the Apps Script editor, attach it to a custom menu item, or trigger it from a checkbox-based button in addition to the scheduled runs.
It's possible under truly overlapping executions, since there's no locking around the read-check-write sequence; wrapping the loop body in a LockService lock would close that narrow race condition for high-frequency triggers.
The function doesn't validate that End comes after Start, so a swapped pair would create or update an event with an end time before its start time, which Calendar generally normalizes but is worth validating before the sync runs.