Transforming Unstructured Text into Actionable Data
Customer feedback, Net Promoter Score (NPS) surveys, and inbound support tickets contain rich qualitative insights, but categorizing thousands of free-text comments manually takes days of repetitive effort. By embedding the Google Gemini API into Google Sheets via a lightweight custom Apps Script function, organizations can classify sentiment, extract key topics, and categorize text automatically in seconds.
Apps Script Custom Function Code
Open your Google Sheet, navigate to Extensions > Apps Script, and paste the following implementation:
/**
* Classifies text using Google Gemini 1.5 Flash
* @param {string} prompt The text to classify
* @param {string} categories Comma-separated list of categories
* @return {string} The assigned category
* @customfunction
*/
function GEMINI_CLASSIFY(text, categories) {
if (!text || !categories) return "";
const API_KEY = "YOUR_GEMINI_API_KEY_HERE";
const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-flash:generateContent?key=${API_KEY}`;
const payload = {
contents: [{
parts: [{
text: `Classify the following text into exactly ONE of these categories: [${categories}]. Respond ONLY with the category name, nothing else.\n\nText: "${text}"`
}]
}],
generationConfig: {
temperature: 0.1,
maxOutputTokens: 20
}
};
const options = {
method: "post",
contentType: "application/json",
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
try {
const response = UrlFetchApp.fetch(url, options);
const json = JSON.parse(response.getContentText());
if (json.candidates && json.candidates[0].content) {
return json.candidates[0].content.parts[0].text.trim();
} else {
return "Error: " + (json.error ? json.error.message : "No response");
}
} catch (err) {
return "Fetch Exception: " + err.message;
}
}
Practical Spreadsheet Usage
In your spreadsheet cell, invoke the custom function like any standard formula:
=GEMINI_CLASSIFY(A2, "Billing, Bug Report, Feature Request, General Praise")
Combine with ARRAYFORMULA or drag the formula down to categorize hundreds of customer comments in real time.
Production Cost & Quota Considerations
Gemini 1.5 Flash provides an extremely generous free tier in Google AI Studio (up to 15 Requests Per Minute). For enterprise sheets with tens of thousands of rows, batch API calls into groups of 50 in Apps Script to avoid hitting per-minute rate limits.
Frequently Asked Questions
Does Google train its models on data sent via the Gemini API?
For paid enterprise accounts on Vertex AI, Google strictly does not use customer prompt data or generated responses to train foundation models. In the free tier of AI Studio, review Google's terms regarding data logging.
How can I make the classification 100% consistent?
Set temperature: 0.0 in your generationConfig to enforce deterministic, repeatable responses across identical inputs.
Handling Batching & API Rate Limits with Exponential Backoff
When running machine learning classification across thousands of rows in Google Sheets, scripts can quickly encounter Gemini API rate limits (such as 15 RPM for free tiers or quota ceilings on Pay-As-You-Go). To prevent script timeouts, implement Exponential Backoff Retry Loops in your Apps Script:
function callGeminiWithRetry(prompt, maxRetries = 3) {
let delay = 1000;
for (let i = 0; i < maxRetries; i++) {
try {
const response = UrlFetchApp.fetch(API_URL, options);
if (response.getResponseCode() === 200) return JSON.parse(response.getContentText());
} catch (e) {
Utilities.sleep(delay);
delay *= 2;
}
}
throw new Error('Gemini API quota exceeded after retries.');
}
This architectural pattern ensures robust, error-free classification pipelines that run continuously without manual supervision.