Mirror project deadlines as calendar events

Northwind’s project list lives in a spreadsheet — every active job with its client, brief, and due date in one row. That sheet is the source of truth, but it is invisible from the calendar where the team actually plans their days. Deadlines slip past because they were never alongside the meetings.

This script mirrors each project’s due date as an all-day calendar event, then remembers the event ID so future runs update the same event instead of creating duplicates. Edit the date in the sheet, the calendar follows. Add a new row, a new event appears.

What you’ll need

• A Projects sheet with these headers in row 1: project, client, dueDate, eventId. Paste the spreadsheet ID into PROJECTS_SHEET_ID.
• The dueDate column must contain real dates (format the column as Date in the sheet) — string dates will be ignored by the instanceof Date guard.
• Leave eventId blank for new rows. The script fills it in on the first run.

The script

// The spreadsheet that holds the Projects list. Headers in row 1 must be
// project, client, dueDate, eventId.
const PROJECTS_SHEET_ID = '1abcProjectsId';

/**
 * Mirrors each row of the Projects sheet as an all-day calendar event on
 * the user's default calendar. Stores the event ID back to the sheet so
 * subsequent runs update the same event rather than creating duplicates.
 */
function syncProjectDeadlines() {
  const sheet = SpreadsheetApp.openById(PROJECTS_SHEET_ID).getSheets()[0];
  const values = sheet.getDataRange().getValues();
  if (values.length < 2) {
    Logger.log('No project rows — nothing to sync.');
    return;
  }

  // 1. Split off the header and build a name -> column-index map. That way
  //    the rest of the script reads as r[col.dueDate] rather than r[2].
  const [header, ...rows] = values;
  const col = Object.fromEntries(header.map((k, i) => [k, i]));
  const cal = CalendarApp.getDefaultCalendar();

  // 2. Walk each row. Skip anything without a real Date in dueDate.
  rows.forEach((r, i) => {
    const due = r[col.dueDate];
    if (!(due instanceof Date)) return;

    const title = `Deadline: ${r[col.project]} (${r[col.client]})`;

    if (r[col.eventId]) {
      // 3a. Existing event — move it to the current dueDate, in case the
      //     date changed in the sheet since the last run.
      const ev = cal.getEventById(r[col.eventId]);
      if (ev) {
        ev.setAllDayDate(due);
      } else {
        // The stored ID no longer exists (event deleted in calendar).
        // Recreate and record the new ID.
        const fresh = cal.createAllDayEvent(title, due);
        values[i + 1][col.eventId] = fresh.getId();
      }
    } else {
      // 3b. New row — create a fresh all-day event and remember its ID.
      const ev = cal.createAllDayEvent(title, due);
      values[i + 1][col.eventId] = ev.getId();
    }
  });

  // 4. Write the whole grid back so any newly-captured eventIds persist.
  sheet.getDataRange().setValues(values);
  Logger.log('Synced ' + rows.length + ' project row(s).');
}

How it works

1. syncProjectDeadlines reads the entire Projects sheet and bails if there are no data rows.
2. It builds a col map from the header so the rest of the script names columns instead of indexing by number.
3. For each row, it skips any without a real Date in dueDate — strings and blanks slip past harmlessly.
4. If the row already has an eventId, it looks the event up and moves it to the current date with setAllDayDate. If the stored ID has been deleted in the calendar, it recreates the event and updates the row.
5. If the row has no eventId, it creates a new all-day event and writes the ID back into the values grid in memory.
6. After the loop, a single setValues call writes the entire grid back — so every newly-captured ID persists.

Example run

Before the run, the Projects sheet looks like this:

project	client	dueDate	eventId
Spring campaign	Fabrikam	2025-09-15
Brochure refresh	Contoso	2025-09-22
Website launch	Adventure Works	2025-10-01	[email protected]

After a run, three all-day events appear on the calendar — Deadline: Spring campaign (Fabrikam) on 15 September, and so on — and the sheet’s eventId column is filled in:

project	client	dueDate	eventId
Spring campaign	Fabrikam	2025-09-15	[email protected]
Brochure refresh	Contoso	2025-09-22	[email protected]
Website launch	Adventure Works	2025-10-01	[email protected]

Change the Website launch date to 8 October in the sheet, run again, and the existing event moves to the new date — same ID, no duplicate.

Trigger it

Run this on a schedule so sheet edits make it onto the calendar without anyone remembering:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Add a trigger for syncProjectDeadlines, time-driven, day timer, very early (3am–4am).
3. Save and approve the calendar and spreadsheet authorisation prompts.

You can also wire it to onEdit for instant sync, but the daily trigger is cheaper and less noisy.

Watch out for

• The sync is one-way, sheet to calendar. Move an event by dragging it on the calendar and the next run will yank it back to the sheet’s date. If you want calendar edits to win, store the calendar date too and compare before deciding which to keep.
• Deleting a row in the sheet does not delete the event. Add an archived column and check for it in the loop if you want hands-off cleanup — or delete events manually when you delete the row.
• setAllDayDate only updates the date; the title stays as it was when the event was first created. To rename, call ev.setTitle(title) next to the setAllDayDate call.
• The whole grid is rewritten via setValues. If anyone else is editing the sheet at the same moment, their change will be overwritten. Run the trigger overnight to avoid this.