Build a GENAI_ARRAY() function for whole columns

A per-cell AI formula is handy, but on a long column it is wasteful — twenty rows means twenty separate API calls, twenty round trips, and twenty chances to hit a rate limit. When Northwind needs the same instruction applied to a whole list, that is a lot of overhead for one job.

This builds =GENAI_ARRAY(), a custom function that takes a prompt and a range. It sends the entire range to Claude as a single numbered list, gets one numbered list back, and spills the answers down the column. One call, one prompt, the whole column answered at once.

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.
• A column of values to process, with an empty column beside it for the result to spill into.

The function

// Claude model used for the batch. Haiku keeps a whole-column call
// fast and cheap.
const MODEL = 'claude-haiku-4-5-20251001';

// Token ceiling for the batched reply. Must be large enough to hold
// one answer per row — raise it for long columns or long answers.
const MAX_TOKENS = 4000;

/**
 * Applies one prompt to an entire range in a single Claude call and
 * spills the answers down a column.
 * @param {string} prompt - The instruction to apply to every value.
 * @param {Array<Array>} range - The cell range to process.
 * @returns {Array<Array<string>>} One answer per input row.
 * @customfunction
 */
function GENAI_ARRAY(prompt, range) {
  // 1. Nothing selected — return blank rather than erroring.
  if (!range) return '';

  // 2. Flatten the 2-D range to a list and drop blank cells.
  const flat = [].concat.apply([], range).filter(Boolean);
  if (!flat.length) return '';

  // 3. Number each value so answers can be matched back to rows.
  const numbered = flat.map((v, i) => `${i + 1}. ${v}`).join('\n');
  const full = `${prompt}\n\n` +
    `Return one answer per line, prefixed with its number.\n\n${numbered}`;

  // 4. Send the whole list to Claude in a single request.
  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: MODEL,
      max_tokens: MAX_TOKENS,
      messages: [{ role: 'user', content: full }],
    }),
  });

  // 5. Split the reply into lines, strip the "1. " prefixes, and
  //    return a column (one answer per row) for Sheets to spill.
  const text = JSON.parse(res.getContentText()).content[0].text;
  return text.split('\n')
    .map((l) => l.replace(/^\d+\.\s*/, '').trim())
    .filter(Boolean)
    .map((v) => [v]);
}

How it works

1. GENAI_ARRAY is marked @customfunction, so Sheets accepts it as a formula. Unlike a per-cell function, it takes a whole range as its second argument.
2. If the range is missing it returns blank, and if every cell is empty it does the same — no point billing for an empty list.
3. It flattens the 2-D range into a flat list, drops blanks, then rebuilds it as a numbered list. The numbers are the trick that lets answers be lined back up with their source rows.
4. It wraps the numbered list in the prompt, adding an instruction to return one numbered answer per line, and posts the lot to Claude in a single request.
5. It splits the reply on newlines, strips each 1. prefix with a regex, drops any blank lines, and wraps each answer in its own array.
6. Returning an array of single-cell rows is what makes the result spill — Sheets fills the answers straight down the column from the formula cell.

Example run

Put values in column A and the formula in B2:

A (input)	B (spilled result)
Quarterly strategy workshop	event
Annual support contract	service
Wireless desk lamp	product
Logo redesign package	service

The formula =GENAI_ARRAY("Categorise each as 'product', 'service', or 'event':", A2:A5) sits in B2, fires one API call, and the four answers spill down B2:B5 automatically.

Use it

Type the formula into the top cell of an empty column:

=GENAI_ARRAY("Categorise each as 'product', 'service', or 'event':", A2:A20)

It spills down the column — one API call processes the whole range.

Watch out for

• The reply must keep one answer per line, in order. If Claude merges two answers or adds a stray blank line, the spilled column drifts out of alignment with the source rows. Keep the prompt’s instruction explicit.
• MAX_TOKENS caps the whole reply, not each row. A long column can be truncated mid-list — raise MAX_TOKENS, or split the range and call the function twice.
• Custom functions time out at 30 seconds. A single batched call is well within that for normal ranges, but a very large range with verbose answers can run long — batch it.
• The spilled range needs empty cells below the formula. If anything is in the way, Sheets shows a #REF! spill error until you clear it.
• This call has no caching, unlike a per-cell GENAI(). Every recalculation re-runs the whole batch, so reach for it on stable data, not a column you edit constantly.