Gemini + Google Apps Script: AI-powered Workspace automations that keep data in your account

The most common request of 2026 used to be "can you automate my sheets?" Now it's "can the automation think a little?" The good news: if your business lives in Google Workspace, you can add AI to Sheets, Gmail and Drive automations without any new infrastructure, any new subscription, and without your data leaving your account. Here's the exact pattern I build for clients.

Why Apps Script + Gemini is the underrated combo

• No servers. Apps Script runs on Google's infrastructure, triggered by the same forms, inboxes and schedules as before.
• Data residency. The automation lives in your Workspace and your API key is yours. No third-party SaaS middleware reading your email to "provide insights".
• Cost. Flash-class models classify and extract for fractions of a cent per item. A 500-emails-a-week triage bot costs less than the coffee that goes with reviewing them.

The pattern: UrlFetchApp → Gemini API → JSON

One function does 90% of the work. Force structured output so the rest of your script can trust it:

function classifyEmail(messageText) {
  const payload = {
    contents: [{
      parts: [{ text:
        'Classify this support email. Respond ONLY with JSON: ' +
        '{"category": "billing|order|bug|other", "urgency": "high|normal|low", ' +
        '"summary": "one sentence"}\n\nEMAIL:\n' + messageText
      }]
    }],
    generationConfig: { temperature: 0, responseMimeType: 'application/json' }
  };

  const res = UrlFetchApp.fetch(
    'https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash:generateContent' +
    '?key=' + PropertiesService.getScriptProperties().getProperty('GEMINI_KEY'),
    { method: 'post', contentType: 'application/json', payload: JSON.stringify(payload) }
  );

  return JSON.parse(JSON.parse(res.getContentText()).candidates[0].content.parts[0].text);
}

Wire it to a 15-minute Gmail trigger and your inbox self-sorts: high-urgency floats to the top, billing goes to the accountant's label, and every row lands in a Sheet with a summary your team can scan in seconds.

Extraction: the other money-maker

Same pattern, different prompt: hand it an invoice PDF's text, get back {"vendor": ..., "amount": ..., "due_date": ...} and write it straight into your ledger Sheet. Temperature 0, schema'd response, and low-confidence results flagged for a human. This is the AI layer on top of the form-to-invoice pipeline — the model reads the messy input, the script does the billing.

Prompt hygiene that separates production from demos

• Demand JSON, validate JSON. Wrap parsing in try/catch and route failures to a review queue — never let a malformed reply silently drop a customer email.
• Temperature discipline: 0 for extraction and classification, ~0.7 for drafting.
• Few-shot examples from YOUR inbox beat clever prompt engineering every time. Three real examples of a "billing" email do more than a paragraph of instructions.
• Human approval on outbound text. AI drafts, you approve — until you've watched it for a few weeks. Auto-sending model-written text to customers on day one is how you lose clients.

The cost math

A typical support email is ~500 tokens in, ~80 out. At flash-class pricing that's well under $0.001 per email — a thousand emails a week costs less than a spreadsheet add-on subscription. Compare that to the per-seat "AI inbox" SaaS at $30/user/month and the build-vs-buy math usually ends the meeting.

Where to draw the line

Keep AI on classify, extract and draft. Keep scripts on anything that moves money, deletes data or sends unreviewed external communication. That split — model proposes, script disposes — is the whole architecture.

Want this running in your own Workspace? That's a standard build for me — fixed price, your accounts, your API key, documented.