Generate contracts from a Sheet and a template

Northwind sends a fresh statement-of-work for every project it takes on. The wording is fixed — it is the same agreement every time — but four details change: the client, the project name, the start date, and the value. Filling those in by hand is quick once, tedious by the tenth time, and a genuine liability if someone leaves a stale value in the contract.

This script keeps the agreement as a Doc template and the deal details in a Contracts queue sheet. For every unprocessed row it copies the template, swaps the four placeholders, files the finished contract in that client’s Drive folder — creating the folder if it does not exist — and links the result back into the sheet. Because it skips rows already marked done, it is safe to run on a schedule.

What you’ll need

• A Doc template containing the placeholders {{client}}, {{project}}, {{startDate}}, and {{value}} wherever those details should appear.
• A Contracts queue Google Sheet with a header row and five columns: client, project, startDate, value, and generated (left blank — the script fills it in).
• A Drive folder that acts as the root for per-client folders.

The script

// The Doc template the script copies for each contract.
const TEMPLATE_ID = '1abcContractTemplateId';

// The sheet of pending contracts to work through.
const QUEUE_SHEET_ID = '1abcContractQueueId';

// The Drive folder under which per-client subfolders live.
const CLIENTS_ROOT_ID = '1abcClientsRootId';

// Locale and timezone for formatting dates and currency.
const DATE_FORMAT = 'd MMM yyyy';
const TIMEZONE = 'GMT';

/**
 * Generates one contract Doc per unprocessed row in the queue sheet,
 * files each under its client folder, and links it back in the sheet.
 */
function generateContracts() {
  const sheet = SpreadsheetApp.openById(QUEUE_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet and map column names to indexes.
  const values = sheet.getDataRange().getValues();
  const [h, ...rows] = values;
  const col = Object.fromEntries(h.map((k, i) => [k, i]));

  // 2. Bail out if there are no data rows.
  if (rows.length === 0) {
    Logger.log('Contracts queue is empty — nothing to do.');
    return;
  }

  let created = 0;

  // 3. Walk every row, skipping any already marked generated so the
  //    script is safe to run on a timer.
  rows.forEach((r, i) => {
    if (r[col.generated]) return;

    // 4. Find or create the client's folder, then copy the template into it.
    const folder = clientFolder(r[col.client]);
    const copy = DriveApp.getFileById(TEMPLATE_ID).makeCopy(
      `Contract — ${r[col.client]} — ${r[col.project]}`, folder);

    // 5. Open the copy and swap each placeholder, formatting the date
    //    and the value as they should read in a contract.
    const doc = DocumentApp.openById(copy.getId());
    const body = doc.getBody();
    body.replaceText('{{client}}', r[col.client]);
    body.replaceText('{{project}}', r[col.project]);
    body.replaceText('{{startDate}}',
      Utilities.formatDate(new Date(r[col.startDate]), TIMEZONE, DATE_FORMAT));
    body.replaceText('{{value}}', `£${Number(r[col.value]).toLocaleString()}`);
    doc.saveAndClose();

    // 6. Record the URL back in the in-memory grid.
    values[i + 1][col.generated] = copy.getUrl();
    created++;
  });

  // 7. Write the whole grid back in one call.
  sheet.getDataRange().setValues(values);
  Logger.log(`Generated ${created} contract(s).`);
}

/**
 * Returns the Drive folder for a client, creating it under the
 * clients root if it does not already exist.
 */
function clientFolder(name) {
  const root = DriveApp.getFolderById(CLIENTS_ROOT_ID);
  const it = root.getFoldersByName(name);
  return it.hasNext() ? it.next() : root.createFolder(name);
}

How it works

1. generateContracts opens the queue sheet, reads every row in one call, and builds a col lookup so the code refers to columns by name.
2. If there are no data rows it logs and stops.
3. It walks each row and skips any that already has a value in generated — that guard is what makes the script idempotent and safe on a schedule.
4. For a fresh row it calls clientFolder, which looks for a subfolder named after the client and creates one if it is missing, then copies the template straight into that folder.
5. It opens the copy and replaces the four placeholders. The start date is run through Utilities.formatDate so it reads as 15 Jun 2025, and the value is formatted as a pound figure with thousands separators.
6. The new Doc’s URL is written back into the in-memory grid.
7. After the loop, one setValues call commits every change, so the generated column now links each finished contract.

Example run

Say the Contracts queue sheet has these rows, with generated empty:

client	project	startDate	value	generated
Kestrel Coffee	Website rebuild	2025-07-01	18000
Harbour & Co	Brand refresh	2025-07-14	9500

After a run:

• A Doc Contract — Kestrel Coffee — Website rebuild sits in a Kestrel Coffee folder under the clients root, with the body reading “…starting 1 Jul 2025, valued at £18,000…”.
• A matching contract is filed under a Harbour & Co folder.
• The generated column holds a link for both rows, so the next run skips them.

Trigger it

Because the script only ever touches unprocessed rows, a time-based trigger keeps the queue clearing itself:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Add a trigger for generateContracts, time-driven, every 15 minutes.
3. Approve the authorisation prompt the first time.

Add a row to the sheet and within 15 minutes the contract is generated and filed. To trigger it on demand instead, add a custom menu item that calls generateContracts.

Watch out for

• The placeholders must match exactly, braces and all. {{ value }} with spaces is left untouched and ships a contract with a literal placeholder.
• startDate must be a real date value, not text. If the cell holds the string 01/07/2025, new Date may misread it — format the column as a date in the sheet so Apps Script receives a proper date object.
• value must be a number. A cell formatted as currency that contains a string like £18,000 breaks Number(...) — keep the cell numeric and let the script add the symbol.
• Folder matching is by exact name. Two clients with slightly different names (Harbour & Co vs Harbour and Co) get separate folders, and a renamed client orphans the old folder.
• A 15-minute trigger means a contract can appear before someone has finished reviewing the row. If contracts need sign-off first, switch to a manual run or add an approved column the script also checks.