Build a contract-renewal calendar

Northwind’s retainer income depends on contracts being renewed on time, but the renewal dates live in a spreadsheet nobody opens between quarters. By the time someone notices a contract is up, the conversation is rushed — or worse, the renewal has already lapsed. Recurring revenue quietly leaks away because the dates were never anywhere the team actually looks.

This script puts those dates where the team will see them. It reads a Contracts sheet and creates an all-day calendar event for each renewal, so the date shows up alongside everyone’s normal schedule. The sales team gets a natural prompt to start the renewal conversation early, instead of finding out after the fact.

What you’ll need

• A Contracts sheet with a header row and columns client, renewalDate and eventId (leave eventId blank; the script fills it in).
• A Google Calendar to hold the renewal events — a dedicated one keeps them separate from personal schedules.
• The Contracts spreadsheet ID and the renewal calendar’s ID, both copied from their respective URLs.

The script

// The calendar that holds the renewal events.
const RENEWAL_CALENDAR_ID = '1abcRenewalCalId';

// The spreadsheet that holds the contract records.
const CONTRACTS_SHEET_ID = '1abcContractsId';

/**
 * Reads the Contracts sheet and creates an all-day calendar event for every
 * contract that has a renewal date but no event yet. The new event's ID is
 * written back so the contract is never double-booked on a later run.
 */
function syncRenewals() {
  const sheet = SpreadsheetApp.openById(CONTRACTS_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 contracts to sync — nothing to do.');
    return;
  }

  // 2. Split off the header and build a name -> column-index lookup.
  const [header, ...rows] = values;
  const col = Object.fromEntries(header.map((name, i) => [name, i]));

  // 3. Open the renewal calendar once, up front.
  const cal = CalendarApp.getCalendarById(RENEWAL_CALENDAR_ID);
  if (!cal) {
    Logger.log('Renewal calendar not found — check RENEWAL_CALENDAR_ID.');
    return;
  }

  // 4. Create an event for each contract that needs one.
  let created = 0;
  rows.forEach((row, i) => {
    // Skip rows without a real renewal date.
    if (!(row[col.renewalDate] instanceof Date)) return;

    // Skip rows that already have an event — keeps the script re-runnable.
    if (row[col.eventId]) return;

    // Create an all-day event and record its ID back on the row.
    const event = cal.createAllDayEvent(
      'Renewal: ' + row[col.client],
      row[col.renewalDate]
    );
    values[i + 1][col.eventId] = event.getId();
    created++;
  });

  // 5. Write the sheet back so the new event IDs are saved.
  sheet.getDataRange().setValues(values);
  Logger.log('Created ' + created + ' renewal event(s).');
}

How it works

1. syncRenewals opens the Contracts spreadsheet and reads the whole data range in one call.
2. If the sheet has only a header it logs a message and stops.
3. It splits off the header and builds a col lookup, so the script does not depend on a fixed column order.
4. It opens the renewal calendar once and bails out with a clear message if the ID is wrong, rather than failing obscurely later.
5. For each row it applies two guards: it skips any row whose renewalDate is not a real date, and any row that already has an eventId. That second guard is what makes the script safe to run repeatedly — an already-synced contract is never given a duplicate event.
6. For each remaining row it creates an all-day event titled Renewal: plus the client name on the renewal date, and stores the new event’s ID back in the eventId cell.
7. After every row is processed it writes the sheet back in one call so the new IDs are saved.

Example run

Say the Contracts sheet looks like this before a run:

client	renewalDate	eventId
Harbour Coffee	2025-12-01
Meridian Books	2026-01-15	[email protected]
Glasshouse Studio	2025-11-20

After the script runs, two new all-day events appear on the renewal calendar — Renewal: Harbour Coffee on 1 December and Renewal: Glasshouse Studio on 20 November — and the sheet records their IDs:

client	renewalDate	eventId
Harbour Coffee	2025-12-01	[email protected]
Meridian Books	2026-01-15	[email protected]
Glasshouse Studio	2025-11-20	[email protected]

Meridian Books already had an eventId, so it was left untouched — no duplicate event was created.

Trigger it

Run this on a time-driven trigger so newly added contracts get an event without anyone remembering to sync:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose syncRenewals, a Day timer, and an early-morning slot.
4. Save and approve the authorisation prompt.

A daily run is plenty — renewal dates are months away, so a one-day delay before an event appears makes no difference.

Watch out for

• The renewalDate column must hold real dates. A date typed as text fails the instanceof Date check and that contract will never get an event — format the column as a date.
• The script only ever creates events. If you change a contract’s renewalDate, the old event is not moved — delete it and clear the eventId cell so the next run creates a fresh one.
• Likewise, deleting a contract row leaves its event on the calendar. Remove stale events by hand, or extend the script to reconcile deletions.
• All-day events have no built-in reminder. Add a default notification on the renewal calendar, or set reminders on the events, so the date actually prompts someone.
• setValues rewrites the whole sheet each run. Avoid editing the Contracts sheet while the script runs, or an in-flight edit could be overwritten — a daily off-hours trigger keeps that window small.