Integrating Gemini AI into Google Sheets: Automating Sentiment Analysis and Data Categorization

Introduction

Spreadsheets are frequently used to aggregate qualitative data: survey responses, customer review comments, support tickets, and sales notes. Categorizing thousands of unstructured text rows manually requires days of tedious labor. By writing a custom Google Sheets formula powered by Google's Gemini 2.5 Flash API, teams can execute automated sentiment scoring, translation, and categorical classification directly inside cell formulas.

Setting Up Google AI Studio API Key in Apps Script

1. Obtain an API key from Google AI Studio.
2. Open your Google Sheet and navigate to Extensions > Apps Script.
3. Click Project Settings (gear icon) > Script Properties > Add script property.
4. Property name: GEMINI_API_KEY, Value: your API key string. Storing the key in Script Properties prevents exposing credentials in public sheet formulas.

The Custom Formula: =GEMINI(prompt, [cell_reference])

Implement the following resilient custom Apps Script function:

/**
 * Custom Google Sheets formula to call Google Gemini API
 * @param {string} prompt The instruction or task to perform.
 * @param {string} text The cell input text to analyze.
 * @return {string} Generated AI response.
 * @customfunction
 */
function GEMINI(prompt, text) {
  if (!prompt) return "Error: Missing prompt.";
  const input = text ? prompt + "\n\nInput: " + text : prompt;
  
  const apiKey = PropertiesService.getScriptProperties().getProperty("GEMINI_API_KEY");
  if (!apiKey) return "Error: GEMINI_API_KEY property missing.";
  
  const url = "https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent?key=" + apiKey;
  
  const payload = {
    contents: [{ parts: [{ text: input }] }],
    generationConfig: {
      temperature: 0.1,
      maxOutputTokens: 250
    }
  };
  
  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 "Network Exception: " + err.message;
  }
}

Real-World Formula Recipes

1. Customer Feedback Sentiment Scoring

=GEMINI("Classify the sentiment of this review as POSITIVE, NEUTRAL, or NEGATIVE. Return ONLY the single classification word.", A2)

2. Support Ticket Categorization

=GEMINI("Classify this IT ticket into one category: [Hardware, Software, Network, Billing, Security]. Return only the category name.", B2)

3. Multilingual Translation & Standardization

=GEMINI("Translate this feedback into professional English and correct grammar errors.", C2)

Caching & Rate-Limiting Caution

Google Sheets re-evaluates custom formulas periodically. For large datasets of 500+ rows, running =GEMINI() in live cells will exceed Apps Script's 20,000 daily UrlFetch quota. For production datasets, build a batch processing script with a custom button that iterates through rows once and writes static text values back into the sheet.