Build a class or course timetable generator

Northwind runs a training term every autumn, and the curriculum is planned in a spreadsheet — a row per course, with the weekday and time it runs and how many weeks it lasts. Turning that plan into a real calendar means creating dozens of events by hand, which is slow and easy to get wrong by a week here or a day there.

This script does the layout for you. It reads the curriculum sheet and, for each course, creates a recurring slot in a dedicated term calendar — one event per week, on the right day, at the right time. Tutors and students subscribe to that calendar once and always see the current schedule.

What you’ll need

• A Timetable sheet with a header row and columns course, dayOfWeek (1 = Monday through 5 = Friday), hour (24-hour, e.g. 14 for 2pm) and weeks (how many weeks the course runs).
• A Google Calendar set aside for the term, and its calendar ID (from the calendar’s settings page).
• The first Monday of term as a fixed start date.

The script

// The calendar that term events are written to.
const TERM_CAL = '1abcTermCalId';

// The Monday term begins. Every course is offset from this date.
const TERM_START = new Date('2026-09-07');

// How long each class runs, in minutes.
const CLASS_LENGTH_MIN = 90;

/**
 * Reads the curriculum sheet and creates one calendar event per course
 * per week, laid out from the fixed term start date.
 */
function generateTermSchedule() {
  const courses = readSheet('1abcTimetableId');

  if (!courses.length) {
    Logger.log('No courses in the timetable — nothing to schedule.');
    return;
  }

  const cal = CalendarApp.getCalendarById(TERM_CAL);

  // Lay out every course, week by week.
  for (const c of courses) {
    for (let w = 0; w < c.weeks; w++) {
      // 1. Find the calendar date: term start + w whole weeks + weekday offset.
      const day = new Date(TERM_START);
      day.setDate(day.getDate() + w * 7 + (c.dayOfWeek - 1));

      // 2. Set the start time, then the end time CLASS_LENGTH_MIN later.
      const start = new Date(day);
      start.setHours(c.hour, 0, 0, 0);
      const end = new Date(start.getTime() + CLASS_LENGTH_MIN * 60_000);

      // 3. Create the event for this week's session.
      cal.createEvent(c.course, start, end);
    }
  }

  Logger.log('Scheduled ' + courses.length + ' courses.');
}

/**
 * Reads a sheet into an array of objects keyed by the header row.
 */
function readSheet(id) {
  const [h, ...rows] = SpreadsheetApp.openById(id).getSheets()[0]
    .getDataRange().getValues();
  return rows.map((r) => Object.fromEntries(h.map((k, i) => [k, r[i]])));
}

How it works

1. generateTermSchedule reads the Timetable sheet into an array of course objects with readSheet, and stops early if the sheet is empty.
2. It opens the term calendar by its ID, so every event lands in one place that people can subscribe to.
3. For each course it loops once per week the course runs (c.weeks).
4. It works out the date of that week’s session: it starts from TERM_START, adds w whole weeks, then adds the weekday offset (dayOfWeek - 1, so Monday adds nothing and Friday adds four days).
5. It sets the start time from the course’s hour, and the end time CLASS_LENGTH_MIN minutes later by adding milliseconds.
6. It creates a calendar event titled with the course name for each session, producing one event per course per week.

Example run

A Timetable sheet like this:

course	dayOfWeek	hour	weeks
Intro to Apps Script	2	14	3
Advanced Sheets	4	10	2

With TERM_START of Monday 7 September 2026, the script creates these events:

• Intro to Apps Script — Tue 8 Sep, Tue 15 Sep, Tue 22 Sep, all 14:00–15:30
• Advanced Sheets — Thu 10 Sep, Thu 17 Sep, both 10:00–11:30

Five events in total, laid out across the right days and weeks.

Run it

This is a once-per-term job, so run it by hand when the curriculum is final:

1. In the Apps Script editor, select generateTermSchedule and click Run.
2. Approve the calendar authorisation prompt the first time.
3. Open the term calendar to check the events landed on the expected days.

Watch out for

• The script does not clear old events first. Running it twice creates every session twice. Either run it only once, or delete the term calendar’s events before a re-run.
• dayOfWeek only handles 1–5 (Monday–Friday) as written. A weekend value still works arithmetically but may not be what you intended — validate the column.
• TERM_START must be a Monday for the dayOfWeek offset to land correctly. If term starts mid-week, adjust the offset logic to match.
• All times are interpreted in the calendar’s own time zone. Around a daylight saving change the wall-clock time can shift by an hour — check sessions that straddle the changeover.
• Creating events one at a time uses a calendar write per session. A large curriculum can be slow and may approach daily calendar quotas — split a very big term across two runs if needed.