Create Calendar events from Sheet rows

1 · Prepare

Put these pieces in place

• Create a Sheet tab named Events with headers in row 1.
• Put the event title in column A, the start in column B, the end in column C, and leave column D empty for the event ID.
• Format the start and end cells as a date with a time. Select them, then choose Format, Number, Date time. A cell holding plain text cannot be read as a date.
• Use an account whose main Google Calendar is a good place for these new events. Run with one operator, wait for completion, and do not edit or reorder rows during execution.

2 · Review before running

What the script can access and change

3 · Copy

Paste this into the Apps Script editor

Create or open a script file, replace the example values marked above, then save the project.

function createCalendarEventsFromSheetRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Events');
  if (!sheet) throw new Error('Add a Sheet tab named Events before running this function.');

  const firstDataRow = 2;
  const lastRow = sheet.getLastRow();
  if (lastRow < firstDataRow) return;

  const calendar = CalendarApp.getDefaultCalendar();
  const rows = sheet.getRange(firstDataRow, 1, lastRow - firstDataRow + 1, 4).getValues();

  rows.forEach((row, index) => {
    const [title, start, end, eventId] = row;
    if (!title || !start || eventId) return;

    const rowNumber = firstDataRow + index;
    const startTime = start instanceof Date ? start : new Date(String(start));
    const endTime = end instanceof Date ? end : new Date(String(end));
    if (isNaN(startTime.getTime()) || isNaN(endTime.getTime())) {
      throw new Error('Row ' + rowNumber + ' needs a date with a time in both the start cell and the end cell.');
    }

    const event = calendar.createEvent(String(title), startTime, endTime);
    sheet.getRange(rowNumber, 4).setValue(event.getId());
  });
}

4 · Run safely

Follow these steps in order

1. Start with one test row

   Put headers in row 1. Add a single row below it with a title, a start, and an end, and leave column D empty. Pick a time later today so the new event is easy to find.

2. Add the script

   Open Extensions, choose Apps Script, paste the function over the starter code, and save.

3. Run it once and approve access

   Choose createCalendarEventsFromSheetRows and press Run. Google shows Authorization required. Approve access to the spreadsheet and to your calendar, then open Google Calendar and look for the test event.

4. Add the remaining rows

   When the test event looks right, fill in the other rows and run the function again. Rows that already show an ID in column D are left alone.

5 · Confirm

How to tell it worked

Each finished row shows a long event ID in column D, and the matching events appear on your calendar at the times from columns B and C.

If the result is different

Troubleshooting

The run fails with "Add a Sheet tab named Events before running this function."

Rename your tab to Events, or change the name in the code and in the editable values above.

The run fails with a message about a date with a time.

Select the start and end cells and choose Format, Number, Date time. Text such as March 4 at 9 is not a date to the script.

The same event was created twice or a run failed without an ID.

Stop before rerunning. Inspect the intended calendar around the start and end from that row, comparing the title and time. Reconcile the existing event with column D using its CalendarApp ID before allowing another creation; a browser event URL is not that ID. Resolve uncertain or multiple matches first. Clearing IDs, overlapping runs, and failure after creation can all produce duplicates.

The event landed on the wrong calendar.

CalendarApp.getDefaultCalendar() is the main calendar of the account running the script. Replace it with CalendarApp.getCalendarById and the calendar ID from Google Calendar settings.