How to Use AI with Google Apps Script
Apps Script is Sheets' automation layer — the equivalent of Excel's VBA. AI can write it (fast, imperfectly) and be called from it (to build custom AI functions). This guide covers both: generating Apps Script with AI for common automation tasks, and calling OpenAI or Gemini APIs from Apps Script to build your own AI-powered Sheets features.
⚡ Quick Answer
Two patterns: (1) AI writes Apps Script — describe automation to ChatGPT/Claude/Gemini, paste result into Extensions → Apps Script, authorize scopes, run. Always test in a copy first. (2) Apps Script calls AI APIs — use UrlFetchApp to hit OpenAI/Anthropic/Gemini APIs, store API keys in Properties Service, build custom =AI()-style functions. AI can write the glue code for both patterns.
📋 What we'll cover
The 2 AI + Apps Script patterns
Pattern 1: AI generates the script
You describe what you want automated. AI writes the Apps Script code. You paste, authorize, run. Good for occasional automation without learning Apps Script from scratch.
Pattern 2: Apps Script calls AI
You write (or AI helps you write) Apps Script that calls OpenAI, Anthropic, or Gemini APIs. Lets you build custom cell functions like =AI_CLASSIFY(A2, "positive/neutral/negative"). More setup but ultimate flexibility.
Getting AI to write Apps Script
What good AI Apps Script prompts include
- The Sheet name and column structure
- The trigger condition (when to run)
- The specific action
- What to do on success and failure
- Any relevant edge cases
- A request for a test-first helper
10 common automations AI handles well
| Automation | Difficulty |
|---|---|
| Send email from row data | Easy |
| Create calendar events from rows | Easy |
| Copy filtered rows to a new sheet | Easy |
| Trigger action when a cell changes | Medium |
| Import data from another Sheet on schedule | Medium |
| Post row data to a Slack/Teams webhook | Medium |
| Update Sheet from a form submission | Medium |
| Generate PDF from a Sheet template | Medium |
| Sync data with an external API on schedule | Hard |
| OAuth-authenticated third-party integration | Hard |
Complex OAuth flows, third-party API authentication (especially newer OAuth 2.0 with PKCE), obscure Advanced Services, edge cases in Sheets' recalculation model, and rate-limit handling for very high-volume automations. For these, expect to iterate on the AI-generated code more than usual.
Calling AI APIs from Apps Script
Custom AI functions in Sheets come from Apps Script that calls an AI API. Here's the pattern for OpenAI:
function aiCall(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}]
};
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());
return data.choices[0].message.content;
}
Store the API key in Script Properties (Project Settings → Script Properties). Never hard-code it in the script itself.
Building custom cell functions
Apps Script functions with no side effects can be called from cells as custom functions.
function AI_CLASSIFY(text, categories) {
if (!text) return '';
const prompt = `Classify this text as one of: ${categories}
Text: ${text}
Return only the category name.`;
return aiCall(prompt);
}
Use in a cell:
=AI_CLASSIFY(A2, "positive, neutral, negative")
Custom functions have a 30-second execution limit and can't send emails or trigger UI actions. They also don't cache — every recalculation calls the AI API again. For bulk operations, prefer a full script that iterates rows once.
Safety and testing
Never run untested Apps Script on production data
Make a copy of your Sheet. Run the script on the copy first. Inspect results before promoting.
Read the script before authorizing
Apps Script's authorization prompt lists all requested permissions. Make sure they match what the script needs.
Add a dry-run mode
Every script that modifies data should have a DRY_RUN = true constant that skips the actual mutation and just logs what would happen.
Set up error notifications
Apps Script triggers can email you on script errors. Enable this for scheduled scripts so failures don't go unnoticed.
Rate-limit AI API calls
Add Utilities.sleep(500) between API calls in bulk scripts to avoid hitting rate limits.
Sheets Wizard — AI without writing Apps Script
Sheets Wizard provides cell-level =AI() functions without you writing Apps Script or managing API keys. Same capabilities as custom Apps Script AI functions — plus better rate-limit handling, caching, and error messages.
Use Sheets Wizard for standard AI-in-cell workflows. Reach for custom Apps Script when you need highly specific behavior or want to build integrations with third-party APIs.
Install Sheets Wizard →Frequently asked questions
Can AI write Google Apps Script?
Yes. Any AI tool that writes code (ChatGPT, Claude, Gemini, GitHub Copilot) writes Apps Script well. The pattern: describe what you want the script to do, paste any relevant Sheets structure, get working code. Paste it into Extensions → Apps Script, authorize the scopes it needs, run. Test in a copy of your sheet before running on production data.
Is AI-generated Apps Script safe?
AI-generated scripts require the same caution as any script. Common issues: wrong OAuth scopes, missing error handling, use of deprecated APIs, incorrect assumptions about data ranges. Always: (1) read the script before running, (2) test in a copy of your sheet first, (3) check what permissions it's requesting, (4) verify APIs against the current Apps Script documentation.
Can I call OpenAI or Gemini from Apps Script?
Yes. Apps Script can call any REST API including OpenAI, Anthropic, and Gemini APIs. This is the pattern for building your own custom AI functions in Sheets. Store the API key in Properties Service (not hard-coded), use UrlFetchApp to call the API, parse the JSON response. AI can write this glue code for you.