Generate tailored cover letters from a profile

A Northwind hire chasing freelance briefs faces the same chore every week: write a fresh cover letter for each opening. The letters are 90% the same — the same voice, the same closing — and 10% specific: the company name, the role, and one line that shows you actually read the brief. Retyping the shared part is wasted effort, and copy-pasting it invites the classic mistake of leaving last week’s company name in this week’s letter.

This script keeps the openings in a Sheet and the letter in a Doc template. For every row that has not been done yet, it copies the template, swaps the three placeholders for that opening’s details, and writes the new Doc’s URL back into the sheet. Run it whenever you have added openings — it only ever touches the new rows.

What you’ll need

• An Openings Google Sheet with a header row and four columns: company, role, hook (the one tailored line for that job), and output (left blank — the script fills it in).
• A cover letter Doc template containing the placeholders {{company}}, {{role}}, and {{hook}} wherever those details should appear.

The script

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

// The sheet of job openings to work through.
const OPENINGS_SHEET_ID = '1abcOpeningsId';

/**
 * Generates one cover letter Doc per unprocessed row in the Openings
 * sheet, then records each Doc's URL back in the "output" column.
 */
function generateCovers() {
  const sheet = SpreadsheetApp.openById(OPENINGS_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet, split off the header, and map column
  //    names to indexes so the code reads by name, not by number.
  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 at all.
  if (rows.length === 0) {
    Logger.log('No openings to process — nothing to do.');
    return;
  }

  let created = 0;

  // 3. Walk every row. Skip any that already has an output URL, so
  //    re-running the script never duplicates a letter.
  rows.forEach((r, i) => {
    if (r[col.output]) return;

    // 4. Copy the template into a clearly named Doc.
    const copy = DriveApp.getFileById(TEMPLATE_ID).makeCopy(
      `Cover — ${r[col.company]} — ${r[col.role]}`);

    // 5. Open the copy and swap each placeholder for this row's value.
    const doc = DocumentApp.openById(copy.getId());
    const body = doc.getBody();
    body.replaceText('{{company}}', r[col.company]);
    body.replaceText('{{role}}', r[col.role]);
    body.replaceText('{{hook}}', r[col.hook]);
    doc.saveAndClose();

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

  // 7. Write the whole grid back in one call so the output column sticks.
  sheet.getDataRange().setValues(values);
  Logger.log(`Generated ${created} cover letter(s).`);
}

How it works

1. generateCovers opens the Openings sheet and reads every row into memory in one getDataRange call.
2. It splits the header off and builds a col lookup, so the rest of the code can say r[col.company] instead of remembering that company is column 0.
3. If there are no data rows it logs and stops.
4. For each row, it skips any that already has a value in output — that is the guard that makes the script safe to run again and again.
5. For a fresh row, it copies the template Doc, names the copy after the company and role, and opens it.
6. replaceText swaps {{company}}, {{role}}, and {{hook}} for the row’s values. The copy is saved and its URL stored back into the in-memory grid.
7. After the loop, one setValues call writes the whole grid back, so the output column now holds a link for every row processed.

Example run

Say the Openings sheet starts like this, with output empty:

company	role	hook	output
Brightside Studio	Brand designer	your rebrand for Kestrel Coffee caught my eye
Harbour & Co	Content strategist	I loved the tone of your sustainability report

After a run, the sheet has links in output, and two new Docs exist:

• Cover — Brightside Studio — Brand designer, where the letter body now reads “…your rebrand for Kestrel Coffee caught my eye…”
• Cover — Harbour & Co — Content strategist, tailored with its own hook.

Add a third opening next week, run the script again, and only that third row gets a new Doc — the first two are skipped.

Run it

This is an on-demand job — run it after you add openings:

1. In the Apps Script editor, select generateCovers and click Run.
2. Approve the authorisation prompt the first time.
3. Open the links that appear in the output column.

To run it from the sheet without opening the editor, add a custom menu:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Cover letters')
    .addItem('Generate covers', 'generateCovers')
    .addToUi();
}

Watch out for

• The placeholders in the template must match exactly, braces included. {{ company }} with spaces will not be replaced and you will mail a letter with a literal placeholder in it.
• replaceText swaps every occurrence. If {{company}} appears in both the address block and the body, both are filled — usually what you want, but worth knowing.
• New Docs land in your My Drive root. To file them, pass a folder to makeCopy as a second argument, or move the file with DriveApp afterwards.
• The script trusts the sheet. A blank hook cell leaves an empty line where the tailored sentence should be — check rows are complete before running.
• Deleting a row’s output value and re-running will generate a second copy of that letter. Clear output cells only when you genuinely want a rebuild.