“Create a calendar event for each booking row” is easy. Running it twice and getting two events per booking is just as easy. createCalendarEventIfMissing(opts) creates an event from title / start / end (plus optional guests, description, location and calendar), or returns the existing event when you pass an eventId that still exists. Either way you get back an eventId to store on the row.
When to use
- Booking / onboarding sheets that should put each row on a calendar once
- Re-runnable sync jobs (the stored id stops doubles)
- Inviting a client or teammate to a slot picked in a sheet
How to use this snippet
/**
* Create a Calendar event, or return the existing one if eventId is given and found.
*
* @param {{title: string, start: Date|string, end: Date|string,
* eventId?: string, calendarId?: string,
* guests?: string|string[], sendInvites?: boolean,
* description?: string, location?: string}} opts
* eventId id stored on the row from a previous run
* calendarId default: the script user's default calendar
* guests emails; invites are sent unless sendInvites === false
* @return {{created: boolean, eventId: string, event: GoogleAppsScript.Calendar.CalendarEvent}}
*/
function createCalendarEventIfMissing(opts) {
opts = opts || {};
var cal = opts.calendarId
? CalendarApp.getCalendarById(opts.calendarId)
: CalendarApp.getDefaultCalendar();
if (!cal) {
throw new Error('createCalendarEventIfMissing: calendar not found: ' + opts.calendarId);
}
// 1. Already created on a previous run? Hand it back, no double.
if (opts.eventId) {
var existing = cal.getEventById(opts.eventId);
if (existing) return { created: false, eventId: existing.getId(), event: existing };
// Not found (deleted by someone): fall through and create a fresh one.
}
// 2. Validate before creating anything.
var start = new Date(opts.start);
var end = new Date(opts.end);
if (!opts.title) throw new Error('createCalendarEventIfMissing: title required');
if (isNaN(start) || isNaN(end) || end <= start) {
throw new Error('createCalendarEventIfMissing: need a valid start before end');
}
// 3. Create with optional details.
var options = {};
if (opts.description) options.description = opts.description;
if (opts.location) options.location = opts.location;
if (opts.guests && opts.guests.length) {
options.guests = [].concat(opts.guests).join(',');
options.sendInvites = opts.sendInvites !== false;
}
var ev = cal.createEvent(opts.title, start, end, options);
return { created: true, eventId: ev.getId(), event: ev };
}
Example
// Bookings: A Client | B Email | C Start | D End | E EventId
function syncBookingRow_(sheet, rowNum) {
var r = sheet.getRange(rowNum, 1, 1, 5).getValues()[0];
var res = createCalendarEventIfMissing({
title: 'Session: ' + r[0],
start: r[2],
end: r[3],
guests: r[1] ? [r[1]] : [],
eventId: r[4] // blank on first run
});
if (res.created) {
sheet.getRange(rowNum, 5).setValue(res.eventId); // store it so reruns skip
}
}
Tips:
- Store the id right after creating. If the run dies between
createEventandsetValue, the next run makes a double. Write the id immediately, before doing anything else. getEventByIdonly looks in the calendar you pass. If you switchcalendarId, old ids won’t be found and new events get created.- Date cells are already real Dates in the sheet’s timezone, so pass them straight through. Avoid building times from strings.
- Guests get invite emails by default. Use
sendInvites: falsewhile testing. - This helper doesn’t update an existing event if the title or time changed. Handle that in your sync (compare, then
setTitle/setTime); the calendar guide in this series shows how.
Tip: NitroGAS Co-Pilot can turn “put every Booking row on the team calendar, invite the client” into a sync loop around createCalendarEventIfMissing that stores the event id on each row.
Happy Coding!
