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.
• Run the script from an account whose main Google Calendar should get these events. Only one person should run it at a time. Wait for each run to finish, and do not edit or sort the rows while it runs.

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

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

   Choose Extensions, then Apps Script. Paste the function over the starter code and save.

3. Run it once and approve access

   Choose createCalendarEventsFromSheetRows in the function dropdown above the code, then click Run. A dialog says Authorization required. Click Review permissions and allow access to your spreadsheets and your calendars. 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 stops with "Add a Sheet tab named Events before running this function."

Rename your tab to Events, or change the tab name in the code to the name of your tab.

The run stops with "Row 2 needs a date with a time in both the start cell and the end cell."

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

The same event was created twice, or a run failed and a row has no ID.

Do not run the script again yet. In Google Calendar, go to the date and time from that row and compare each event title and time with the row. When you find the matching event, get its ID with CalendarEvent.getId() in a script and paste it into column D. The event link in your browser is not that ID. If several events match, or you cannot tell whether any of them came from this row, decide what to keep before you run again. Duplicates come from clearing IDs in column D, from two runs at the same time, and from a run that fails after creating an event.

The event landed on the wrong calendar.

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