Build an AI Automation Agent for Google Workspace

by

🚀 Apps Script + Gemini Mastery — Issue #31

Build an AI Automation Agent for Google Workspace

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

Until now, most of the AI projects in this series have followed a familiar pattern:

User asks → Gemini analyzes → Gemini returns an answer.

This issue takes the next step.

We’re going to let Gemini decide which approved Apps Script tools should be used to complete a task.

Imagine asking:

“Analyze the sales data in this spreadsheet, create a Google Doc with the key findings, and prepare an email summary.”

Instead of returning instructions for you to follow, our application can determine that it needs to:

readSheetData
      ↓
analyzeSalesData
      ↓
createReport
      ↓
prepareEmail

Apps Script then executes those approved actions.

We’re beginning to move from an AI assistant toward an AI automation agent.

The important part is that Gemini does not receive unrestricted access to Google Workspace.

We define exactly which tools it can request.

⭐ What You Will Build

In this issue, you’ll create an AI Automation Agent that can:

🤖 Understand a natural-language request

🧠 Decide which Workspace tools are required

📊 Read spreadsheet data

📄 Create Google Docs reports

📧 Prepare Gmail drafts

🔗 Chain multiple actions together

🛡️ Validate AI-requested actions

📋 Maintain an execution log

👤 Require approval before sensitive actions

This introduces one of the most important patterns in practical AI application development:

Controlled tool use.

🧠 From Chatbot to Agent

Consider this request:

“Create a report from this month’s sales and draft an email to management.”

A traditional AI application might respond:

First analyze the spreadsheet, then create a document, then write an email.

Useful—but you still have to perform the work.

Our agent instead creates an execution plan:

1. readSheetData
2. analyzeData
3. createGoogleDoc
4. createGmailDraft

Apps Script executes those functions.

That creates a fundamentally different workflow.

🧩 Architecture

User Request
     │
     ▼
┌──────────────────────┐
│       Gemini         │
│ Understand Intent    │
└──────────┬───────────┘
           │
           ▼
   Structured Plan
           │
           ▼
┌──────────────────────┐
│   Tool Validator     │
│ Is action allowed?   │
└──────────┬───────────┘
           │
           ▼
     User Approval
           │
           ▼
┌──────────────────────┐
│ Apps Script Executor │
└──────────┬───────────┘
           │
           ▼
   Google Workspace
     ↙     ↓      ↘
 Sheets   Docs   Gmail
           │
           ▼
     Execution Log

Notice the separation between:

Planning

and

Execution

Gemini plans.

Apps Script validates and executes.

🧱 Step 1 — Create the Agent Menu

Code.gs

function onOpen() {

  SpreadsheetApp.getUi()
    .createMenu("AI Tools")
    .addItem(
      "AI Automation Agent",
      "showAgentSidebar"
    )
    .addToUi();

}

function showAgentSidebar() {

  const html =
    HtmlService
      .createHtmlOutputFromFile("Sidebar")
      .setTitle("AI Automation Agent");

  SpreadsheetApp
    .getUi()
    .showSidebar(html);

}

🧱 Step 2 — Create the Agent Sidebar

Sidebar.html

<!DOCTYPE html>
<html>

<head>

<base target="_top">

<style>

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

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

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

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

</style>

</head>

<body>

<h2>AI Automation Agent</h2>

<p>
Describe what you want the agent to accomplish.
</p>

<textarea id="request">
Analyze the sales data in this sheet,
create a Google Doc report,
and prepare an email summary.
</textarea>

<button onclick="planWorkflow()">
Generate Plan
</button>

<button
  id="executeButton"
  onclick="executeWorkflow()"
  disabled>
Approve & Execute
</button>

<pre id="output"></pre>

<script>

let currentPlan = null;

function planWorkflow() {

  output.textContent =
    "Planning workflow...";

  google.script.run

    .withSuccessHandler(function(plan) {

      currentPlan = plan;

      output.textContent =
        JSON.stringify(
          plan,
          null,
          2
        );

      executeButton.disabled = false;

    })

    .withFailureHandler(function(error) {

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

    })

    .createAgentPlan(
      request.value
    );

}

function executeWorkflow() {

  if (!currentPlan) {
    return;
  }

  executeButton.disabled = true;

  output.textContent =
    "Executing approved workflow...";

  google.script.run

    .withSuccessHandler(function(result) {

      output.textContent =
        JSON.stringify(
          result,
          null,
          2
        );

    })

    .withFailureHandler(function(error) {

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

      executeButton.disabled = false;

    })

    .executeAgentPlan(
      currentPlan
    );

}

</script>

</body>

</html>

The approval button is important.

The AI can propose actions.

It doesn’t automatically receive permission to perform them.

🧱 Step 3 — Define the Tools

Instead of telling Gemini:

“You can do anything in Google Workspace.”

we give it a specific list.

ToolRegistry.gs

function getAvailableTools_() {

  return [

    {
      name: "readSheetData",
      description:
        "Read data from the active Google Sheet."
    },

    {
      name: "analyzeData",
      description:
        "Analyze spreadsheet data and return key findings."
    },

    {
      name: "createGoogleDoc",
      description:
        "Create a Google Doc containing a report."
    },

    {
      name: "createGmailDraft",
      description:
        "Create a Gmail draft. Does not send email."
    }

  ];

}

Notice something deliberate here.

We provide:

createGmailDraft

not:

sendEmail

For our first agent, drafting is safer than automatically sending.

The user remains in control.

🧱 Step 4 — Ask Gemini to Create a Plan

AgentPlanner.gs

function createAgentPlan(userRequest) {

  if (!userRequest.trim()) {

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

  }

  const tools =
    getAvailableTools_();

  const prompt = `
You are a Google Workspace automation planner.

Determine which approved tools are needed
to complete the user's request.

AVAILABLE TOOLS:

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

Return JSON only.

Use this structure:

{
  "goal": "",
  "steps": [
    {
      "tool": "",
      "reason": ""
    }
  ]
}

RULES:

1. Use only tools from AVAILABLE TOOLS.
2. Do not invent tools.
3. Use the minimum number of tools required.
4. Put steps in execution order.
5. Do not execute anything.
6. Do not claim an action has occurred.
7. If the request cannot be completed with
   the available tools, explain that in "goal"
   and return only the supported steps.

USER REQUEST:

${userRequest}
`;

  let result =
    callGemini(
      prompt,
      ""
    );

  result =
    cleanJsonResponse_(
      result
    );

  const plan =
    JSON.parse(result);

  validateAgentPlan_(plan);

  return plan;

}

This is a crucial design principle.

Gemini does not decide what capabilities exist.

Our application does.

🧱 Step 5 — Validate the Plan

Never assume that an AI-generated tool name is valid.

AgentValidator.gs

function validateAgentPlan_(plan) {

  if (
    !plan ||
    !Array.isArray(plan.steps)
  ) {

    throw new Error(
      "Invalid agent plan."
    );

  }

  const allowedTools =
    getAvailableTools_()
      .map(function(tool) {

        return tool.name;

      });

  plan.steps.forEach(
    function(step) {

      if (
        !allowedTools.includes(
          step.tool
        )
      ) {

        throw new Error(
          "Unsupported tool: " +
          step.tool
        );

      }

    }
  );

  return true;

}

This prevents Gemini from suddenly requesting something like:

deleteAllFiles

when no such capability exists.

🧱 Step 6 — Read Spreadsheet Data

SheetTool.gs

function toolReadSheetData_() {

  const sheet =
    SpreadsheetApp
      .getActiveSheet();

  const values =
    sheet
      .getDataRange()
      .getDisplayValues();

  return {

    sheetName:
      sheet.getName(),

    rows:
      values.length,

    data:
      values

  };

}

For a production system, you may want to limit how much data is returned.

For example:

const MAX_ROWS = 100;

Then only provide a controlled sample.

🧱 Step 7 — Analyze the Data

AnalysisTool.gs

function toolAnalyzeData_(
  sheetData
) {

  if (!sheetData) {

    throw new Error(
      "No spreadsheet data available."
    );

  }

  const prompt = `
You are a business data analyst.

Analyze the spreadsheet data below.

Identify:

- important trends
- notable values
- anomalies
- useful observations
- recommended actions

Do not invent information.

DATA:

${JSON.stringify(sheetData)}
`;

  return callGemini(
    prompt,
    ""
  );

}

Our agent can now use Gemini twice:

First as a planner.

Then as an analyst.

🧱 Step 8 — Create the Google Doc

DocsTool.gs

function toolCreateGoogleDoc_(
  analysis
) {

  if (!analysis) {

    throw new Error(
      "No analysis available."
    );

  }

  const doc =
    DocumentApp.create(
      "AI Sales Analysis"
    );

  const body =
    doc.getBody();

  body
    .appendParagraph(
      "AI Sales Analysis"
    )
    .setHeading(
      DocumentApp
        .ParagraphHeading
        .TITLE
    );

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

  body.appendParagraph(
    analysis
  );

  return {

    documentId:
      doc.getId(),

    documentUrl:
      doc.getUrl()

  };

}

🧱 Step 9 — Prepare the Email

GmailTool.gs

function toolCreateGmailDraft_(
  analysis,
  documentUrl
) {

  const prompt = `
Create a concise professional email
summarizing the following sales analysis.

Mention that the full report is available
at the provided Google Docs URL.

Do not invent recipient names.

ANALYSIS:

${analysis}

REPORT:

${documentUrl}
`;

  const body =
    callGemini(
      prompt,
      ""
    );

  return {

    subject:
      "Sales Analysis Report",

    body:
      body,

    status:
      "Email content prepared. Recipient required before draft creation."

  };

}

There is an important detail here.

The original user request didn’t specify a recipient.

We should not invent one.

So our agent prepares the email content but stops before creating the actual Gmail draft.

🧠 Agents Need Missing-Information Handling

Suppose the user says:

“Email the report to Sarah.”

Who is Sarah?

An agent should not guess.

A better workflow is:

User Request
      ↓
Agent detects missing recipient
      ↓
Ask user for email address
      ↓
Continue workflow

This becomes increasingly important as agents perform real actions.

🧱 Step 10 — Build the Executor

AgentExecutor.gs

function executeAgentPlan(plan) {

  validateAgentPlan_(plan);

  const context = {};

  const log = [];

  plan.steps.forEach(
    function(step) {

      const started =
        new Date();

      let result;

      switch (step.tool) {

        case "readSheetData":

          result =
            toolReadSheetData_();

          context.sheetData =
            result;

          break;

        case "analyzeData":

          result =
            toolAnalyzeData_(
              context.sheetData
            );

          context.analysis =
            result;

          break;

        case "createGoogleDoc":

          result =
            toolCreateGoogleDoc_(
              context.analysis
            );

          context.document =
            result;

          break;

        case "createGmailDraft":

          result =
            toolCreateGmailDraft_(
              context.analysis,
              context.document
                ? context.document.documentUrl
                : ""
            );

          context.email =
            result;

          break;

        default:

          throw new Error(
            "Unsupported tool."
          );

      }

      log.push({

        tool:
          step.tool,

        started:
          started,

        completed:
          new Date(),

        success:
          true

      });

    }
  );

  writeAgentLog_(
    plan,
    log
  );

  return {

    goal:
      plan.goal,

    results:
      context,

    executionLog:
      log

  };

}

🧱 Step 11 — Add an Execution Log

Agents need observability.

You should know:

  • what the AI requested
  • what actually ran
  • when it ran
  • whether it succeeded

AgentLogger.gs

function writeAgentLog_(
  plan,
  executionLog
) {

  const ss =
    SpreadsheetApp
      .getActiveSpreadsheet();

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

  if (!sheet) {

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

    sheet.appendRow([
      "Timestamp",
      "Goal",
      "Tool",
      "Success"
    ]);

  }

  executionLog.forEach(
    function(item) {

      sheet.appendRow([

        new Date(),

        plan.goal,

        item.tool,

        item.success

      ]);

    }
  );

}

Now every action leaves a record.

🔐 Why Approval Matters

Consider the difference between:

readSheetData

and:

sendEmail

Reading data is generally lower impact.

Sending an email represents the user externally.

Similarly:

createGoogleDoc

is different from:

deleteGoogleDoc

Agent tools should therefore have risk levels.

For example:

ToolRisk
Read spreadsheetLow
Analyze dataLow
Create documentMedium
Create draftMedium
Send emailHigh
Delete fileHigh

High-impact tools should require explicit confirmation.

🧱 Step 12 — Add Tool Risk Levels

Upgrade the registry.

function getAvailableTools_() {

  return [

    {
      name:
        "readSheetData",

      risk:
        "LOW"
    },

    {
      name:
        "analyzeData",

      risk:
        "LOW"
    },

    {
      name:
        "createGoogleDoc",

      risk:
        "MEDIUM"
    },

    {
      name:
        "createGmailDraft",

      risk:
        "MEDIUM"
    }

  ];

}

Your UI can now display:

PLANNED ACTIONS

✓ Read spreadsheet
✓ Analyze data
⚠ Create Google Doc
⚠ Prepare Gmail draft

[ APPROVE & EXECUTE ]

This makes agent behavior much more transparent.

🧪 Example Workflow

Suppose our spreadsheet contains:

MonthRegionRevenue
JanuaryEast42,000
JanuaryWest51,000
FebruaryEast48,000
FebruaryWest57,000
MarchEast61,000
MarchWest65,000

The user asks:

“Analyze these sales, create a report, and prepare an email summary.”

Gemini returns:

{
  "goal": "Analyze sales performance and prepare a report and email summary.",
  "steps": [
    {
      "tool": "readSheetData",
      "reason": "Sales data must be retrieved."
    },
    {
      "tool": "analyzeData",
      "reason": "The data must be analyzed."
    },
    {
      "tool": "createGoogleDoc",
      "reason": "The user requested a report."
    },
    {
      "tool": "createGmailDraft",
      "reason": "The user requested an email summary."
    }
  ]
}

The user reviews the plan.

Then clicks:

Approve & Execute

Apps Script performs the workflow.

🤯 Why This Is Powerful

We’ve separated the system into reusable layers.

Intelligence

Gemini determines intent.

Tools

Apps Script exposes controlled capabilities.

Validation

Our application decides what’s allowed.

Execution

Apps Script performs approved operations.

Logging

The system records what happened.

Approval

The user retains control.

That architecture can scale far beyond our simple sales example.

🔥 Challenge 1 — Add Calendar Tools

Add:

readCalendarEvents
createCalendarEvent

Then ask:

“Review my meetings for tomorrow and prepare a briefing document.”

The agent could plan:

readCalendarEvents
        ↓
analyzeMeetings
        ↓
createGoogleDoc

🔥 Challenge 2 — Add Gmail Tools

Add controlled functions such as:

searchEmails
summarizeEmails
createGmailDraft

Then request:

“Find recent customer support emails and create a summary.”

🔥 Challenge 3 — Tool Dependencies

What happens if Gemini requests:

createGoogleDoc

before:

analyzeData

Your validator can enforce dependencies.

For example:

const dependencies = {

  analyzeData:
    ["readSheetData"],

  createGoogleDoc:
    ["analyzeData"],

  createGmailDraft:
    ["analyzeData"]

};

Now you’re no longer merely validating tool names.

You’re validating workflow logic.

🔥 Challenge 4 — Add Execution Limits

Agents should have limits.

For example:

const MAX_AGENT_STEPS = 6;

Then:

if (
  plan.steps.length >
  MAX_AGENT_STEPS
) {

  throw new Error(
    "Agent plan exceeds maximum steps."
  );

}

Other useful limits include:

  • maximum rows read
  • maximum documents created
  • maximum drafts generated
  • maximum Gemini requests
  • execution timeout thresholds

🔥 Challenge 5 — Dry Run Mode

Before executing anything, return:

DRY RUN

1. Read active spreadsheet
2. Analyze 247 rows
3. Create Google Doc
4. Prepare Gmail draft

No actions have been executed.

Dry runs are especially useful when agents become more complex.

🧠 Don’t Give the Model Arbitrary Code Execution

One tempting approach would be to ask Gemini:

“Generate Apps Script code and execute it.”

Avoid designing the agent that way.

Instead:

Gemini
   ↓
Chooses from approved tools
   ↓
Validator
   ↓
Known Apps Script functions

The model selects capabilities.

It does not create arbitrary executable code.

That distinction makes the system much more predictable and controllable.

🔥 Advanced Agent Features

Once the basic version works, experiment with:

✅ Multi-step tool calling

✅ Tool dependencies

✅ Risk classifications

✅ Approval checkpoints

✅ Dry-run mode

✅ Execution limits

✅ Retry handling

✅ Error recovery

✅ Workflow history

✅ Agent memory stored in Sheets

✅ Scheduled agent workflows

✅ Gmail search tools

✅ Calendar tools

✅ Drive tools

✅ Docs tools

✅ Human-in-the-loop approvals

🌟 The Bigger Lesson

AI agents don’t need unlimited autonomy to be useful.

In fact, one of the most practical architectures is:

AI decides what should happen.

Your application decides what may happen.

The user decides what will happen.

Apps Script makes it happen.

That’s a powerful model for Google Workspace automation.

We already know how to connect Apps Script to:

📊 Google Sheets

📧 Gmail

📄 Google Docs

📅 Google Calendar

📁 Google Drive

🌐 External APIs

Now Gemini can become the intelligence layer that coordinates those capabilities.

But Apps Script remains the controlled execution layer.

That gives us something far more useful than a chatbot:

an AI-powered automation system.

🧪 Your Challenge

Create an agent with these four tools:

readSheetData
analyzeData
createGoogleDoc
createGmailDraft

Then test these requests:

“Analyze this spreadsheet.”

“Analyze this spreadsheet and create a report.”

“Analyze this spreadsheet and prepare an email.”

“Analyze this spreadsheet, create a report, and prepare an email.”

Gemini should select a different combination of tools depending on the request.

That’s when the agent starts to feel genuinely intelligent.

🔜 Next Issue — #32

AI Apps Script Multi-Agent Workflow

One agent is useful.

But what happens when we give different AI components specialized responsibilities?

In Issue #32 we’ll build a workflow with:

🔎 Research Agent — gathers and structures information

📊 Analysis Agent — finds patterns and insights

✍️ Report Agent — turns findings into polished content

🛡️ Review Agent — checks the result before delivery

Instead of one massive prompt trying to do everything, we’ll create a coordinated pipeline:

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

We’ll explore specialized prompts, shared context, agent handoffs, validation, retries, and how Apps Script can orchestrate the entire workflow across Google Workspace.

The next step isn’t just giving AI tools. It’s teaching multiple AI components how to work together.