Build a content calendar with status automation

Northwind’s content team plans every blog post and newsletter in a single Calendar sheet. The trouble is that the status column never keeps up: a post gets approved but stays marked draft, or its publish date passes and the row still says scheduled. Nobody updates statuses by hand reliably, so the calendar slowly drifts out of sync with reality.

This script keeps the status column honest. On a schedule it reads every row and moves each post one step forward when the conditions are met — an approved draft becomes scheduled, and a scheduled post whose publish date has arrived becomes published. The team gets a calendar they can trust at a glance, without anyone policing it.

What you’ll need

• A Google Sheet with a Calendar tab (the first sheet) and a header row in row 1.
• A status column holding one of draft, scheduled or published.
• An approved column — a checkbox or any truthy value once a post is signed off.
• A publishDate column containing real dates (formatted as dates, not text).
• The spreadsheet ID, which you can copy from the sheet’s URL.

The script

// The spreadsheet that holds your content calendar.
const CALENDAR_SHEET_ID = '1abcContentCalendarId';

/**
 * Walks every row of the content calendar and advances each post's status
 * one step when its conditions are met:
 *   draft + approved        -> scheduled
 *   scheduled + date passed -> published
 */
function advanceContentStatuses() {
  const sheet = SpreadsheetApp.openById(CALENDAR_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet in one call — header plus data rows.
  const values = sheet.getDataRange().getValues();
  if (values.length < 2) {
    Logger.log('No content rows to process — nothing to do.');
    return;
  }

  // 2. Split off the header and build a name -> column-index lookup,
  //    so the code never depends on a fixed column order.
  const [header, ...rows] = values;
  const col = Object.fromEntries(header.map((name, i) => [name, i]));
  const today = new Date();

  // 3. Check each row and update the status cell in place.
  let changed = 0;
  rows.forEach((row, i) => {
    const status = row[col.status];

    // An approved draft is ready to be scheduled.
    if (status === 'draft' && row[col.approved]) {
      values[i + 1][col.status] = 'scheduled';
      changed++;
    }

    // A scheduled post whose publish date has arrived is now published.
    if (
      status === 'scheduled' &&
      row[col.publishDate] instanceof Date &&
      row[col.publishDate] <= today
    ) {
      values[i + 1][col.status] = 'published';
      changed++;
    }
  });

  // 4. Write the whole grid back in one call — only the changed
  //    status cells differ from what was already there.
  sheet.getDataRange().setValues(values);
  Logger.log('Advanced ' + changed + ' post(s).');
}

How it works

1. advanceContentStatuses opens the calendar spreadsheet and reads the entire data range in a single call, which is far faster than reading cell by cell.
2. If the sheet holds only a header (or is empty), it logs a message and stops.
3. It splits the header from the data rows and builds a col lookup that maps each column name to its index — so renaming or reordering columns will not break the script, as long as the header names stay the same.
4. For each row it checks two transitions. A post marked draft with a truthy approved value moves to scheduled. A post marked scheduled whose publishDate is a real date on or before today moves to published.
5. Each change is written back into the in-memory values grid, not straight to the sheet.
6. After every row is checked, the whole grid is written back in one setValues call. Only the status cells that changed will actually differ.

Example run

Say the Calendar tab looks like this before a run, with today being 13 May:

title	status	approved	publishDate
Spring product update	draft	✓	2025-05-20
Hiring announcement	scheduled	✓	2025-05-12
Q3 roadmap teaser	draft		2025-06-01

After the script runs:

title	status	approved	publishDate
Spring product update	scheduled	✓	2025-05-20
Hiring announcement	published	✓	2025-05-12
Q3 roadmap teaser	draft		2025-06-01

The approved draft advanced to scheduled, the scheduled post whose date had passed advanced to published, and the unapproved draft was left alone.

Trigger it

Run this on a time-driven trigger so statuses stay current without anyone touching them:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose advanceContentStatuses, an Hour timer, and Every hour.
4. Save and approve the authorisation prompt.

Hourly is plenty for a content calendar — publish dates are days apart, so nothing waits long for its status to catch up.

Watch out for

• Statuses only ever move forward. The script never demotes a post, so if you un-approve something or change a date, fix the status cell by hand.
• The publishDate column must contain real dates. A date typed as text fails the instanceof Date check and the post will never advance — format the column as a date.
• The transition is one step per run, but because the trigger fires hourly an approved draft with a past publish date reaches published within two runs.
• setValues rewrites the whole grid every run. If editors are typing in the sheet at the same moment, a rare edit could be overwritten — an hourly trigger keeps that window small, but avoid running it during a busy editing session.
• The script assumes the calendar is the first sheet. If you add tabs, point getSheets()[0] at the correct one or fetch it by name with getSheetByName('Calendar').