🚀 Apps Script + Gemini Mastery — Issue #32

Build a Multi-Agent AI Workflow with Google Apps Script + Gemini

https://github.com/lsvekis/Apps-Script-Code-Snippets

In the last issue, we built an AI Automation Agent that could understand a request, select approved tools, create an execution plan, and perform controlled actions inside Google Workspace.

Now we’re going one step further.

Instead of asking one AI agent to do everything, we’re going to create a team of specialized AI agents.

Imagine asking:

“Analyze this sales data and create an executive report.”

Instead of sending one enormous prompt to Gemini, Apps Script coordinates four specialists:

🔎 Research Agent — understands the source data

📊 Analysis Agent — identifies patterns and insights

✍️ Report Agent — turns those findings into a professional report

🛡️ Review Agent — checks the report before delivery

The workflow becomes:

USER REQUEST
      ↓
RESEARCH AGENT
      ↓
ANALYSIS AGENT
      ↓
REPORT AGENT
      ↓
REVIEW AGENT
      ↓
FINAL REPORT

Each agent has one clearly defined responsibility.

Apps Script becomes the orchestrator connecting them.

⭐ What You Will Build

Our Multi-Agent Workflow will:

📊 Read data from Google Sheets

🔎 Create a structured research brief

🧠 Analyze the findings

✍️ Generate an executive report

🛡️ Review the report for quality

🔄 Request revisions when necessary

📄 Create the final Google Doc

📋 Record each agent’s activity

Instead of building one complicated AI prompt, we’ll create a pipeline of smaller specialized prompts.

🧠 Why Use Multiple Agents?

Suppose you give Gemini this instruction:

“Analyze this spreadsheet, identify trends, find anomalies, recommend actions, write an executive report, verify every statement, improve the writing, and make sure nothing is unsupported.”

Gemini can attempt all of that.

But the prompt now contains many different responsibilities.

Another approach is to divide the problem.

Research Agent

Understand and structure the available evidence.

Analysis Agent

Determine what the evidence means.

Report Agent

Communicate those findings clearly.

Review Agent

Challenge the result before it is delivered.

This creates separation of responsibilities similar to how we structure software.

🧩 Architecture

                USER
                  │
                  ▼
          Google Sheets Data
                  │
                  ▼
        ┌───────────────────┐
        │  RESEARCH AGENT   │
        │ Structure Facts   │
        └─────────┬─────────┘
                  │
            Research Brief
                  │
                  ▼
        ┌───────────────────┐
        │  ANALYSIS AGENT   │
        │ Find Insights     │
        └─────────┬─────────┘
                  │
             Analysis
                  │
                  ▼
        ┌───────────────────┐
        │   REPORT AGENT    │
        │ Create Narrative  │
        └─────────┬─────────┘
                  │
             Draft Report
                  │
                  ▼
        ┌───────────────────┐
        │   REVIEW AGENT    │
        │ Validate Quality  │
        └─────────┬─────────┘
                  │
           PASS / REVISE
             ↙         ↘
         Revision     Final
             │          │
             └────┬─────┘
                  ▼
             Google Doc

Apps Script controls the entire sequence.

🧱 Step 1 — Create the Menu

`Code.gs`

function onOpen() {

  SpreadsheetApp.getUi()
    .createMenu("AI Tools")
    .addItem(
      "Multi-Agent Report",
      "showMultiAgentSidebar"
    )
    .addToUi();

}

function showMultiAgentSidebar() {

  const html =
    HtmlService
      .createHtmlOutputFromFile("Sidebar")
      .setTitle("Multi-Agent AI Workflow");

  SpreadsheetApp
    .getUi()
    .showSidebar(html);

}

🧱 Step 2 — Create the Sidebar

`Sidebar.html`

<!DOCTYPE html>
<html>

<head>

<base target="_top">

<style>

body {
  font-family: Arial, sans-serif;
  padding: 14px;
}

textarea {
  width: 100%;
  height: 120px;
  box-sizing: border-box;
}

button {
  margin-top: 10px;
  padding: 9px 14px;
}

#status {
  margin-top: 15px;
  padding: 10px;
  background: #f5f5f5;
  white-space: pre-wrap;
}

</style>

</head>

<body>

<h2>Multi-Agent AI Workflow</h2>

<p>
What should the AI team investigate?
</p>

<textarea id="request">
Analyze this sales data and create an executive performance report with key trends, concerns, opportunities, and recommended actions.
</textarea>

<button onclick="runWorkflow()">
Run AI Team
</button>

<pre id="status"></pre>

<script>

function runWorkflow() {

  const request =
    document
      .getElementById("request")
      .value;

  status.textContent =
    "AI team is working...";

  google.script.run

    .withSuccessHandler(
      function(result) {

        status.textContent =
          "Workflow complete.\n\n" +
          "Report:\n" +
          result.documentUrl;

      }
    )

    .withFailureHandler(
      function(error) {

        status.textContent =
          "Error: " +
          error.message;

      }
    )

    .runMultiAgentWorkflow(
      request
    );

}

</script>

</body>

</html>

🧱 Step 3 — Read the Source Data

We’ll start with the active spreadsheet.

`DataReader.gs`

const MAX_DATA_ROWS = 100;

function readSourceData_() {

  const sheet =
    SpreadsheetApp
      .getActiveSheet();

  const lastRow =
    sheet.getLastRow();

  const lastColumn =
    sheet.getLastColumn();

  if (
    lastRow === 0 ||
    lastColumn === 0
  ) {

    throw new Error(
      "The active sheet contains no data."
    );

  }

  const rowsToRead =
    Math.min(
      lastRow,
      MAX_DATA_ROWS
    );

  const values =
    sheet
      .getRange(
        1,
        1,
        rowsToRead,
        lastColumn
      )
      .getDisplayValues();

  return {

    sheetName:
      sheet.getName(),

    totalRows:
      lastRow,

    rowsRead:
      rowsToRead,

    data:
      values

  };

}

Again, we’re intentionally limiting the amount of spreadsheet data sent to Gemini.

🔎 Step 4 — Build the Research Agent

The Research Agent’s job is not to make recommendations.

Its job is to understand the evidence.

`ResearchAgent.gs`

function runResearchAgent_(
  userRequest,
  sourceData
) {

  const prompt = `
You are the RESEARCH AGENT.

Your job is to examine the source data
and create a factual research brief.

USER OBJECTIVE:

${userRequest}

SOURCE DATA:

${JSON.stringify(sourceData)}

Return JSON only:

{
  "datasetSummary": "",
  "importantFacts": [],
  "notableValues": [],
  "possiblePatterns": [],
  "dataLimitations": []
}

RULES:

1. Stay grounded in the provided data.
2. Do not invent missing facts.
3. Do not make business recommendations.
4. Clearly identify uncertainty.
5. Separate observations from assumptions.
`;

  const response =
    callGemini(
      prompt,
      ""
    );

  return JSON.parse(
    cleanJsonResponse_(
      response
    )
  );

}

Notice the instruction:

Do not make business recommendations.

Why?

Because recommendations belong to the next agent.

📊 Step 5 — Build the Analysis Agent

Now we pass the Research Agent’s output to a specialist responsible for interpretation.

`AnalysisAgent.gs`

function runAnalysisAgent_(
  userRequest,
  research
) {

  const prompt = `
You are the ANALYSIS AGENT.

Your job is to analyze a factual
research brief and determine what
the evidence means.

USER OBJECTIVE:

${userRequest}

RESEARCH BRIEF:

${JSON.stringify(
  research,
  null,
  2
)}

Return JSON only:

{
  "keyInsights": [],
  "trends": [],
  "concerns": [],
  "opportunities": [],
  "recommendedActions": []
}

RULES:

1. Base conclusions on the research brief.
2. Do not invent supporting evidence.
3. Distinguish evidence from interpretation.
4. Prioritize the most important findings.
5. Make recommendations actionable.
`;

  const response =
    callGemini(
      prompt,
      ""
    );

  return JSON.parse(
    cleanJsonResponse_(
      response
    )
  );

}

We now have two different AI outputs:

RAW DATA
   ↓
RESEARCH
   ↓
ANALYSIS

✍️ Step 6 — Build the Report Agent

The Report Agent doesn’t need the entire spreadsheet.

It receives the structured research and analysis.

`ReportAgent.gs`

function runReportAgent_(
  userRequest,
  research,
  analysis
) {

  const prompt = `
You are the REPORT AGENT.

Create a professional executive report.

USER OBJECTIVE:

${userRequest}

RESEARCH:

${JSON.stringify(
  research,
  null,
  2
)}

ANALYSIS:

${JSON.stringify(
  analysis,
  null,
  2
)}

The report should contain:

# Executive Summary

# Key Findings

# Trends

# Concerns

# Opportunities

# Recommended Actions

# Data Limitations

RULES:

1. Write for a business audience.
2. Keep the report concise.
3. Do not introduce new facts.
4. Do not hide uncertainty.
5. Make recommendations practical.
6. Use clear headings.
`;

  return callGemini(
    prompt,
    ""
  );

}

This agent focuses on communication, not raw analysis.

🛡️ Step 7 — Build the Review Agent

This is where the workflow becomes particularly interesting.

Before creating the final Google Doc, another AI agent reviews the report.

`ReviewAgent.gs`

function runReviewAgent_(
  research,
  analysis,
  report
) {

  const prompt = `
You are the REVIEW AGENT.

Review the report against the
research and analysis.

RESEARCH:

${JSON.stringify(
  research,
  null,
  2
)}

ANALYSIS:

${JSON.stringify(
  analysis,
  null,
  2
)}

REPORT:

${report}

Return JSON only:

{
  "status": "PASS",
  "score": 0,
  "issues": [],
  "revisionInstructions": []
}

Evaluate:

- factual grounding
- unsupported claims
- missing major findings
- clarity
- usefulness
- recommendation quality

Score from 0 to 100.

Use:

PASS

only if the report is ready for delivery.

Otherwise use:

REVISE
`;

  const response =
    callGemini(
      prompt,
      ""
    );

  return JSON.parse(
    cleanJsonResponse_(
      response
    )
  );

}

Now our AI system is checking its own work—but with a separate prompt and responsibility.

🔄 Step 8 — Revision Loop

If the Review Agent rejects the report, send it back to the Report Agent.

But don’t allow unlimited retries.

`RevisionAgent.gs`

function reviseReport_(
  report,
  review,
  research,
  analysis
) {

  const prompt = `
You are the REPORT REVISION AGENT.

Improve the report using the
reviewer's instructions.

ORIGINAL REPORT:

${report}

REVIEW ISSUES:

${JSON.stringify(
  review.issues
)}

REVISION INSTRUCTIONS:

${JSON.stringify(
  review.revisionInstructions
)}

RESEARCH:

${JSON.stringify(
  research
)}

ANALYSIS:

${JSON.stringify(
  analysis
)}

Return the complete revised report.

Do not introduce unsupported facts.
`;

  return callGemini(
    prompt,
    ""
  );

}

🧱 Step 9 — Create the Orchestrator

This is the heart of the project.

`Orchestrator.gs`

function runMultiAgentWorkflow(
  userRequest
) {

  if (
    !userRequest ||
    !userRequest.trim()
  ) {

    throw new Error(
      "Enter a request."
    );

  }

  const workflowId =
    Utilities.getUuid();

  const sourceData =
    readSourceData_();

  logAgentStep_(
    workflowId,
    "SYSTEM",
    "Source data loaded"
  );

  const research =
    runResearchAgent_(
      userRequest,
      sourceData
    );

  logAgentStep_(
    workflowId,
    "RESEARCH",
    "Research completed"
  );

  const analysis =
    runAnalysisAgent_(
      userRequest,
      research
    );

  logAgentStep_(
    workflowId,
    "ANALYSIS",
    "Analysis completed"
  );

  let report =
    runReportAgent_(
      userRequest,
      research,
      analysis
    );

  logAgentStep_(
    workflowId,
    "REPORT",
    "Initial report completed"
  );

  let review =
    runReviewAgent_(
      research,
      analysis,
      report
    );

  logAgentStep_(
    workflowId,
    "REVIEW",
    review.status
  );

  if (
    review.status === "REVISE"
  ) {

    report =
      reviseReport_(
        report,
        review,
        research,
        analysis
      );

    logAgentStep_(
      workflowId,
      "REVISION",
      "Report revised"
    );

    review =
      runReviewAgent_(
        research,
        analysis,
        report
      );

    logAgentStep_(
      workflowId,
      "FINAL REVIEW",
      review.status
    );

  }

  const document =
    createFinalReport_(
      report,
      review
    );

  return {

    workflowId:
      workflowId,

    review:
      review,

    documentUrl:
      document.documentUrl

  };

}

Look at what’s happening.

Apps Script isn’t doing the research or writing.

It’s coordinating specialists.

🧠 Apps Script Becomes the Orchestration Layer

Our architecture now looks like:

Apps Script
    │
    ├── Research Agent
    │
    ├── Analysis Agent
    │
    ├── Report Agent
    │
    ├── Review Agent
    │
    └── Revision Agent

Apps Script manages:

sequencing
data movement
retries
logging
Google Workspace access
final output

Gemini provides the intelligence.

🧱 Step 10 — Create the Final Google Doc

`DocumentWriter.gs`

function createFinalReport_(
  report,
  review
) {

  const doc =
    DocumentApp.create(
      "AI Multi-Agent Report"
    );

  const body =
    doc.getBody();

  body
    .appendParagraph(
      "AI Multi-Agent Report"
    )
    .setHeading(
      DocumentApp
        .ParagraphHeading
        .TITLE
    );

  body.appendParagraph(
    "Generated: " +
    new Date()
  );

  body.appendParagraph(
    report
  );

  body.appendParagraph(
    "Quality Review"
  )
  .setHeading(
    DocumentApp
      .ParagraphHeading
      .HEADING2
  );

  body.appendParagraph(
    "Status: " +
    review.status
  );

  body.appendParagraph(
    "Score: " +
    review.score +
    "/100"
  );

  return {

    documentId:
      doc.getId(),

    documentUrl:
      doc.getUrl()

  };

}

📋 Step 11 — Log Every Agent

When multiple agents are involved, observability becomes even more important.

`AgentLogger.gs`

function logAgentStep_(
  workflowId,
  agent,
  message
) {

  const ss =
    SpreadsheetApp
      .getActiveSpreadsheet();

  let sheet =
    ss.getSheetByName(
      "AI Agent Log"
    );

  if (!sheet) {

    sheet =
      ss.insertSheet(
        "AI Agent Log"
      );

    sheet.appendRow([
      "Timestamp",
      "Workflow ID",
      "Agent",
      "Message"
    ]);

  }

  sheet.appendRow([

    new Date(),

    workflowId,

    agent,

    message

  ]);

}

Your log might look like:

Time	Workflow	Agent	Result
10:01	abc123	SYSTEM	Source data loaded
10:01	abc123	RESEARCH	Research completed
10:02	abc123	ANALYSIS	Analysis completed
10:02	abc123	REPORT	Initial report completed
10:03	abc123	REVIEW	REVISE
10:03	abc123	REVISION	Report revised
10:04	abc123	FINAL REVIEW	PASS

Now you can see exactly how the AI team worked.

🧱 Step 12 — Gemini Helper

`GeminiHelpers.gs`

const GEMINI_MODEL =
  "gemini-2.5-flash";

function getGeminiApiKey_() {

  const key =
    PropertiesService
      .getScriptProperties()
      .getProperty(
        "GEMINI_API_KEY"
      );

  if (!key) {

    throw new Error(
      "Set GEMINI_API_KEY in Script Properties."
    );

  }

  return key;

}

function callGemini(
  prompt,
  additionalText
) {

  const key =
    getGeminiApiKey_();

  const url =
    "https://generativelanguage.googleapis.com/v1/models/" +
    GEMINI_MODEL +
    ":generateContent?key=" +
    encodeURIComponent(key);

  const text =
    prompt +
    (
      additionalText
        ? "\n\n" +
          additionalText
        : ""
    );

  const payload = {

    contents: [{

      parts: [{

        text: text

      }]

    }]

  };

  const response =
    UrlFetchApp.fetch(
      url,
      {

        method:
          "post",

        contentType:
          "application/json",

        payload:
          JSON.stringify(
            payload
          ),

        muteHttpExceptions:
          true

      }
    );

  const json =
    JSON.parse(
      response.getContentText()
    );

  if (json.error) {

    throw new Error(
      json.error.message
    );

  }

  return json
    .candidates[0]
    .content.parts[0]
    .text;

}

🧱 Step 13 — JSON Cleanup Helper

`Utilities.gs`

function cleanJsonResponse_(
  text
) {

  return String(
    text || ""
  )
  .replace(
    /```json/gi,
    ""
  )
  .replace(
    /```/g,
    ""
  )
  .trim();

}

🧪 Example Dataset

Suppose our sheet contains:

Month	Region	Revenue	Orders	Returns
January	East	42,000	310	12
January	West	51,000	365	18
February	East	48,000	342	11
February	West	57,000	391	16
March	East	61,000	420	14
March	West	65,000	448	21

Then enter:

“Analyze sales performance. Identify important trends, potential concerns, opportunities, and recommended actions for management.”

🔎 Research Agent

The first agent might identify:

Revenue increased in both regions.

West produced more revenue each month.

East increased from $42,000 to $61,000.

West increased from $51,000 to $65,000.

West also recorded more returns.

The dataset contains only three months.

Notice how factual this stage is.

📊 Analysis Agent

The next agent can reason from those facts:

Revenue growth is positive across both regions.

East appears to be growing faster proportionally.

West remains the larger revenue contributor.

West's higher return count deserves investigation.

Three months of data is insufficient for strong
long-term seasonality conclusions.

✍️ Report Agent

Now the Report Agent transforms the analysis into something management can quickly understand.

Instead of dumping raw numbers, it creates:

executive summary
key findings
opportunities
concerns
recommendations

🛡️ Review Agent

Then the reviewer asks:

Are all of these claims actually supported?

For example, suppose the report says:

“Customer satisfaction is improving.”

Where did that come from?

Our spreadsheet contains:

Revenue
Orders
Returns

It contains no customer satisfaction data.

The Review Agent should flag the statement as unsupported.

That’s exactly why this additional stage can be valuable.

⚠️ Multi-Agent Does Not Automatically Mean Better

This is important.

Don’t create ten agents simply because you can.

Every additional agent introduces:

another API request
additional latency
more cost
more potential failure points
more context management
another output to validate

Use specialized agents when the separation provides real value.

For a simple task:

Summarize this email.

One Gemini request may be enough.

For:

Analyze business data,
create recommendations,
write an executive report,
verify the claims,
and publish the result.

A pipeline becomes much more interesting.

🔥 Challenge 1 — Add a Data Quality Agent

Before research begins:

DATA
 ↓
DATA QUALITY AGENT
 ↓
RESEARCH AGENT

The Data Quality Agent checks:

missing values
inconsistent formats
duplicate rows
suspicious numbers
incomplete columns

Then it warns downstream agents about weaknesses in the dataset.

🔥 Challenge 2 — Add a Critic Agent

Instead of reviewing grammar, create a specialist whose job is to challenge the analysis.

Ask:

“What alternative explanations could account for these findings?”

Your pipeline becomes:

Research
   ↓
Analysis
   ↓
Critic
   ↓
Report

This can help reduce overly confident conclusions.

🔥 Challenge 3 — Confidence Scores

Ask every agent to include:

{
  "confidence": 0.87
}

Then define a threshold:

const MIN_CONFIDENCE = 0.75;

Low-confidence outputs could require:

another review
more data
user confirmation

🔥 Challenge 4 — Parallel Agents

Not every workflow needs to be sequential.

Imagine:

             SOURCE DATA
                 │
        ┌────────┼────────┐
        ▼        ▼        ▼
     SALES    RETURNS   ORDERS
     AGENT     AGENT     AGENT
        │        │        │
        └────────┼────────┘
                 ▼
          SYNTHESIS AGENT
                 ▼
              REPORT

Different agents analyze different aspects of the same dataset.

Then another agent combines the results.

🔥 Challenge 5 — Human Review

Before creating the final document:

AI REVIEW
    ↓
USER PREVIEW
    ↓
APPROVE
    ↓
CREATE DOCUMENT

Human-in-the-loop design becomes increasingly important as workflows become more powerful.

🔥 Challenge 6 — Agent Performance Tracking

Extend the log with:

Agent
Duration
Input Size
Output Size
Review Score
Revision Required
Success

Now you can begin measuring which prompts and agents perform best.

🧠 Shared Context

One of the biggest challenges in multi-agent systems is deciding:

What information does each agent actually need?

You could send everything to every agent.

But that isn’t always ideal.

Instead:

Research Agent
→ receives raw data

Analysis Agent
→ receives research brief

Report Agent
→ receives research + analysis

Review Agent
→ receives research + analysis + report

This is controlled context passing.

Each agent receives the information required for its responsibility.

🧠 Agent Handoffs

Think of every agent’s output as a contract.

Research returns:

{
  "importantFacts": [],
  "possiblePatterns": [],
  "dataLimitations": []
}

Analysis knows exactly what it will receive.

This is much more reliable than passing unstructured conversations between agents.

🌟 The Bigger Lesson

The goal isn’t to create AI personalities talking to each other.

The useful idea is much simpler:

Break complex AI work into specialized, testable stages.

Instead of:

ONE MASSIVE PROMPT
        ↓
     RESULT

we can build:

SOURCE
   ↓
RESEARCH
   ↓
ANALYSIS
   ↓
REPORT
   ↓
REVIEW
   ↓
FINAL OUTPUT

And Apps Script controls every handoff.

That gives us:

✔ specialization

✔ structured context

✔ validation

✔ retries

✔ logging

✔ Google Workspace integration

✔ human approval

The AI components provide intelligence.

Apps Script provides orchestration and control.

🚀 Where This Gets Interesting

We’ve now built several important pieces throughout this series.

We have:

🧠 Prompt engineering

🐞 AI debugging

📊 AI dashboards

🏗️ AI project scaffolding

🤖 AI agents

🛠️ Controlled tools

🔗 Multi-step workflows

👥 Specialized AI agents

🛡️ Review stages

📋 Execution logs

Put those pieces together and we’re getting much closer to building complete AI-powered Google Workspace applications.

🧪 Your Challenge

Start with the four-agent workflow:

RESEARCH
   ↓
ANALYSIS
   ↓
REPORT
   ↓
REVIEW

Run it against a real spreadsheet.

Then deliberately introduce a problem.

For example:

remove several values
add an unusually large number
create inconsistent categories
provide only a few rows of data

Watch what happens at each stage.

Does the Research Agent notice it?

Does the Analysis Agent become more cautious?

Does the Report Agent acknowledge the limitation?

Does the Review Agent catch unsupported conclusions?

That’s where you’ll begin seeing the real value of the architecture.

🔜 Next Issue — #33

Build an AI Workspace Command Center

We’ve built individual AI tools.

We’ve built agents.

We’ve built multi-agent workflows.

Next, we’ll bring them together into one Google Workspace AI interface.

Imagine opening a sidebar and typing:

“Analyze this spreadsheet.”

or:

“Create a report.”

or:

“Summarize this data.”

or:

“Review this Apps Script.”

or:

“Prepare an email.”

The Command Center determines what you want and routes the request to the appropriate AI capability.

We’ll build:

🎛️ A unified AI sidebar

🧠 Intent detection

🔀 Intelligent request routing

🛠️ Reusable tool modules

🤖 Agent selection

📚 Prompt templates

📋 Workflow history

🛡️ Approval controls

📊 Sheets integration

📄 Docs integration

📧 Gmail preparation

Instead of building another isolated AI feature, we’ll start assembling the pieces into a reusable AI operating layer for Google Workspace.

One interface. Multiple AI capabilities. Google Apps Script coordinating everything.