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.

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), write to a spreadsheet (SpreadsheetApp), and 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.

Two ways to authenticate the call:

• API key from [Google AI Studio](https://aistudio.google.com): fastest to start. Good for personal automations and prototypes.
• Vertex AI with a service account: the right choice when the automation belongs to an organization, 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. We will use that and flag 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 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 API supports a responseSchema so the model is constrained to valid JSON. That removes the fragile "parse the model's paragraph" step.

The classification call

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:

• The key lives in Script Properties, never in source. Set it once under Project Settings, or with 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.
• Batch the emails into one call. Sending one request per email burns quota and time. One prompt with all of last week's snippets is far cheaper 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 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.

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 are different 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, 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.

2. Request only the scopes you need

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.

3. Never trust model output as control flow

The model returns categories. Validate them against your enum before using them as object keys, exactly as the schema 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.

4. Handle quota, latency, and partial failure

UrlFetchApp calls fail sometimes. Apps Script also has execution-time limits per run. Wrap the API 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');
}

5. Watch cost and privacy

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 API. 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.

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 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:

• [Agent Development Kit (ADK)](https://google.github.io/adk-docs/): an open-source framework for building agents with tools, planning, and multi-agent orchestration, deployable on your own infrastructure or 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: for developer-side automation in your terminal and IDE, not Workspace document workflows.
• Gems: for reusable, instruction-tuned assistants inside the Gemini app, when a human is in the loop and you do not need code at all.

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.

A pattern worth stealing: grounding the report

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 schema-bound (a single `summary` string field) so it stays predictable in an unattended run. Small reasoning, big readability gain, 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, 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.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.
• Engineer for unattended failure: retries with backoff, validation against your enums, billing alerts, and logged errors you can actually read.
• Graduate deliberately. Stay on Apps Script for bounded scheduled tasks; move to ADK on Vertex AI when you need real agent orchestration or enterprise data controls.