Having explored the foundational services and theoretical building blocks in the previous sections, we now arrive at the most anticipated part of this case study: assembling the complete, functional script. You've seen the individual components for accessing Gmail, manipulating Sheets, and creating Calendar events. Now, we will weave them together into a single, cohesive AI-powered workflow.
This is where theory becomes practice. The following script is the engine for our entire invoice and meeting scheduling system. It's designed to be the central nervous system of our automation, capable of reading incoming emails, understanding intent using a generative AI model, and taking precise actions within your Google Workspace.
Before we dive into the code itself, let's visualize the workflow our script will execute. Understanding this flow is crucial for debugging and future modifications. The process starts with a trigger, scans for specific emails, and then branches based on the AI's analysis.
graph TD
A[Time-Driven Trigger] --> B{Scan Gmail for unread emails with 'invoice' or 'meeting'};
B --> C{For Each New Email};
C --> D[Extract Email Body Text];
D --> E{Call Generative AI API with Prompt};
E --> F[Receive Structured JSON Response];
F --> G{Parse AI Response: Intent & Entities};
G --> H{Is intent 'log_invoice'?};
G --> I{Is intent 'schedule_meeting'?};
H -- Yes --> J[Append Data to Google Sheet];
I -- Yes --> K[Create Google Calendar Event];
J --> L[Mark Email as Read];
K --> L[Mark Email as Read];
As the diagram shows, the logic is straightforward but powerful. Now, let's look at the complete Google Apps Script that brings this diagram to life. Copy the entire block of code below and paste it into a new script file in your Google Workspace Studio project.
/**
* @fileoverview Main script for AI-powered invoice and meeting processing.
* This script scans Gmail, uses a generative AI to understand emails,
* and then logs invoices to a Sheet or schedules meetings in Calendar.
*/
// --- CONFIGURATION ---
// IMPORTANT: Replace these placeholder values with your actual data.
const SPREADSHEET_ID = 'YOUR_SPREADSHEET_ID_HERE'; // The ID of your Google Sheet
const SHEET_NAME = 'Invoices'; // The name of the tab where invoices are logged
const GEMINI_API_KEY = 'YOUR_GEMINI_API_KEY_HERE'; // Your Google AI Studio API Key
const GEMINI_MODEL = 'gemini-1.5-flash'; // Or any other suitable model
const GMAIL_SEARCH_QUERY = 'is:unread label:process-me'; // Example query to find relevant emails
/**
* The main function, intended to be run by a time-driven trigger.
* It orchestrates the entire workflow from reading emails to taking action.
*/
function processIncomingRequests() {
const threads = GmailApp.search(GMAIL_SEARCH_QUERY);
for (const thread of threads) {
const message = thread.getMessages()[0]; // Process the first message in the thread
const emailBody = message.getPlainBody();
try {
const aiResponse = callGenerativeAI(emailBody);
if (aiResponse) {
processAIResponse(aiResponse);
GmailApp.markThreadRead(thread); // Mark as read only on successful processing
}
} catch (e) {
console.error(`Failed to process email thread ${thread.getId()}: ${e.toString()}`);
// Optional: Add a label 'processing-failed' to the email thread
}
}
}
/**
* Calls the generative AI model with the email content to get structured data.
* @param {string} textContent The plain text from the email body.
* @return {object | null} A JavaScript object parsed from the AI's JSON response, or null on failure.
*/
function callGenerativeAI(textContent) {
const prompt = `
Analyze the following email text and determine the user's intent. The possible intents are 'log_invoice' orLet's quickly break down the key functions you've just assembled. The processIncomingRequests() function is your entry point; it's the one you'll connect to a time-based trigger to run automatically every hour or every day. It finds the right emails and passes them one by one to the AI.
The callGenerativeAI() function is the heart of the intelligence. Notice the detailed prompt engineering—we're not just asking a question, we are instructing the AI on its role, the exact format for its response, and providing clear examples. This structured prompting is a critical skill for building reliable AI workflows. Remember to replace the placeholder GEMINI_API_KEY with your actual key.
Finally, the processAIResponse() function acts as a router. Based on the intent field returned by the AI, it decides whether to call logInvoiceToSheet() or scheduleMeetingInCalendar(). This simple conditional logic makes your script modular and easy to expand with more intents in the future.
With the complete code now in your hands, you have a fully assembled automation engine. But a built engine is only useful if you can start it, test it, and make sure it runs smoothly. What happens if an email is formatted unexpectedly? Or if the AI returns an error? Our next step is to move from building to deploying, where we'll cover the essential practices of testing, debugging, and setting up triggers to bring your script to life.
References
- Google. (2024). UrlFetch Service (UrlFetchApp). Google Apps Script Documentation.
- Google AI. (2024). Gemini API Documentation. Google for Developers.
- Rose, A. (2022). Practical Google Apps Script: Automating and Integrating Your Google Workspace. Apress.
- Labnol.org. (2023). How to use Generative AI in Google Sheets and Docs.
- Meyer, B. (2021). Google Apps Script for Beginners. O'Reilly Media.