Schedule content as a publishing calendar

Northwind plans content in a spreadsheet — one row per post or video, with the channel, publish date, and status. It is a perfectly fine plan, but it lives in a tab no one opens until Friday, so the team only finds out what is going live this week when the deadline is already on top of them.

This script pushes the plan onto a dedicated content calendar as all-day events, one per row. Now the same plan shows up alongside meetings — visible, shareable, draggable. Edit the date in the sheet, the event moves. Add a row, a new event appears. The sheet stays the source of truth.

What you’ll need

• A Content sheet with these headers in row 1: title, channel, publishDate, status, eventId. Paste its ID into CONTENT_SHEET.
• A separate calendar to use as the publishing calendar — easier to hide/share than putting events on your default one. Paste its ID into CONTENT_CAL. Find it under Settings → Integrate calendar for that calendar.
• publishDate should be a real Date (format the column as Date) — strings are skipped silently.

The script

// The dedicated calendar that displays the content plan as events.
const CONTENT_CAL = '1abcContentCalId';

// The spreadsheet that holds the content plan.
const CONTENT_SHEET = '1abcContentSheetId';

/**
 * Mirrors each row of the Content sheet as an all-day event on the
 * dedicated publishing calendar. Stores the event ID back to the sheet
 * so subsequent runs update the same event instead of duplicating.
 */
function syncContentCalendar() {
  const sheet = SpreadsheetApp.openById(CONTENT_SHEET).getSheets()[0];
  const values = sheet.getDataRange().getValues();
  if (values.length < 2) {
    Logger.log('No content rows — nothing to sync.');
    return;
  }

  // 1. Split off the header and build a name -> column-index map.
  const [header, ...rows] = values;
  const col = Object.fromEntries(header.map((k, i) => [k, i]));
  const cal = CalendarApp.getCalendarById(CONTENT_CAL);
  if (!cal) {
    throw new Error('Cannot open calendar: check CONTENT_CAL id.');
  }

  // 2. Walk each row. Skip rows with no publishDate (drafts in planning).
  rows.forEach((r, i) => {
    if (!(r[col.publishDate] instanceof Date)) return;
    const title = `${r[col.channel]}: ${r[col.title]}`;

    if (r[col.eventId]) {
      // 3a. Existing event — move it to the current publishDate, in case
      //     the row was rescheduled in the sheet since last run.
      const ev = cal.getEventById(r[col.eventId]);
      if (ev) {
        ev.setAllDayDate(r[col.publishDate]);
      } else {
        // Stored ID no longer resolves (event manually deleted). Recreate.
        const fresh = cal.createAllDayEvent(title, r[col.publishDate]);
        values[i + 1][col.eventId] = fresh.getId();
      }
    } else {
      // 3b. New row — create the event and capture its ID for next time.
      const ev = cal.createAllDayEvent(title, r[col.publishDate]);
      values[i + 1][col.eventId] = ev.getId();
    }
  });

  // 4. Persist any newly captured eventIds in a single write.
  sheet.getDataRange().setValues(values);
  Logger.log('Synced ' + rows.length + ' content row(s).');
}

How it works

1. syncContentCalendar opens the content spreadsheet and bails if no data rows exist.
2. It builds a col map from the header row so the rest of the script reads r[col.publishDate] instead of numeric indices.
3. For each row, it skips anything without a real publishDate — that way draft rows still being scoped do not show up on the calendar.
4. If the row already has an eventId, it looks the event up and moves it to the current publishDate. If the lookup fails (someone deleted the event in the calendar), it recreates the event and writes the new ID back.
5. If the row has no eventId, it creates a new all-day event titled channel: title and stores the ID.
6. After the loop, one setValues call persists every newly-captured ID.

Example run

Before the run, the Content sheet looks like this:

title	channel	publishDate	status	eventId
Behind the camera: edit room	YouTube	2025-08-12	Scheduled
Fabrikam case study	Blog	2025-08-14	Draft
Trailer cut	Instagram	2025-08-18	Scheduled	[email protected]
Q4 podcast lineup	Newsletter		Backlog

After a run on the dedicated publishing calendar:

• YouTube: Behind the camera: edit room appears on 12 Aug.
• Blog: Fabrikam case study appears on 14 Aug.
• Instagram: Trailer cut already had an event — it stays put.
• The newsletter row is skipped because publishDate is blank.

And the sheet now has IDs in column E for the first three rows. Reschedule the Fabrikam case study to 21 Aug in the sheet, run again, and the existing event moves — no duplicate appears.

Trigger it

Run this overnight so sheet edits flow onto the calendar by morning:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Add a trigger for syncContentCalendar, 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 if the team needs instant sync, but a single nightly run keeps the noise down.

Watch out for

• The sync is one-way. Drag an event on the calendar and the next run will move it back to the sheet’s date. Treat the sheet as the source of truth and the calendar as a view.
• Title and channel changes after first sync update on the next run only if you also call ev.setTitle(title) next to setAllDayDate. The current script intentionally only moves the date — add the title update if your team renames rows often.
• Deleting a row in the sheet does not delete its event. Add a Status = Cancelled check that calls ev.deleteEvent() if you want hands-off cleanup.
• The whole grid is rewritten via setValues. Schedule the trigger when no one is editing the sheet so concurrent changes are not lost.