Gemini · Automating with apps script and workspace
Google Apps Script plus the Gemini APIAPIApplication Programming Interface: a standardised interface that lets applications communicate and exchange data without knowing each other's internal workings.View full definition → can turn Workspace from a place you do work into a place that does work for you. A script can auto-label incoming email by intent, draft replies, or compile a weekly Sheet report from raw rows without you touching it. Apps Script already lives inside Gmail, Sheets, Drive, and Calendar with authenticated access to your data, and Gemini gives it judgment. You wire the two together with a single HTTP call.
This lesson covers the wiring, a concrete working example, and the guardrails that matter once the script runs unattended on a trigger.
Why Apps Script is the right glue
Apps Script is Google's serverless JavaScript runtime built into Workspace. You do not provision anything. A script can read your Gmail threads (GmailApp) and write to a spreadsheet (SpreadsheetApp). It can also run on a time-based trigger, all under your account's permissions.
What it does not have natively is reasoning. That is the Gemini part. You call the Gemini API from inside the script with `UrlFetchApp`, send it text, and get structured output back.
There are two ways to authenticate the call:
- An API key from Google AI Studio is the fastest way to start. It works well for personal automations and prototypes.
- Vertex AI with a service account is the right choice when the automation belongs to an organization or needs IAM controls, data residencydata residencyThe requirement that data is physically stored and processed in a specific country or region, often driven by law or contract.View full definition →, or VPC boundaries. See cloud.google.com/vertex-ai.
For a personal weekly report, the API key path is fine. The example below uses it and flags where you would switch.
The example: a weekly support-email report
The goal: every Monday at 7am, scan last week's emails labeled `support`, have Gemini classify each one by category and urgency, and append a tidy summary row to a tracking Sheet.
The example is reliable because it uses structured output. You ask Gemini to return JSON matching a schemaschemaA schema is the formal blueprint that defines how data is structured, named, typed, and related within a database, file, or message.View full definition → instead of prose. The Gemini API supports a responseSchema, which constrains the model to valid JSON. That removes the fragile step of parsing the model's paragraph.
The classification call
This is the core: one function that sends a batch of email subjects and snippets and gets back typed classifications.
const GEMINI_KEY = PropertiesService.getScriptProperties().getProperty('GEMINI_KEY');
const MODEL = 'gemini-flash-latest';
function classifyEmails(emails) {
const url = `https://generativelanguage.googleapis.com/v1beta/models/${MODEL}:generateContent?key=${GEMINI_KEY}`;
const prompt = 'Classify each support email. Categories: billing, bug, feature_request, other. ' +
'Urgency: low, medium, high.\n\n' +
emails.map((e, i) => `[${i}] Subject: ${e.subject}\nSnippet: ${e.snippet}`).join('\n\n');
const payload = {
contents: [{ parts: [{ text: prompt }] }],
generationConfig: {
responseMimeType: 'application/json',
responseSchema: {
type: 'array',
items: {
type: 'object',
properties: {
index: { type: 'integer' },
category: { type: 'string', enum: ['billing', 'bug', 'feature_request', 'other'] },
urgency: { type: 'string', enum: ['low', 'medium', 'high'] }
},
required: ['index', 'category', 'urgency']
}
}
}
};
const res = UrlFetchApp.fetch(url, {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify(payload),
muteHttpExceptions: true
});
if (res.getResponseCode() !== 200) {
throw new Error(`Gemini API ${res.getResponseCode()}: ${res.getContentText()}`);
}
const body = JSON.parse(res.getContentText());
return JSON.parse(body.candidates[0].content.parts[0].text);
}Notes a senior engineer would care about:
- The key lives in Script Properties, never in source. Set it once under Project Settings, or run
PropertiesService.getScriptProperties().setProperty('GEMINI_KEY', '...'). gemini-flash-latestis an alias that points at the current Flash model. Flash is fast and cheap, and classification is exactly what it does well. Reserve Pro for long synthesis or hard multi-step judgment.- The alias protects an unattended script from model retirements. Google shuts down older versions on a published schedule, and a script pinned to a retired name fails silently every Monday. The trade-off is that behavior can shift when the alias moves. If you need reproducible output, pin a specific version from the models page and check the deprecations list every few months.
muteHttpExceptions: truelets you read the error body instead of getting a generic throw. You will want that text in your logs.- Batch the emails into one call. Sending one request per email burns quota and time. One prompt with all of last week's snippets costs far less, and the schema keeps results aligned by
index.
Wiring it into gmail and sheets
The orchestration around the call is plain Apps Script:
function weeklyReport() {
const since = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000);
const query = `label:support after:${Utilities.formatDate(since, 'GMT', 'yyyy/MM/dd')}`;
const threads = GmailApp.search(query, 0, 50);
const emails = threads.map(t => {
const msg = t.getMessages()[0];
return { subject: msg.getSubject(), snippet: msg.getPlainBody().slice(0, 400) };
});
if (emails.length === 0) return;
const results = classifyEmails(emails);
const sheet = SpreadsheetApp.openById('YOUR_SHEET_ID').getSheetByName('Weekly');
const counts = { billing: 0, bug: 0, feature_request: 0, other: 0, high: 0 };
results.forEach(r => {
counts[r.category]++;
if (r.urgency === 'high') counts.high++;
});
sheet.appendRow([new Date(), emails.length, counts.billing, counts.bug,
counts.feature_request, counts.other, counts.high]);
}Then set a trigger so it runs unattended. In the editor, click the clock icon (Triggers), or create the trigger once in code:
function installTrigger() {
ScriptApp.newTrigger('weeklyReport')
.timeBased().onWeekDay(ScriptApp.WeekDay.MONDAY).atHour(7).create();
}Run installTrigger once. From then on, `weeklyReport` fires every Monday morning with no human in the loop. That is the whole point, and it is also exactly where the risk begins.
GuardrailsGuardrailsRules and controls that keep an AI system inside safe, legal and on-brand boundaries, blocking outputs and actions that cross the line.View full definition → for unattended runs
A script that runs while you sleep, holds your Gmail scope, and calls an external model needs discipline. The failure modes differ from interactive prompting because nobody is watching the output.
1. Cap the blast radius
This example only reads Gmail and appends to a Sheet. It never deletes, sends, or modifies threads. Keep automations read-and-append by default. Once a script can send email or delete files unattended, a bad model output or a bug becomes an action you cannot undo.
If you move on to auto-replies or auto-labeling that moves mail, add a dry-run mode: write the proposed actions to a Sheet for a week and review them before letting the script execute for real.
2. Request only the scopes you need
Apps Script infers OAuth scopes from the APIs you call. Pin them explicitly in the manifest so a stray new function cannot silently widen access. In appsscript.json:
{
"oauthScopes": [
"https://www.googleapis.com/auth/gmail.readonly",
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/script.external_request"
]
}With gmail.readonly, even a buggy version cannot send or delete mail. That single line is your strongest guardrail.
3. Never trust model output as control flow
The model returns categories. Before you use them as object keys, validate them against the same values your enum allows. If an unexpected value arrives, log it and skip that item. Do not crash the whole run or write garbage to the Sheet. Structured output makes this rare, and the extra check costs almost nothing.
4. Handle quota, latency, and partial failure
UrlFetchApp calls sometimes fail, and Apps Script limits execution time per run. Wrap the API call in a retry with backoff for transient errors. Design the job so a failure on Monday does not silently lose Monday's data:
function fetchWithRetry(url, options, tries = 3) {
for (let i = 0; i < tries; i++) {
const res = UrlFetchApp.fetch(url, options);
if (res.getResponseCode() < 500) return res;
Utilities.sleep(1000 * Math.pow(2, i));
}
throw new Error('Gemini API failed after retries');
}5. Watch cost and privacy
Flash is inexpensive, but costs add up with a runaway loop or a trigger misconfigured to run hourly. Set a billing alert on the project.
You are also sending email content to an API. For personal use the AI Studio key is acceptable. For anything touching customer or employee data, move to Vertex AI, which gives you enterprise data handling, IAM, and the ability to keep data in a region. For organizational deployments this is a requirement. It is the line between a prototype and something compliance will approve.
Build a Gmail automation with Apps Script and Gemini
Knowledge check
1. According to the lesson, what does the combination of Apps Script and the Gemini API fundamentally provide that neither has fully on its own?
2. Why does the lesson recommend using structured output (a responseSchema) when calling Gemini from the script?
3. In which scenario would you prefer Vertex AI with a service account over a Google AI Studio API key?
4. Select ALL statements that correctly describe why Apps Script is described as 'the right glue' for this kind of automation.
Select all the correct answers.
5. Select ALL tasks the lesson presents as things this Apps Script + Gemini automation can do for you.
Select all the correct answers.
When to reachreachThe number of unique people exposed to your message in a given period. Unlike impressions, reach counts each person once, no matter how often they see it.View full definition → past Apps Script
Apps Script fits tasks that live inside Workspace and do one bounded job on a schedule. It is the wrong tool once you need true multi-step agent behavior: tools that call other tools, planning, memory across steps, or orchestration you want to test and version like real software.
At that point the Gemini ecosystem has better-fit options:
- Agent Development Kit (ADK) is an open-source framework for building agents with tools, planning, and multi-agent orchestration. You can deploy it on your own infrastructure or on Vertex AI AgentAI AgentSoftware that pursues a goal on its own: it plans steps, uses tools and takes actions with limited human input.View full definition → Engine. Use it when the logic outgrows a single script.
- Gemini CLI and Gemini Code Assist handle developer-side automation in your terminal and IDE. They are not built for Workspace document workflows.
- Gems are reusable, instruction-tuned assistants inside the Gemini app. Use them when a human is in the loop and you do not need code at all.
The decision rule is simple. If the task is "read some Workspace data, ask Gemini one focused question, write the result back to Workspace, on a schedule," Apps Script wins on simplicity. If an agent has to decide which actions to take across many steps, graduate to ADK on Vertex AI.
A pattern worth stealing: grounding the report
You can make the weekly summary smarter by having Gemini draft a short narrative of trends alongside the counts. Add a second call that takes this week's counts plus the previous week's row and asks for a two-sentence "what changed" note to append to the Sheet. Keep that prompt tightly scoped and still schema-bound (a single `summary` string field) so it stays predictable in an unattended run. It is a small amount of reasoning for a big gain in readability, and it is still cheap on Flash.
Key Takeaways
- Apps Script is the glue, Gemini is the judgment. Read Workspace data with native services, call the Gemini API with
UrlFetchApp, and write results back, all under your own auth and a time-based trigger. - Use structured output (
responseSchema) and batch your requests. Constrained JSON removes fragile parsing, and one batched call per run beats one call per item on cost, speed, and quota. - Make read-and-append the default and pin minimal OAuth scopes.
gmail.readonlyinappsscript.jsonis a stronger safety guarantee than any code comment. Add a dry-run review before any script sends, moves, or deletes. - Engineer for unattended failure. Use retries with backoff, validation against your enums, billing alerts, and logged errors you can actually read. Use a model alias such as
gemini-flash-latest, or track deprecation dates, so a retired model name does not break your trigger. - Graduate deliberately. Stay on Apps Script for bounded scheduled tasks, and move to ADK on Vertex AI when you need real agent orchestration or enterprise data controls.
Frequently asked questions
How do you connect Google Apps Script to the Gemini API?
You call the Gemini API from Apps Script with a single HTTP request using `UrlFetchApp`, pointing at the generateContent endpoint and passing your key. Apps Script already has authenticated access to Gmail, Sheets, Drive and Calendar, so it supplies the data and Gemini supplies the judgment. Store the key in Script Properties rather than in the source file.
Should I use an AI Studio API key or Vertex AI for a Workspace automation?
An API key from Google AI Studio is fine for personal automations and prototypes, and it is the fastest way to start. Move to Vertex AI with a service account as soon as the automation belongs to an organization or touches customer or employee data, because it gives you IAM controls, enterprise data handling and data residency. That switch is usually the line between a prototype and something compliance will approve.
How do I get reliable JSON back from Gemini instead of a paragraph?
Use structured output: set `responseMimeType` to `application/json` and pass a `responseSchema` in `generationConfig`, which constrains the model to valid JSON matching your schema. This removes the fragile step of parsing prose. Use `enum` values for fields like category or urgency so the output stays within a fixed set.
How do I schedule an Apps Script to run automatically every week?
Add a time-based trigger, either with the clock icon in the Apps Script editor or in code with `ScriptApp.newTrigger('weeklyReport').timeBased().onWeekDay(ScriptApp.WeekDay.MONDAY).atHour(7).create()`. You run the installer function once and the job then fires on its own with no human in the loop. Check that you do not install the trigger twice, since duplicates mean duplicate runs and duplicate API cost.
What safeguards does an unattended Apps Script using Gemini need?
Five things: keep the script read-and-append so it cannot delete or send, pin minimal OAuth scopes in `appsscript.json` (`gmail.readonly` is the strongest single guardrail), validate model output against your own enum before using it as control flow, retry failed `UrlFetchApp` calls with exponential backoff, and set a billing alert. Nobody is watching the output when the trigger fires at 7am, so failures have to be handled in code. Before letting a script send or move anything, run it in dry-run mode for a week and write the proposed actions to a Sheet for review.
When should I move from Apps Script to the Agent Development Kit?
Stay on Apps Script when the task is bounded: read Workspace data, ask Gemini one focused question, write the result back, on a schedule. Move to ADK on Vertex AI when you need real agent behaviour, meaning tools calling other tools, planning, memory across steps, or orchestration you want to version and test like software. ADK is an open-source framework you can deploy on your own infrastructure or on Vertex AI Agent Engine.