A Bookings sheet full of appointment details is only useful once those bookings actually appear on a calendar, and createCalendarEventsFromRows bridges that gap by turning every new row into a real CalendarApp event in one pass.
Manually creating a calendar event for every row means retyping the title, start time, end time, and location that are already sitting right there in the spreadsheet, and it's easy to mistype a time or double-book a slot in the process.
This example loops over every row on the Bookings sheet, skips any row that's missing required fields or already has an event ID recorded, and creates a new event with the location and a standard description before writing the new event's ID back into the sheet.
You'll finish with a Bookings sheet where filling in a row is enough to get it onto the calendar, and a built-in guard that keeps re-running the function safe rather than creating duplicate events.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Column | Field | Purpose |
|---|---|---|
| A | Title | Event title used directly as the calendar event's name |
| B | Start Time | Event start - must be a real Date/time value |
| C | End Time | Event end - must be a real Date/time value |
| D | Location | Optional location text passed to the calendar event |
| F | Event Id | Written back after creation - also the guard against duplicates |
What it does
The function reads columns A through E for every existing row, and for each one checks whether it has a title, a valid start date, and no existing event ID before creating a new CalendarApp event. Successfully created events have their ID written back into column F immediately.
- Only rows missing an event ID are eligible for creation, making repeated runs safe
- startTime instanceof Date check filters out rows where the date wasn't entered correctly
- The default calendar is used, via CalendarApp.getDefaultCalendar()
Prerequisites
Start and End Time columns must contain actual date-time values recognized by Sheets, not text that merely looks like a date, or the instanceof Date check will silently skip that row.
- A sheet named 'Bookings' with Title, Start Time, End Time, and Location in columns A-D
- Column F reserved specifically for the resulting event ID
- Calendar write access granted to the script during authorization
- Consistent date-time formatting so getValues() returns real Date objects
Walkthrough
The loop reads eventIdCell fresh on every iteration via sheet.getRange(i + 2, 6) rather than pulling it from the earlier bulk getValues() call, ensuring the duplicate check reflects the sheet's current state even if a previous iteration in the same run just wrote to that column.
calendar.createEvent's fourth argument is an options object accepting location and description, which is where any additional per-event metadata beyond the basic title and time range gets attached.
Edge cases
A row with a valid title and start time but a missing end time will still create an event, since only title and startTime are checked - CalendarApp treats an undefined endTime by using the start time as both start and end, producing a zero-duration event.
- Rows added after this function has already run for earlier rows need it to be re-run to pick up the new ones
- A Location value containing only whitespace still gets passed through and shows up on the calendar as a blank-looking location
- Two rows with identical times but different titles both create separate, non-conflicting events, since there's no overlap-detection logic here
Testing
Add a new booking row with a real title and future start and end times, run the function, and confirm both that the event appears on the calendar and that column F now shows a non-empty event ID.
- Re-run the function immediately after a successful creation and confirm no duplicate event is created
- Test a row with a missing end time and observe the resulting zero-duration event on the calendar
- Test a row with a text string in the Start Time column instead of a real date, confirming it's skipped rather than erroring
Hardening
This function only ever creates events - it never updates an event if the corresponding row's Start Time or Title changes after the initial creation, which can leave the calendar and the sheet out of sync over time.
- Combine with the update-aware pattern from sync-sheet-to-calendar if rows are expected to change after their initial creation
- Add a default end time (for example, start time plus one hour) when the End Time column is blank, rather than allowing a zero-duration event
- Validate that End Time is after Start Time before creating the event, to catch obviously backwards entries
Variations
The same row-to-event pattern can target a specific shared calendar instead of the default one, or attach guests pulled from an additional column so invitees are added automatically at creation time.
- Use CalendarApp.getCalendarById() to target a shared team calendar instead of the script owner's default calendar
- Add a Guests column and pass those addresses into the options object's guests field
- Trigger this automatically on form submission instead of running it manually over a batch of rows
Full code: createCalendarEventsFromRows()
The function uses the Event Id column as both the record of success and the guard against re-creating the same booking twice, which makes it safe to run repeatedly as new rows are added.
function createCalendarEventsFromRows() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Bookings');
var lastRow = sheet.getLastRow();
if (lastRow < 2) return;
var data = sheet.getRange(2, 1, lastRow - 1, 5).getValues();
var calendar = CalendarApp.getDefaultCalendar();
for (var i = 0; i < data.length; i++) {
var title = data[i][0];
var startTime = data[i][1];
var endTime = data[i][2];
var location = data[i][3];
var eventIdCell = sheet.getRange(i + 2, 6);
if (!title || !(startTime instanceof Date) || eventIdCell.getValue()) continue;
var event = calendar.createEvent(title, startTime, endTime, {
location: location,
description: 'Created automatically from the Bookings sheet.'
});
eventIdCell.setValue(event.getId());
}
}- Line 4: Exits immediately if there's no data below the header row to process.
- Line 6: Reads columns A through E for every existing booking row in one bulk call.
- Line 14: Re-reads the Event Id cell fresh for this specific row, used both to check and later to write.
- Line 16: Skips rows missing a title, missing a valid start date, or already carrying an event ID.
- Line 18: Creates the calendar event using the row's title, start time, and end time.
- Line 23: Writes the newly created event's ID back into the sheet, marking this row as processed.
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 running the calendar creation
- 1Start Time and End Time columns confirmed to contain real dates, not text
- 2Event Id column (F) left empty for any booking that hasn't been created yet
- 3Calendar authorization scopes granted during the first run
- 4Tested the duplicate-prevention guard by running the function twice
- 5Considered a default end time for rows missing one
- 6Confirmed which calendar (default vs. shared) is the intended target
Frequently asked questions
Without that check, running the function a second time - for example after adding a few new rows - would recreate an event for every row that already has one, resulting in duplicate calendar entries for the same booking.
Replace CalendarApp.getDefaultCalendar() with CalendarApp.getCalendarById using the calendar ID found in that calendar's settings, and make sure the script's account has edit access to it.
No - this function only creates new events for rows without an event ID; it has no logic to detect or apply changes to a row's date or title after the corresponding event already exists, so an edit to Start Time won't move the event automatically.
Nothing - the calendar event has no ongoing link back to the row it came from beyond the ID stored in column F, so deleting the row leaves the event exactly as it was on the calendar.
Yes, by adding a guests field (a comma-separated string of emails) to the options object passed as the fourth argument to createEvent, which will send standard Calendar invitations to each address.
Store times explicitly with their intended time zone context (for example, always enter times in the calendar's own time zone) since Apps Script's Date objects are tied to the script's default time zone unless you explicitly convert them.