Write a custom opening line per prospect

Cold outreach lives or dies on the first sentence. Northwind keeps its prospect list in a spreadsheet, and the body of the email is a solid template — but a template that opens with “Hi, I hope this finds you well” gets deleted unread. The fix is a genuine, specific first line per prospect, and writing two hundred of those by hand is nobody’s idea of a good week.

This script writes that line for every row. It reads the prospect list, hands Claude the name, company, and a scrap of context for each one, and asks for a single personalised opener of twenty words or less — no flattery — written into an opener column. You paste the body underneath and the email feels written for one person because the first sentence was.

What you’ll need

• A Google Sheet of prospects with columns headed firstName, company, context, and an empty opener column for the results.
• The context cell should hold a short, true detail — a recent launch, a job posting, a piece they published — for the opener to hook onto.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.

The script

// The spreadsheet that holds the prospect list.
const PROSPECTS_SHEET_ID = '1abcProspectsId';

// Hard cap on opener length, in words — kept short so it scans fast.
const MAX_OPENER_WORDS = 20;

/**
 * Walks the prospect list and writes a personalised opening line
 * into the opener column for every row that has none yet.
 */
function writeOpeners() {
  const sheet = SpreadsheetApp.openById(PROSPECTS_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet and split off the header row.
  const [h, ...rows] = sheet.getDataRange().getValues();

  if (!rows.length) {
    Logger.log('No prospects to process — nothing to do.');
    return;
  }

  // 2. Map header names to column indexes so column order is flexible.
  const col = Object.fromEntries(h.map((k, i) => [k, i]));

  // 3. Walk each row, skipping any that already have an opener.
  rows.forEach((r, i) => {
    if (r[col.opener]) return;

    // 4. Build a per-prospect prompt from name, company, and context.
    const prompt =
      'Write a one-sentence personalised opener (≤' + MAX_OPENER_WORDS +
      ' words) for a cold email to ' + r[col.firstName] + ' at ' +
      r[col.company] + '. Context: ' + r[col.context] + '. No flattery.';

    sheet.getRange(i + 2, col.opener + 1).setValue(callClaude(prompt));
  });
  Logger.log('Opener pass complete.');
}

/**
 * Minimal Anthropic API call. Haiku is plenty for one-line openers,
 * and the key lives in Script Properties — never in the code.
 */
function callClaude(prompt, model = 'claude-haiku-4-5-20251001', maxTokens = 400) {
  const key = PropertiesService.getScriptProperties()
    .getProperty('ANTHROPIC_API_KEY');
  const res = UrlFetchApp.fetch('https://api.anthropic.com/v1/messages', {
    method: 'post',
    contentType: 'application/json',
    headers: { 'x-api-key': key, 'anthropic-version': '2023-06-01' },
    payload: JSON.stringify({
      model,
      max_tokens: maxTokens,
      messages: [{ role: 'user', content: prompt }],
    }),
    muteHttpExceptions: true,
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. writeOpeners opens the prospect spreadsheet and reads the full data range, splitting the header row from the data.
2. If there are no data rows, it logs a message and stops.
3. It builds a name-to-index map from the header so the script does not break if the columns are reordered.
4. It walks each row, skipping any that already have an opener — so a re-run only writes new lines and never burns API calls twice.
5. For each remaining row it builds a prompt from the prospect’s first name, company, and context, capping the result at MAX_OPENER_WORDS and banning flattery so the line stays grounded.
6. It calls Claude Haiku — fast and cheap, which matters across a long list — and writes the opener straight into the opener cell.

Example run

Say the prospect sheet holds these rows:

firstName	company	context	opener
Priya	Larkfield Joinery	Just opened a second workshop in Leeds
Daniel	Coastline Tours	Hiring three new guides for summer

After a run, the opener column fills in:

firstName	opener
Priya	Saw Larkfield just opened the Leeds workshop — congratulations on the second site.
Daniel	Three new summer guides at Coastline Tours is a busy season ahead.

Each line names a real, specific detail, so the prospect can tell it was not mail-merged from a blank template.

Run it

This is an on-demand job — run it once per outreach batch:

1. In the Apps Script editor, select writeOpeners and click Run.
2. Approve the authorisation prompt the first time.
3. Read down the opener column, fix anything that misfires, then mail-merge.

To let the sales team run it themselves, add a custom menu to the sheet:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Outreach tools')
    .addItem('Write openers', 'writeOpeners')
    .addToUi();
}

Watch out for

• The opener is only as good as the context. A vague or empty context cell produces a generic line — the specific detail is what does the work.
• Always read the column before sending. Claude can misread an ambiguous context note, and a wrong fact in the first sentence is worse than a dull one.
• Filled opener cells are skipped. If you change a prospect’s context and want a fresh line, clear the opener cell first.
• A long list means a long run. Apps Script caps execution at roughly six minutes, so process very large lists in batches.