Google Apps Script and the Gemini API Tutorial: Smart Data Extraction

Manual data entry is the enemy of productivity. If your team relies on extracting data from operational emails, like invoices, booking confirmations, or customer feedback forms, you are likely familiar with the nightmare of traditional parsing methods. 

Historically, developers relied on Regular Expressions (Regex) to extract details like invoice numbers or billing amounts. But Regex is incredibly brittle; the moment a vendor changes their email template or adds a random line break, the entire parser breaks silently.

In this tutorial, I will show you how to build a fully automated, headless pipeline that solves this problem using Generative AI. We will write a lightweight script that reads unread Gmail messages, passes the unstructured text to the Gemini API for intelligent extraction, and logs the perfectly structured JSON data directly into a Google Sheet.

Prerequisites:

• A Google account
• Basic familiarity with JavaScript (Google Apps Script)
• A Gemini API key

What Is Google Apps Script?

Google Apps Script is a cloud-based, rapid application development platform based on JavaScript that allows you to automate, customize, and extend Google Workspace applications like Google Docs, Sheets, Slides, and more. 

Because the platform runs directly on Google's servers, there is no need to install software, configure local environments, or manage infrastructure. 

Apps Script gives the ability to developers/ low-code developers to interact programmatically with Google Workspace tools like Docs, Sheets, Slides, Drive and more. You can build and run automations through custom menus, button clicks, or time-based schedules, or use the service to build and publish add-ons for the Google Workspace Marketplace.

How Google Apps Script works

Google Apps Script abstracts away the heavy lifting of backend configuration and API authentication, allowing you to focus directly on logic and data. It does this with several features:

• Cloud-Native Editor: You write, debug, and deploy your JavaScript code entirely within a web-based IDE built directly into your browser.
• Built-in Services: Instead of wrestling with OAuth and complex API calls, you use out-of-the-box global objects (like SpreadsheetApp, MailApp, or DriveApp) to interact natively with Google services.
• Event-Driven Triggers: Scripts can be executed manually via custom menu buttons, or they can be scheduled to run automatically using time-driven triggers (e.g., every midnight) and event-driven triggers (e.g., when a user submits a Google Form).

The Google Apps Script Architecture: Gmail to Sheets Pipeline

Before writing the script, let's map out how data moves through this pipeline. The architecture relies on four distinct components working together:

• Gmail (Source): The process begins by querying your inbox using standard search operators (like is:unread label:invoices). The script isolates the correct email threads and extracts both the plain text body and any attached PDF files.
• Apps Script (Compute): This acts as the orchestration layer. It iterates through the unread threads, formats the attachments into base64 data, and handles the logic for error reporting and batch processing.
• Gemini API (Transformation Engine): Apps Script sends a multimodal payload (the email text plus the PDFs) to the Gemini 2.5 Flash model. By passing a strict JSON response schema in the API call, we force the model to pull out exact values like the vendor, total amount, and currency, rather than generating conversational text.
• Google Sheets (Destination): Once Gemini returns the structured JSON, Apps Script parses the results, bundles them into a single array, and batch-inserts the data into the next empty row of your active spreadsheet using setValues().

Why use Google Apps Script as the compute layer for this build? 

The primary benefit is the complete removal of infrastructure overhead. There is zero server provisioning required, and you do not need to configure Docker containers or set up complicated OAuth flows just to read your emails or write to a spreadsheet. 

Because Apps Script natively integrates with services like GmailApp and SpreadsheetApp, Google handles the authentication internally. You simply write the code and connect the endpoints.

Step 1: Prepare Your Google Apps Script Environment

Before we write any code, we need to prepare our external dependencies. This involves securing access to the Gemini API, creating our destination spreadsheet, and configuring our Gmail inbox to properly tag incoming invoices.

Generating the Gemini API Key

To power our intelligent data extraction, we need access to the Gemini API.

1. Navigate to Google AI Studio and sign in with your Google account.
2. In the left-hand navigation menu, click on Get API key.
3. Click the Create API key button. You can choose to create this key in an existing Google Cloud project or let AI Studio create a new one for you.
4. Copy the generated string. 

Security Best Practice: Do not lose this key, but more importantly, do not plan to hardcode it in plain text within your code. Keep it copied to your clipboard or a secure notepad for now; we will securely lock it away inside our Apps Script environment in the next step.

Preparing the Google Sheet and Securing the Key

We need a place to store the extracted data. This sheet will also act as the launchpad for our automation script.

1. Set up the database headers Create a new blank Google Sheet. In the first row, create the following column headers to match the JSON schema we will define in our script:

• Date
• Vendor
• Invoice Number
• Total Amount
• Currency
• Items Summary
• Email Link

Freeze the top row so the headers remain visible as your data grows.

2. Access Apps Script and store the API Key Now that the Sheet exists, we can open the Apps Script editor directly from it. This automatically links our code to this specific spreadsheet.

1. In your Google Sheet, click Extensions > Apps Script from the top menu.
2. Once the editor opens, look at the left sidebar and click the gear icon to open Project Settings.

3. Scroll down to the bottom to find the Script Properties section.
4. Click Add script property.
5. Under Property, type exactly GEMINI_API_KEY.
6. Under Value, paste the API key you copied from AI Studio earlier.
7. Click Save script properties.

The code we write later will now securely fetch this value at runtime without exposing your credentials.

Step 2: Write the Google Apps Script Code

While you are in your Apps Script editor, go ahead and delete any default code. We will build our automation engine block by block.

1. Configuration and UI setup

We start by defining a global configuration object. This is a clean code best practice that groups all hardcoded variables in one place, so you only ever have to edit this top block.

We also add an onOpen() function. This built-in Apps Script trigger automatically creates a custom menu in your Google Sheet, allowing non-technical users to run the script manually without ever opening the code editor.

const CONFIG = {
 searchQuery: "is:unread label:invoices",
 model: "gemini-2.5-flash",
 adminEmail: Session.getActiveUser().getEmail() // Automatically emails the script owner if it crashes
};

/**
* Creates a custom menu in Google Sheets so non-technical users can run the script manually.
*/
function onOpen() {
 const ui = SpreadsheetApp.getUi();
 ui.createMenu('🤖 AI Automations')
   .addItem('Fetch New Invoices', 'processInvoices')
   .addToUi();
}

2. The orchestration setup

The processInvoices() function acts as the main orchestrator. It starts by securely pulling your API key from the Script Properties we set up earlier. 

Then, it uses the built-in GmailApp service to query your inbox.

By calling GmailApp.search(CONFIG.searchQuery), the script retrieves an array of email threads that match our is:unread label:invoices rule. 

If it finds matches, it loops through each thread, grabbing the latest message, extracting the plain text body, and isolating any attached PDF files.

function processInvoices() {
  const apiKey = PropertiesService.getScriptProperties().getProperty("GEMINI_API_KEY");
  if (!apiKey) {
    throw new Error("GEMINI_API_KEY is missing! Please set it in Script Properties.");
  }

  const threads = GmailApp.search(CONFIG.searchQuery);
  if (threads.length === 0) return;

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const batchData = [];
  const processedThreads = [];

3. Processing emails and multimodal PDFs

As we loop through the email threads, we grab the latest message. However, invoices rarely arrive as plain text; they are almost always attached as PDFs. 

By utilizing Gemini 2.5 Flash, which is natively multimodal, our script loops through latestMessage.getAttachments(), grabs any PDFs, converts them to a Base64 string, and passes them directly to the API payload as inlineData. 

Gemini "reads" the PDF visually and extracts the data just as easily as it reads text.

for (let i = 0; i < threads.length; i++) {
   const thread = threads[i];
   const latestMessage = thread.getMessages().pop();
  
   const emailBody = latestMessage.getPlainBody();
   const emailDate = latestMessage.getDate();
   const emailLink = https://mail.google.com/mail/u/0/#inbox/${thread.getId()};
   const attachments = latestMessage.getAttachments();
   try {
     const geminiParts = [];
     geminiParts.push({ text: "Extract invoice details from this email and any attached documents:\n\n" + emailBody });
    
     // Add PDF attachments as base64 to the prompt
     for (const attachment of attachments) {
       if (attachment.getContentType() === "application/pdf") {
         geminiParts.push({
           inlineData: {
             mimeType: "application/pdf",
             data: Utilities.base64Encode(attachment.getBytes())
           }
         });
       }
     }
     // Invoke Gemini API
     const extractedData = callGeminiAPI(apiKey, geminiParts);

4. Batch insertion and error handling

To optimize performance, we don't write to the spreadsheet one row at a time. 

Instead, we push the extracted JSON data into an array (batchData) and write it all at once at the very end using setValues().

Notice the try...catch block. 

If Gemini hallucinates or fails on a specific email, the script doesn't crash entirely. Instead, it gracefully catches the error and uses MailApp.sendEmail() to alert the administrator immediately, ensuring high observability.

 // Push to batch array instead of writing one-by-one
     batchData.push([
       emailDate,
       extractedData.vendor || "Unknown Vendor",
       extractedData.invoiceNumber || "N/A",
       extractedData.totalAmount || 0.00,
       extractedData.currency || "USD",
       extractedData.itemsSummary || "N/A",
       emailLink
     ]);
     processedThreads.push(thread);
   } catch (error) {
     MailApp.sendEmail(CONFIG.adminEmail, "🚨 Invoice Pipeline Error", error.toString());
   }
 }
 // Batch Write to Sheets
 if (batchData.length > 0) {
   const lastRow = Math.max(sheet.getLastRow(), 1);
   sheet.getRange(lastRow + 1, 1, batchData.length, batchData[0].length).setValues(batchData);
 }
 // Mark all processed threads as read so they aren't parsed again
 processedThreads.forEach(t => t.markRead());
}

Finally, the script calls thread.markRead() on every processed email. This ensures that the next time the script runs, it completely ignores these emails and only looks for new ones.

5. The Gemini API helper function (JSON mode)

When calling LLMs, developers often use Prompt Engineering like "Return a JSON block". However, models frequently reply with conversational padding (e.g. "Sure, here is your data: [JSON]"), which causes downstream code parsers to crash.

By setting responseMimeType: "application/json" and providing a strict responseSchema, we force the Gemini REST API to reply only with a guaranteed JSON object containing our exact spreadsheet column headers.

function callGeminiAPI(apiKey, partsArray) {
 const url = https://generativelanguage.googleapis.com/v1beta/models/${CONFIG.model}:generateContent?key=${apiKey};
  const responseSchema = {
   type: "OBJECT",
   properties: {
     vendor: { type: "STRING" },
     invoiceNumber: { type: "STRING" },
     totalAmount: { type: "NUMBER" },
     currency: { type: "STRING" },
     itemsSummary: { type: "STRING" }
   },
   required: ["vendor", "totalAmount"]
 };
  const payload = {
   contents: [{ parts: partsArray }],
   generationConfig: {
     responseMimeType: "application/json",
     responseSchema: responseSchema
   }
 };
  const options = {
   method: "POST",
   contentType: "application/json",
   payload: JSON.stringify(payload),
   muteHttpExceptions: true
 };
  const response = UrlFetchApp.fetch(url, options);
 const jsonResponse = JSON.parse(response.getContentText());
  return JSON.parse(jsonResponse.candidates[0].content.parts[0].text);
}

Step 3: Automate Your Google Apps Script Pipeline

Code is only valuable if it makes your life easier. 

Google Apps Script provides powerful execution environments ranging from interactive, user-facing buttons to completely headless, background cron jobs.

Method 1: Triggering via the spreadsheet UI

If you are building this tool for a non-technical accounting team, they probably don't want to open the Apps Script code editor just to process today's invoices. 

Because we included the onOpen() function in our code, Apps Script automatically binds a custom menu directly into the Google Sheets interface every time the spreadsheet is opened.

1. Go back to your Google Sheet and refresh the page.
2. Wait a few seconds, and look at the top menu bar. You will see a new dropdown called 🤖 AI Automations.
3. Click it and select Fetch New Invoices.

4. The first time you run this, Google will ask you to authorize the script (to read your Gmail and send emails on your behalf). Once authorized, the script will execute in real-time. You can watch the extracted JSON data populate into the rows right before your eyes!

Method 2: Headless automation via time-driven triggers

If you want true set it and forget it automation, you can use native Time-Driven Triggers to run the pipeline autonomously in the background.

1. In the Apps Script editor, click the Triggers (Clock Icon) on the left sidebar.

2. Click Add Trigger at the bottom right corner.

3. Set the function to processInvoices.
4. Change the event source to Time-driven and set it to run on an Hour timer (e.g., every 6 hours).
5. Click Save.

Your headless AI pipeline is now fully autonomous.

Even if your computer is completely turned off, Google's servers will wake up every six hours, scan your inbox, parse the invoices via Gemini, log the data into Sheets, and go back to sleep. 

You can monitor the success or failure of these background executions by clicking the Executions tab in the Apps Script editor to view the logs.

Step 4: Testing the AI Data Extraction

Now that the code is in place, we need to test it to ensure the extraction pipeline works as expected.

Let's look at two practical scenarios to see how the Gemini API handles different data formats and what the actual responses look like.

Test 1: Plain text email extraction

First, we will test an invoice embedded directly in the email body.

1. Send yourself an email with the subject "Invoice INV-2023-88493".
2. In the body, write unstructured text like: "Hi Team, please pay Acme Corp $450.00 USD for the Q3 software license."
3. Apply the invoices label to the email and ensure it remains unread.

4. Run the script via the custom menu.

Behind the scenes, Gemini receives this unstructured text and strictly adheres to our JSON schema. 

Instead of replying with conversational padding, it returns a raw payload. 

Because the response is perfectly structured JSON, our batchData.push() array seamlessly reads extractedData.totalAmount and logs 6550 straight into the Google Sheet without requiring any complex Regex logic.

Test 2: Extracting from a PDF attachment

Next, let's test the true power of Gemini 2.5 Flash by passing it a PDF document.

1. Generate or download a sample PDF invoice (e.g., for $20,700.00 from ProTech Cloud Services).
2. Email yourself the PDF document. You can leave the email body completely blank!
3. Apply the invoices label and mark it unread.

4. Run the script. The pipeline will detect the attachment, convert it to Base64, and Gemini will visually read the document to extract the vendor and total amount.

This is where the architecture proves its value. 

You didn't have to write custom OCR (Optical Character Recognition) logic, manage a separate PDF parsing library, or write regular expressions to find the total amount. 

The LLM handled the unstructured visual data natively.

Conclusion

Automating business workflows doesn't require managing heavy infrastructure or fighting with complex authorization loops. 

By pairing Google Apps Script with the Gemini API, we built an intelligent, serverless data pipeline that runs entirely within the cloud ecosystem you already use every day.

The strength of this architecture lies in its simplicity. Instead of provisioning servers, configuring webhooks, or managing secure credential files on an external server, Apps Script handles the context, authentication, and execution natively. 

Meanwhile, Gemini eliminates the need for brittle regular expressions or expensive OCR software, transforming messy email bodies and unstructured PDFs into predictable database rows with a single API call.

To take your skills further and explore what else you can build with programmatic access to LLMs, I recommend checking out DataCamp’s Introduction to APIs in Python or diving deep into designing intelligent workflows with the AI Agent Fundamentals course.