Build a proposal generator with dynamic pricing

Every Northwind proposal is the same shape — a client name, a list of work items, prices, and a total — but building each one by hand is slow and error-prone. Quote the wrong unit price, fumble the sum, and the proposal goes out wrong. Worse, when prices change, old templates keep the old numbers.

This script keeps pricing in one place and assembles the document. A Pricing sheet holds the rate for every work type; a Proposals queue sheet lists the clients waiting for a quote and their chosen line items. For each queued row the script copies a Doc template, builds a priced line-item table, fills in the total, and writes the finished Doc’s URL back to the queue.

What you’ll need

• A Google Doc template containing the placeholders {{client}} and {{total}} where you want those values to land.
• A Proposals queue sheet with a header row and columns: client, lineItems (a JSON array such as [{"code":"DESIGN","qty":3}]), and output (left blank — the script writes the Doc URL here).
• A Pricing sheet with a header row and columns: code, description, and unitPrice. Each code matches the codes used in the queue’s lineItems.
• The template Doc, queue sheet, and pricing sheet IDs for the config constants. Finished Docs are created in your Drive root.

The script

// File IDs for the template Doc and the two sheets.
const TEMPLATE = '1abcProposalTemplateId';  // Doc with {{client}} / {{total}}.
const PROPOSALS = '1abcProposalsQueueId';   // The "Proposals queue" sheet.
const PRICING = '1abcPricingId';            // The "Pricing" sheet.

/**
 * Processes every unprocessed row in the proposals queue: builds a
 * priced Doc and writes its URL back to the "output" column.
 */
function generateProposals() {
  // 1. Load pricing into a code-keyed lookup for fast access.
  const pricing = Object.fromEntries(
    readSheet(PRICING).map((p) => [p.code, p]));

  // 2. Read the queue, splitting off the header row.
  const sheet = SpreadsheetApp.openById(PROPOSALS).getSheets()[0];
  const values = sheet.getDataRange().getValues();
  const [header, ...rows] = values;
  const col = Object.fromEntries(header.map((k, i) => [k, i]));

  // 3. Bail out early if there is nothing queued.
  if (!rows.length) {
    Logger.log('Proposals queue is empty — nothing to do.');
    return;
  }

  // 4. Build a proposal for each row that has no output yet.
  let built = 0;
  rows.forEach((r, i) => {
    if (r[col.output]) return;  // Already done — skip it.

    const items = JSON.parse(r[col.lineItems] || '[]');
    const file = buildProposal(r[col.client], items, pricing);

    // Write the URL straight back into the in-memory values array.
    values[i + 1][col.output] = file.getUrl();
    built++;
  });

  // 5. Write the whole sheet back once, so URLs are saved.
  sheet.getDataRange().setValues(values);
  Logger.log('Built ' + built + ' proposal(s).');
}

/**
 * Copies the template Doc, builds a priced line-item table, fills in
 * the client name and total, and returns the new file.
 * @param {string} client - Client name for this proposal.
 * @param {Object[]} items - Line items: { code, qty }.
 * @param {Object} pricing - Code-keyed pricing lookup.
 * @return {File} The finished proposal Doc.
 */
function buildProposal(client, items, pricing) {
  // 1. Copy the template and open the copy for editing.
  const copy = DriveApp.getFileById(TEMPLATE).makeCopy('Proposal — ' + client);
  const doc = DocumentApp.openById(copy.getId());
  const body = doc.getBody();
  body.replaceText('{{client}}', client);

  // 2. Start a table with a header row.
  let total = 0;
  const table = body.appendTable([['Item', 'Qty', 'Unit', 'Total']]);

  // 3. Add one priced row per line item.
  for (const it of items) {
    const p = pricing[it.code];
    if (!p) continue;  // Unknown code — skip rather than guess.

    const lineTotal = p.unitPrice * it.qty;
    total += lineTotal;

    table.appendTableRow()
      .appendTableCell(p.description)
      .getParent().appendTableCell(String(it.qty))
      .getParent().appendTableCell('£' + p.unitPrice)
      .getParent().appendTableCell('£' + lineTotal);
  }

  // 4. Fill in the grand total and save.
  body.replaceText('{{total}}', '£' + total.toLocaleString());
  doc.saveAndClose();
  return copy;
}

/**
 * Reads a sheet's first tab and returns its rows as objects keyed
 * by the header row.
 * @param {string} id - Spreadsheet ID.
 * @return {Object[]} One object per data row.
 */
function readSheet(id) {
  const [headers, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((r) =>
    Object.fromEntries(headers.map((k, i) => [k, r[i]])));
}

How it works

1. generateProposals reads the Pricing sheet through readSheet and turns it into a pricing lookup keyed by code, so any rate is one property access away.
2. It reads the proposals queue, splits off the header row, and builds a col map of column name to index — so the rest of the code reads col.client rather than guessing positions.
3. If the queue has no data rows, it logs and stops.
4. For each queued row it skips any that already have an output URL, parses the row’s lineItems JSON, and calls buildProposal. The resulting URL is written into the in-memory values array.
5. After the loop it writes the whole values array back to the sheet in one setValues call, so every new URL is saved together.
6. buildProposal copies the template Doc, replaces {{client}}, and appends a four-column table.
7. For each line item it looks up the price by code, skips unknown codes, multiplies unitPrice by qty for the line total, and adds a table row.
8. It accumulates the running total, replaces {{total}} with the formatted sum, saves the Doc, and returns the file.

Example run

The Pricing sheet:

code	description	unitPrice
DESIGN	Brand design day	650
DEV	Development day	550

A queued row for “Riverside Ltd” with lineItems of [{"code":"DESIGN","qty":3},{"code":"DEV","qty":2}] produces a Doc whose table reads:

Item	Qty	Unit	Total
Brand design day	3	£650	£1950
Development day	2	£550	£1100

The {{total}} placeholder becomes £3,050, and the queue’s output cell fills with the new Doc’s URL.

Trigger it

Run this on a short schedule so queued proposals turn into Docs without anyone watching:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add trigger. Choose generateProposals, a Time-driven source, and a Minutes timer set to every 10 minutes.
3. Save and approve the authorisation prompt.
4. Add a row to the proposals queue and confirm a Doc URL appears within ten minutes.

Watch out for

• The lineItems cell must be valid JSON. A stray quote or trailing comma makes JSON.parse throw and stops the whole run — validate the field, or wrap the parse in try/catch to skip just the bad row.
• Unknown codes are silently skipped. If a queue row references a code that is not in the Pricing sheet, that line simply vanishes from the table — the proposal looks complete but is under-priced. Log skipped codes if that worries you.
• Prices are read at run time, so changing the Pricing sheet only affects proposals built after the change. Already-generated Docs keep their old numbers.
• The currency symbol is hard-coded as £. Prices are not rounded either, so a decimal unitPrice shows every digit — format with .toFixed(2) if you quote pence.
• Every run copies the template Doc, so the queue and your Drive both grow. Clear processed rows, or move finished Docs to a dedicated folder, to keep things tidy.