Building a RAG Context Manager with Apps Script and Gemini Pro

May 05, 2026

Retrieval-Augmented Generation (RAG) is the key to making AI an expert on your private data, but its power is limited by the critical context window challenge.

Introduction: The Context Window Challenge in RAG

Why Retrieval-Augmented Generation is a Game-Changer

Generative AI models like Google’s Gemini Pro are nothing short of revolutionary. They can write code, draft emails, and brainstorm ideas with startling fluency. But they have an Achilles’ heel: their knowledge is frozen in time. They only “know” what they were taught during their last training run and have no access to your private, real-time, or domain-specific information. This leads to generic answers and, occasionally, confident-sounding fabrications known as “hallucinations.”

Enter Retrieval-Augmented Generation (RAG).

RAG is an elegant solution that transforms a general-purpose LLM into a bespoke expert on your data. The concept is brilliantly simple: instead of just asking the model a question, you first retrieve relevant snippets of information from your own knowledge base (e.g., documents in your Google Drive, internal wikis, or project plans). Then, you “augment” the prompt by packaging this retrieved context alongside your original question.

It’s the difference between asking an expert a question from memory versus giving them an open-book test with all the relevant reference materials right in front of them. The result? Answers that are not only accurate and up-to-date but are also grounded in your specific source material, complete with citations. It’s a paradigm shift, turning LLMs from creative-but-unreliable partners into powerful, fact-based reasoning engines.

The Bottleneck: Large Documents vs. Limited API Context

So, RAG is the answer. We just point it at our 100-page project proposal in Google Docs and let it work its magic, right? Not so fast. Here we collide with a fundamental technical constraint: the context window.

Think of the context window as the model’s working memory or the amount of text (both your input and its generated output) that it can consider at one time. This limit is measured in “tokens”—pieces of words. While models like Gemini 1.0 Pro have a generous 32,768-token window, this is still a hard limit. A dense, 50-page report can easily exceed 40,000 tokens.

This creates a critical bottleneck. You can’t simply feed an entire large document into the API call. The API will reject the request for being too long. The alternative—manually trimming the document down to fit—is a terrible workaround. You risk cutting out the very paragraph that contains the answer you need. The document is a firehose of information, but the API can only accept a trickle. How do we bridge this gap without losing critical information?

This is where we get clever. The solution isn’t to force the entire document through the API’s narrow door. Instead, we strategically break the document down into smaller, semantically meaningful pieces. This process is called chunking.

By dividing a large document into a collection of smaller chunks (e.g., paragraphs, sections, or fixed-size blocks of text), we create a searchable index. When a user asks a question, our RAG system doesn’t look at the whole document. It first performs a quick, efficient search to find the top 3-5 most relevant chunks related to the query. Then, it feeds only these highly-relevant, bite-sized chunks to the LLM. This approach easily fits within the context window and provides the model with precisely the information it needs to formulate a high-quality, contextualized answer.

And what better place to build this document pre-processor than right inside the ecosystem where our documents live? By leveraging [AI Powered Cover Letter [Automated Job Creation in Real Time Jobber and Google Sheets Integration from Gmail](https://votuduc.com/Automated-Job-Creation-in-Jobber-from-Gmail-p115606) Engine](https://votuduc.com/AI-Powered-Cover-Letter-Automated Quote Generation and Delivery System for Jobber-Engine-p111092), we can create a seamless, automated workflow directly within AC2F Streamline Your Google Drive Workflow.

In this article, we’re going to build exactly that: a lightweight, automated RAG Context Manager. This script will live in a Google Sheet, allowing you to simply provide a Google Doc ID. It will then automatically process the document, perform intelligent chunking, and organize the data, preparing it to be the backbone of a powerful, custom RAG system powered by Gemini Pro.

Architectural Overview and Prerequisites

Before we jump into the code, let’s establish a solid foundation. Understanding the architecture—how the different pieces of our system talk to each other—and getting our environment configured correctly is 90% of the battle. Getting this right now will save you a world of debugging headaches later.

System Diagram: How Apps Script and Gemini Pro Interact

At its core, our RAG Context Manager is a clever orchestrator. Apps Script acts as the “brain” of the operation, fetching information from our knowledge base in Google Drive and using it to have an intelligent conversation with the Gemini Pro model.

Here’s a high-level look at the data flow:

User Trigger: The process kicks off when a user interacts with a custom menu in a Google Doc or Sheet.
Context Retrieval: The Apps Script function is triggered. It uses the DriveApp service to access a designated Google Drive folder. It scans this folder, finds relevant documents (our knowledge base), and reads their content into memory.
[Prompt Engineering for Reliable Autonomous Workspace Agents for Reliable Autonomous Workspace Agents](https://votuduc.com/prompt-engineering-for-reliable-autonomous-workspace-agents-p-20260319404106): The script then takes the user’s query and dynamically constructs a detailed prompt. This isn’t just the user’s question; it’s a carefully crafted message that includes the retrieved text from the Drive files as “context.”
API Call: Using the UrlFetchApp service, Apps Script sends this context-rich prompt in an authenticated POST request to the [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) Gemini Pro API endpoint.
LLM Generation: Gemini Pro receives the request. Because we’ve provided it with specific context, it doesn’t have to rely solely on its general training data. It uses the information from our documents to generate a highly relevant and accurate answer.
Response Handling: The API sends the generated text back to our Apps Script.
Display to User: Finally, the script parses the JSON response from the API and displays the clean, formatted answer to the user, perhaps in a sidebar or a dialog box within their Google Doc.

Here’s that flow visualized as a diagram:


sequenceDiagram

participant User

participant [Automated Client Onboarding with Google Forms and Google Drive.](https://votuduc.com/Automated-Client-Onboarding-with-Google-Forms-and-Google-Drive-p440350) (Doc/Sheet)

participant Apps Script

participant Google Drive

participant Gemini Pro API

User->>[Automated Discount Code Management System](https://votuduc.com/Automated-Discount-Code-Management-System-p773671) (Doc/Sheet): Clicks custom menu item "Ask..."

[Automated Email Journey with Google Sheets and Google Analytics](https://votuduc.com/Automated-Email-Journey-with-Google-Sheets-and-Google-Analytics-p965570) (Doc/Sheet)->>Apps Script: Executes RAG function with user query

Apps Script->>Google Drive: Request files from knowledge base folder

Google Drive-->>Apps Script: Returns file content

Apps Script->>Apps Script: Constructs prompt (Query + Context)

Apps Script->>Gemini Pro API: Sends authenticated POST request

Gemini Pro API-->>Apps Script: Returns generated response (JSON)

Apps Script->>[Automated Google Slides Generation with Text Replacement](https://votuduc.com/Automated-Google-Slides-Generation-with-Text-Replacement-p850694) (Doc/Sheet): Displays formatted answer to user

[Automated Order Processing Wordpress to Gmail to Google Sheets to Jobber](https://votuduc.com/Automated-Order-Processing-Wordpress-to-Gmail-to-Google-Sheets-to-Jobber-p649487) (Doc/Sheet)-->>User: Shows answer in UI dialog

Setting Up Your Google Cloud Project and Enabling the Gemini API

Apps Script can’t talk to Gemini Pro out of the box. We need to authorize this connection by linking our script to a Google Cloud Platform (GCP) project where the Vertex AI API (which hosts Gemini) is enabled.

1. Create or Select a Google Cloud Project

First, you need a home for your API configuration.

Navigate to the Google Cloud Console.
In the top-left corner, click the project selector dropdown.
You can either select an existing project or, for better organization, click “NEW PROJECT”. Give it a descriptive name like gemini-rag-manager and complete the creation process.
Important: Make sure your project is linked to a billing account. While there’s a generous free tier, a billing account is required to enable most APIs, including Vertex AI.

2. Enable the Vertex AI API

Next, we need to “turn on” the API within your new GCP project.

In the Google Cloud Console, with your project selected, use the top search bar to find “Vertex AI API”.
Select it from the results and click the “ENABLE” button. This may take a minute or two to provision.

3. Link the GCP Project to Your Apps Script

This is the final, critical step that connects your script to the cloud project you just configured.

Open your Apps Script project.
Click on the “Project Settings” (⚙️) icon in the left-hand sidebar.
Scroll down to the “Google Cloud Platform (GCP) Project” section.
Click “Change project”.
Paste your GCP Project Number into the text box. You can find this number on your Google Cloud Console dashboard.
Click “Set project”.

Your script now has a direct line to the services enabled in your GCP project, including the powerful Gemini API.

Essential Apps Script Permissions for DriveApp

For security, Google requires us to explicitly declare what our script needs to access. We do this by defining “OAuth scopes” in a special manifest file. This ensures that users running the script know exactly what permissions they are granting.

First, you need to make the manifest file visible in the editor:

In the Apps Script editor, click View > Show manifest file.
This will reveal a file named appsscript.json in your file list.

Now, open appsscript.json and add or modify the oauthScopes array to include the following permissions. Your file should look something like this:


{

"timeZone": "America/New_York",

"dependencies": {},

"exceptionLogging": "STACKDRIVER",

"runtimeVersion": "V8",

"oauthScopes": [

"https://www.googleapis.com/auth/script.external_request",

"https://www.googleapis.com/auth/drive.readonly",

"https://www.googleapis.com/auth/documents.currentonly"

]

}

Let’s break down why we need each of these:

https://www.googleapis.com/auth/script.external_request: This is non-negotiable. It grants our script permission to call external APIs, which is exactly what we’re doing when we call the Gemini endpoint with UrlFetchApp.
https://www.googleapis.com/auth/drive.readonly: This scope allows our script to view and read files from Google Drive. Note that we are specifically requesting readonly access. This is a security best practice—our RAG system only needs to read context, not modify or delete files, so we should only ask for the minimum permission required.
https://www.googleapis.com/auth/documents.currentonly: This allows the script to interact with the Google Doc that it’s bound to (e.g., to display a UI). If you were building this in a Google Sheet, you would use https://www.googleapis.com/auth/spreadsheets.currentonly instead.

After saving this manifest file, the next time you run a function from the script, Google will present you with an authorization dialog asking you to approve these specific permissions for your account. This is expected and necessary for the script to function.

Step 1: Extracting Content from Google Docs

Before our RAG system can answer questions, it needs a knowledge base to draw from. This is the “Retrieval” part of Retrieval-Augmented Generation. For our project, this knowledge base will be a collection of Google Docs. Our first task is to programmatically access these documents, pull out their raw text content, and prepare it for the next stage. We’ll accomplish this using two powerful, built-in Genesis Engine AI Powered Content to Video Production Pipeline services: DriveApp and DocumentApp.

Using DriveApp to Target Specific Documents

Think of DriveApp as your script’s hands, capable of reaching into Google Drive to find and manipulate files and folders. To build a scalable context manager, we won’t hardcode individual document IDs. Instead, we’ll designate a specific folder in Google Drive to hold all our source documents. This approach allows us to easily add or remove knowledge simply by managing the files in that folder.

The process is straightforward:

Obtain the unique ID of your target folder. You can find this in the URL when you have the folder open in Google Drive (it looks like .../folders/A_LONG_UNIQUE_ID).
Use the DriveApp.getFolderById(folderId) method to get a Folder object.
From this Folder object, call the getFiles() method.

This getFiles() method doesn’t return a simple array. It returns a FileIterator, which is an efficient way to loop through a collection of items, one at a time, without loading them all into memory at once. We’ll iterate through this object to process each document individually.

Reading the Document Body as Plain Text

Once we have a File object from our FileIterator, we need to open it and read its contents. While DriveApp gets us the file, DocumentApp is the service specifically designed to interact with the content inside a Google Doc.

Here’s the key step: we’ll use DocumentApp.openById(fileId) to access the document. From the opened document, we can get its Body object. The Body contains everything—text, images, tables, formatting, and all. For a language model, this extra information is just noise. We need pure, unadulterated text.

This is where the magic getText() method comes in. Calling body.getText() strips away all formatting, lists, tables, and embedded objects, returning a single string of plain text. This is the clean, raw data our model needs to effectively learn and retrieve information.

Code Snippet for Text Extraction and Cleaning

Let’s combine these concepts into a reusable function. This function will take a folder ID, iterate through all the Google Docs within it, and extract their cleaned text content.

We’ll also add a crucial best practice: checking the file’s MIME type. This ensures our script only attempts to process actual Google Docs, preventing errors if other files (like images or spreadsheets) are in the same folder. Finally, we’ll perform a minor but important cleaning step using a regular expression to normalize whitespace, which improves the quality of the text we’ll eventually feed into our model.


/**

* Extracts and cleans plain text from all Google Docs within a specified Google Drive folder.

*

* @param {string} folderId The ID of the Google Drive folder containing the source documents.

* @returns {Array<Object>} An array of objects, where each object contains the document ID, title, and its cleaned text content.

*/

function extractTextFromDocsInFolder(folderId) {

const sourceFolder = DriveApp.getFolderById(folderId);

const files = sourceFolder.getFiles();

const documentsData = [];

console.log(`Starting text extraction from folder: "${sourceFolder.getName()}"`);

while (files.hasNext()) {

const file = files.next();

// Best practice: Only process actual Google Docs and skip other file types.

if (file.getMimeType() === MimeType.GOOGLE_DOCS) {

try {

const doc = DocumentApp.openById(file.getId());

const body = doc.getBody();

let text = body.getText();

// Clean the text: replace multiple whitespace chars (including newlines) with a single space

// and trim leading/trailing whitespace. This creates a more uniform text body for the model.

let cleanedText = text.replace(/\s+/g, ' ').trim();

if (cleanedText.length > 0) {

documentsData.push({

id: file.getId(),

title: doc.getName(),

content: cleanedText

});

console.log(`Successfully extracted and cleaned content from: "${doc.getName()}"`);

} else {

console.log(`Skipping empty document: "${doc.getName()}"`);

}

} catch (e) {

console.error(`Failed to process file: ${file.getName()} (ID: ${file.getId()}). Error: ${e.message}`);

}

}

}

console.log(`Extraction complete. Processed ${documentsData.length} documents.`);

return documentsData;

}

// --- Example Usage ---

function runExtraction() {

const FOLDER_ID = "YOUR_FOLDER_ID_HERE"; // <-- IMPORTANT: Replace with your folder ID

const extractedData = extractTextFromDocsInFolder(FOLDER_ID);

// You can now see the structured data in the Apps Script logs

// Logger.log(JSON.stringify(extractedData, null, 2));

}

Dissecting the Code:

DriveApp.getFolderById(folderId): This is our entry point, targeting the specific container for our knowledge base.
while (files.hasNext()): This is the standard, memory-efficient loop for processing a FileIterator.
file.getMimeType() === MimeType.GOOGLE_DOCS: This conditional check is our guardrail. It ensures we don’t try to call DocumentApp methods on a JPEG or a Google Sheet, which would throw an error.
text.replace(/\s+/g, ' ').trim(): This is our text cleaning operation. The regular expression \s+ matches one or more whitespace characters (spaces, tabs, newlines). We replace them all with a single space and then trim() any leftover whitespace from the beginning or end of the string. This normalization is a simple but effective way to improve data quality.
documentsData.push({...}): We store the results in a structured array of objects. This format, containing an ID, title, and the content, is incredibly useful for tracking, debugging, and for the subsequent steps of our RAG pipeline.

Step 2: Implementing the Smart Chunking Logic

With our text extracted, we arrive at a foundational step in any RAG system: chunking. You can’t simply feed a massive document into Gemini—it has context limits, and more importantly, providing a large, unfocused wall of text leads to poor, unfocused answers. Chunking is the art of breaking down a large document into smaller, semantically meaningful pieces. Each piece, or “chunk,” is small enough to be efficiently embedded and processed but large enough to contain a coherent idea.

Choosing a Strategy: Paragraph-Based Chunking

There are several ways to slice up a document, each with its own trade-offs:

Fixed-Size Chunking: The simplest method. You chop the text every N characters or words. It’s fast but dumb, often slicing sentences and ideas right down the middle, destroying context.
Sentence-Based Chunking: A step up. You split the text by sentences, which are complete grammatical units. This is much better for preserving meaning but can be tricky if your text has complex or unconventional sentence structures.
Recursive Chunking: A more advanced technique that attempts to split text using a hierarchy of separators (e.g., try splitting by paragraphs first, then by sentences, then by words) to keep related text together.

For our project, we’re choosing Paragraph-Based Chunking. Why? It strikes an excellent balance between implementation simplicity and semantic integrity. Paragraphs are, by design, self-contained units that explore a specific idea. The author has already done the hard work of grouping related sentences for us! In the context of [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), where we don’t have access to sophisticated NLP libraries like NLTK or spaCy, splitting text by a reliable delimiter like a double newline (\n\n) is both robust and highly effective.

Writing the Apps Script Function to Split Text into Chunks

Let’s translate our strategy into code. We’ll create a helper function in our Code.gs file whose sole job is to take a string of text and split it into an array of paragraphs.

Here is the function:


/**

* Splits a block of text into chunks based on paragraphs.

* Paragraphs are assumed to be separated by one or more blank lines.

* @param {string} text The full text content to be split.

* @returns {string[]} An array of text chunks, where each chunk is a paragraph.

*/

function chunkTextByParagraph(text) {

if (!text || typeof text !== 'string') {

return [];

}

// The regex /\n\s*\n/ splits the text by one or more blank lines,

// which is a reliable way to identify paragraph breaks.

// \n: matches a newline character.

// \s*: matches any whitespace character (space, tab, etc.) zero or more times.

// \n: matches the subsequent newline character.

const paragraphs = text.split(/\n\s*\n/);

// After splitting, we might have empty strings if there are trailing

// newlines or multiple blank lines. This filters them out.

return paragraphs.filter(p => p.trim() !== '');

}

Breaking Down the Code:

Function Definition: We define a simple function chunkTextByParagraph that accepts one argument, text. Good JSDoc comments are included to explain what it does, its parameters, and what it returns.
Input Validation: A quick check ensures that we’re actually working with a string.
**The Split Logic: **The core of the function is text.split(/\n\s*\n/). This regular expression is a powerful way to define a paragraph break. It looks for a newline, followed by any amount of whitespace (including none), followed by another newline. This correctly handles text with single blank lines, multiple blank lines, or lines containing only spaces between paragraphs.
Cleanup: The .filter(p => p.trim() !== '') call is a crucial cleanup step. The split method can sometimes produce empty strings in its results array, especially if the source text has leading or trailing blank lines. This line iterates through the array and keeps only the elements that are not empty after trimming whitespace.

Managing Chunk Size and Overlap for Contextual Cohesion

Our paragraph-based function is a great start, but it has a potential flaw: what if a single paragraph is exceptionally long? A paragraph spanning several pages would defeat the purpose of chunking. Furthermore, what if a user’s query relates to an idea that transitions from the end of one paragraph to the beginning of the next?

To build a truly “smart” chunker, we need to introduce two more concepts:

Maximum Chunk Size: This is a hard limit (e.g., 2000 characters) that no chunk should exceed. It ensures every chunk is a manageable size for both the embedding model and for Gemini’s final context.
Chunk Overlap: This is the secret to maintaining context across boundaries. When we are forced to split a large piece of text, we don’t make a clean cut. Instead, the next chunk will begin with a small piece of the end of the previous chunk (e.g., 200 characters). This “contextual glue” ensures that ideas aren’t abruptly severed, giving our RAG system a much better chance of finding relevant information that spans a split.

Let’s evolve our logic into a new function, createSmartChunks, that incorporates these rules. This function will use our paragraph-based approach as its foundation but add a layer of intelligence to handle oversized content.


/**

* Splits text into semantically relevant chunks, handling oversized

* paragraphs and adding overlap for better contextual cohesion.

* @param {string} text The full text to chunk.

* @param {number} [maxSize=2000] The target maximum size of a chunk in characters.

* @param {number} [overlap=200] The number of characters to overlap between sub-chunks of a large paragraph.

* @returns {string[]} An array of processed text chunks.

*/

function createSmartChunks(text, maxSize = 2000, overlap = 200) {

// 1. Start with our basic paragraph chunking to respect semantic boundaries.

const paragraphs = chunkTextByParagraph(text);

const finalChunks = [];

for (const p of paragraphs) {

// 2. If the paragraph is already a manageable size, just add it.

if (p.length <= maxSize) {

finalChunks.push(p);

} else {

// 3. The paragraph is too long. We must split it into smaller pieces.

// This is where overlap becomes critical.

let offset = 0;

while (offset < p.length) {

const end = offset + maxSize;

const chunk = p.substring(offset, end);

finalChunks.push(chunk);

// 4. Move the offset forward, but pull it back by the overlap amount.

// This creates the sliding window effect.

offset += maxSize - overlap;

}

}

}

return finalChunks;

}

How This Smart Chunker Works:

This function represents a powerful hybrid approach.

It begins by calling chunkTextByParagraph to get an array of semantically grouped paragraphs. It trusts the author’s structure first.
It then iterates through each paragraph. If the paragraph is within our maxSize limit, it’s added to our final list as-is. We’ve successfully created a perfect semantic chunk.
However, if a paragraph is too long, the else block kicks in. A while loop performs a “sliding window” split only on that oversized paragraph.
The magic is in the line offset += maxSize - overlap;. Instead of jumping ahead by the full maxSize, it only moves forward by the size of the chunk minus the overlap. This means the next chunk will start before the previous one ended, creating that crucial contextual bridge we need for high-quality retrieval.

Step 3: Integrating Chunks with the Gemini 1.5 Pro API

With our document neatly sliced into contextually-aware chunks, we’ve arrived at the core of our RAG system: the “Generation” phase. This is where we hand over the relevant information and our user’s query to the powerful Gemini 1.5 Pro model. We’ll construct a precise API request, craft a prompt that guides the model effectively, and then parse its response to extract the final answer. For all our HTTP needs, we’ll be using Google Apps Script’s robust, built-in UrlFetchApp service.

Structuring the API Payload with UrlFetchApp

Communicating with the Gemini API involves sending a POST request with a specifically structured JSON payload. This payload tells the model everything it needs to know: who is asking, what they’re asking, and what configuration to use for the generation.

Our payload will have two main top-level keys: contents and generationConfig.

contents: This is an array that holds the conversational turn. For a simple query, it will contain a single object with a role of “user” and a parts array. The parts array is where we’ll place our carefully crafted prompt.
generationConfig: This object allows us to tweak the model’s behavior. We can control its creativity (temperature), limit the length of its response (maxOutputTokens), and fine-tune its sampling strategy (topK, topP).

Let’s see how this translates into Apps Script code. We’ll create a function that assembles the complete request options object required by UrlFetchApp.


/**

* Prepares the options object for the UrlFetchApp call to the Gemini API.

* @param {string} prompt The full prompt text, including context and the user query.

* @return {object} The options object for UrlFetchApp.fetch().

*/

function getGeminiRequestOptions(prompt) {

// Retrieve your API key securely from Properties Service.

// Go to Project Settings > Script Properties to add your key.

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

if (!API_KEY) {

throw new Error("GEMINI_API_KEY not found in Script Properties. Please set it.");

}

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

const payload = {

"contents": [{

"role": "user",

"parts": [{

"text": prompt

}]

}],

"generationConfig": {

"temperature": 0.2, // Lower temperature for more factual, less creative responses

"topK": 1,

"topP": 1,

"maxOutputTokens": 2048,

}

};

const options = {

'method': 'post',

'contentType': 'application/json',

'muteHttpExceptions': true, // Important for handling errors gracefully

'payload': JSON.stringify(payload)

};

return { url, options };

}

A key detail here is muteHttpExceptions: true. By default, UrlFetchApp will throw an error if it receives a non-2xx HTTP response (like a 400 or 500 error). Setting this to true allows our script to continue running, so we can inspect the response code and body ourselves to implement more robust error handling.

Crafting a Prompt to Query the Document Chunks

This is arguably the most critical part of the entire RAG process. A well-crafted prompt is the difference between a precise, contextually-grounded answer and a generic, unhelpful one. Our goal is to instruct the model to act as a query engine for the specific text we provide.

A strong RAG prompt should contain three elements:

**The Instruction: A clear directive telling the model how to behave. We must command it to base its answer only on the provided context and to state when the answer is not present. This drastically reduces the chance of hallucination.
The Context: The concatenated document chunks that are relevant to the user’s query. We’ll wrap this in clear markers to help the model distinguish it from the rest of the prompt.
The Question: The user’s original query.

Here’s a function that assembles these pieces into a single, powerful prompt string.


/**

* Crafts a RAG prompt by combining instructions, context, and a user query.

* @param {string} query The user's question.

* @param {string[]} chunks An array of relevant document text chunks.

* @return {string} The fully assembled prompt for the Gemini API.

*/

function craftRagPrompt(query, chunks) {

const context = chunks.join("\n---\n"); // Join chunks with a clear separator

const prompt = `

INSTRUCTION:

Your task is to answer the following question based ONLY on the provided context.

Read the context carefully before answering.

If the answer to the question cannot be found in the context, you MUST respond with "I am sorry, but the answer to that question is not found in the provided document."

Do not use any prior knowledge.

--- CONTEXT START ---

${context}

--- CONTEXT END ---

QUESTION:

${query}

ANSWER:

`;

return prompt;

}

By providing this explicit structure, we’re not just asking a question; we’re programming the model’s behavior for this specific task, forcing it to adhere to the boundaries of our document.

Executing the API Call and Handling the Response

With our payload and prompt ready, it’s time to send the request and process the result. This function will be the orchestrator, bringing together the previous pieces to get our final answer.

We’ll make the call using UrlFetchApp.fetch(), check the HTTP response code for success, and then parse the JSON response. The Gemini API’s response has a nested structure, and the actual generated text is located at candidates[0].content.parts[0].text. It’s crucial to navigate this path correctly and include error handling in case the expected structure is missing.


/**

* Sends a query and context chunks to the Gemini API and returns the answer.

* @param {string} query The user's question.

* @param {string[]} chunks The relevant document chunks.

* @return {string} The generated answer from the Gemini model.

*/

function queryGeminiWithContext(query, chunks) {

try {

// 1. Craft the detailed RAG prompt

const prompt = craftRagPrompt(query, chunks);

// 2. Get the structured request options

const { url, options } = getGeminiRequestOptions(prompt);

// 3. Execute the API call

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

const responseCode = response.getResponseCode();

const responseBody = response.getContentText();

// 4. Handle the response

if (responseCode === 200) {

const parsedResponse = JSON.parse(responseBody);

// Safely navigate the nested response object to find the answer

const answer = parsedResponse.candidates?.[0]?.content?.parts?.[0]?.text;

if (answer) {

return answer.trim();

} else {

// This can happen if the model's response is blocked for safety reasons

Logger.log(`Could not extract text from response. Full response: ${responseBody}`);

return "Error: The model generated a response, but its content could not be extracted.";

}

} else {

// Handle non-200 responses

Logger.log(`API Error: Received HTTP ${responseCode}. Response: ${responseBody}`);

return `Error: The API returned a status code of ${responseCode}. Please check the logs for more details.`;

}

} catch (e) {

Logger.log(`An unexpected error occurred: ${e.message}\nStack: ${e.stack}`);

return "An unexpected error occurred while contacting the AI model. Please check the execution logs.";

}

}

This function is the complete engine for our “Generation” step. It takes the user’s query and the retrieved chunks, formats them perfectly for the Gemini 1.5 Pro model, executes the request, and safely extracts the final, context-aware answer.

Putting It All Together: A Complete Workflow

We’ve built the individual components: a function to chunk text, a service to call the Gemini API for embeddings, and a method to write data to our Google Sheet vector store. Now it’s time to connect these gears and build the engine. This section details the main controller function that orchestrates the entire process, provides a complete, commented script, and walks you through testing the system from end to end.

The Main Controller Function: Orchestrating the Process

Every good application needs an entry point, a conductor that tells each section of the orchestra when to play. In our case, this is the processDocumentForRAG() function. Its job is simple but crucial: to execute our helper functions in the correct sequence.

Here’s the high-level game plan for our controller:

Identify the Target: Get the content of the currently active Google Doc.
Prepare the Canvas: Access our Google Sheet and clear any old data to make way for the new context.
Slice and Dice: Pass the document’s text to our chunkText() function to break it down into manageable, overlapping pieces.
Translate to Vectors: Send these text chunks to the Gemini API via our getGeminiProEmbeddings() function to convert them into numerical representations (embeddings).
Store the Knowledge: Write the original text chunks and their corresponding embeddings into the Google Sheet for long-term storage and retrieval.
Signal Success: Let the user know the process is complete with a simple UI alert.

This function acts as the bridge between the user’s action (clicking a menu item) and the complex machinery working in the background.


/**

* Main controller function to process the active Google Doc.

* It chunks the text, generates embeddings, and stores them in a Google Sheet.

*/

function processDocumentForRAG() {

const doc = DocumentApp.getActiveDocument();

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

if (text.trim().length === 0) {

DocumentApp.getUi().alert('The document is empty. Please add content to process.');

return;

}

console.log('Starting document processing...');

// 1. Chunk the document text

const chunks = chunkText(text, 1000, 200);

console.log(`Document split into ${chunks.length} chunks.`);

// 2. Generate embeddings for the chunks

const embeddings = getGeminiProEmbeddings(chunks);

if (!embeddings || embeddings.length !== chunks.length) {

DocumentApp.getUi().alert('Failed to generate embeddings. Check the logs for details.');

console.error('Mismatch between number of chunks and embeddings, or API call failed.');

return;

}

console.log('Successfully generated embeddings for all chunks.');

// 3. Store chunks and embeddings in the Google Sheet

storeEmbeddingsInSheet(chunks, embeddings);

console.log('Data successfully stored in Google Sheet.');

// 4. Notify the user

DocumentApp.getUi().alert('Document processing complete!', 'The document has been chunked, embedded, and stored in the RAG context sheet.', DocumentApp.getUi().ButtonSet.OK);

}

Full Script Walkthrough and Explanation

Below is the complete, final script for our Code.gs file. It includes the controller function we just discussed, all the necessary helper functions, and a special onOpen() trigger to create a custom menu in Google Docs, making the script easy to run.

The Complete Code.gs


// --- CONFIGURATION ---

// IMPORTANT: Replace with your actual API Key and Spreadsheet ID.

const API_KEY = 'YOUR_GEMINI_API_KEY';

const SPREADSHEET_ID = 'YOUR_SPREADSHEET_ID';

const SHEET_NAME = 'VectorStore'; // The name of the sheet to store data

// --- UI & TRIGGERS ---

/**

* Creates a custom menu in the Google Docs UI when the document is opened.

*/

function onOpen() {

DocumentApp.getUi()

.createMenu('RAG Context Manager')

.addItem('Process Document', 'processDocumentForRAG')

.addToUi();

}

// --- MAIN CONTROLLER ---

/**

* Main controller function to process the active Google Doc.

* It chunks the text, generates embeddings, and stores them in a Google Sheet.

*/

function processDocumentForRAG() {

const doc = DocumentApp.getActiveDocument();

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

if (text.trim().length === 0) {

DocumentApp.getUi().alert('The document is empty. Please add content to process.');

return;

}

console.log('Starting document processing...');

// 1. Chunk the document text

const chunks = chunkText(text, 1000, 200); // 1000-char chunks, 200-char overlap

console.log(`Document split into ${chunks.length} chunks.`);

// 2. Generate embeddings for the chunks

const embeddings = getGeminiProEmbeddings(chunks);

if (!embeddings || embeddings.length !== chunks.length) {

DocumentApp.getUi().alert('Failed to generate embeddings. Check the logs for details.');

console.error('Mismatch between number of chunks and embeddings, or API call failed.');

return;

}

console.log('Successfully generated embeddings for all chunks.');

// 3. Store chunks and embeddings in the Google Sheet

storeEmbeddingsInSheet(chunks, embeddings);

console.log('Data successfully stored in Google Sheet.');

// 4. Notify the user

DocumentApp.getUi().alert('Document processing complete!', 'The document has been chunked, embedded, and stored in the RAG context sheet.', DocumentApp.getUi().ButtonSet.OK);

}

// --- HELPER FUNCTIONS ---

/**

* Chunks a long string of text into smaller, overlapping segments.

* @param {string} text The full text to be chunked.

* @param {number} chunkSize The desired character length of each chunk.

* @param {number} overlap The number of characters to overlap between chunks.

* @returns {string[]} An array of text chunks.

*/

function chunkText(text, chunkSize, overlap) {

const chunks = [];

let i = 0;

while (i < text.length) {

const end = Math.min(i + chunkSize, text.length);

chunks.push(text.substring(i, end));

i += chunkSize - overlap;

if (end === text.length) break;

}

return chunks;

}

/**

* Generates embeddings for an array of text chunks using the Gemini Pro API.

* @param {string[]} textChunks An array of text strings.

* @returns {Array<number[]>|null} An array of embedding arrays, or null on failure.

*/

function getGeminiProEmbeddings(textChunks) {

const url = `https://generativelanguage.googleapis.com/v1beta/models/embedding-001:batchEmbedText?key=${API_KEY}`;

const requests = textChunks.map(chunk => ({

model: "models/embedding-001",

text: chunk

}));

const payload = {

requests: requests

};

const options = {

method: 'post',

contentType: 'application/json',

payload: JSON.stringify(payload),

muteHttpExceptions: true // Important for custom error handling

};

try {

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

const responseCode = response.getResponseCode();

const responseBody = response.getContentText();

if (responseCode === 200) {

const jsonResponse = JSON.parse(responseBody);

return jsonResponse.embeddings.map(e => e.values);

} else {

console.error(`API Error: ${responseCode} - ${responseBody}`);

return null;

}

} catch (e) {

console.error(`Network or execution error: ${e.toString()}`);

return null;

}

}

/**

* Stores the text chunks and their corresponding embeddings in a Google Sheet.

* @param {string[]} chunks The array of original text chunks.

* @param {Array<number[]>} embeddings The array of embedding vectors.

*/

function storeEmbeddingsInSheet(chunks, embeddings) {

const ss = SpreadsheetApp.openById(SPREADSHEET_ID);

const sheet = ss.getSheetByName(SHEET_NAME) || ss.insertSheet(SHEET_NAME);

// Clear existing content and set headers

sheet.clear();

sheet.appendRow(['Chunk Text', 'Embedding']);

sheet.setFrozenRows(1);

// Prepare data for batch writing

const data = chunks.map((chunk, index) => {

// The embedding array needs to be stringified for storage in a single cell.

return [chunk, JSON.stringify(embeddings[index])];

});

if (data.length > 0) {

// Write all data in a single API call for efficiency

sheet.getRange(2, 1, data.length, 2).setValues(data);

}

}

Breakdown of Key Components:

onOpen(): This is a special simple trigger in Apps Script. It runs automatically whenever the Google Doc is opened. Our implementation creates a new menu item named “RAG Context Manager,” which contains the option to run our main processDocumentForRAG function. This provides a much better user experience than having to run functions from the script editor.
Configuration Constants: Placing API_KEY, SPREADSHEET_ID, and SHEET_NAME at the top makes the script easy to configure without digging through the code. Remember to replace the placeholder values with your own!
Error Handling: The main function includes checks for an empty document and a failed embedding generation process. It uses DocumentApp.getUi().alert() to communicate issues directly to the user. The getGeminiProEmbeddings function also uses a try...catch block and muteHttpExceptions: true to gracefully handle API or network errors, logging them for debugging instead of crashing the script.
Efficiency: The storeEmbeddingsInSheet function writes all the data to the spreadsheet in a single operation using setValues(data). This is vastly more efficient than writing cell-by-cell in a loop, which would quickly exceed Apps Script execution time limits for large documents.

Testing the System with a Long-Form Sample Document

Now for the moment of truth. Let’s feed our system a real document and see it in action.

Step 1: Get Sample Content

Open a new Google Doc. Find a long-form article to use as your source material. A great option is a comprehensive Wikipedia article. For this test, let’s use the content from the Wikipedia page for “Machine learning”. Copy the text from the article and paste it into your blank Google Doc.

Step 2: Run the Processor

Once your content is in the Doc, refresh the page to ensure the onOpen() trigger has run and the custom menu appears.

Click on the new “RAG Context Manager” menu item.
Select “Process Document” from the dropdown.

You’ll see a “Running script” toast message at the top of the page. Depending on the length of the document, this process might take anywhere from a few seconds to a minute. The script is chunking the text, making a batched API call to Google, and then writing the results to your spreadsheet.

Step 3: Verify the Output

Once the script finishes, you’ll see a confirmation pop-up.

Conclusion and Advanced Considerations

We’ve successfully journeyed from concept to a functional, context-aware agent, all within the accessible and powerful environment of Google Apps Script. By leveraging Google Sheets as a knowledge base and Gemini Pro as our reasoning engine, we’ve built a practical Retrieval-Augmented Generation (RAG) system. This architecture provides a robust foundation for grounding LLM responses in your specific, curated data, mitigating hallucinations and ensuring relevance.

But this is just the beginning. The framework we’ve constructed is a launchpad for more sophisticated, scalable, and intelligent systems. Let’s recap what we’ve built and explore the exciting paths forward.

Recap of Our Context-Aware RAG Agent

At its core, our project demonstrates a powerful pattern:

Orchestration with Apps Script: We used Apps Script as the serverless “brain” of our operation, handling user input, orchestrating API calls, and processing data without needing to manage any infrastructure.
A Controllable Knowledge Base: Google Sheets served as a simple yet effective database. It allowed us to easily store, view, and manage the specific context (the “ground truth”) we want our AI to use.
Keyword-Based Retrieval: Our retrieval mechanism, while basic, effectively scans this knowledge base for relevant keywords to find and extract pertinent information.
Augmented Generation with Gemini Pro: We then passed this retrieved context along with the original query to Gemini Pro, enabling it to generate an informed, contextually accurate response instead of relying solely on its general training data.

This model is incredibly effective for many use cases, especially within the Automated Payment Transaction Ledger with Google Sheets and PayPal ecosystem. However, to unlock the next level of performance, we need to evolve our retrieval mechanism.

Potential Enhancements: Vector Embeddings for Semantic Search

Our current keyword-based search is effective but brittle. It fails if the user’s query uses synonyms or phrases a concept differently than the source text. For example, a search for “quarterly revenue report” might miss a document titled “Q3 Financial Performance Summary.”

The solution is semantic search powered by vector embeddings.

Vector embeddings are numerical representations of text that capture its underlying meaning. Instead of matching keywords, you compare the “meaning vectors” of the query and the knowledge base documents.

Here’s how you could integrate this into our architecture:

Generate Embeddings: For each row or chunk of text in your Google Sheet, you would make a call to an embedding model (like Google’s text-embedding-004 API). This converts your text into a vector (a list of numbers). You would store this vector in a new column next to its corresponding text. This is a one-time process for static data.
Embed the Query: When a user submits a query, you first send their query to the same embedding model to generate its vector.
Calculate Similarity: Instead of a keyword search, you would now iterate through the stored vectors in your Sheet and calculate the mathematical similarity (e.g., using Cosine Similarity) between the query vector and each text chunk’s vector.
Retrieve and Augment: You retrieve the top N text chunks with the highest similarity scores and pass them to Gemini Pro as context.

This approach is vastly more powerful as it finds results based on conceptual relevance, not just lexical overlap. It understands that “company earnings” and “business profits” are semantically close, even if the words are different.

Scaling Your Architecture for Production Workloads

While Apps Script and Google Sheets are brilliant for prototyping and internal tools, high-volume, mission-critical applications demand a more robust architecture. If you’re ready to move from proof-of-concept to production, consider these scaling strategies:

1. Evolve Your Data Store:

Problem: Google Sheets has limitations on cell count, API call frequency, and query performance for large datasets.
Solution: Migrate your knowledge base to a dedicated database. For storing and efficiently searching vector embeddings, a specialized vector database (like Pinecone, Weaviate, or Google’s own Vertex AI Vector Search) is the industry standard. These are purpose-built for performing incredibly fast similarity searches across millions or even billions of vectors.

2. Upgrade Your Orchestration Layer:

Problem: Apps Script has execution time limits (e.g., 6 minutes for a standard Gmail account) and can be less suited for complex, high-throughput applications.
Solution: Move your orchestration logic to a more scalable serverless platform like Google Cloud Functions or Cloud Run.
Cloud Functions: Ideal for event-driven, single-purpose functions (e.g., “process one user query”).
Cloud Run: Perfect for containerizing your entire application, giving you more control over the runtime environment and dependencies.
Benefits: These services offer superior performance, support for languages like JSON-to-Video Automated Rendering Engine and Node.js (which have rich AI/ML ecosystems), and seamless integration with other Google Cloud services.

3. Implement Robust Monitoring and Management:

Problem: Our current script lacks sophisticated logging and error handling. In production, you need visibility into what’s happening.
Solution: Integrate Cloud Logging and Cloud Monitoring. Log every request, the context retrieved, and the final response from Gemini. Set up alerts for high error rates or latency spikes. This is non-negotiable for maintaining a reliable service. By taking these steps, you can transform your innovative prototype into a resilient, scalable, and production-grade AI application.

Vo Tu Duc

A Google Developer Expert, Google Cloud Innovator

Stop Doing Manual Work. Scale with AI.

Hi, I'm Vo Tu Duc (Danny), a recognised Google Developer Expert (GDE). I architect custom AI agents and Google Workspace solutions that help businesses eliminate chaos and save thousands of hours.

Want to turn these blog concepts into production-ready reality for your team?