Github https://github.com/lsvekis/Google-Apps-Script-APIs-and-Gemini
In Lessons 1 and 2, you learned how to:
– Call public APIs using Apps Script
– Read text from a Google Drive folder
– Combine both sources into a Gemini prompt
– Build a full UI web app
Now it’s time to level up.
In Lesson 3, we introduce:
– Chunking (splitting documents into manageable pieces)
– Embeddings using Gemini
– Cosine similarity
– Retrieval-Augmented Generation (RAG)
– Using cached embeddings for speed
This allows your assistant to search your Drive folder intelligently, returning only the most relevant parts to Gemini — resulting in better, faster answers.
🎯 What You Will Build
By the end of Lesson 3, your assistant will:
– Read all text from a Drive folder
– Break each file into chunks
– Convert each chunk into a vector embedding
– Compute similarity between your question and each chunk
– Retrieve only the top-K most relevant chunks
– Send only those chunks to Gemini
– Produce a precise, grounded answer
This is the same technique used in professional RAG systems such as:
– ChatGPT Retrieval
– Gemini File Bench
– LLM-powered search engines
You’ll now have your own miniature version — in pure Google Apps Script.
🧠 Part 1 — Why Use Embeddings?
Before today, your assistant worked like this:
“Dump the entire folder text into one giant prompt.”
This works — but:
– Large folders exceed prompt limits
– Gemini must analyze irrelevant text
– Answers may be vague or slow
Embeddings solve this.
When you create an embedding, Gemini converts your text into a vector (a list of numbers) that represents its meaning.
Then you can compare embeddings using cosine similarity, which tells you how related two pieces of text are.
Think of it as:
“Find the most relevant paragraphs before asking Gemini.”
This is dramatically faster and more accurate.
🧩 Part 2 — Overall Flow (Diagram)
Here’s how the pipeline works:
┌─────────────────────────┐
│ Drive Folder Files │
└────────────┬────────────┘
│
▼
┌────────────────────┐
│ Chunk the text │
└─────────┬──────────┘
│ each chunk
▼
┌────────────────────┐
│ Embed each chunk │───► Stored in cache / sheet
└─────────┬──────────┘
│
User Question │
│ ▼
└────► Embed question vector
│
▼
┌─────────────────────┐
│ Compare similarity │
│ (cosine similarity) │
└─────────┬───────────┘
│ top K chunks
▼
┌─────────────────────────┐
│ Build Gemini prompt │
└────────────┬────────────┘
│
▼
Gemini API
│
▼
┌────────────────┐
│ Final Answer │
└────────────────┘
This is real RAG — Retrieval-Augmented Generation — done entirely with Apps Script.
🔧 Part 3 — Full Lesson 3 Code (Apps Script)
Create a new file:
Lesson3_RAG.gs
Paste this clean and optimized version:
/**
* Lesson 3 — Add Embeddings + Retrieval (RAG)
*
* What this file includes:
* – Chunking text
* – Creating embeddings for each chunk
* – Cosine similarity
* – Retrieving top-K relevant chunks
* – Sending ONLY relevant chunks to Gemini
*
* NOTE: Insert your folder ID in DEFAULT_FOLDER_ID.
*/
/***************************************
* CONFIG
***************************************/
const DEFAULT_FOLDER_ID = ‘PASTE_YOUR_FOLDER_ID_HERE’;
const EMBEDDING_MODEL = ‘models/gemini-embedding-001’;
const GENERATION_MODEL = ‘gemini-2.5-flash’;
const CHUNK_SIZE = 1200; // characters per chunk
const TOP_K = 5; // how many chunks to return
const MAX_OUTPUT_TOKENS = 1024;
/***************************************
* MAIN ENTRY
***************************************/
function answerWithRag(question) {
question = question.trim();
if (!question) {
throw new Error(‘Question cannot be empty.’);
}
// 1. Read & chunk the folder contents
const chunks = getFolderChunks_(DEFAULT_FOLDER_ID);
if (chunks.length === 0) {
return ‘No readable files found in the folder.’;
}
// 2. Embed all chunks (with caching)
const chunkEmbeddings = chunks.map(c => ({
chunk: c,
embedding: embedTextCached_(c.text)
}));
// 3. Embed the user’s question
const queryEmbedding = embedText_(question);
// 4. Compute cosine similarity
const ranked = chunkEmbeddings
.map(item => ({
chunk: item.chunk,
score: cosineSimilarity_(queryEmbedding, item.embedding)
}))
.sort((a, b) => b.score – a.score);
// 5. Take top-K chunks
const selected = ranked.slice(0, TOP_K);
// 6. Build the context payload
const context = selected
.map((c, i) =>
`[#${i + 1} — ${c.chunk.fileName} — score=${c.score.toFixed(3)}]\n${c.chunk.text}`
)
.join(‘\n\n’);
const prompt =
‘You are an AI assistant using retrieval. Use ONLY the information below:\n\n’ +
‘— Retrieved Chunks —\n’ +
context +
‘\n— End Chunks —\n\n’ +
‘Question: ‘ + question + ‘\n’ +
‘If the answer is not contained in the retrieved text, say you do not know.’;
// 7. Generate final answer using Gemini
return callGeminiText_(prompt);
}
/***************************************
* STEP 1 — Read + Chunk Drive Files
***************************************/
function getFolderChunks_(folderId) {
const folder = DriveApp.getFolderById(folderId);
const files = folder.getFiles();
const chunks = [];
while (files.hasNext()) {
const file = files.next();
let text = ”;
if (file.getMimeType() === MimeType.GOOGLE_DOCS) {
text = DocumentApp.openById(file.getId()).getBody().getText();
} else if (file.getMimeType() === MimeType.PLAIN_TEXT) {
text = file.getBlob().getDataAsString();
} else {
continue;
}
text = text.trim();
if (!text) continue;
// split into fixed-size chunks
let start = 0;
let index = 0;
while (start < text.length) {
const end = Math.min(start + CHUNK_SIZE, text.length);
chunks.push({
id: file.getId() + ‘:’ + index,
fileName: file.getName(),
text: text.substring(start, end)
});
start = end;
index++;
}
}
return chunks;
}
/***************************************
* STEP 2 — Embeddings
***************************************/
function embedText_(text) {
const apiKey = getGeminiKey_();
const url = `https://generativelanguage.googleapis.com/v1beta/${EMBEDDING_MODEL}:embedContent?key=${apiKey}`;
const payload = {
model: EMBEDDING_MODEL,
content: { parts: [{ text }] },
taskType: ‘RETRIEVAL_DOCUMENT’
};
const res = UrlFetchApp.fetch(url, {
method: ‘post’,
contentType: ‘application/json’,
payload: JSON.stringify(payload)
});
const data = JSON.parse(res.getContentText());
return data.embedding?.values || data.embeddings?.[0]?.values;
}
/**
* Cached version — stores embeddings in script cache.
* Speeds up repeated queries by avoiding recomputation.
*/
function embedTextCached_(text) {
const cache = CacheService.getScriptCache();
const key = ’embed_’ + Utilities.base64Encode(text).slice(0, 200);
const cached = cache.get(key);
if (cached) return JSON.parse(cached);
const embedding = embedText_(text);
cache.put(key, JSON.stringify(embedding), 21600); // cache 6 hours
return embedding;
}
/***************************************
* STEP 3 — Cosine Similarity
***************************************/
function cosineSimilarity_(a, b) {
let dot = 0,
magA = 0,
magB = 0;
for (let i = 0; i < a.length; i++) {
dot += a[i] * b[i];
magA += a[i] * a[i];
magB += b[i] * b[i];
}
return dot / (Math.sqrt(magA) * Math.sqrt(magB));
}
/***************************************
* STEP 4 — Gemini Text Call
***************************************/
function callGeminiText_(prompt) {
const apiKey = getGeminiKey_();
const url = `https://generativelanguage.googleapis.com/v1beta/models/${GENERATION_MODEL}:generateContent?key=${apiKey}`;
const payload = {
contents: [{ role: ‘user’, parts: [{ text: prompt }] }],
generationConfig: { maxOutputTokens: MAX_OUTPUT_TOKENS }
};
const res = UrlFetchApp.fetch(url, {
method: ‘post’,
contentType: ‘application/json’,
payload: JSON.stringify(payload)
});
const data = JSON.parse(res.getContentText());
return data.candidates?.[0]?.content?.parts?.[0]?.text || ”;
}
/***************************************
* UTIL — API KEY
***************************************/
function getGeminiKey_() {
return PropertiesService.getScriptProperties().getProperty(
‘GEMINI_API_KEY’
);
}
💬 How to Use Lesson 3
Run this in your Apps Script console:
Logger.log(answerWithRag(“What does the folder say about onboarding?”));
The script will:
– Read ALL Drive files
– Break them into chunks
– Embed each chunk
– Compare embeddings to your question
– Retrieve only the top-K
– Ask Gemini to answer using ONLY those chunks
This is a complete retrieval pipeline.
🧪 Practice Exercises for The Learner
– Change CHUNK_SIZE
– Try 500, 1200, 2000
– What happens to accuracy?
– Make TOP_K adjustable
– Add query parameters like:
answerWithRag(question, topK)
– Add PDF extraction
– Convert PDF → Google Doc → text
– Chunk it the same way
– Cache the entire chunk list
– Store chunk/embedding arrays in CacheService
– Dramatically reduces Drive reading time
