How to Connect Google Sheets to an AI Model Without Zapier

You connect Google Sheets to an AI model without Zapier by calling the model’s API directly from Apps Script — Sheets’ built-in scripting layer — using UrlFetchApp to send a request and write the response back into a cell. No middleware account, no task quota, no monthly fee for the connector itself. You write maybe 20 lines of JavaScript once, and it runs from a custom formula, a menu button, or a trigger.

This works for OpenAI, Anthropic, Gemini, or any REST API that accepts a POST request with a JSON body — which is all of them. Below is the actual setup, plus where it breaks down and what to use instead when it does.

Why skip Zapier for this specific job

Zapier is built for connecting apps that don’t expose an easy scripting surface — Slack to Trello, Typeform to Notion, that kind of thing. Google Sheets isn’t one of those apps. It ships with Apps Script, a full JavaScript runtime attached to every spreadsheet, with an HTTP client (UrlFetchApp) built in. Routing a Sheets-to-API call through Zapier means paying for and configuring a tool to do something Sheets already does natively.

The practical costs of the Zapier route: each row processed burns a task from your plan’s monthly allowance, multi-step Zaps (read row → call AI → parse response → write back) usually need the paid tier to chain more than one action, and every run is capped by Zapier’s own execution time limit — batches of dozens or hundreds of rows can stall out mid-run. The Apps Script route has none of that. Your only limits are Google’s own script execution quotas (6 minutes per execution on a consumer account, 30 on Workspace) and whatever rate limit the AI provider sets on your API key.

Method 1: A custom function that calls the AI model live

This is the fastest way to get an answer in a cell. Open Extensions → Apps Script from your spreadsheet, delete the boilerplate, and paste something like this:

function ASK_AI(prompt) {
  const apiKey = PropertiesService.getScriptProperties().getProperty('OPENAI_API_KEY');
  const url = 'https://api.openai.com/v1/chat/completions';

  const payload = {
    model: 'gpt-4o-mini',
    messages: [{ role: 'user', content: prompt }],
    temperature: 0.3
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + apiKey },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };

  const response = UrlFetchApp.fetch(url, options);
  const data = JSON.parse(response.getContentText());

  if (data.error) return 'ERROR: ' + data.error.message;
  return data.choices[0].message.content.trim();
}


Save it, then in any cell type `=ASK_AI(A1)` where `A1` holds a prompt. Sheets treats `ASK_AI` like `=SUM()` or `=VLOOKUP()` — it recalculates when the input changes, and you can drag-fill it down a column.

Two things that trip people up here. First, custom functions can't make external requests until you authorize the script — the first run prompts a Google OAuth consent screen, which is expected, not a bug. Second, custom functions recalculate automatically whenever the sheet recalculates, which means editing an unrelated cell can silently re-trigger every `ASK_AI` call in the sheet and burn API usage you didn't intend to spend. For anything beyond a handful of test rows, that's a reason to move to a triggered batch script instead of a live formula.

## Storing the API key without hardcoding it

Never paste the key directly into the script body — anyone with edit access to the spreadsheet can open the script editor and read it. Use **Project Settings → Script Properties** in the Apps Script editor to store `OPENAI_API_KEY` (or `ANTHROPIC_API_KEY`, `GEMINI_API_KEY`) as a key-value pair outside the visible code. The `PropertiesService.getScriptProperties().getProperty(...)` call in the snippet above reads it from there. If you ever need to share the sheet with a collaborator, they get the formula but not the credential.

## Method 2: Batch-process a whole column with a menu button

Live formulas are fine for testing, but for processing 100+ rows — summarizing feedback, tagging support tickets, classifying leads — a one-shot batch script run from a custom menu is more reliable and easier to rate-limit deliberately.

```javascript
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('AI Tools')
    .addItem('Process Column B', 'batchProcess')
    .addToUi();
}

function batchProcess() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  const inputs = sheet.getRange(2, 2, lastRow - 1, 1).getValues();
  const apiKey = PropertiesService.getScriptProperties().getProperty('OPENAI_API_KEY');

  for (let i = 0; i < inputs.length; i++) {
    const text = inputs[i][0];
    if (!text) continue;

    const result = callModel(text, apiKey);
    sheet.getRange(i + 2, 3).setValue(result);
    Utilities.sleep(300); // stay under per-minute rate limits
  }
}

function callModel(text, apiKey) {
  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + apiKey },
    payload: JSON.stringify({
      model: 'gpt-4o-mini',
      messages: [{ role: 'user', content: 'Classify this feedback as Positive, Negative, or Neutral: ' + text }],
      temperature: 0
    }),
    muteHttpExceptions: true
  };
  const response = UrlFetchApp.fetch('https://api.openai.com/v1/chat/completions', options);
  const data = JSON.parse(response.getContentText());
  return data.error ? 'ERROR' : data.choices[0].message.content.trim();
}


This adds a menu item to the sheet ("AI Tools → Process Column B") that pulls every row in column B, sends each one to the model, and writes the result in column C. The `Utilities.sleep(300)` between calls exists specifically to avoid hitting the provider's requests-per-minute cap on a free or low tier — without it, a script looping through 200 rows in a couple of seconds is a common way to trip a rate limit mid-run and end up with half the sheet processed.

## Method 3: Trigger it automatically on edit or on a schedule

If you want new rows to get processed without opening the script manually, Apps Script has two trigger types worth knowing:

- **`onEdit` trigger** — runs a function whenever a cell changes. Good for "process this row the moment someone fills it in," but risky if the AI call is slow, since `onEdit` triggers have a hard execution ceiling and a failed run can leave the sheet in an inconsistent state.
- **Time-driven trigger** — set from **Triggers** (clock icon) in the Apps Script editor to run `batchProcess` every hour, every morning, or on whatever cadence fits. This is the steadier option for anything beyond a quick demo, and it mirrors the same idea covered in [How to Schedule a ChatGPT Prompt to Run Every Morning Automatically](/posts/schedule-chatgpt-prompt-every-morning) — same mechanism, just running inside Sheets instead of a separate scheduler.

For time-driven triggers, add a check so the script skips rows that already have a result in column C, otherwise every scheduled run reprocesses the entire sheet from scratch.

## Getting structured output back into separate columns

Classifying text into one word is easy — the model's response goes straight into a cell. Extracting multiple fields (sentiment, category, priority) into separate columns needs the model to return valid JSON, which it won't always do reliably from a loose prompt alone. The fix is the same one covered in [How to Write a System Prompt That Forces JSON-Only Output](/posts/system-prompt-force-json-only-output): a strict system message plus a rigid schema in the prompt, then `JSON.parse()` on the response inside the Apps Script function before writing each field to its own column with `setValue()`.

## When Apps Script isn't the right tool anymore

This approach is built for one spreadsheet talking to one API. It stops being the right tool once you need any of the following: pulling data from Sheets into a database or CRM as part of the same flow, branching logic based on the AI's response (route to a different action depending on the classification), or processing volumes large enough that Apps Script's 6-minute execution cap becomes a real constraint even with batching.

At that point, a self-hosted automation tool like n8n is the more honest comparison to Zapier — not Apps Script. If you're already leaning that way, [How to Process a Large CSV in N8n Without Timing Out](/posts/n8n-process-large-csv-batches-without-timeout) covers the same batching-and-rate-limit problem from that side, and it's worth reading before committing to either path.

## A quick sanity check before you build on this

Test the `ASK_AI` custom function on five rows first, confirm the output format is what you expect, and only then wire up the trigger or the menu button for the full sheet. API responses vary in structure between providers and even between model versions from the same provider — parsing code written for one model's response shape can throw silently on another, and the cheapest place to catch that is five test rows, not five hundred.