🚀 Apps Script + Gemini Mastery — Issue #33
https://github.com/lsvekis/Apps-Script-Code-Snippets
Build an AI Workspace Command Center with Google Apps Script + Gemini
Over the last several issues, we’ve built increasingly powerful AI tools.
We’ve created AI systems that can:
🐞 Debug Apps Script
🏗️ Scaffold projects
📊 Build dashboards
🧠 Engineer prompts
🤖 Plan automation workflows
👥 Coordinate multiple AI agents
🛡️ Review AI-generated results
But there’s a problem.
Every capability has its own interface.
What if we could bring them together?
Imagine opening one sidebar inside Google Workspace and typing:
“Analyze this spreadsheet and tell me what matters.”
Or:
“Create an executive report from this data.”
Or:
“Review this Apps Script function for problems.”
Or:
“Write a professional email summarizing these results.”
Instead of choosing a tool first, the system determines what you’re trying to accomplish.
That’s what we’re building today.
🎛️ The AI Workspace Command Center
Our architecture becomes:
USER REQUEST
↓
AI COMMAND CENTER
↓
INTENT ROUTER
↓
┌────┼─────┬─────┐
↓ ↓ ↓ ↓
DATA REPORT CODE EMAIL
↓ ↓ ↓ ↓
TOOLS / AGENTS / WORKFLOWS
↓
RESULT
One interface.
Multiple AI capabilities.
Apps Script controls the routing.
Gemini understands the intent.
Google Workspace provides the tools.
⭐ What We’re Building
The Command Center will:
✅ Accept natural-language instructions
✅ Detect what the user wants
✅ Route requests to specialized capabilities
✅ Analyze Google Sheets data
✅ Create Google Docs reports
✅ Review Apps Script code
✅ Generate email content
✅ Summarize spreadsheet information
✅ Require confirmation for selected actions
✅ Record command history
Most importantly, we’ll design it so new capabilities can be added later without rebuilding the entire application.
🧠 The Core Idea: Intent Routing
Suppose the user types:
“Analyze the active spreadsheet.”
Gemini might classify that as:
{
"intent": "ANALYZE_DATA",
"confidence": 0.97
}
But:
“Create an executive report from this spreadsheet.”
might become:
{
"intent": "CREATE_REPORT",
"confidence": 0.96
}
And:
“Check this Apps Script code for bugs.”
could become:
{
"intent": "REVIEW_CODE",
"confidence": 0.99
}
We’re separating two responsibilities:
UNDERSTAND REQUEST
↓
CHOOSE CAPABILITY
↓
EXECUTE CAPABILITY
That separation is extremely useful.
🧱 Step 1 — Create the Command Center Menu
Code.gs
function onOpen() {
SpreadsheetApp.getUi()
.createMenu("AI Command Center")
.addItem(
"Open Command Center",
"showCommandCenter"
)
.addToUi();
}
function showCommandCenter() {
const html =
HtmlService
.createHtmlOutputFromFile(
"CommandCenter"
)
.setTitle(
"AI Workspace Command Center"
);
SpreadsheetApp
.getUi()
.showSidebar(html);
}
Now our Google Sheet has one entry point for the entire AI system.
🎛️ Step 2 — Build the Command Center Interface
CommandCenter.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
body {
font-family: Arial, sans-serif;
padding: 16px;
}
textarea {
width: 100%;
height: 130px;
box-sizing: border-box;
resize: vertical;
}
button {
width: 100%;
margin-top: 10px;
padding: 11px;
cursor: pointer;
}
#status {
margin-top: 15px;
padding: 12px;
background: #f5f5f5;
white-space: pre-wrap;
}
.example {
cursor: pointer;
margin: 6px 0;
color: #1a73e8;
}
</style>
</head>
<body>
<h2>AI Workspace</h2>
<p>What would you like to do?</p>
<textarea id="command"
placeholder="Describe a task..."></textarea>
<button onclick="runCommand()">
Run Command
</button>
<h3>Try:</h3>
<div class="example"
onclick="setCommand('Analyze this spreadsheet')">
Analyze this spreadsheet
</div>
<div class="example"
onclick="setCommand('Create an executive report')">
Create an executive report
</div>
<div class="example"
onclick="setCommand('Summarize this spreadsheet')">
Summarize this spreadsheet
</div>
<pre id="status">Ready.</pre>
<script>
function setCommand(text) {
document
.getElementById("command")
.value = text;
}
function runCommand() {
const command =
document
.getElementById("command")
.value;
const status =
document
.getElementById("status");
status.textContent =
"Understanding request...";
google.script.run
.withSuccessHandler(
function(result) {
status.textContent =
JSON.stringify(
result,
null,
2
);
}
)
.withFailureHandler(
function(error) {
status.textContent =
"Error: " +
error.message;
}
)
.processCommand(command);
}
</script>
</body>
</html>
This interface doesn’t ask the user to select:
Analyze Data
or:
Create Report
The user simply describes the goal.
🧠 Step 3 — Define the Available Capabilities
We don’t want Gemini inventing commands.
Create a registry.
CapabilityRegistry.gs
function getCapabilities_() {
return [
{
name: "ANALYZE_DATA",
description:
"Analyze data from the active spreadsheet.",
risk: "LOW"
},
{
name: "SUMMARIZE_DATA",
description:
"Create a concise summary of spreadsheet data.",
risk: "LOW"
},
{
name: "CREATE_REPORT",
description:
"Analyze spreadsheet data and create a Google Doc report.",
risk: "MEDIUM"
},
{
name: "REVIEW_CODE",
description:
"Review Apps Script code supplied by the user.",
risk: "LOW"
},
{
name: "PREPARE_EMAIL",
description:
"Prepare professional email content without sending it.",
risk: "MEDIUM"
}
];
}
This registry becomes the contract between Gemini and our application.
Gemini can choose a capability.
It cannot create one.
🔀 Step 4 — Build the Intent Router
IntentRouter.gs
function detectIntent_(
command
) {
const capabilities =
getCapabilities_();
const prompt = `
You are an intent router for a
Google Workspace AI application.
AVAILABLE CAPABILITIES:
${JSON.stringify(
capabilities,
null,
2
)}
USER COMMAND:
${command}
Return JSON only:
{
"intent": "",
"confidence": 0,
"reason": ""
}
RULES:
1. Choose only an available capability.
2. Do not invent capabilities.
3. confidence must be between 0 and 1.
4. Choose the capability that best
matches the user's primary objective.
`;
return parseGeminiJson_(
callGemini(
prompt,
""
)
);
}
Gemini isn’t doing the task yet.
It’s only deciding where the task belongs.
🛡️ Step 5 — Validate the Intent
Never blindly trust model output.
IntentValidator.gs
const MIN_INTENT_CONFIDENCE =
0.70;
function validateIntent_(
routing
) {
const allowed =
getCapabilities_()
.map(function(item) {
return item.name;
});
if (
!allowed.includes(
routing.intent
)
) {
throw new Error(
"Unsupported intent: " +
routing.intent
);
}
if (
Number(
routing.confidence
) <
MIN_INTENT_CONFIDENCE
) {
throw new Error(
"I'm not confident enough " +
"to route this request."
);
}
return true;
}
Now we have an important pattern:
Gemini suggests.
Apps Script validates.
That pattern should look familiar from our agent architecture.
⚙️ Step 6 — Create the Main Command Processor
CommandProcessor.gs
function processCommand(
command
) {
if (
!command ||
!command.trim()
) {
throw new Error(
"Enter a command."
);
}
const commandId =
Utilities.getUuid();
const routing =
detectIntent_(
command
);
validateIntent_(
routing
);
logCommand_(
commandId,
command,
routing.intent,
routing.confidence
);
let result;
switch (
routing.intent
) {
case "ANALYZE_DATA":
result =
executeDataAnalysis_(
command
);
break;
case "SUMMARIZE_DATA":
result =
executeDataSummary_(
command
);
break;
case "CREATE_REPORT":
result =
executeReportWorkflow_(
command
);
break;
case "REVIEW_CODE":
result =
executeCodeReview_(
command
);
break;
case "PREPARE_EMAIL":
result =
executeEmailPreparation_(
command
);
break;
default:
throw new Error(
"No handler available."
);
}
return {
commandId:
commandId,
routing:
routing,
result:
result
};
}
This is our central dispatcher.
📊 Step 7 — Create the Spreadsheet Reader
Several capabilities need spreadsheet data.
Instead of rewriting that logic repeatedly, create a reusable tool.
SheetReader.gs
const MAX_DATA_ROWS =
100;
function readActiveSheet_() {
const sheet =
SpreadsheetApp
.getActiveSheet();
const lastRow =
sheet.getLastRow();
const lastColumn =
sheet.getLastColumn();
if (
!lastRow ||
!lastColumn
) {
throw new Error(
"The active sheet contains no data."
);
}
const rows =
Math.min(
lastRow,
MAX_DATA_ROWS
);
return {
sheetName:
sheet.getName(),
totalRows:
lastRow,
rowsRead:
rows,
values:
sheet
.getRange(
1,
1,
rows,
lastColumn
)
.getDisplayValues()
};
}
Now multiple AI capabilities can share the same trusted data-access function.
📊 Step 8 — Data Analysis Capability
DataAnalysis.gs
function executeDataAnalysis_(
command
) {
const data =
readActiveSheet_();
const prompt = `
You are a business data analyst.
USER REQUEST:
${command}
SPREADSHEET DATA:
${JSON.stringify(data)}
Identify:
- important trends
- anomalies
- notable values
- opportunities
- concerns
- recommended actions
Do not invent information.
Clearly identify limitations.
`;
const analysis =
callGemini(
prompt,
""
);
return {
type:
"ANALYSIS",
content:
analysis
};
}
📝 Step 9 — Data Summary Capability
Sometimes the user doesn’t need deep analysis.
They simply want a summary.
DataSummary.gs
function executeDataSummary_(
command
) {
const data =
readActiveSheet_();
const prompt = `
Summarize the spreadsheet data.
USER REQUEST:
${command}
DATA:
${JSON.stringify(data)}
Provide:
- what the dataset contains
- major values
- obvious patterns
- important limitations
Keep the response concise.
Do not invent facts.
`;
return {
type:
"SUMMARY",
content:
callGemini(
prompt,
""
)
};
}
Notice how both capabilities use the same spreadsheet reader but different AI instructions.
📄 Step 10 — Report Workflow
Now we can reuse the multi-agent architecture from Issue #32.
ReportWorkflow.gs
function executeReportWorkflow_(
command
) {
const data =
readActiveSheet_();
const research =
runResearchAgent_(
command,
data
);
const analysis =
runAnalysisAgent_(
command,
research
);
let report =
runReportAgent_(
command,
research,
analysis
);
let review =
runReviewAgent_(
research,
analysis,
report
);
if (
review.status ===
"REVISE"
) {
report =
reviseReport_(
report,
review,
research,
analysis
);
}
const doc =
createCommandCenterReport_(
report
);
return {
type:
"REPORT",
review:
review,
documentUrl:
doc.documentUrl
};
}
We’re no longer building isolated projects.
We’re starting to reuse capabilities.
That’s a major architectural shift.
💻 Step 11 — Code Review Capability
For a spreadsheet-bound project, one simple approach is to let users paste code into the command.
CodeReview.gs
function executeCodeReview_(
command
) {
const prompt = `
You are an expert Google Apps Script
code reviewer.
Review the code or programming
request supplied by the user.
USER INPUT:
${command}
Check for:
- syntax problems
- runtime errors
- Apps Script API mistakes
- unnecessary API calls
- performance issues
- maintainability problems
- security concerns
Return:
1. Summary
2. Problems Found
3. Recommended Fixes
4. Improved Code if appropriate
5. Testing Suggestions
Do not invent errors.
`;
return {
type:
"CODE_REVIEW",
content:
callGemini(
prompt,
""
)
};
}
Now the same sidebar can handle programming requests too.
📧 Step 12 — Email Preparation Capability
EmailPreparation.gs
function executeEmailPreparation_(
command
) {
const prompt = `
You are a professional email assistant.
USER REQUEST:
${command}
Prepare:
SUBJECT
and
BODY
Do not invent:
- recipient addresses
- names
- dates
- commitments
- factual details
If essential information is missing,
clearly identify what is needed.
Do not send anything.
`;
const content =
callGemini(
prompt,
""
);
return {
type:
"EMAIL_DRAFT",
content:
content,
sent:
false
};
}
Again, notice the boundary:
Prepare email
is different from:
Send email
Sending would be a higher-risk capability.
🛡️ Step 13 — Add Risk Levels
We already included risk levels in the registry.
Let’s actually use them.
RiskManager.gs
function getCapabilityRisk_(
intent
) {
const capability =
getCapabilities_()
.find(function(item) {
return item.name ===
intent;
});
if (!capability) {
throw new Error(
"Capability not found."
);
}
return capability.risk;
}
Now we can classify actions:
| Capability | Risk |
|---|---|
| Analyze data | LOW |
| Summarize data | LOW |
| Review code | LOW |
| Create Google Doc | MEDIUM |
| Prepare email | MEDIUM |
| Send email | HIGH |
| Delete Drive file | HIGH |
This gives us a foundation for approval policies.
🛡️ Step 14 — Approval Rules
Imagine later adding:
function requiresApproval_(
intent
) {
const risk =
getCapabilityRisk_(
intent
);
return (
risk === "MEDIUM" ||
risk === "HIGH"
);
}
Then our architecture becomes:
REQUEST
↓
ROUTE
↓
VALIDATE
↓
CHECK RISK
↓
┌─────────────┐
│ APPROVAL? │
└──────┬──────┘
↓
EXECUTE
This becomes increasingly important as the Command Center gains more powerful Workspace capabilities.
📋 Step 15 — Command History
Let’s record what users ask the system to do.
CommandLogger.gs
function logCommand_(
commandId,
command,
intent,
confidence
) {
const ss =
SpreadsheetApp
.getActiveSpreadsheet();
let sheet =
ss.getSheetByName(
"AI Command History"
);
if (!sheet) {
sheet =
ss.insertSheet(
"AI Command History"
);
sheet.appendRow([
"Timestamp",
"Command ID",
"Command",
"Intent",
"Confidence"
]);
sheet.setFrozenRows(1);
}
sheet.appendRow([
new Date(),
commandId,
command,
intent,
confidence
]);
}
Now every request has an ID.
That becomes useful for:
- debugging
- analytics
- audit history
- performance tracking
- user feedback
- prompt optimization
📄 Step 16 — Google Docs Output
DocumentWriter.gs
function createCommandCenterReport_(
report
) {
const doc =
DocumentApp.create(
"AI Workspace Report"
);
const body =
doc.getBody();
body
.appendParagraph(
"AI Workspace Report"
)
.setHeading(
DocumentApp
.ParagraphHeading
.TITLE
);
body.appendParagraph(
"Generated: " +
new Date()
);
body.appendParagraph(
report
);
return {
documentId:
doc.getId(),
documentUrl:
doc.getUrl()
};
}
🧠 Step 17 — Gemini Helper
As with our recent projects, keep the API key out of the source code.
Store:
GEMINI_API_KEY
in:
Apps Script → Project Settings → Script Properties
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 18 — JSON Helper
Utilities.gs
function cleanJsonResponse_(
text
) {
return String(
text || ""
)
.replace(
/```json/gi,
""
)
.replace(
/```/g,
""
)
.trim();
}
function parseGeminiJson_(
text
) {
const cleaned =
cleanJsonResponse_(
text
);
try {
return JSON.parse(
cleaned
);
} catch (error) {
throw new Error(
"Gemini returned invalid JSON."
);
}
}
🧪 Test the Router
Try these commands individually.
Command 1
Analyze this spreadsheet and identify anything management should know.
Expected:
ANALYZE_DATA
Command 2
Give me a quick overview of what’s in this sheet.
Expected:
SUMMARIZE_DATA
Command 3
Create an executive performance report from this data.
Expected:
CREATE_REPORT
Command 4
Review this Apps Script function and tell me why it fails.
Expected:
REVIEW_CODE
Command 5
Write an email explaining these results to management.
Expected:
PREPARE_EMAIL
The user doesn’t need to know which internal tool handles the task.
They describe the goal.
The application handles the routing.
🔥 Challenge 1 — Multi-Intent Requests
What happens when someone says:
“Analyze the spreadsheet, create a report, and prepare an email.”
That’s not really one intent.
Upgrade the router to return:
{
"goal": "",
"intents": [
"ANALYZE_DATA",
"CREATE_REPORT",
"PREPARE_EMAIL"
]
}
Now the Command Center becomes a workflow planner.
We’ve connected Issue #31’s agent architecture with today’s intent-routing architecture.
🔥 Challenge 2 — Add Calendar Capabilities
Add:
READ_CALENDAR
CREATE_EVENT
Then the user could type:
“Show me my schedule for tomorrow.”
or:
“Prepare a meeting for the project review.”
Creating an event should require stronger validation than simply reading information.
🔥 Challenge 3 — Add Gmail Intelligence
Possible capabilities:
SUMMARIZE_EMAILS
SEARCH_EMAILS
PREPARE_REPLY
CREATE_DRAFT
Then imagine:
“Summarize the important messages from this project and prepare responses.”
Now the Command Center starts becoming genuinely useful as a Workspace assistant.
🔥 Challenge 4 — Add Drive Capabilities
Possible tools:
SEARCH_DRIVE
SUMMARIZE_DOCUMENT
CREATE_DOCUMENT
ORGANIZE_FILES
But again:
SEARCH
and:
DELETE
should never have the same risk classification.
🔥 Challenge 5 — Add Conversation Context
Right now every command stands alone.
We could maintain a small context object:
{
lastIntent: "",
lastDocument: "",
lastAnalysis: "",
lastCommand: ""
}
Then users could say:
“Analyze this spreadsheet.”
followed by:
“Now create a report.”
and finally:
“Prepare an email about it.”
The system understands that it refers to the previous analysis.
That’s the beginning of a much more natural Workspace assistant.
🔥 Challenge 6 — Add Command Suggestions
After completing a task, Gemini could suggest logical next actions.
After analysis:
Suggested next actions:
→ Create executive report
→ Build dashboard
→ Prepare email summary
After creating a report:
→ Prepare email
→ Create presentation
→ Review recommendations
The Command Center begins helping users discover workflows rather than requiring them to know every available command.
🔥 Challenge 7 — Dynamic Capability Registry
Today our capabilities are hardcoded.
Eventually, imagine registering modules like:
registerCapability_({
name:
"BUILD_DASHBOARD",
description:
"Create a dashboard from spreadsheet data.",
risk:
"MEDIUM",
handler:
"executeDashboardBuilder_"
});
Now adding a new AI capability becomes closer to installing a plugin.
🌟 The Architecture We’ve Built
Step back and look at what has happened across the last few issues.
We started with individual AI tools.
Then:
PROMPTS
↓
AI TOOLS
↓
AI AGENTS
↓
MULTI-AGENT WORKFLOWS
↓
AI COMMAND CENTER
The Command Center sits above everything.
USER
│
▼
AI WORKSPACE COMMAND CENTER
│
▼
INTENT ROUTER
│
┌────────────┼────────────┐
▼ ▼ ▼
TOOLS AGENTS WORKFLOWS
│ │ │
└────────────┼────────────┘
▼
GOOGLE WORKSPACE
This is much more powerful than adding Gemini to one spreadsheet function.
We’re designing an AI application architecture.
🧠 The Most Important Principle
The language model should not control your application.
It should participate in your application.
Gemini can:
🧠 understand intent
🔀 recommend routes
📊 analyze information
✍️ generate content
🤖 plan workflows
But Apps Script should control:
🛡️ permissions
🛠️ available tools
🔍 validation
👤 approval
🔄 retries
📋 logging
⚙️ execution
That boundary matters.
A useful rule:
AI interprets. Code governs. Users approve. Apps Script executes.
🚀 Where Could This Go?
Imagine opening one sidebar inside Google Workspace and typing:
“Find the important information in this spreadsheet, create a management report, prepare an email, and schedule a meeting to discuss it.”
The system could determine:
READ SHEET
↓
ANALYZE DATA
↓
CREATE REPORT
↓
PREPARE EMAIL
↓
PROPOSE MEETING
↓
USER APPROVAL
↓
EXECUTE
That’s no longer a single AI feature.
It’s an AI orchestration layer connecting Google Workspace applications.
And we’ve already built many of the pieces needed to make it happen.
🧪 Your Challenge
Start with these five capabilities:
ANALYZE_DATA
SUMMARIZE_DATA
CREATE_REPORT
REVIEW_CODE
PREPARE_EMAIL
Run at least ten different natural-language commands through the router.
Don’t just test obvious wording.
Try:
“What jumps out at you from these numbers?”
“Turn this into something I can show management.”
“Can you find what’s wrong with this function?”
“Give me the short version of this data.”
“Help me explain these results in an email.”
Then inspect:
intent
confidence
reason
Where does the router fail?
Those failures will teach you how to improve the capability descriptions and routing prompt.
🔜 Next Issue — #34
Build an AI Workflow Memory System
Our Command Center can understand a request.
But every request currently starts almost from scratch.
Next we’ll give our Workspace AI something extremely useful:
controlled workflow memory.
Instead of:
“Analyze this spreadsheet.”
“Create a report from the spreadsheet.”
“Prepare an email about the report.”
the system can understand the sequence:
ANALYSIS
↓
REPORT
↓
EMAIL
We’ll explore:
🧠 Session context
🔗 References between commands
📋 Workflow state
💾 PropertiesService storage
🗂️ Structured context objects
⏳ Context expiration
🛡️ Safe memory boundaries
🔄 Continuing previous workflows
🧹 Clearing stored context
The goal isn’t to make Gemini remember everything.
It’s to let our application deliberately decide what should be remembered, for how long, and why.
That gives us another important principle:
Don’t give AI unlimited memory. Give your application controlled state.