Build a contract-clause assembly system

Every Northwind client agreement is mostly the same — payment terms, scope, liability, IP — assembled from a handful of clauses the team has already had a lawyer approve. The slow, risky part is the assembly: someone copies clauses out of an old contract, hopes they grabbed the current wording, and pastes them into a new Doc. One stale paragraph and the agreement is wrong.

This script makes assembly a lookup instead of a copy-paste job. You keep every approved clause in a Clauses sheet, and for each new contract you list the client and the clause IDs you want. The script builds the Doc — pulling the current, approved wording for each clause — drops it in a Drive folder, and writes the link back. The clause library is the single source of truth.

What you’ll need

• A Clauses sheet with a header row and columns id, title, text — one approved clause per row.
• A Contracts to build sheet with a header row and columns client, clauseIds (a comma-separated list of clause IDs) and output (left blank; the script fills it with the Doc link).
• A Drive folder to hold the generated contracts.
• The IDs of both sheets and the folder, copied from their URLs.

The script

// The sheet that holds the library of approved clauses.
const CLAUSES_SHEET_ID = '1abcClausesId';

// The sheet listing contracts still to be built.
const QUEUE_SHEET_ID = '1abcContractQueueId';

// The Drive folder the finished contract Docs are moved into.
const OUTPUT_FOLDER_ID = '1abcContractsFolderId';

/**
 * Reads the build queue and, for each unbuilt row, assembles a contract Doc
 * from the listed clause IDs, files it in the output folder, and writes the
 * Doc link back to the queue.
 */
function assembleContracts() {
  // 1. Load the clause library into an {id: clause} lookup.
  const clauses = Object.fromEntries(
    readSheet(CLAUSES_SHEET_ID).map((c) => [c.id, c])
  );

  // 2. Read the build queue — header plus data rows.
  const sheet = SpreadsheetApp.openById(QUEUE_SHEET_ID).getSheets()[0];
  const values = sheet.getDataRange().getValues();
  if (values.length < 2) {
    Logger.log('No contracts queued — nothing to build.');
    return;
  }
  const [header, ...rows] = values;
  const col = Object.fromEntries(header.map((name, i) => [name, i]));
  const folder = DriveApp.getFolderById(OUTPUT_FOLDER_ID);

  // 3. Build each row that does not yet have an output link.
  let built = 0;
  rows.forEach((row, i) => {
    if (row[col.output]) return; // already built — skip

    // Parse the comma-separated clause IDs into a clean list.
    const ids = String(row[col.clauseIds])
      .split(',')
      .map((s) => s.trim())
      .filter(Boolean);

    // Create the Doc and move it straight into the contracts folder.
    const doc = DocumentApp.create('Contract — ' + row[col.client]);
    DriveApp.getFileById(doc.getId()).moveTo(folder);

    // Title, then each clause as a heading-2 followed by its text.
    const body = doc.getBody();
    body
      .appendParagraph('Agreement — ' + row[col.client])
      .setHeading(DocumentApp.ParagraphHeading.TITLE);

    for (const id of ids) {
      const clause = clauses[id];
      if (!clause) {
        Logger.log('Unknown clause id "' + id + '" — skipped.');
        continue;
      }
      body
        .appendParagraph(clause.title)
        .setHeading(DocumentApp.ParagraphHeading.HEADING2);
      body.appendParagraph(clause.text);
    }

    doc.saveAndClose();

    // Record the Doc link back on the queue row.
    values[i + 1][col.output] = doc.getUrl();
    built++;
  });

  // 4. Write the queue back so the output links are saved.
  sheet.getDataRange().setValues(values);
  Logger.log('Built ' + built + ' contract(s).');
}

/**
 * Reads a sheet and returns its rows as an array of {column: value} objects.
 *
 * @param {string} id - The spreadsheet ID to read.
 * @return {Object[]} One object per data row.
 */
function readSheet(id) {
  const [header, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((row) =>
    Object.fromEntries(header.map((name, i) => [name, row[i]]))
  );
}

How it works

1. assembleContracts first calls readSheet on the Clauses sheet and builds an {id: clause} lookup, so any clause can be fetched instantly by ID.
2. It reads the build queue. If the queue has only a header it logs a message and stops.
3. It builds a col lookup from the queue header so the script does not depend on a fixed column order.
4. For each queue row it checks output first — a row that already has a link is skipped, which makes the whole script safe to re-run.
5. It splits the clauseIds cell on commas, trims each ID and drops any blanks, producing a clean ordered list.
6. It creates a new Doc named after the client and immediately moves it into the output folder so finished contracts never clutter the Drive root.
7. It writes a title, then walks the clause IDs in order. For each known ID it appends the clause title as a heading and the clause text as a paragraph; an unknown ID is logged and skipped rather than crashing the run.
8. It saves the Doc and records its URL back into the queue row’s output cell.
9. After every row is processed it writes the whole queue back in one call.

Example run

The Clauses sheet holds the approved library:

id	title	text
PAY	Payment terms	Invoices are due within 14 days…
IP	Intellectual property	On final payment, IP transfers to the client…
LIA	Limitation of liability	Northwind’s liability is capped at fees paid…

The Contracts to build sheet has one queued row:

client	clauseIds	output
Harbour Coffee	PAY, IP, LIA

After a run, the output cell holds a link to a new Doc in the contracts folder, titled Agreement — Harbour Coffee, containing the Payment terms, Intellectual property and Limitation of liability clauses in that order — each with its approved wording lifted straight from the library.

Run it

Contracts are built in batches when deals are signed, so run this on demand:

1. Add a row to the Contracts to build sheet for each new contract, filling in client and clauseIds and leaving output blank.
2. In the Apps Script editor, select assembleContracts and click Run.
3. Approve the authorisation prompt the first time.
4. Open the link that appears in each output cell.

To let non-coders trigger it, add an onOpen custom menu so Assemble contracts appears in the spreadsheet itself.

Watch out for

• The clause library is the single source of truth — but only if it stays current. Update wording in the Clauses sheet, never in a generated Doc, or the next contract will use the old text.
• An ID in clauseIds that does not exist in the library is logged and skipped silently. Check the execution log after a run so a missing clause never slips into a signed contract.
• Clause order follows the order of IDs in the clauseIds cell — list them in the sequence you want them to appear.
• setValues rewrites the whole queue. Avoid editing the queue sheet while the script runs, or an in-flight edit could be overwritten.
• Generated agreements still need a human (and, for anything unusual, a lawyer) to review them. This automates assembly, not legal sign-off.
• Creating and moving a Doc per row is comparatively slow; a large batch may approach the six-minute execution limit. Split very large queues across several runs.