How to create recurring Google Calendar events from Google Sheets

Why not just import a CSV?

Google Calendar’s import (Settings → Import & export) reads a CSV with columns such as Subject, Start Date, Start Time, End Date and End Time. There is no column for repetition, so every row becomes exactly one event. To get a weekly meeting for a year you would need 52 rows, and the result is 52 unrelated events: change the time once and you change it 52 times.

A recurring event is different. It’s one series with a rule (“weekly on Tuesday, 20 times”), and Google Calendar lets you edit “this event”, “this and following” or “all events”. Two ways to create real series from a sheet: an Apps Script, below, or an add-on that reads a Repeat column. An iCalendar (.ics) file with RRULE lines also works, but building one by hand from a sheet is fiddly and still imports as a one-off copy.

Set up the sheet

One row per series, with these headers in row 1:

• Start and End must be real dates with times, not text. Type them as shown, or build them from separate columns with =B2+C2 (a date plus a time), and format the column as Format → Number → Date time. If a cell is left-aligned, Sheets is treating it as text.
• No End gives a one-hour event. No End and no time on the Start (midnight) gives an all-day series, as for Rent due and the birthday.
• Times is the total number of occurrences, the first one included. Until is the last date. Fill in one or neither; if both are filled, Times wins.
• Series ID is filled in by the script. Leave it empty.

Repeat rules you can use

The script reads plain English in the Repeat column. Case doesn’t matter.

Rules like “the second Tuesday of every month” or “the last Friday” aren’t in this list. Create those series in Google Calendar directly (Does not repeat → Monthly on the second Tuesday), or see the add-on below, which accepts a raw rule.

The free script

Paste it into the spreadsheet’s script editor. The only setting is the calendar.

// Sheet rows -> repeating Google Calendar events (one series per row).
// Header in row 1:
// A Title | B Start | C End | D Repeat | E Times | F Until | G Series ID (the script fills this in)
// Start/End: real date-time cells. A Start with no time and an empty End makes an all-day series.
// Repeat: daily, weekdays, weekly, weekly on Mon, Wed, Fri, every 2 weeks, every other week, monthly, yearly
// Times: how many occurrences in total (optional). Until: last date (optional). Leave both empty to repeat
// with no end date (Google Calendar stops a series at 730 occurrences).
const CALENDAR_ID = 'primary';
const DAYS = { sun: 'SUNDAY', mon: 'MONDAY', tue: 'TUESDAY', wed: 'WEDNESDAY', thu: 'THURSDAY', fri: 'FRIDAY', sat: 'SATURDAY' };

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Calendar')
    .addItem('Send repeating events to Calendar', 'sendRecurringToCalendar')
    .addToUi();
}

function parseRepeat_(text) {   // "every 2 weeks on Mon, Wed" -> { unit: 'week', every: 2, days: ['MONDAY', 'WEDNESDAY'] }
  const s = String(text).toLowerCase().trim().replace(/^every other /, 'every 2 ');
  if (/^(every )?weekdays?$/.test(s)) return { unit: 'week', every: 1, days: ['MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY'] };
  const m = s.match(/^(?:(daily|weekly|monthly|yearly|annually)|every (?:(\d+) )?(day|week|month|year)s?)(?: on (.+))?$/);
  if (!m) return null;
  const word = { daily: 'day', weekly: 'week', monthly: 'month', yearly: 'year', annually: 'year' };
  const r = { unit: m[1] ? word[m[1]] : m[3], every: Number(m[2] || 1), days: [] };
  if (m[4]) {
    for (const d of m[4].split(/[\s,&]+/)) {
      if (!d || d === 'and') continue;
      const key = d.slice(0, 3);
      if (!DAYS[key]) return null;
      r.days.push(DAYS[key]);
    }
  }
  if (r.days.length && r.unit !== 'week') return null;   // "on Monday" only makes sense for weekly rules
  return r;
}

function buildRecurrence_(rep, times, until) {
  const rec = CalendarApp.newRecurrence();
  const add = { day: 'addDailyRule', week: 'addWeeklyRule', month: 'addMonthlyRule', year: 'addYearlyRule' }[rep.unit];
  let rule = rec[add]();
  if (rep.every > 1) rule = rule.interval(rep.every);
  if (rep.days.length) rule = rule.onlyOnWeekdays(rep.days.map((d) => CalendarApp.Weekday[d]));
  if (times) rule = rule.times(times);
  else if (until) rule = rule.until(new Date(until.getFullYear(), until.getMonth(), until.getDate(), 23, 59, 59));
  return rec;
}

function sendRecurringToCalendar() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const last = sheet.getLastRow();
  if (last < 2) return;
  const cal = CALENDAR_ID === 'primary' ? CalendarApp.getDefaultCalendar() : CalendarApp.getCalendarById(CALENDAR_ID);
  const rows = sheet.getRange(2, 1, last - 1, 7).getValues();
  const isDate = (v) => v && typeof v.getTime === 'function' && !isNaN(v.getTime());
  let created = 0, updated = 0;
  const problems = [];
  const ids = rows.map(function (r, i) {
    const [title, start, end, repeat, times, until, id] = r;
    if (!title) return [id];
    const rep = parseRepeat_(repeat);
    if (!isDate(start) || !rep) { problems.push('row ' + (i + 2) + (rep ? ': Start is not a date' : ': Repeat not understood')); return [id]; }
    const rec = buildRecurrence_(rep, Number(times) || 0, isDate(until) ? until : null);
    const allDay = !isDate(end) && start.getHours() === 0 && start.getMinutes() === 0;
    const stop = isDate(end) ? end : new Date(start.getTime() + 60 * 60 * 1000);   // no end: 1 hour
    let series = id ? cal.getEventSeriesById(String(id)) : null;
    if (series) {                     // edited row: change the whole series in place
      series.setTitle(title);
      if (allDay) series.setRecurrence(rec, start); else series.setRecurrence(rec, start, stop);
      updated++;
    } else {
      series = allDay ? cal.createAllDayEventSeries(title, start, rec) : cal.createEventSeries(title, start, stop, rec);
      created++;
    }
    return [series.getId()];
  });
  sheet.getRange(2, 7, ids.length, 1).setValues(ids);
  SpreadsheetApp.getUi().alert(created + ' series created, ' + updated + ' updated.' + (problems.length ? '\nSkipped ' + problems.join('; ') : ''));
}

For each row with a title it reads the Repeat text, builds the rule with Calendar’s recurrence tools, and creates one series. The series ID goes into column G, which is how the script knows next time that the row has already been sent. Rows it can’t understand are listed at the end, for example Skipped row 7: Repeat not understood, and nothing is created for them.

Install and run it

1. In the spreadsheet, choose Extensions → Apps Script, replace the sample code with the script, and click Save.
2. Optional: to use another calendar, copy its ID from Google Calendar (the calendar’s Settings and sharing → Integrate calendar) and paste it in place of 'primary'. Testing on a separate calendar first is the safe way: if something is wrong, delete that calendar and every series goes with it.
3. Reload the spreadsheet. A Calendar menu appears.
4. Open the tab with your series and choose Calendar → Send repeating events to Calendar. The first time, Google asks you to authorise the script; since it’s your own unpublished script, click Advanced and then Go to your project.
5. A message reports, for example, 5 series created, 0 updated. Open Google Calendar and check a few weeks ahead.

Changing a series later

Edit the row and run the menu again. Because the Series ID is there, the script updates the existing series instead of creating a new one: new title, new times, new rule, new end. Two things to know:

• It changes every occurrence. Editing in the sheet is the same as choosing “All events” in Calendar. Changes you made to single occurrences in Calendar (one moved meeting, one cancelled class) are replaced by the rule.
• Deleting a row doesn’t delete the series. Delete it in Calendar (click an occurrence, delete, All events), then clear the row.

To stop a series from a date onwards and keep the past, set Until to the last date you want and run the menu again.

Limits and surprises

• 730 occurrences at most. Google Calendar’s help says repeating events have a limit of 730 occurrences. A daily series with no end stops after two years; a weekday series after about 2.8 years. Use Until for anything that should run longer, and plan to extend it.
• Make Start the first real occurrence. For “weekly on Tue, Thu”, put the first Tuesday or Thursday in Start. A Start on a Monday doesn’t match the rule, and calendar apps don’t agree on whether that first, off-pattern date is included, so you may see an extra or a missing first event.
• Monthly on the 29th, 30th or 31st skips months that don’t have that day: a series on the 31st has no event in April, June, September or November. For bills due at the end of the month, use the 1st of the next month, or create the series in Calendar with “Monthly on the last day”.
• Time zones. Dates in the sheet are read in the script’s time zone. For a script created from the spreadsheet, that is the spreadsheet’s zone at the time you created it. If events are an hour off, compare File → Settings in the sheet with Project Settings in Apps Script. Daylight saving changes are handled by the calendar: a 9:00 meeting stays at 9:00 local time all year.
• Guests and reminders aren’t set by this script. Add them once to the series in Calendar, or extend the script with series.addGuest() and series.addPopupReminder().

The other way: one row per date

Sometimes separate events are what you want, for example a rota where each date may be swapped later. Generate the dates with a formula and send them as ordinary events. Every Tuesday for 16 weeks from the date in B2:

=ARRAYFORMULA(B2 + 7 * SEQUENCE(16, 1, 0))

Every weekday between two dates in B2 and C2:

=FILTER(SEQUENCE(C2 - B2 + 1, 1, B2), WEEKDAY(SEQUENCE(C2 - B2 + 1, 1, B2), 2) <= 5)

Format the results as dates, add a title column next to them, and send them with the free script in our Sheets-to-Calendar guide or a CSV import. Each date is then its own event, which is easy to change one at a time but has to be changed one at a time.

A Repeat column without code: CalendarRows Pro

CalendarRows sends rows to Google Calendar from a sidebar, and on Pro it reads a Repeat column, so a row becomes a repeating event in the same send as your one-off events.

• Plain rules: daily, weekly, monthly or yearly, optionally with a count or an end, such as weekly x 12, daily 10 times or monthly until 2027-06-30.
• Anything else as a raw iCalendar rule, such as RRULE:FREQ=WEEKLY;BYDAY=TU,TH or RRULE:FREQ=MONTHLY;BYDAY=2TU for the second Tuesday of each month.
• Preview checks every rule before anything is created and names the row with a rule it can’t read.
• Send again after an edit and CalendarRows updates the event it created for that row instead of adding another.

On the free plan, a column called Repeat is still recognised, and a row with a rule in it is not sent: its status reads “Repeating events are part of CalendarRows Pro.”. Everything else, including single events, guests, colours and updates, works on Free.

Common problems

• “Skipped row N: Start is not a date”. The cell is text. Re-type it, or convert with =DATEVALUE() plus =TIMEVALUE() in a helper column and paste back as values.
• “Repeat not understood”. Check the spelling against the table. Days must follow “on” (weekly on Tue), and days only work with weekly rules.
• Every series appears twice. The Series ID column was cleared or overwritten, so the script created new ones. Delete the duplicates in Calendar (“All events”) and keep the IDs in column G.
• All-day series turned into one-hour events. The Start has a time other than midnight. Use a date without a time for all-day series.
• “Action not allowed” or a permissions error. You don’t have edit rights to that calendar. Use a calendar you own, or ask its owner for “Make changes to events”.

Frequently asked questions

Related

• How to create Google Calendar events from Google Sheets
• How to put a shift schedule from Google Sheets into Google Calendar
• How to send Google Form responses to Google Calendar
• CalendarRows documentation
• All guides

Disclosure: we make CalendarRows, the Google Sheets add-on mentioned in this guide. We describe the other methods as fairly as we can, including when they are the better choice.

Google Sheets and Google Calendar are trademarks of Google LLC. CalendarRows and Cellbrook are not affiliated with or endorsed by Google.