A spreadsheet column that thinks

Why this one first

It has the highest ratio of usefulness to effort on the whole site. Most of the repetitive judgment work in a business already lives in a spreadsheet — categorizing feedback, tagging leads, cleaning job titles, deciding which support emails are urgent. This puts a model directly into that grid without asking anyone to learn a new tool, log into anything, or change how they work.

It's also the gentlest possible introduction to calling a model from code. There's no environment to set up, nothing to install, and no server. If it works, it works for everyone you share the sheet with.

Before you start

You need a Google account and an API key from any model provider. This walkthrough uses Anthropic's API; the same code works with OpenAI's by changing the URL, the headers and one line of the response parsing — noted at the bottom.

⚠ A word on cost before you drag that formula down 5,000 rows: every cell is an API call. At current small-model prices a short classification is a small fraction of a cent, so a thousand rows lands somewhere around a few cents — but a thousand rows of long text through a large model is a different story. Test on ten rows and check your usage dashboard before you scale up.

Step 1 — Open the script editor

In your Google Sheet, go to Extensions → Apps Script. A new tab opens with a file called Code.gs containing an empty myFunction. Delete everything in it.

Step 2 — Store your API key properly

Don't paste your key into the code — anyone you share the sheet with can read it. Apps Script has a proper place for secrets. In the script editor, click Project Settings (the gear), scroll to Script Properties, click Add script property, and add:

Property:  ANTHROPIC_API_KEY
Value:     sk-ant-...your key...

Save it. The code below reads the key from there.

Step 3 — The function

Paste this into Code.gs. It's the whole thing — there's nothing omitted.

/**
 * Ask an AI model a question about some cells.
 *
 * @param {string} question What to ask, e.g. "Is this angry? Answer yes or no."
 * @param {string|range} context The cell or range to ask about.
 * @return The model's answer.
 * @customfunction
 */
function ASK(question, context) {
  if (!question) return "";

  // A range comes in as a 2D array — flatten it to plain text.
  var text = Array.isArray(context)
    ? context.map(function (row) { return row.join(" | "); }).join("\n")
    : String(context == null ? "" : context);

  if (!text.trim()) return "";

  var key = PropertiesService.getScriptProperties().getProperty("ANTHROPIC_API_KEY");
  if (!key) return "Missing ANTHROPIC_API_KEY in Script Properties";

  // Cache identical questions for 6 hours so re-calculation is free.
  var cache = CacheService.getScriptCache();
  var cacheKey = Utilities.base64Encode(
    Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, question + "::" + text)
  );
  var hit = cache.get(cacheKey);
  if (hit) return hit;

  var payload = {
    model: "claude-haiku-4-5",
    max_tokens: 300,
    system: "You answer questions about spreadsheet data. Reply with the answer only " +
            "— no preamble, no explanation, no quotes. If the question asks for a " +
            "category or yes/no, reply with exactly one word.",
    messages: [{ role: "user", content: question + "\n\nData:\n" + text }]
  };

  var response = 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(payload),
    muteHttpExceptions: true
  });

  var code = response.getResponseCode();
  var body = response.getContentText();

  if (code === 429) return "Rate limited — wait a moment";
  if (code !== 200) return "Error " + code + ": " + body.slice(0, 100);

  var answer = JSON.parse(body).content[0].text.trim();
  cache.put(cacheKey, answer, 21600); // 6 hours
  return answer;
}

Save the project (the disk icon) and give it a name. Then go back to your sheet.

Step 4 — Use it

In any cell, type:

=ASK("Is this feedback positive, negative or neutral? One word.", B2)

The first time, Google will ask you to authorize the script — it needs permission to call an external service. Approve it. The cell will show Loading... for a few seconds, then the answer.

Now drag it down. A few useful variations:

=ASK("Categorise as: billing, bug, feature request, or other. One word.", B2)
=ASK("Summarise in under 12 words.", B2)
=ASK("Does this mention a competitor? If yes, name it. If no, say no.", B2)
=ASK("Rate urgency 1-5 where 5 needs a reply today. Number only.", B2:D2)

That last one passes a whole range, so the model sees several columns at once — the subject, the body and the customer tier, say.

Why the caching matters

Google Sheets recalculates custom functions far more often than you'd expect — when you edit an unrelated cell, when you reopen the file, sometimes on its own. Without the cache, every recalculation is a fresh round of API calls on every row, and your bill quietly triples for no benefit. The six-hour cache means a sheet you're actively working in only pays once per unique question.

When you genuinely want a fresh answer, change the question text slightly — the cache key includes it.

When it goes wrong

Every cell says "Loading..." forever. Google limits how many custom functions run at once. Don't drag the formula down 2,000 rows in one go — do a few hundred, let them finish, then continue. For anything really large, use the batch approach below instead.

"Error 401". The key is wrong or wasn't saved in Script Properties. Check for a trailing space.

Answers come back chatty ("Based on the feedback provided, I would categorise this as..."). Tighten the system prompt or add "One word only." to the question. Small models follow format instructions best when the instruction is the last thing they read.

You hit rate limits. That's the 429. Filling a column fires many calls at once; wait a minute and refill the failed cells, or move to the batch version.

Make it yours

Freeze the answers. Once a column is filled, select it, copy, then Paste special → Values only. Now it's data, not formulas, and it will never recalculate or cost you again. Do this for anything you're keeping.

Batch it for big sheets. Instead of one call per row, write a menu-driven function that reads 50 rows, sends them in a single request asking for 50 answers, and writes the column back. It's roughly 20× cheaper and immune to the concurrency limit. This is the natural next step once you're doing thousands of rows.

Give it your context. Put your own categories, tone rules or product names into the system prompt and it stops guessing at your business.

Use OpenAI instead. Change the URL to https://api.openai.com/v1/chat/completions, the headers to { "Authorization": "Bearer " + key }, move the system prompt into the messages array as a {role:"system"} entry, and parse JSON.parse(body).choices[0].message.content. Everything else is identical.