Intermediate · I5

Drive and Calendar

Drive and Calendar are where a teacher's term lives: folders for every class, files to sort, exam dates to add. This chapter sets up folders in one run, reports what is in them, and turns a sheet of dates into calendar events.

Iterators: one at a time

Drive methods that find several items (getFiles, getFolders, getFilesByName, searchFiles) don't return arrays. They return an iterator, which hands you one item at a time:

Code.gs

function listMyDrive() {
  const files = DriveApp.getRootFolder().getFiles();   // an iterator, not an array
  let count = 0;
  while (files.hasNext() && count < 10) {
    const f = files.next();
    console.log(`${f.getName()}  (${f.getMimeType()})`);
    count++;
  }
}

Execution log

Lesson Plan Week 1  (application/vnd.google-apps.document)
Budget 2026  (application/vnd.google-apps.spreadsheet)
report.pdf  (application/pdf)
…

The shape is always the same: while (it.hasNext()) { const x = it.next(); … }. Add a counter so a huge Drive doesn't run past the 6-minute limit.

Google type	MIME type	`MimeType.`
Docs	`application/vnd.google-apps.document`	`GOOGLE_DOCS`
Sheets	`application/vnd.google-apps.spreadsheet`	`GOOGLE_SHEETS`
Slides	`application/vnd.google-apps.presentation`	`GOOGLE_SLIDES`
Folder	`application/vnd.google-apps.folder`	`FOLDER`
PDF	`application/pdf`	`PDF`

Next: folders →

A folder for every class

Code.gs

// Make a folder per room, with the same sub-folders inside each.
function makeClassFolders() {
  const rooms = ['M.5/1', 'M.5/2', 'M.5/3'];
  const parts = ['Worksheets', 'Submissions', 'Marks'];

  const parent = getOrCreateFolder(DriveApp.getRootFolder(), 'Computing 2026');
  rooms.forEach((room) => {
    const roomFolder = getOrCreateFolder(parent, room);
    parts.forEach((p) => getOrCreateFolder(roomFolder, p));
  });
  console.log(parent.getUrl());
}

// Reuse a folder if it already exists, so running twice makes no duplicates.
function getOrCreateFolder(parent, name) {
  const found = parent.getFoldersByName(name);
  return found.hasNext() ? found.next() : parent.createFolder(name);
}

Computing 2026
├── M.5/1
│   ├── Worksheets
│   ├── Submissions
│   └── Marks
├── M.5/2   (same three)
└── M.5/3   (same three)

getOrCreateFolder is a habit worth keeping: look for the folder first and only create it if it isn't there. Run the script twice and you still have 12 folders, not 24.

Names are not unique

Drive lets two folders have the same name in the same place. Each one has its own ID, so when it matters, store and use IDs (from the URL, or folder.getId()) rather than names.

Next: files →

Create, copy, move, search

Code.gs

function fileJobs() {
  const folder = DriveApp.getFoldersByName('Computing 2026').next();

  // Create a text file.
  const notes = folder.createFile('notes.txt', 'Bring laptops on Friday.', MimeType.PLAIN_TEXT);

  // Copy, rename and move.
  const copy = notes.makeCopy('notes (backup).txt');
  copy.moveTo(folder);

  // Search with Drive's query language.
  const old = DriveApp.searchFiles("title contains 'backup' and trashed = false");
  while (old.hasNext()) console.log('Found', old.next().getName());

  // Share a file with one person (they get an email from Google).
  // notes.addViewer('ploy@school.example');
}

Execution log

Found notes (backup).txt

Method	Does
`folder.createFile(name, text, mime)`	A new file
`file.makeCopy(name, folder)`	A copy, optionally into a folder
`file.moveTo(folder)`	Move
`file.setName(name)`	Rename
`file.setTrashed(true)`	Move to Trash (recoverable for 30 days)
`file.addEditor(email)` / `addViewer`	Share with one person
`file.getAs(MimeType.PDF)`	A PDF copy as a blob, for email or saving

searchFiles takes Drive's query language: title contains 'x', mimeType = 'application/pdf', modifiedDate > '2026-01-01', trashed = false, joined with and / or.

Next: Drive to Sheet →

What is in this folder?

A report of every file in a folder and all its sub-folders, with size, date and a clickable link. Paste a folder ID from its URL:

Code.gs

// List every file in one folder (and its sub-folders) into the sheet.
function folderReport() {
  const folder = DriveApp.getFolderById('PASTE_A_FOLDER_ID_HERE');
  const rows = [];
  collect(folder, folder.getName(), rows);

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Files') || ss.insertSheet('Files');
  sheet.clearContents();
  const table = [['Path', 'Name', 'Type', 'Size (KB)', 'Updated', 'Link'], ...rows];
  sheet.getRange(1, 1, table.length, table[0].length).setValues(table);
}

function collect(folder, path, rows) {
  const files = folder.getFiles();
  while (files.hasNext()) {
    const f = files.next();
    rows.push([path, f.getName(), f.getMimeType(), Math.round(f.getSize() / 1024), f.getLastUpdated(), f.getUrl()]);
  }
  const subs = folder.getFolders();
  while (subs.hasNext()) {
    const s = subs.next();
    collect(s, `${path}/${s.getName()}`, rows);   // the function calls itself: recursion
  }
}

	A	B	C	D
1	Path	Name	Type	Size (KB)
2	Class Work	Marks M.5	…spreadsheet	0
3	Class Work/M.5/3	Worksheet 4.pdf	application/pdf	184

collect handles one folder, then calls itself for each folder inside. That is recursion: it works however deep the folders go. Google files (Docs, Sheets) report size 0 because they don't count toward storage in the same way.

Next: Calendar →

Create events

Code.gs

function addExamEvents() {
  const cal = CalendarApp.getDefaultCalendar();

  // A timed event: month is 0-based in JavaScript, so 10 = November.
  const start = new Date(2026, 10, 20, 9, 0);
  const end = new Date(2026, 10, 20, 11, 0);
  cal.createEvent('Computing exam – M.5/3', start, end, {
    location: 'Room 301',
    description: 'Bring a pencil. No phones.',
  });

  // An all-day event.
  cal.createAllDayEvent('Sports day', new Date(2026, 10, 27));
}

Months start at 0

new Date(2026, 10, 20) is 20 November, not October. Reading dates from a sheet avoids this mistake, which is one more reason to keep data in sheets.

From a sheet of dates

Make an Events tab: Title, Date, Start (a time like 9:00), End, Location, and an empty Event ID column.

Code.gs

// Sheet "Events": Title | Date | Start | End | Location | Event ID
function syncEvents() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Events');
  const cal = CalendarApp.getDefaultCalendar();
  const data = sheet.getDataRange().getValues();

  for (let i = 1; i < data.length; i++) {
    const [title, date, startTime, endTime, place, id] = data[i];
    if (!title || id) continue;                       // empty row, or already created

    const start = new Date(date);
    start.setHours(startTime.getHours(), startTime.getMinutes());
    const end = new Date(date);
    end.setHours(endTime.getHours(), endTime.getMinutes());

    const ev = cal.createEvent(title, start, end, { location: place });
    sheet.getRange(i + 1, 6).setValue(ev.getId());    // remember it: no duplicates
  }
}

The Event ID column works like the Sent column in I4: rows with an ID are skipped, so you can add rows and run it again.

Read what's coming

Code.gs

function nextWeek() {
  const now = new Date();
  const weekLater = new Date(now.getTime() + 7 * 24 * 60 * 60 * 1000);
  const events = CalendarApp.getDefaultCalendar().getEvents(now, weekLater);

  const tz = Session.getScriptTimeZone();
  events.forEach((e) => {
    const when = Utilities.formatDate(e.getStartTime(), tz, 'EEE d MMM HH:mm');
    console.log(`${when}  ${e.getTitle()}`);
  });
}

Execution log

Wed 4 Nov 15:00  Staff meeting
Thu 5 Nov 09:00  M.5 Computing
Sun 8 Nov 17:00  Parents evening

Utilities.formatDate(date, timeZone, pattern) turns a Date into text. Patterns: yyyy year, MM month number, MMM Nov, dd day, EEE Wed, HH:mm 24-hour time. Always pass Session.getScriptTimeZone(), and set the project's time zone (⚙ Project settings) to Asia/Bangkok.

Next: practice →

Practice

1. What fills my Drive? Easy

Count your files by MIME type and print the counts, most common first.

Show a solution

Code.gs

function countByType() {
  const counts = {};
  const files = DriveApp.getFiles();
  while (files.hasNext()) {
    const type = files.next().getMimeType();
    counts[type] = (counts[type] || 0) + 1;
  }
  Object.entries(counts)
    .sort((a, b) => b[1] - a[1])
    .forEach(([type, n]) => console.log(n, type));
}

2. Birthday calendar Medium

From a Birthdays tab (Name, Birthday), add a yearly repeating all-day event for each person.

Show a solution

Code.gs

// Sheet "Birthdays": Name | Birthday. Adds a yearly all-day event for each.
function addBirthdays() {
  const cal = CalendarApp.getDefaultCalendar();
  const rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Birthdays').getDataRange().getValues().slice(1);
  const year = new Date().getFullYear();
  rows.forEach(([name, bday]) => {
    if (!(bday instanceof Date)) return;
    const recur = CalendarApp.newRecurrence().addYearlyRule();
    cal.createAllDayEventSeries(`🎂 ${name}`, new Date(year, bday.getMonth(), bday.getDate()), recur);
  });
}

3. Old file clean-up Medium

List the files in a folder that haven't been changed for a year. Only trash them once the list looks right.

Show a solution

Code.gs

// Move files not touched for a year from a folder to the trash (they can be restored for 30 days).
function trashOldFiles() {
  const folder = DriveApp.getFolderById('PASTE_A_FOLDER_ID_HERE');
  const cutoff = new Date();
  cutoff.setFullYear(cutoff.getFullYear() - 1);
  const files = folder.getFiles();
  let n = 0;
  while (files.hasNext()) {
    const f = files.next();
    if (f.getLastUpdated() < cutoff) {
      console.log('Trashing', f.getName());   // check the log first, then uncomment the next line
      // f.setTrashed(true);
      n++;
    }
  }
  console.log(`${n} old files`);
}

End of the Intermediate level

Your scripts now have menus, run on triggers, and work across Sheets, Docs, Gmail, Drive and Calendar. Advanced adds Forms and Slides, your own web pages, and other websites' data.

Next: quiz →

Check yourself

1. DriveApp.getFiles() returns…

2. getFoldersByName("Marks") — why might it return more than one folder?

3. new Date(2026, 0, 15) is…

4. Why save each event ID back into the sheet?

5. collect() calls itself for each sub-folder. This is called…

6. f.setTrashed(true) does what?