# 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: auto-label incoming email by intent, draft replies, or compile a weekly Sheet report from raw rows without you touching it. The unlock is that 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 shows the wiring, a concrete working example, and the guardrails that matter once the script runs unattended on a trigger.
Apps Script is Google's serverless JavaScript runtime built into Workspace. You do not provision anything. A script can read your Gmail threads (GmailApp), write to a spreadsheet (SpreadsheetApp), and run on a time-based trigger, all under your account's permissions.
UrlFetchAppTwo ways to authenticate the call:
For a personal weekly report, the APIAPIApplication Programming Interface: a standardised interface that lets applications communicate and exchange data without knowing each other's internal workings.View full definition → key path is fine. We will use that and flag where you would switch.
The goal: every Monday at 7am, scan last week's emails labeled support, have Gemini classify each by category and urgency, and append a tidy summary row to a tracking Sheet.
The trick that makes this reliable is 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 APIAPIApplication Programming Interface: a standardised interface that lets applications communicate and exchange data without knowing each other's internal workings.View full definition → supports a responseSchema so the model is constrained to valid JSON. That removes the fragile "parse the model's paragraph" step.
Here 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-2.5-flash';
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:
PropertiesService.getScriptProperties().setProperty('GEMINI_KEY', '...').gemini-2.5-flash is the right tier here. Flash is fast and cheap, and classification is exactly its sweet spot. Reserve Pro for tasks needing deeper reasoning (long synthesis, hard multi-step judgment). Check current model names at ai.google.dev before deploying, since tiers evolve.muteHttpExceptions: true lets you read the error body instead of getting a generic throw. You will want that text in your logs.index.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 do it in code once:
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 also exactly where the risk begins.
A script that runs while you sleep, holds your Gmail scope, and calls an external model needs discipline. The failure modes are different from interactive promptingpromptingPrompt engineering is the practice of designing and refining text inputs to guide large language models toward accurate, relevant, and reliable outputs.View full definition → because nobody is watching the output.
This example only reads Gmail and appends to a Sheet. It never deletes, never sends, never modifies threads. Keep automations read-and-append by default. The moment a script can send email or delete files unattended, a bad model output or a bug becomes an action you cannot undo.
If you do progress to auto-replies or auto-labeling that moves mail, add a dry-run mode: write the proposed action to a Sheet for a week and review it before letting the script execute for real.
Apps Script infers OAuth scopes from the APIs you call, but you should 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"
]
}gmail.readonly means even a buggy version literally cannot send or delete. That single line is your strongest guardrail.
The model returns categories. Validate them against your enum before using them as object keys, exactly as the 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 → enforces. If a value arrives that you did not expect, log it and skip rather than crashing the whole run or writing garbage. Structured output makes this rare, but defense in depth is cheap.
UrlFetchApp calls fail sometimes. Apps Script also has execution-time limits per run. Wrap the APIAPIApplication Programming Interface: a standardised interface that lets applications communicate and exchange data without knowing each other's internal workings.View full definition → call in a retry with backoff for transient errors, and 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');
}Flash is inexpensive, but a runaway loop or a trigger misconfigured to run hourly adds up. Set a billing alert on the project. On privacy: you are sending email content to an APIAPIApplication Programming Interface: a standardised interface that lets applications communicate and exchange data without knowing each other's internal workings.View full definition →. For personal use the AI Studio key is acceptable, but for anything touching customer or employee data, move to Vertex AI, where you get enterprise data handling, IAM, and the ability to keep data in a region. That is not a nice-to-have for organizational deployments; it is the line between a prototype and something compliance will approve.
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.
Apps Script is perfect for "this lives inside Workspace and does a bounded task 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 gives you better-fit options:
The decision rule: if the task is "read some Workspace data, ask Gemini one focused question, write the result somewhere in Workspace, on a schedule," Apps Script wins on simplicity. If the task involves an agent making decisions about which actions to take across many steps, graduate to ADK on Vertex AI.
You can make the weekly summary smarter by having Gemini draft a short narrative of trends, not just counts. Add a second call that takes the week's counts plus the previous week's row and asks for a two-sentence "what changed" note appended to the Sheet. Keep that prompt tightly scoped and still 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 →-bound (a single summary string field) so it stays predictable in an unattended run. Small reasoning, big readability gain, still cheap on Flash.
UrlFetchApp, write results back, all under your own auth and a time-based trigger.gmail.readonly in appsscript.json is a stronger safety guarantee than any code comment. Add a dry-run review before any script sends, moves, or deletes.