Build a Gemini AI Agent for Automated Contract Renewals in Finance

Manual contract management is more than just inefficient—it’s a high-stakes gamble with your company’s profitability and compliance. Discover the tangible financial consequences of relying on outdated spreadsheets and fallible human memory.

The High Cost of Manual Contract Management

In the world of finance, precision and timeliness aren’t just best practices; they’re the bedrock of profitability and compliance. Yet, one of the most critical operational functions—[Build A Contract Lifecycle Agent Using Google Workspace And [Building Self Correcting Agentic Workflows with Building Self-Correcting Agentic Workflows with Vertex AI](https://votuduc.com/building-self-correcting-agentic-workflows-with-vertex-ai-p-20260321542526)](https://votuduc.com/build-a-contract-lifecycle-agent-using-google-workspace-and-vertex-ai-p-20260321873802)—often remains mired in manual processes. We rely on spreadsheets that are perpetually out of date, calendar reminders that get dismissed, and the institutional memory of key personnel. This isn’t just inefficient; it’s a high-stakes gamble with tangible financial consequences.

The silent killer of revenue: Missed renewals and compliance gaps

The true cost of manual contract management is rarely found in a single line item on a budget. It’s a “death by a thousand cuts” scenario, a silent drain on resources and revenue that manifests in several critical ways:

• Lost Revenue from Missed Renewals: Every contract that auto-renews without a strategic review is a missed opportunity. It could be a chance to renegotiate terms, adjust pricing based on new market realities, or upsell services. Worse still are the contracts that simply expire without action, leading to an abrupt and entirely preventable loss of a revenue stream. Manual tracking, dependent on a single person’s calendar, is a fragile system waiting to break.

• Operational Drag and Inefficiency: How many hours are spent searching through shared drives and email chains for the “latest version” of a contract? The time your team spends manually tracking dates, deciphering legal jargon, and compiling reports is time they aren’t spending on high-value strategic activities. This operational friction slows down decision-making and inflates overhead.

• Critical Compliance Risks: In a heavily regulated industry like finance, a missed deadline or an overlooked clause isn’t just a mistake—it’s a compliance breach. Manual processes are inherently prone to human error. A mis-keyed date or a forgotten regulatory obligation can lead to failed audits, hefty fines, and significant reputational damage. Without a centralized, automated system, you lack the visibility needed to ensure every contractual obligation is met.

Introducing the AI solution: An autonomous contract lifecycle agent

Imagine a system that doesn’t just store your contracts but actively manages them. This is the promise of an AI-powered autonomous agent. It’s not just another software tool; it’s a proactive, intelligent partner designed to handle the entire contract renewal lifecycle.

This agent operates 24/7, serving as a tireless digital paralegal. Its core functions are to:

1. Monitor Proactively: It constantly scans your contract repository, identifying key dates and deadlines far in advance. No more last-minute scrambles.

2. **Analyze Intelligently: Using a powerful large language model (LLM) like Gemini, the agent reads and understands the contract text. It extracts critical information like renewal terms, notification periods, pricing clauses, and non-standard obligations that are easily missed by the human eye.

3. Communicate Autonomously: The agent can generate concise summaries of complex contracts, draft renewal notification emails for stakeholders, and create alerts for your legal or finance teams, flagging any unusual terms for human review.

This shifts the paradigm from reactive problem-solving to proactive, data-driven strategy. Your team is no longer bogged down in administrative tasks; they are elevated to a role of strategic oversight, armed with clear, AI-generated insights to make better, faster decisions.

Our blueprint for [Automated Job Creation in Real Time Jobber and Google Sheets Integration from Gmail](https://votuduc.com/Automated-Job-Creation-in-Jobber-from-Gmail-p115606) with [AI Powered Cover Letter Automated Quote Generation and Delivery System for Jobber Engine](https://votuduc.com/AI-Powered-Cover-Letter-Automated Work Order Processing for UPS-Engine-p111092) and Gemini

So, how do we build this? We don’t need a massive enterprise software budget or a dedicated data science team. We can create a powerful, bespoke solution using a combination of readily available, robust tools: [Automatically create new folders in Google Drive, generate templates in new folders, fill out text automatically in new files, and save info in [Automated Web Scraping with [Multilingual Text-to-Speech Tool with SocialSheet Streamline Your Social Media Posting 123](https://votuduc.com/Multilingual-Text-to-Speech-Tool-with-Google-Workspace-p809282)](https://votuduc.com/Automated-Web-Scraping-with-Google-Sheets-p292968)](https://workspace.google.com/marketplace/app/auto_create_folder_and_files/430076014869) and the Gemini API.

Here’s the blueprint for the agent we’re about to build:

• The Orchestrator - Genesis Engine AI Powered Content to Video Production Pipeline: This is the serverless “glue” that holds our entire workflow together. Running directly within the Google ecosystem, it will act as our central controller, scheduling tasks, calling other services, and managing the flow of data.

• The Database - Google Sheets: We’ll use a simple Google Sheet as our contract control center. It will house metadata like contract names, counterparties, and status, and our script will enrich it with AI-extracted insights.

• The Brain - Google’s Gemini API: This is where the magic happens. We will send the raw text of our contracts (stored in Google Drive) to the Gemini API. We’ll prompt it to act as an expert legal analyst, asking it to extract key dates, summarize obligations, and identify potential risks.

• The Messenger - Gmail: Once the agent has analyzed a contract and determined action is needed, it will use Apps Script to interface with the Gmail API to automatically draft and send detailed notification emails to the appropriate stakeholders.

This architecture is elegant in its simplicity and power. It leverages the tools your team likely already uses, creating a low-friction, high-impact automation that directly addresses the costs and risks of manual contract management. Now, let’s start building it.

Architecting Your Autonomous Finance Agent

Before we write a single line of code, let’s step back and architect our solution. A robust system isn’t just a collection of API calls; it’s a well-designed workflow where each component has a clear purpose. Our goal is to create a resilient, secure, and truly autonomous agent that operates seamlessly within the AC2F Streamline Your Google Drive Workflow ecosystem. This design prioritizes using managed services to minimize infrastructure overhead, allowing us to focus on the core logic of contract management.

Core components: Google Drive, Sheets, Gemini, and Gmail

The beauty of this architecture lies in its simplicity and the powerful, native integration between Google’s services. We’re essentially creating a data processing pipeline where each tool plays a specialized, critical role.

• Google Drive: The Digital Filing Cabinet. This is the entry point for our entire workflow. Drive will serve as the monitored repository for all incoming contract documents (PDFs, DOCX, even scanned images). We’ll designate a specific folder, and any new file added to it will act as the trigger for our agent to spring into action. It’s our unstructured data lake.

• Google Sheets: The Structured Ledger. This is our agent’s single source of truth. Once Gemini extracts the vital information from a contract, it needs a structured, queryable home. Sheets is perfect for this. It’s more than a spreadsheet; it’s a lightweight, cloud-based database where we’ll log every contract’s key details: counterparty, effective date, renewal date, notice period, value, and a link back to the original file in Drive.

• Gemini API: The Cognitive Engine. This is the brain of our operation. We’ll leverage a powerful model like Gemini 1.5 Pro for its massive context window and advanced reasoning capabilities. Its responsibilities are twofold:

1. Extraction & Analysis: It will receive the raw text or image of a contract and, guided by a precise prompt, identify and extract the key data points we need, returning them in a structured format like JSON.

2. Generation & Communication: When a renewal date is approaching, we’ll feed the structured data from Sheets back to Gemini, asking it to compose a clear, professional notification email. This offloads the tedious task of manual email drafting.

• Gmail API: The Communication Channel. This is the agent’s voice. After Gemini drafts the renewal alert, we’ll use the Gmail API to send the notification to the designated stakeholders (e.g., the finance team, legal department, or a specific account manager).

• The Glue: [Architecting Multi Tenant AI Workflows in Building Modular Agentic Apps Script with Gemini Function Calling](https://votuduc.com/architecting-multi-tenant-ai-workflows-in-google-apps-script-p-20260321290501). While not a “core” data component, Apps Script is the orchestration layer that connects everything. It’s the serverless “nervous system” that listens for triggers in Drive, shuttles data between services by calling the Gemini and Sheets APIs, runs scheduled checks for upcoming renewals, and finally, tells the Gmail API to send the alert.

The end-to-end workflow: From document scan to renewal alert

Visualizing the flow of data is key to understanding the system. Here’s a step-by-step breakdown of the agent’s lifecycle for a single contract:

1. Ingestion: A team member uploads a new, signed contract (e.g., NewVendor_SaaS_Agreement.pdf) into the designated “Incoming Contracts” folder in Google Drive.

2. Trigger: A pre-configured Google Apps Script trigger, bound to the Drive folder, detects the new file and immediately initiates our processing function.

3. Extraction: The script reads the new document’s content. It then makes a call to the Gemini API, sending the document data along with a carefully crafted prompt, such as: “Analyze the following contract. Extract the Counterparty Name, Renewal Date, Notice Period in days, and Contract Value. Return the data as a clean JSON object.”

4. Structuring: Gemini processes the document and returns a structured JSON object: { "counterparty": "NewVendor Inc.", "renewal_date": "2025-12-31", "notice_period_days": 90, "value": 50000 }.

5. Logging: The Apps Script parses this JSON response and appends a new row to our “Contract Master” Google Sheet, populating the respective columns. It also adds the current date and a direct link to the source file in Drive for easy reference.

6. Monitoring (The Autonomous Loop): A separate, time-based Apps Script trigger runs automatically every 24 hours (e.g., at 8 AM).

7. Decision Logic: This daily script scans the Google Sheet. Its core logic is to find all rows where (Renewal Date - Notice Period) falls within a predefined window (e.g., is 7 days from today). This calculation identifies contracts that require immediate attention.

8. Alert Generation: For each contract flagged in the previous step, the script makes another call to the Gemini API. This time, the prompt is for generation: “Draft a renewal alert email for the contract with [Counterparty]. The renewal is on [Renewal Date], and the deadline to provide notice is [Calculated Notice Deadline]. Mention the contract value is [Value].”

9. Delivery: The script takes the AI-generated email body, sets the subject line, and uses the Gmail API to send the alert to a predefined distribution list, such as [email protected]. The workflow is now complete for that contract’s renewal cycle.

Security and permissions: Safeguarding sensitive contract data

We are dealing with highly sensitive financial and legal documents. Security cannot be an afterthought; it must be a foundational part of our architecture. Here’s how we lock it down:

• The Principle of Least Privilege: Our Apps Script project will only request the permissions it absolutely needs to function. We will use granular OAuth scopes to prevent overreach. For example:

• https://www.googleapis.com/auth/drive.readonly: To read new files from the designated folder, not write or delete.

• https://www.googleapis.com/auth/spreadsheets.currentonly: To access and modify only the specific Google Sheet it’s bound to, not all sheets in the user’s Drive.

• https://www.googleapis.com/auth/gmail.send: To send emails, but not to read, delete, or modify the user’s inbox.

• https://www.googleapis.com/auth/script.external_request: To make API calls to the Gemini AI platform.

• API Key Management: Never hardcode your Gemini API key directly in the code. Instead, store it securely using Apps Script’s PropertiesService. This service provides a key-value store scoped to the script, user, or document, keeping your credentials out of the source code and version control.

• Data Access Controls: The underlying Google Drive folder and the master Google Sheet must have their sharing permissions tightly controlled. Restrict access to only the specific individuals and groups who need to view or manage the contracts. Disable any public or “anyone with the link” sharing settings.

• **Human-in-the-Loop (HITL) Option: For maximum safety, especially when first deploying the system, you can modify the final step. Instead of having the agent send the email directly, configure it to create a draft in a specific manager’s Gmail account using GmailApp.createDraft(). This allows for a final human review before the notification is officially sent, blending the efficiency of automation with the assurance of human oversight.

Step 1: Scanning and Identifying Contracts in Google Drive

Before we can unleash our Gemini agent, we need a robust mechanism to feed it the right documents. The foundation of any reliable automation is a clean, predictable data pipeline. For our use case, this starts with systematically identifying new contract files as they appear in Google Drive. We’ll use the power of Google Apps Script, the native scripting language for Automated Client Onboarding with Google Forms and Google Drive., to build this crucial first step.

Using DriveApp to monitor a dedicated contracts folder

The first rule of automation is to create a controlled environment. Instead of scanning an entire Google Drive, we’ll designate a specific folder—let’s call it “Incoming Contracts”—as the drop-off point for all new agreements pending renewal analysis. This keeps our process focused and prevents the agent from accidentally processing personal files or irrelevant documents.

Google Apps Script provides the DriveApp service, a powerful API that lets our code interact directly with files and folders in Google Drive. We’ll use it to target our dedicated folder and list its contents.

A best practice is to reference the folder by its unique ID rather than its name. A folder’s name can change, but its ID is permanent. You can find the ID in the folder’s URL (it’s the string of characters after folders/).

Here’s a simple Apps Script function to get started. It connects to our “Incoming Contracts” folder and logs the names of all the files it finds.

/**

* Scans a dedicated Google Drive folder and logs the files found within.

*/

function scanForNewContracts() {

// Replace with the actual ID of your "Incoming Contracts" folder.

const INCOMING_FOLDER_ID = 'YOUR_INCOMING_FOLDER_ID_HERE';

try {

// Get the folder object using its unique ID.

const incomingFolder = DriveApp.getFolderById(INCOMING_FOLDER_ID);

Logger.log(`Scanning folder: "${incomingFolder.getName()}"`);

// Get an iterator for all files in the folder.

const files = incomingFolder.getFiles();

// Loop through the files as long as there's a next one.

while (files.hasNext()) {

const file = files.next();

Logger.log(`- Found file: ${file.getName()} (ID: ${file.getId()})`);

// In the next section, we'll add logic to process this file.

}

if (!files.hasNext()) {

Logger.log('No new files to process.');

}

} catch (e) {

// Log any errors, e.g., if the folder ID is incorrect or permissions are missing.

Logger.log(`Error accessing Drive folder: ${e.toString()}`);

}

}

This script provides a solid starting point. It successfully identifies the files we need to process. However, if we run it multiple times, it will find the same files over and over again. Our next step is to solve that problem.

Filtering and organizing files for systematic processing

To build a true automation, our script needs to be idempotent—meaning, running it multiple times should not produce different results or re-process old data. We need a way to distinguish new contracts from those that have already been analyzed.

The simplest and most effective strategy is a “queuing” system using folders. We’ll create a second folder named “Processed Contracts.” Once our script successfully analyzes a file from the “Incoming Contracts” folder, it will move it to the “Processed Contracts” folder. This way, the next time the script runs, the incoming folder will only contain genuinely new files.

We should also add a filter to ensure we’re only attempting to process valid document types, like PDFs and Google Docs, while ignoring any stray images or spreadsheets.

Let’s enhance our script with this filtering and file-moving logic:

/**

* Identifies new contracts, filters them by type, and moves them after processing.

*/

function processNewContracts() {

// --- Configuration ---

const INCOMING_FOLDER_ID = 'YOUR_INCOMING_FOLDER_ID_HERE';

const PROCESSED_FOLDER_ID = 'YOUR_PROCESSED_FOLDER_ID_HERE';

// Define the file types our agent can handle.

const SUPPORTED_MIME_TYPES = [

MimeType.PDF,

MimeType.GOOGLE_DOCS,

'application/vnd.openxmlformats-officedocument.wordprocessingml.document' // for .docx

];

// --- Execution ---

try {

const incomingFolder = DriveApp.getFolderById(INCOMING_FOLDER_ID);

const processedFolder = DriveApp.getFolderById(PROCESSED_FOLDER_ID);

const files = incomingFolder.getFiles();

Logger.log(`Scanning folder: "${incomingFolder.getName()}" for new contracts...`);

while (files.hasNext()) {

const file = files.next();

const fileName = file.getName();

const mimeType = file.getMimeType();

// 1. Filter: Skip files that are not a supported document type.

if (!SUPPORTED_MIME_TYPES.includes(mimeType)) {

Logger.log(`Skipping '${fileName}' due to unsupported type: ${mimeType}`);

continue; // Proceed to the next file in the loop.

}

Logger.log(`Processing contract: ${fileName}`);

// ==========================================================

// PLACEHOLDER: The AI analysis logic from Step 2 will go here.

// For now, we'll assume the analysis is successful.

const isAnalysisSuccessful = true;

// ==========================================================

// 2. Organize: If processing was successful, move the file.

if (isAnalysisSuccessful) {

file.moveTo(processedFolder);

Logger.log(`Successfully moved '${fileName}' to "${processedFolder.getName()}".`);

} else {

Logger.log(`Analysis failed for '${fileName}'. File will not be moved.`);

}

}

} catch (e) {

Logger.log(`A critical error occurred: ${e.toString()}`);

}

}

With this structure in place, we now have a reliable, self-organizing pipeline. Each time this function runs (which can be automated with a time-based trigger), it will:

1. Look inside the “Incoming Contracts” folder.

2. Ignore any unsupported file types.

3. Process each valid contract one by one.

4. Move the processed file to a safe, archived location.

This ensures that our Gemini agent only ever sees fresh, relevant documents, setting the stage perfectly for the next step: extracting the text and sending it for analysis.

Step 2: Extracting Key Terms with Gemini 1.5 Pro

With our contracts identified, the next crucial step is to transform them from dense, unstructured documents into clean, structured data. This is where the real power of a Large Language Model comes into play. We’ll use Gemini 1.5 Pro, Google’s latest flagship model, for this task. Its massive 1 million token context window means it can handle even the most verbose legal agreements, and its advanced reasoning capabilities are perfect for pinpointing the exact information we need.

Crafting the perfect prompt for structured JSON output

Simply asking the model to “find the renewal date” isn’t enough. We need a reliable, repeatable process that returns data in a format our script can easily parse and use. The gold standard for this is JSON (JavaScript Object Notation). A well-crafted prompt is the difference between a chaotic, unpredictable text response and a perfectly structured, machine-readable object.

The key is to be incredibly specific in your instructions. Our prompt will have four main components:

1. **Persona: Tell the model what it is. This primes it to access the right kind of knowledge and reasoning.

2. **Task: Clearly define what it needs to do.

3. Format Specification: Explicitly demand JSON output and define the exact structure, or “schema,” you expect. This is the most critical part.

4. Context: Provide the raw contract text for analysis.

Let’s build the ultimate prompt for our use case.

You are an expert AI paralegal specializing in the analysis of financial service agreements. Your primary function is to extract key data points from legal documents with extreme accuracy.

**Task:**

From the contract text provided below, extract the following key terms and return them as a single, valid JSON object.

**JSON Schema and Rules:**

- The output MUST be a valid JSON object.

- Do NOT include any explanatory text, comments, or markdown formatting like ```json before or after the JSON object.

- If a specific piece of information cannot be found in the text, the value for that key must be `null`.

- All dates must be formatted as "YYYY-MM-DD".

- For numeric values like `notice_period_days` or `annual_cost`, return an integer or float, not a string.

{

"vendor_name": "The full legal name of the vendor or service provider.",

"service_description": "A brief, one-sentence summary of the service being provided.",

"effective_date": "The start date or effective date of the contract.",

"termination_date": "The end date or termination date of the initial term.",

"renewal_term": "The length of the automatic renewal period (e.g., '1 year', 'monthly', '24 months').",

"notice_period_days": "The number of days required for non-renewal notification.",

"annual_cost": "The total annual cost of the service. Extract only the numeric value."

}

**Contract Text to Analyze:**

[Your contract text will be inserted here]

This prompt is powerful because it leaves no room for ambiguity. It defines the model’s role, its task, the exact shape of the output, and rules for handling edge cases like missing data or specific data types. This level of detail is essential for building a robust and automated system.

Handling diverse contract formats like PDFs and Google Docs

In the real world, contracts aren’t always in a clean text file. They live in Google Docs, and more often than not, as PDFs—sometimes scanned images of physical paper. Our agent needs to be able to handle this.

For Google Docs:

This is the most straightforward scenario. Google Apps Script has a native DocumentApp service that can directly access and read the content of any Google Doc. We can simply open the document by its ID and grab all the text from its body.

// Example of getting text from a Google Doc

const doc = DocumentApp.openById('YOUR_DOCUMENT_ID');

const contractText = doc.getBody().getText();

For PDFs:

PDFs are trickier. They can be “native” (text-based) or “scanned” (image-based). Gemini 1.5 Pro can natively process PDFs if you upload them directly, but doing this programmatically within a pure Apps Script environment requires a clever workaround using Google Drive’s powerful capabilities.

Our strategy will be to use Drive’s built-in Optical Character Recognition (OCR) to convert the PDF into a temporary, text-based Google Doc.

Here’s the automated workflow:

1. Our script identifies a new PDF file in our designated “Contracts to Process” folder.

2. Using the Drive advanced service in Apps Script, we programmatically convert the PDF blob into a new Google Doc. Drive’s OCR will automatically extract any text, even from scanned images.

3. We then use DocumentApp to read the text content from this newly created Google Doc.

4. This extracted text is passed to our Gemini prompt.

5. After we’ve successfully extracted the data, we delete the temporary Google Doc to keep our Drive clean.

This method unifies our pipeline, ensuring that whether the source is a Doc or a PDF, what we ultimately send to Gemini is clean, extracted text.

Code deep dive: Calling the Gemini API from Apps Script

Now, let’s wire it all together. This function will be the heart of our extraction agent. It takes the contract text, sends it to the Gemini API with our carefully crafted prompt, and parses the response.

First, ensure you have your Gemini API key. You can get this from Google AI Studio. Store it securely using Apps Script’s PropertiesService.

Here is the complete function to call the Gemini API from Apps Script:

/**

* Extracts structured contract data from text using the Gemini 1.5 Pro API.

*

* @param {string} contractText The full text of the contract to be analyzed.

* @return {object|null} A JSON object with the extracted data, or null on failure.

*/

function extractContractDetails(contractText) {

// It's best practice to store your API key as a Script Property

const API_KEY = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');

const API_URL = `https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-pro-latest:generateContent?key=${API_KEY}`;

// Construct the full prompt using our template

const prompt = `

You are an expert AI paralegal specializing in the analysis of financial service agreements. Your primary function is to extract key data points from legal documents with extreme accuracy.

**Task:**

From the contract text provided below, extract the following key terms and return them as a single, valid JSON object.

**JSON Schema and Rules:**

- The output MUST be a valid JSON object.

- Do NOT include any explanatory text, comments, or markdown formatting like \`\`\`json before or after the JSON object.

- If a specific piece of information cannot be found in the text, the value for that key must be null.

- All dates must be formatted as "YYYY-MM-DD".

- For numeric values like notice_period_days or annual_cost, return an integer or float, not a string.

{

"vendor_name": "The full legal name of the vendor or service provider.",

"service_description": "A brief, one-sentence summary of the service being provided.",

"effective_date": "The start date or effective date of the contract.",

"termination_date": "The end date or termination date of the initial term.",

"renewal_term": "The length of the automatic renewal period (e.g., '1 year', 'monthly', '24 months').",

"notice_period_days": "The number of days required for non-renewal notification.",

"annual_cost": "The total annual cost of the service. Extract only the numeric value."

}

**Contract Text to Analyze:**

${contractText}

`;

// Define the payload for the API request

const payload = {

"contents": [{

"parts": [{

"text": prompt

}]

}],

"generationConfig": {

"response_mime_type": "application/json", // A great feature to enforce JSON output!

"temperature": 0.1, // Lower temperature for more deterministic, factual output

}

};

// Set up the options for the UrlFetchApp call

const options = {

'method': 'post',

'contentType': 'application/json',

'payload': JSON.stringify(payload),

'muteHttpExceptions': true // Important for custom error handling

};

try {

const response = UrlFetchApp.fetch(API_URL, options);

const responseCode = response.getResponseCode();

const responseBody = response.getContentText();

if (responseCode === 200) {

const parsedJson = JSON.parse(responseBody);

// The actual content is nested. We parse the text which itself is the JSON we asked for.

const extractedDataText = parsedJson.candidates[0].content.parts[0].text;

return JSON.parse(extractedDataText);

} else {

Logger.log(`API Error: ${responseCode} - ${responseBody}`);

return null;

}

} catch (e) {

Logger.log(`Exception during API call: ${e.message}`);

return null;

\}

\}

Key things to note in this code:

• response_mime_type: In the generationConfig, we set this to "application/json". This is a powerful feature that instructs the Gemini model to ensure its output is valid JSON, reducing parsing errors.

• temperature: We set this to a low value like 0.1. For extractive, fact-based tasks, a low temperature makes the model’s output more predictable and less “creative,” which is exactly what we want.

• Error Handling: The try...catch block and muteHttpExceptions: true are crucial for building a resilient script. If the API call fails, we log the error and return null instead of crashing the entire execution.

• Response Parsing: The API response has a specific structure. The text we want is nested inside candidates[0].content.parts[0].text. We extract this string and then parse it one final time to get our clean JavaScript object.

Step 3 Populating the Renewal Tracker in Google Sheets

With our AI model primed to extract contract data, we need a destination for that information—a central hub where the data is not just stored, but becomes a dynamic and actionable tool. This is where Google Sheets shines. It’s more than a spreadsheet; it’s our application’s front-end, our single source of truth for every contract renewal. In this step, we’ll transform a blank sheet into an intelligent renewal tracker.

Designing the master tracker sheet for clarity and control

Before writing a single line of code, we must design our tracker. A well-structured sheet is crucial for usability. The goal is to create a dashboard that the finance team can understand and interact with at a glance. A chaotic data dump will undermine the entire automation effort.

Let’s create a new Google Sheet named “Contract Renewal Tracker” and set up the following columns in the first sheet, which we’ll name “MasterTracker”.

| Column Header | Data Type | Purpose |

| :--- | :--- | :--- |

| ContractID | Text | A unique identifier for the contract, extracted by Gemini. This is our primary key. |

| VendorName | Text | The name of the vendor or counterparty. |

| ServiceDescription | Text | A brief description of the service or product covered by the contract. |

| ContractStartDate | Date | The effective start date of the contract term. |

| ContractEndDate | Date | The end date of the current term. |

| RenewalNoticePeriod | Number | The notice period in days (e.g., 90, 60, 30). |

| NoticeDeadline | Date | Calculated Field: ContractEndDate - RenewalNoticePeriod. This is the critical date. |

| AnnualValue | Currency | The annual contract value (ACV) for prioritization. |

| RenewalStatus | Dropdown | The current stage of the renewal process (e.g., Pending Review, Approved). |

| Owner | Text | The team member responsible for this contract’s renewal decision. |

| AIExtractedSummary | Text | The concise summary generated by Gemini. |

| SourceDocumentLink | URL | A direct link to the contract PDF in Google Drive. |

| LastUpdated | Timestamp | A timestamp automatically updated when the row is modified by our script. |

Pro Tip: Freeze the header row (View > Freeze > 1 row) so it remains visible as you scroll. Using alternating colors for rows can also significantly improve readability. This initial design provides a solid foundation for both our script and the end-users.

Using SheetsApp to parse JSON and update rows automatically

Now, let’s bridge the gap between Gemini’s JSON output and our newly designed sheet. We’ll use Google Apps Script and its powerful SpreadsheetApp service to programmatically write the data.

Our Gemini function (from the previous step) will return a JSON object. Let’s assume it looks like this:

\{

"contractId": "VEN-CLD-2024-01",

"vendorName": "CloudCorp Solutions",

"serviceDescription": "Enterprise Tier Cloud Storage - 10TB",

"startDate": "2023-09-01",

"endDate": "2024-08-31",

"renewalNoticeDays": 90,

"annualValue": 25000,

"summary": "Standard enterprise cloud storage agreement with 99.99% uptime SLA and dedicated support. Renewal must be cancelled 90 days prior to end date."

\}

Here is the Apps Script function that will parse this JSON and intelligently update our sheet. This function is designed to be idempotent: it will update an existing contract record if it finds a matching ContractID or append a new row if it doesn’t.

// Add this function to your project's .gs file

/**

* Parses contract data from a JSON object and updates the MasterTracker sheet.

* If a contract with the same ID exists, it updates the row. Otherwise, it appends a new row.

*

* @param \{object\} contractData The JSON object containing extracted contract details.

* @param \{string\} documentUrl The URL of the source contract document.

*/

function updateTrackerSheet(contractData, documentUrl) \{

try \{

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MasterTracker");

if (!sheet) \{

throw new Error("MasterTracker sheet not found!");

\}

const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

const data = sheet.getDataRange().getValues();

// Find the column index for the ContractID to perform lookups

const idColIdx = headers.indexOf("ContractID");

if (idColIdx === -1) \{

throw new Error("Column 'ContractID' not found in the tracker.");

\}

let existingRow = -1;

// Start from row 2 (index 1) to skip headers

for (let i = 1; i < data.length; i++) \&#123;

if (data[i][idColIdx] === contractData.contractId) \&#123;

existingRow = i + 1; // Sheet rows are 1-indexed

break;

\&#125;

\&#125;

// Calculate the notice deadline

const endDate = new Date(contractData.endDate);

const noticeDeadline = new Date(endDate.setDate(endDate.getDate() - contractData.renewalNoticeDays));

// Prepare the data array in the correct column order

const newRowData = headers.map(header => \&#123;

switch (header) \&#123;

case "ContractID": return contractData.contractId || "";

case "VendorName": return contractData.vendorName || "";

case "ServiceDescription": return contractData.serviceDescription || "";

case "ContractStartDate": return new Date(contractData.startDate);

case "ContractEndDate": return new Date(contractData.endDate);

case "RenewalNoticePeriod": return contractData.renewalNoticeDays || "";

case "NoticeDeadline": return noticeDeadline;

case "AnnualValue": return contractData.annualValue || "";

case "RenewalStatus": return existingRow > -1 ? sheet.getRange(existingRow, headers.indexOf("RenewalStatus") + 1).getValue() : "Pending Review"; // Preserve status if row exists

case "Owner": return existingRow > -1 ? sheet.getRange(existingRow, headers.indexOf("Owner") + 1).getValue() : ""; // Preserve owner

case "AIExtractedSummary": return contractData.summary || "";

case "SourceDocumentLink": return documentUrl;

case "LastUpdated": return new Date();

default: return "";

\&#125;

\&#125;);

if (existingRow > -1) \&#123;

// Update the existing row

Logger.log(`Updating existing row ${existingRow} for ContractID: ${contractData.contractId}`);

sheet.getRange(existingRow, 1, 1, newRowData.length).setValues([newRowData]);

\&#125; else \&#123;

// Append a new row

Logger.log(`Appending new row for ContractID: ${contractData.contractId}`);

sheet.appendRow(newRowData);

\&#125;

\&#125; catch (e) \&#123;

Logger.log(`Error in updateTrackerSheet: ${e.message}`);

// Optional: Add more robust error handling, like sending an email notification

\&#125;

\&#125;

This script is the engine of our tracker. It reliably translates the AI’s output into a structured row, handling the logic of creating or updating records automatically.

Implementing data validation and status tracking

A raw data table is useful, but an interactive dashboard is powerful. We can enhance our sheet with features that guide user input and provide immediate visual feedback.

1. Data Validation for Status Control

We want to control the RenewalStatus field to ensure consistency. A dropdown menu is perfect for this.

1. In your “MasterTracker” sheet, select the entire RenewalStatus column (Column I, if you followed the layout above).

2. Go to Data > Data validation.

3. In the Criteria dropdown, select List of items.

4. Enter the allowed statuses, separated by commas: Pending Review, Approved for Renewal, Negotiation, Do Not Renew, Renewed.

5. Ensure “Show dropdown list in cell” is checked.

6. Click Save.

Now, users can only select from this predefined list, preventing typos and keeping your data clean for reporting and filtering.

2. Conditional Formatting for Visual Prioritization

Conditional formatting turns your tracker from a passive list into an active monitoring tool by changing a cell’s appearance based on its value. This helps the finance team instantly spot what needs attention.

Let’s set up a few critical rules:

• Highlight Urgent Deadlines:

1. Select the entire data range (e.g., A2:M1000).

2. Go to Format > Conditional formatting.

3. Under “Format rules,” choose “Custom formula is”.

4. Enter the formula: =AND($G2<>"", $G2<=TODAY()+30) (assuming NoticeDeadline is in column G). This highlights any row where the notice deadline is within the next 30 days.

5. Set the formatting style to a light red fill color.

6. Click Done.

• Visualize Renewal Status:

1. With the same range selected, add another rule.

2. Choose “Custom formula is” and enter: =$I2="Renewed" (assuming RenewalStatus is in column I).

3. Set the formatting style to a light green fill.

4. Add another rule with the formula =$I2="Do Not Renew" and set the style to strikethrough text with a gray background.

With these rules in place, your sheet now visually communicates priority and status, allowing anyone to understand the state of contract renewals in seconds. You’ve successfully built the core of your automated tracking system.

Step 4 Triggering Proactive Alerts with GmailApp

Our agent now has the intelligence to analyze contracts, but intelligence without action is just data. In this step, we’ll give our agent a voice and a sense of timing. We’ll automate the entire notification process by setting up a daily trigger that scans for upcoming renewals and dispatches detailed, AI-enhanced email alerts to the relevant stakeholders. This is where the system transitions from a passive repository to a proactive assistant, ensuring no deadline ever slips through the cracks. We’ll leverage two core Google Apps Script services: Triggers for scheduling and GmailApp for communication.

Setting up time-based triggers for upcoming renewal dates

A time-based trigger is essentially a scheduler for your script, a “cron job” for the Automated Discount Code Management System ecosystem. It allows us to execute a specific function automatically on a recurring schedule—daily, weekly, hourly, you name it. For our contract renewal agent, a daily check is perfect. It’s frequent enough to be timely but not so frequent that it becomes noisy.

You can set up triggers in two ways: programmatically via the ScriptApp service or manually through the Apps Script editor’s UI. For reliability and ease of setup, we’ll use the UI.

Here’s how to set up your daily trigger:

1. Open the Triggers Panel: In your Apps Script editor, look for the clock icon on the left-hand sidebar and click on it. This is the “Triggers” panel.

2. Add a New Trigger: Click the + Add Trigger button in the bottom-right corner. A configuration dialog will pop up.

3. Configure the Trigger: You’ll need to set the following options to have our script run every morning.

• Choose which function to run: Select the main function that orchestrates the entire process (e.g., checkRenewalsAndNotify). This is the function we’ll build that reads the sheet, checks dates, and calls our email function.

• Choose which deployment should run: Leave this as Head. This means it will always run the latest saved version of your code.

• Select event source: Change this from the default From spreadsheet to Time-driven.

• Select type of time-based trigger: Choose Day timer. This allows for a daily execution schedule.

• Select time of day: A good practice is to set it for early in the business day, like 8am - 9am. This ensures stakeholders receive the alert as they are starting their workday.

Your final configuration should look something like this:

Once saved, the trigger is active and will execute your script according to the schedule you’ve defined.

Implementing the Notification Logic

With the trigger in place, our script will now run automatically every day. But what will it run? We need to write the checkRenewalsAndNotify function that we selected in the trigger setup. This function serves as the brain of our daily operation. It will iterate through our contract list, calculate the time until renewal for each, and if a contract falls within our notification window (e.g., 30 days), it will call another function to compose and send the email alert.

Here is the complete code that accomplishes this. You can add this to your Code.gs file.

// A constant to define our notification window in days.

const NOTIFICATION_WINDOW_DAYS = 30;

/**

* Main function to be called by the time-based trigger.

* It iterates through contracts and sends notifications for upcoming renewals.

*/

function checkRenewalsAndNotify() \{

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Contracts");

const dataRange = sheet.getDataRange();

const values = dataRange.getValues();

// Get today's date at the beginning of the day for consistent comparison.

const today = new Date();

today.setHours(0, 0, 0, 0);

// Loop through all rows, skipping the header row (i=0).

for (let i = 1; i < values.length; i++) \&#123;

const row = values[i];

// Assuming column layout: 0:Contract Name, 1:Renewal Date, 2:Stakeholder Email, 3:AI Summary

const contractName = row[0];

const renewalDate = new Date(row[1]);

const stakeholderEmail = row[2];

const aiSummary = row[3];

// Calculate the difference in days between today and the renewal date.

const timeDiff = renewalDate.getTime() - today.getTime();

const daysUntilRenewal = Math.ceil(timeDiff / (1000  *3600*  24));

// Check if the renewal is within our notification window and hasn't passed.

if (daysUntilRenewal > 0 && daysUntilRenewal <= NOTIFICATION_WINDOW_DAYS) \&#123;

sendRenewalEmail(stakeholderEmail, contractName, renewalDate, daysUntilRenewal, aiSummary);

\&#125;

\&#125;

\&#125;

/**

* Composes and sends a renewal alert email using GmailApp.

* @param \&#123;string\&#125; recipient The email address of the stakeholder.

* @param \&#123;string\&#125; contractName The name of the contract.

* @param \&#123;Date\&#125; renewalDate The renewal date of the contract.

* @param \&#123;number\&#125; daysUntilRenewal The number of days until renewal.

* @param \&#123;string\&#125; aiSummary The AI-generated summary of the contract.

*/

function sendRenewalEmail(recipient, contractName, renewalDate, daysUntilRenewal, aiSummary) \&#123;

const subject = `ACTION REQUIRED: Contract Renewal for "${contractName}" in ${daysUntilRenewal} days`;

const formattedDate = renewalDate.toLocaleDateString();

const body = `

<p>Hello,</p>

<p>This is an automated alert regarding the upcoming contract renewal for <strong>${contractName}</strong>.</p>

<ul>

<li><strong>Renewal Date:</strong> ${formattedDate}</li>

<li><strong>Time Remaining:</strong> ${daysUntilRenewal} days</li>

</ul>

<p><strong>AI-Generated Summary of Key Terms:</strong></p>

<blockquote style="border-left: 4px solid #ccc; padding-left: 1em; margin-left: 0;">

<p><em>${aiSummary.replace(/\n/g, '<br />')}</em></p>

</blockquote>

<p>Please review the terms and take the necessary action for renewal or termination.</p>

<p>Thank you,<br />Automated Contract Management System</p>

`;

// Use GmailApp to send the email. The htmlBody option allows for rich formatting.

GmailApp.sendEmail(recipient, subject, "", \&#123;

htmlBody: body,

name: 'Contract Renewal Agent' // Optional: set a custom sender name

\&#125;);

\&#125;

In this script, the checkRenewalsAndNotify function reads every contract from our sheet. For each one, it calculates the number of days until the renewal date. If that number falls within our NOTIFICATION_WINDOW_DAYS, it calls the sendRenewalEmail function, passing along all the relevant details.

The sendRenewalEmail function is where GmailApp shines. It constructs a professional, easy-to-read HTML email that includes all the critical information at a glance: the contract name, the exact renewal date, and the number of days left. Most importantly, it embeds the AI-generated summary directly into the email body, giving the stakeholder immediate context without them needing to open the original contract file. This simple, automated action transforms our spreadsheet from a static list into a dynamic, intelligent, and indispensable part of your contract management workflow.

Conclusion: Beyond Renewals – The Future of AI in Finance Ops

We’ve successfully engineered more than just a script; we’ve built a cognitive assistant for a critical financial workflow. The Gemini-powered agent for contract renewals is a powerful proof-of-concept, but its true value lies in serving as a launchpad for a broader transformation. This isn’t just about automating a single task—it’s about fundamentally rethinking how financial operations function in an AI-native world. By moving from reactive, manual processes to proactive, intelligent automation, you’re laying the groundwork for a more resilient and efficient FinOps ecosystem.

Measuring the ROI: Reduced revenue leakage and increased efficiency

Before scaling, it’s crucial to quantify the impact. The business case for this agent isn’t abstract; it’s rooted in tangible metrics that directly affect the bottom line.

• Revenue Leakage: The most direct metric is the prevention of missed renewal opportunities and unwanted auto-renewals of unfavorable terms. Implement robust logging within your agent to track every contract it flags for action. Tag these events with the contract value and potential revenue impact. Over a quarter, you can aggregate this data to present a clear figure: “Our Gemini agent prevented $X in potential revenue leakage by identifying Y at-risk contracts.”

• Operational Efficiency: Calculate the delta between manual and automated processing. Time the end-to-end manual process—from locating the contract to extracting key dates and terms—and compare it to the agent’s execution time. This often reveals a shift from hours to minutes per document. Frame this as “reclaimed FTE hours,” which can be reallocated to higher-value strategic tasks like negotiation and vendor relationship management. Build a simple dashboard (e.g., with Looker Studio or Grafana) to visualize contracts processed per day, average processing time, and human-in-the-loop escalations to make this efficiency gain visible to all stakeholders.

Expanding the agent’s capabilities: Compliance checks and spend analysis

The true power of a Large Language Model is its versatility. The core architecture you’ve built—ingesting documents, reasoning with an LLM, and triggering actions—is a reusable pattern. Consider these logical next steps:

• Compliance & Risk Auditing: Enhance the agent’s prompt templates and toolset to perform compliance checks. It can be trained to scan contracts for the presence or absence of specific clauses mandated by regulations like SOX, GDPR, or internal corporate policies. For example, the agent could automatically flag any new vendor agreement that lacks your company’s standard data privacy addendum or contains non-standard liability clauses, routing it immediately to the legal team.

• Intelligent Spend Analysis: Point the agent at your entire repository of vendor contracts to perform a comprehensive spend analysis. It can be tasked with extracting not just renewal dates, but also pricing tables, payment terms, usage tiers, and termination penalties. By structuring this data across hundreds of agreements, you can uncover insights that are nearly impossible to find manually, such as identifying redundant software licenses across different departments or pinpointing opportunities for volume-based discounts.

Your next step: Scale your automation architecture

A single, powerful agent is a victory. A fleet of interconnected, specialized agents is a competitive advantage. Moving from a standalone script to a scalable, Architecting an Event-Driven Workspace with PubSub Firebase and Gemini is the final piece of the puzzle.

• Adopt an Event-Driven Model: Instead of relying on scheduled batch jobs, re-architect your solution around events. Use a trigger, such as a new file being uploaded to a specific cloud storage bucket, to invoke your agent via a serverless function (e.g., Google Cloud Functions). This creates a real-time, highly scalable system that processes documents as they arrive.

• Implement an Orchestration Layer: As you build more specialized agents (for compliance, spend analysis, etc.), you’ll need an orchestrator. A tool like Google Cloud Workflows can act as a central “brain,” routing a document to the appropriate agent based on its type or metadata. This creates a modular and maintainable “AI assembly line” for document processing.

• Integrate Human-in-the-Loop (HITL): Acknowledge that full automation isn’t always the goal. The most robust systems blend AI efficiency with human expertise. Your architecture should include a clear escalation path. When the Gemini agent encounters a highly ambiguous clause or a contract with a value exceeding a certain threshold, it shouldn’t fail silently. It should automatically create a task in a system like Jira or ServiceNow, complete with a summary of the issue and a link to the document, assigning it to the correct human expert for final review.

The foundation is laid. You’ve proven the concept. Now is the time to scale your architecture and transform your financial operations from a cost center into a strategic, data-driven engine for growth.