Building AI-Powered Workflows in Google Workspace with Gemini and AppSheet

Most "AI in Workspace" discussions stop at "Gemini can summarize this doc." The interesting work starts when you wire Gemini into AppSheet so a human request kicks off a multi-step pipeline that touches Drive, Sheets, Gmail, and Calendar without anyone clicking through five tabs. This post walks through three pipelines you can build today, then deep-dives one full implementation: a contract review pipeline with Gemini summarization and AppSheet approval routing.

If your stack is M365-leaning, the comparable build is Power Automate + Copilot Studio. We compare both at the end so you can decide where the seams actually fall.

Three workflows worth building first

Contract review: Sales drops a redlined MSA into a Drive folder. A Drive trigger fires Gemini against the file, extracts deviations from the standard template, scores risk, and inserts a row into an AppSheet table. Legal sees a queue, approves or rejects with a single tap, and on approval the agreement is moved to /Active/Contracts with an audit row written to Sheets.

Customer onboarding: A Google Form captures a new customer. AppSheet picks up the row, calls Gemini to generate a kickoff agenda and a welcome email tailored to the customer's industry, schedules the kickoff via Calendar API, and emails the welcome package via Gmail. Total elapsed time under 90 seconds.

Expense audit: Employees forward receipts to expenses@yourdomain.com. A Gmail filter labels them, an Apps Script trigger pushes attachments to Gemini for line-item extraction, AppSheet reconciles against a budget Sheet, and anything that violates policy gets routed to a manager for review.

All three share the same skeleton: trigger, Gemini call, AppSheet action, downstream Workspace API. The skeleton is what we'll build below.

The contract review build, end to end

Step 1: Drive trigger

Create a Drive folder named /Inbox/Contracts. In Apps Script (Tools, Script editor from a bound Sheet, or standalone at script.google.com), add a time-driven trigger that scans the folder every 5 minutes for new files. Apps Script does not yet have native push triggers on Drive folders for arbitrary file types without Advanced Drive API, so polling is the pragmatic default.

```javascript function scanContractsInbox() { const folderId = PropertiesService.getScriptProperties().getProperty('CONTRACTS_INBOX_ID'); const folder = DriveApp.getFolderById(folderId); const files = folder.getFiles(); const processed = SpreadsheetApp.openById(PROCESSED_SHEET_ID).getSheetByName('Processed'); const seen = new Set(processed.getRange('A:A').getValues().flat());

while (files.hasNext()) { const file = files.next(); if (seen.has(file.getId())) continue; try { const analysis = callGeminiOnContract(file); writeToAppSheetQueue(file, analysis); processed.appendRow([file.getId(), new Date(), 'queued']); } catch (err) { console.error(`Failed on ${file.getName()}: ${err}`); processed.appendRow([file.getId(), new Date(), `error: ${err.message}`]); } } } ```

Step 2: Gemini call via Vertex AI

You have two integration options. The Workspace-native Gemini API (gemini.googleapis.com) is simpler but does not yet expose your enterprise's grounding sources. Vertex AI does, and lets you pin a model version, use prompt caching, and route through a VPC Service Controls perimeter.

For contract review, use Vertex AI with `gemini-2.5-pro` for the analysis pass. Add file upload via the Files API so the model can read the actual PDF rather than receiving text-extracted contents.

```javascript function callGeminiOnContract(file) { const accessToken = ScriptApp.getOAuthToken(); const projectId = PropertiesService.getScriptProperties().getProperty('GCP_PROJECT_ID'); const location = 'us-central1'; const model = 'gemini-2.5-pro';

const pdfBytes = file.getBlob().getBytes(); const pdfBase64 = Utilities.base64Encode(pdfBytes);

const standardTemplate = DriveApp.getFileById(STANDARD_TEMPLATE_ID).getBlob().getDataAsString();

const systemPrompt = 'You are a senior contracts attorney. Compare the attached contract against the standard MSA template provided in context. Identify every material deviation. For each deviation output JSON with fields: clause, standard_text, proposed_text, risk_score (1-5), category (commercial|legal|operational|ip|liability), recommendation. Output a single JSON array. No prose.';

const body = { contents: [{ role: 'user', parts: [ { text: `Standard template:\n${standardTemplate}` }, { inlineData: { mimeType: 'application/pdf', data: pdfBase64 } }, { text: 'Now analyze the attached contract.' } ] }], systemInstruction: { parts: [{ text: systemPrompt }] }, generationConfig: { temperature: 0.1, maxOutputTokens: 8192, responseMimeType: 'application/json' } };

const url = `https://${location}-aiplatform.googleapis.com/v1/projects/${projectId}/locations/${location}/publishers/google/models/${model}:generateContent`;

const response = UrlFetchApp.fetch(url, { method: 'post', contentType: 'application/json', headers: { Authorization: `Bearer ${accessToken}` }, payload: JSON.stringify(body), muteHttpExceptions: true });

if (response.getResponseCode() !== 200) { throw new Error(`Vertex returned ${response.getResponseCode()}: ${response.getContentText()}`); }

const parsed = JSON.parse(response.getContentText()); const text = parsed.candidates[0].content.parts[0].text; return JSON.parse(text); } ```

Three production details to notice. `temperature: 0.1` keeps deviations stable across runs. `responseMimeType: 'application/json'` forces structured output. And `muteHttpExceptions: true` lets you read the error body. Without it, UrlFetchApp throws a generic exception with no payload.

Step 3: Write to AppSheet queue

AppSheet apps are backed by Sheets. Push each deviation as a row.

```javascript function writeToAppSheetQueue(file, deviations) { const queue = SpreadsheetApp.openById(QUEUE_SHEET_ID).getSheetByName('Queue'); const fileUrl = file.getUrl(); const submittedAt = new Date(); const rows = deviations.map(d => [ Utilities.getUuid(), file.getId(), file.getName(), fileUrl, submittedAt, d.clause, d.standard_text, d.proposed_text, d.risk_score, d.category, d.recommendation, 'pending' ]); queue.getRange(queue.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows); } ```

Step 4: AppSheet bot for routing

In AppSheet, open the bound app and create a Bot:

• Event: Data change on the Queue table, condition `[status] = "pending" AND [risk_score] >= 3`
• Process steps:
  1. Branch on `[category]`: legal goes to General Counsel, commercial to CFO, ip to CTO
  2. Run a "Send an email" task with deep link back into the AppSheet view filtered to that contract
  3. Wait for approval (a checkbox column toggled in the AppSheet view)
  4. On approve: call a webhook to a second Apps Script that moves the file to /Active/Contracts and appends an audit row
  5. On reject: email the sales owner with the redline summary

Risk score 1 to 2 auto-approves to keep low-risk MSAs flowing. The threshold is a property in the Bot configuration, not hard-coded, so legal can tune it.

Step 5: Error handling and observability

Three things will go wrong:

1. Vertex 429: Gemini 2.5 Pro has tight default quotas. Retry with exponential backoff up to 4 attempts. Cache the standard template in a Script property plus use Vertex prompt caching keyed on the template hash to reduce cost.
2. PDF parse failures: scanned PDFs occasionally choke. Fall back to Document AI OCR before re-submitting to Gemini.
3. Bot stalls: AppSheet bots silently fail if the event condition has a typo. Wire the Apps Script audit Sheet to also log bot completion events via the AppSheet API.

Build a simple "Pipeline health" Sheet that lists the last 100 runs with elapsed time, token count, and final status. Anyone debugging an incident starts there.

Two more workflows in less depth

Customer onboarding pipeline. The trigger is a Google Form submission for new customer intake. AppSheet picks up the form row and calls a Gemini prompt that takes the customer's industry, plan, and stated goals and produces a tailored kickoff agenda plus a welcome email. The agenda includes specific discovery questions relevant to the industry (a healthcare customer gets HIPAA-related questions, a retail customer gets POS integration questions). AppSheet then writes a Calendar invite via the Calendar API for the kickoff slot, attaches the agenda as a Google Doc, and sends the welcome email via Gmail with the doc embedded. Total elapsed time from form submission to invite landing in the customer's inbox is under 90 seconds. The trick is to keep the Gemini call narrow (industry plus plan plus goals to JSON output of agenda items and email body) and let AppSheet handle the deterministic steps. Do not ask Gemini to "do the whole onboarding"; ask it for the two artifacts that need writing.

Expense audit pipeline. Employees forward receipts to expenses@yourdomain.com. A Gmail filter labels them and triggers an Apps Script function. The script extracts the attachment, sends it to Gemini with a prompt requesting structured line-item extraction (merchant, amount, currency, date, category, tax). Output schema is locked with `responseSchema` so AppSheet can read it without parsing. AppSheet reconciles the row against a budget Sheet (per category, per cost center). Anything within policy auto-approves; anything outside (over the per-receipt cap, wrong category, missing tax) goes to a manager queue with a deep link back to the original Gmail thread. The audit log is a separate Sheet that records the full Gemini response for every receipt so a finance audit can replay the extraction.

Both share the same skeleton as contract review. The pattern is: thin trigger, narrow Gemini call returning structured JSON, AppSheet handles workflow state, downstream Workspace API handles the side effect.

Comparing to Power Automate plus Copilot

Same workflow on the Microsoft side:

| Stage | Google Workspace | Microsoft 365 | |-------|------------------|---------------| | Trigger | Apps Script time trigger on Drive folder | Power Automate "When a file is created in SharePoint" | | AI call | Vertex AI Gemini 2.5 Pro via UrlFetchApp | Azure OpenAI GPT-4o or Copilot Studio with grounding | | Queue store | Sheets plus AppSheet | Dataverse plus Power Apps | | Approval routing | AppSheet Bot | Power Automate Approvals connector | | Audit | Sheet plus Apps Script logs | Dataverse audit table plus Purview | | Identity | Workspace OAuth via Apps Script | Entra ID service principal | | Cost (est., 1,000 contracts per month) | ~$140 Vertex plus AppSheet Core licenses | ~$180 Azure OpenAI plus Power Automate Premium |

Power Automate has a slight edge in pre-built connectors and an arguably more mature approvals UX. AppSheet wins on iteration speed (the bot designer is faster than Power Automate's flow editor once you are past the learning curve), and Vertex AI gives you finer control over model selection and prompt caching than Copilot Studio currently exposes.

Identity, auth, and the OAuth gotchas

Apps Script uses the script's executing user identity by default, which is wrong for a production pipeline. Switch to a Workspace service account with domain-wide delegation, then have the Apps Script impersonate a dedicated automation user via the JWT flow. The pattern: create a project-specific user (`automation-contracts@yourdomain.com`), grant it edit access to the Drive folders, Sheets, and AppSheet apps it needs, and configure the service account to impersonate that user. Now permission removal is a single off-boarding action, and audit logs show actions attributed to the automation user, not the engineer who wrote the script.

For Vertex AI calls specifically, the service account needs the `aiplatform.user` role on the GCP project. Avoid granting broader roles like `Editor` even in dev environments. The principle of least privilege is the only thing standing between a misconfigured prompt and a costly mistake.

Checklist before you ship

• [ ] Confirm the Vertex AI API is enabled in your GCP project and the Apps Script project is bound to that GCP project (Resources, then Cloud Platform project)
• [ ] Store all IDs (folder, sheet, GCP project) in Script Properties, never inline
• [ ] Set a quota alert in GCP for Vertex AI per-minute requests at 80 percent of your assigned quota
• [ ] Build the audit Sheet before the workflow, not after
• [ ] Pilot with 20 historical contracts before turning on live processing
• [ ] Add a "human override" column on the Queue Sheet so legal can correct Gemini's risk score and feed it back as labeled data
• [ ] Document the prompt and pin the model version (`gemini-2.5-pro-001`) so behavior is reproducible

For the broader operating model around tracking these agents in production, agent-observability-metrics covers latency, token, and quality SLIs. And before you put any of this in front of a regulated business, ai-governance-framework-template gives you the policy scaffolding.

Next steps

Pick one of the three workflows above and time-box a one-week prototype. Contract review usually has the cleanest ROI because legal review is a real bottleneck. We help Workspace-first teams stand up Gemini and AppSheet pipelines with the AppSheet bots, Vertex prompts, and Apps Script glue. Reach out if you want to skip the trial-and-error phase.