Build a prompt-template library in Sheets

Once Northwind had more than a couple of AI scripts running, the prompts were a problem. The same instructions were pasted into half a dozen files, each slightly different, and tweaking the tone of a customer reply meant hunting through every script to find the wording.

This automation makes the spreadsheet the single source of truth. A Prompts sheet holds every prompt template by name; a small library reads it, caches it, and lets any script call a prompt by name with variables filled in. Edit a prompt in the sheet and every script picks up the change — no code edits, no copies drifting out of sync.

What you’ll need

• A Google Sheet with a Prompts tab. Three columns with a header row: name (the key scripts call), template (the prompt text, with {{placeholders}} for variables), and model (optional — the Claude model to use, with a default applied if blank).
• The sheet’s ID for the PROMPTS_SHEET constant.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.

The library

// The spreadsheet whose "Prompts" tab holds every template.
const PROMPTS_SHEET = '1abcPromptsId';

// Used when a prompt row leaves the "model" column blank.
const DEFAULT_MODEL = 'claude-haiku-4-5-20251001';

// How long (seconds) to cache the loaded prompts before re-reading.
const CACHE_TTL = 3600;

/**
 * Loads every prompt from the sheet into a name-keyed object.
 * Caches the result so repeated calls do not re-read the sheet.
 * @return {Object} Map of prompt name to { template, model }.
 */
function loadPrompts() {
  // 1. Return the cached copy if it is still warm.
  const cache = CacheService.getScriptCache();
  const hit = cache.get('prompts');
  if (hit) return JSON.parse(hit);

  // 2. Cache miss — read the Prompts sheet, dropping the header row.
  const [, ...rows] = SpreadsheetApp.openById(PROMPTS_SHEET)
    .getSheets()[0]
    .getDataRange()
    .getValues();

  // 3. Build a { name: { template, model } } map.
  const out = Object.fromEntries(rows
    .filter((r) => r[0])  // Skip any blank rows.
    .map((r) => [r[0], { template: r[1], model: r[2] || DEFAULT_MODEL }]));

  // 4. Cache it and return.
  cache.put('prompts', JSON.stringify(out), CACHE_TTL);
  return out;
}

/**
 * Looks up a prompt by name, substitutes variables, and calls Claude.
 * @param {string} promptName - The "name" column value to use.
 * @param {Object} vars - Values for the prompt's {{placeholders}}.
 * @return {string} Claude's reply text.
 */
function ask(promptName, vars = {}) {
  // 1. Find the template, or fail loudly if the name is unknown.
  const tpl = loadPrompts()[promptName];
  if (!tpl) throw new Error('No prompt named: ' + promptName);

  // 2. Replace every {{key}} in the template with its value.
  const prompt = Object.entries(vars).reduce(
    (text, [k, v]) => text.replaceAll('{{' + k + '}}', String(v)),
    tpl.template);

  // 3. Send the finished prompt to Claude.
  return callClaude(prompt, tpl.model);
}

/**
 * Minimal Anthropic API call. The key lives in Script Properties — it
 * is never pasted into the code.
 */
function callClaude(prompt, model) {
  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: 1000,
      messages: [{ role: 'user', content: prompt }],
    }),
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. loadPrompts first checks CacheService for a prompts entry. If one is there, it parses and returns it — no spreadsheet read at all.
2. On a cache miss it reads the Prompts sheet, drops the header row, and skips any blank rows so a stray empty line cannot break the map.
3. It builds an object keyed by the name column, each value carrying the template text and a model — falling back to DEFAULT_MODEL when the model column is empty.
4. It stores that object in the cache for CACHE_TTL seconds, so a burst of calls within the hour all hit memory instead of the sheet.
5. ask calls loadPrompts, looks up the requested prompt, and throws a clear error if the name does not exist — better a loud failure than a silent one.
6. It walks the vars object and replaces each {{key}} placeholder in the template with the supplied value, then passes the finished prompt to callClaude.
7. callClaude reads the API key from Script Properties and POSTs the prompt to Anthropic, returning the reply text.

Example run

The Prompts sheet holds:

name	template	model
customer-support-reply	Write a friendly reply to a customer about “{{subject}}”. Their message: {{body}}
episode-summary	Summarise this podcast episode in two sentences: {{transcript}}	claude-sonnet-4-6

Any script can then call a prompt by name:

function demo() {
  const reply = ask('customer-support-reply', {
    subject: 'Login issue',
    body: 'I cannot reset my password.',
  });
  Logger.log(reply);
}

ask fills {{subject}} and {{body}}, sends the prompt to Claude Haiku (the default, since that row’s model column is blank), and logs a finished support reply. Editing the wording in the sheet changes the output everywhere.

Run it

The library is not run on its own — it is called by your other scripts.

1. Add this code to a shared Apps Script project, or copy it into each project that needs prompts.
2. Fill the Prompts sheet with your templates.
3. From any function, call ask('prompt-name', { ...vars }) to get a reply.
4. To pick up a prompt edit immediately rather than waiting for the cache to expire, run a one-line function that calls CacheService.getScriptCache().remove('prompts').

Watch out for

• The cache is the trade-off. Prompt edits take up to CACHE_TTL (an hour) to appear unless you clear the cache. Lower the TTL while iterating on prompts, raise it once they settle.
• Placeholder names must match exactly. {{body}} in the template only fills if you pass a body key — a typo leaves the literal {{body}} in the prompt.
• replaceAll does a plain string swap, so unmatched {{...}} tokens pass straight through to Claude. Check your output if a template uses placeholders you did not supply.
• Every ask call is a billable Anthropic request. The cache saves sheet reads, not API calls — there is no caching of Claude’s replies here.
• The Script Cache holds at most 100KB per key. A library of very long prompts could exceed that; keep templates reasonable, or split into multiple keys.