Bulk-create events from a Sheet

When Northwind announces a new training programme, somebody ends up creating two dozen calendar events by hand — copy the title, set the date, paste the description, add the attendees, save, repeat. It is the kind of work that takes an hour and produces typos in roughly half the events.

This script reads a queue sheet and creates one event per row in one pass. It stamps the new event’s ID back into the row so the next run skips anything already created — the ledger pattern that keeps bulk-creators safe to re-run.

What you’ll need

• An Events queue Google Sheet with these columns: title, start, end, description, attendees, eventId. The first five are filled in by whoever queues the events; eventId is left blank for the script.
• start and end should be real Date cells, not text. Format them as “Date time” in the sheet so they parse correctly.
• attendees is a comma-separated list of email addresses. Leave it blank for events with no guests.

The script

// The queue sheet the script reads from.
const EVENTS_SHEET = '1abcEventsQueueId';

/**
 * Reads the events queue, creates a calendar event for every row
 * without an eventId, and writes the new event ID back to the row.
 */
function createEventsFromSheet() {
  const sheet = SpreadsheetApp.openById(EVENTS_SHEET).getSheets()[0];
  const values = sheet.getDataRange().getValues();
  const [h, ...rows] = values;
  const col = Object.fromEntries(h.map((k, i) => [k, i]));
  const cal = CalendarApp.getDefaultCalendar();

  rows.forEach((r, i) => {
    // 1. Skip rows that already carry an event ID — already created.
    if (r[col.eventId]) return;

    // 2. Skip rows missing the essentials. A half-filled row should not
    //    crash the run; leave it for the human to finish.
    if (!r[col.title] || !(r[col.start] instanceof Date) || !(r[col.end] instanceof Date)) {
      return;
    }

    // 3. Normalise the attendees cell: split on commas, trim each, and
    //    rejoin. Calendar expects a single comma-separated string.
    const guests = String(r[col.attendees] || '')
      .split(',')
      .map((s) => s.trim())
      .filter(Boolean)
      .join(',');

    // 4. Create the event with whatever optional fields we have.
    const event = cal.createEvent(r[col.title], r[col.start], r[col.end], {
      description: r[col.description] || '',
      guests,
    });

    // 5. Stamp the event ID back into the row so the next run skips it.
    values[i + 1][col.eventId] = event.getId();
  });

  // 6. One bulk write at the end — cheaper than setValue per row.
  sheet.getDataRange().setValues(values);
}

How it works

1. createEventsFromSheet opens the queue sheet, reads every row at once, and builds a col lookup so the rest of the code reads r[col.title] instead of r[0] — order-independent and easier to scan.
2. It walks each row and skips anything that already has an eventId. That cell is the row’s record of having been processed; clear it to re-run.
3. It also skips rows missing a title or with non-Date start/end cells. That guards against half-filled rows breaking the whole batch.
4. It tidies the attendees cell into the comma-separated string Calendar wants, trimming spaces around each email.
5. It calls createEvent with the cleaned values and writes the new event’s ID back into the in-memory array.
6. After the loop, one setValues call flushes every change to the sheet.

Example run

Say the Events queue sheet looks like this before a run:

title	start	end	description	attendees	eventId
Q3 kickoff	2025-07-01 09:00	2025-07-01 10:00	Goals and OKRs	[email protected], [email protected]
Onboarding chat	2025-07-02 14:00	2025-07-02 14:30		[email protected]
(untitled draft)

After createEventsFromSheet runs, two new events sit on the default calendar and the sheet reads:

title	start	end	description	attendees	eventId
Q3 kickoff	2025-07-01 09:00	2025-07-01 10:00	Goals and OKRs	[email protected], [email protected]	[email protected]
Onboarding chat	2025-07-02 14:00	2025-07-02 14:30		[email protected]	[email protected]
(untitled draft)

The third row is untouched — no title, no start, no end — so the script left it for whoever is still drafting it.

Trigger it

This works as either an on-demand or a scheduled job. For a queue people fill through the day, schedule it hourly:

1. In the Apps Script editor, open Triggers (clock icon).
2. Add a trigger for createEventsFromSheet, event source Time-driven, type Hours timer, every 1 hour.
3. Approve the authorisation prompt the first time.

Or just keep it manual — paste rows, click Run, done.

Watch out for

• The eventId column is the ledger. If you clear it, the next run will create a second event for the same row. To re-issue an event cleanly, delete it in Calendar first, then clear the cell.
• Editing the sheet does not edit the calendar. This script only creates; it does not update. If a queued event changes, delete it in Calendar and let the script recreate it.
• Invitations fire on creation. For a “draft first” workflow, add sendInvites: false to the options object, review the events, then re-send manually.
• Dates in the sheet are read in the spreadsheet’s timezone. Set the sheet’s timezone (File → Settings) to match where the events happen.