Call OpenAI from Google Apps Script: a complete tutorial

You can call OpenAI from any Google Sheet via Apps Script in under 50 lines of code. This tutorial covers the right way to do it — API key storage in PropertiesService, error handling, rate-limit retries, and the model-parameter quirks newer reasoning models impose. By the end you'll have a working =AI() formula.

This is also where the DIY path runs out of steam — and we'll cover that too.

Step 1: get an OpenAI API key

Go to platform.openai.com/api-keys → Create new secret key. Copy it (starts with sk-...). Keep it safe — anyone with this key can spend your money.

Step 2: open Apps Script

In your Google Sheet → Extensions → Apps Script. A new editor tab opens with a default Code.gs file.

Step 3: store the key in PropertiesService

Don't hardcode the key in the script. Apps Script projects can be shared or copied; the key would leak. Use PropertiesService.getUserProperties(), which is scoped to your Google account.

function setApiKey() {
  PropertiesService.getUserProperties().setProperty(
    'OPENAI_API_KEY', 'sk-...'  // paste your key here
  );
}

Run setApiKey() once from the editor (▶ Run button). Then delete the literal key from the script so it doesn't sit in your code history.

Step 4: the AI function

/**
 * Call OpenAI from a cell.
 * @param {string} prompt The prompt.
 * @param {string} [value] Optional value to act on.
 * @param {string} [model] Model name (default gpt-4o-mini).
 * @return {string} The model response.
 * @customfunction
 */
function AI(prompt, value, model) {
  const apiKey = PropertiesService.getUserProperties().getProperty('OPENAI_API_KEY');
  if (!apiKey) return '[error] API key not set — run setApiKey() once';

  const userMsg = value ? prompt + '\n\nInput: ' + value : String(prompt);
  const payload = {
    model: model || 'gpt-4o-mini',
    messages: [{ role: 'user', content: userMsg }],
  };
  // Reasoning-class models (gpt-5, o3, o4) reject the temperature parameter.
  if (!/^(gpt-5|o[1-4])/i.test(payload.model)) payload.temperature = 0.3;

  const r = UrlFetchApp.fetch('https://api.openai.com/v1/chat/completions', {
    method: 'post',
    contentType: 'application/json',
    headers: { 'Authorization': 'Bearer ' + apiKey },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true,
  });

  if (r.getResponseCode() !== 200) {
    return '[OpenAI ' + r.getResponseCode() + '] ' + r.getContentText().substring(0, 140);
  }
  const data = JSON.parse(r.getContentText());
  return data.choices[0].message.content.trim();
}

Step 5: test it

Save the script (Cmd+S). Back in your Sheet, in any cell type:

=AI("Capital of France")

You'll get a one-time authorization prompt — accept it (the script needs network access). Then the cell resolves to "Paris."

Step 6: handle rate limits

OpenAI returns 429s when you hit rate limits. Add a simple exponential backoff:

function fetchWithRetry_(url, options, maxRetries) {
  for (let i = 0; i < (maxRetries || 3); i++) {
    const r = UrlFetchApp.fetch(url, options);
    if (r.getResponseCode() !== 429) return r;
    Utilities.sleep(1000 * Math.pow(2, i));  // 1s, 2s, 4s
  }
  return UrlFetchApp.fetch(url, options);  // final attempt
}

Step 7: the 14 limits you'll hit next

Now you have one working formula. Here's what you'll discover as you push it:

1. One formula isn't enough. You'll want =AI_LIST, =AI_CLASSIFY, =AI_TABLE. Each requires its own system prompt + parsing logic.
2. Bulk recalculation is slow. A column of 1,000 cells re-running every time someone edits the workbook is painful.
3. No model switching. Hardcoded provider = lock-in. Adding Claude/Gemini means hand-writing the same wrapper for two more APIs.
4. No sidebar UI. Provider switching, model picking, Bulk runs all happen by editing the script.
5. No chat agent. If you want "ask the LLM to write a chart for me", you build the function-calling loop yourself.
6. No connectors. Stripe, HubSpot, Postgres — every integration is another set of UrlFetchApp.fetch wrappers.

If you genuinely just want one =AI() for one specific use case, the script above is all you need. The moment you find yourself building the second or third feature on top, you're rebuilding what gptsheet already ships — for less than two hours of your time at consulting rates.

Cost of DIY vs an add-on

Both end paths pay OpenAI the same list price. The difference is the engineering time you spend (or don't spend) to get there.