Build a GENAI() custom function for Sheets

Northwind’s team lives in spreadsheets, and half the small writing jobs that land there — tidying a subject line, summarising a note, rephrasing a blurb — are quick for an AI but tedious by hand. Copying each cell into a chat window and pasting the answer back is the slow part, not the thinking.

This builds a custom function, =GENAI(), that calls Claude straight from a cell. Type a prompt, get the answer in the next column, and fill it down like any other formula. It also caches every result, so re-opening the sheet or copying a formula does not re-bill you for an answer Claude has already given.

What you’ll need

• An Anthropic API key stored as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.
• A bound script — open the spreadsheet, then Extensions → Apps Script — so the function is available in that sheet.
• Nothing else. CacheService is built in and needs no setup.

The function

// Default Claude model. Haiku is fast and cheap — ideal for the
// short, high-volume jobs a cell formula tends to throw at it.
const DEFAULT_MODEL = 'claude-haiku-4-5-20251001';

// How long a result stays in the cache, in seconds (6 hours).
const CACHE_TTL_SECONDS = 6 * 60 * 60;

// Token ceiling per call. Keeps replies cell-sized and costs predictable.
const MAX_TOKENS = 512;

/**
 * Calls Claude with a prompt and returns the text answer.
 * Identical prompts are served from cache, so they never re-bill.
 * @param {string} prompt - The instruction to send to Claude.
 * @param {string} [model] - Optional model override.
 * @returns {string} Claude's reply.
 * @customfunction
 */
function GENAI(prompt, model = DEFAULT_MODEL) {
  // 1. An empty cell means nothing to do — return blank, no API call.
  if (!prompt) return '';

  // 2. Build a cache key from a hash of the model and prompt, so the
  //    same request always maps to the same slot.
  const cache = CacheService.getScriptCache();
  const key = 'genai:' + Utilities.base64Encode(
    Utilities.computeDigest(
      Utilities.DigestAlgorithm.SHA_1, model + ':' + prompt));

  // 3. Serve a cached answer if we have one.
  const hit = cache.get(key);
  if (hit) return hit;

  // 4. Cache miss — call the Anthropic API. The key lives in Script
  //    Properties and is never written into the sheet or the code.
  const apiKey = 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': apiKey, 'anthropic-version': '2023-06-01' },
    payload: JSON.stringify({
      model,
      max_tokens: MAX_TOKENS,
      messages: [{ role: 'user', content: prompt }],
    }),
    muteHttpExceptions: true,
  });

  // 5. Pull the text out of the response, store it, and return it.
  const data = JSON.parse(res.getContentText());
  const text = (data.content?.[0]?.text ?? '').trim();
  cache.put(key, text, CACHE_TTL_SECONDS);
  return text;
}

How it works

1. GENAI is marked @customfunction, so Sheets exposes it as a formula you can type into any cell, just like =SUM or =VLOOKUP.
2. If the prompt cell is empty it returns an empty string straight away — no wasted API call when you fill the formula down past your data.
3. It builds a cache key by SHA-1 hashing the model and prompt together. The hash keeps the key short and unique, and including the model means switching models does not collide with an old answer.
4. It checks CacheService for that key. A hit returns instantly with no network call — this is what stops re-opens and copies from re-billing.
5. On a miss it reads the API key from Script Properties and posts the prompt to Anthropic’s Messages API. muteHttpExceptions keeps a bad response from throwing a raw error into the cell.
6. It extracts the reply text, stores it in the cache for CACHE_TTL_SECONDS, and returns it for Sheets to display.

Example run

With the function in place, put a prompt in a formula and reference your data:

A (raw note)	B (formula)	B (result)
meeting moved to thurs 3pm	=GENAI("Rewrite as a friendly subject line: " & A2)	Quick update: our meeting’s now Thursday at 3pm
client wants the logo bigger	=GENAI("Rewrite as a friendly subject line: " & A3)	A small tweak — making your logo bigger

Fill the formula down and every row gets its own answer. Edit a cell in column A and only that row re-runs; everything else is served from cache.

Use it

Type the formula straight into a cell, concatenating your instruction with a cell reference:

=GENAI("Rewrite as a friendly subject line: " & A2)

Pass a second argument to override the model for a heavier job:

=GENAI("Summarise this support thread in one sentence: " & A2, "claude-sonnet-4-6")

Watch out for

• Custom functions time out after 30 seconds. GENAI() is built for short prompts — for long jobs or large batches, use a bound script with a button or menu instead of a cell formula.
• Recalculation can fan out fast. Filling the formula down 500 rows fires 500 API calls on first run; the cache covers re-runs, not the first pass. Watch your Anthropic spend on big sheets.
• The 6-hour cache means an answer can go stale — that is the trade-off for not re-billing. Editing the upstream cell changes the prompt, so it re-runs naturally; identical prompts will not.
• CacheService values are capped at 100 KB each. The MAX_TOKENS of 512 keeps replies well under that, but raising it for verbose output could push a result past the limit and skip caching.
• Custom functions run anonymously and cannot read your identity, so the API key must come from Script Properties — it cannot be passed in as an argument.