Google Looker Studio × Gemini API: Automate Business Report Analysis with AI

What Is Google Looker Studio × Gemini API?

Business decisions rely on data, and Google Looker Studio (formerly Google Data Studio) is one of the most popular free BI tools for visualizing it. However, charts and tables alone can't tell you why sales dropped last week or what action to take next — that still requires human interpretation.

By connecting Looker Studio to the Gemini API, you can automate that interpretation layer. Using Google Sheets as a data hub, you can send Looker Studio data to Gemini for AI-powered analysis, then write the results back into your report automatically.

Prerequisites

Before you start, make sure you have the following ready.

Google Account (free)

Looker Studio, Google Sheets, and Apps Script all work with a standard Google account. A Google Workspace subscription is not required, though it's recommended for business use.

Gemini API Key

Visit Google AI Studio and click "Create API key." The free tier allows up to 60 requests per minute and 1,500 requests per day (as of April 2026).

Google Sheets as a Data Source

This pipeline uses Google Sheets — the same spreadsheet that Looker Studio already references as a data source. No new infrastructure needed.

Overall Architecture

Google Sheets (data)
    ↓ Apps Script runs on a daily trigger
Gemini API (AI analysis)
    ↓ Writes AI comments back to the sheet
Looker Studio (visualization)
    ↓ Reads the sheet and displays the latest AI comments
Finished business report

Step 1: Prepare Google Sheets and Your Data Source

Start with the Google Sheets file that Looker Studio already uses. Add a new sheet called AI_Analysis where Gemini's output will be stored.

Example Sheet Structure

Sheet 1: Sales_Data
  Column A: Date
  Column B: Revenue
  Column C: Product Category
  Column D: Customer Count

Sheet 2: AI_Analysis
  Column A: Analysis Timestamp
  Column B: Period Covered
  Column C: AI Analysis Comment
  Column D: Recommended Actions

In Looker Studio, add AI_Analysis as an additional data source and display the analysis comment in a text widget on your dashboard.

Step 2: Call the Gemini API from Apps Script

Open your Google Sheets file, go to Extensions → Apps Script, and paste the following code.

// ============================================================
// Gemini API × Looker Studio Auto-Analysis Script
// Replace YOUR_GEMINI_API_KEY with your actual key from AI Studio
// ============================================================
 
const GEMINI_API_KEY = "YOUR_GEMINI_API_KEY"; // ← from Google AI Studio
const GEMINI_MODEL   = "gemini-2.5-flash";
const ANALYSIS_SHEET = "AI_Analysis";
const DATA_SHEET     = "Sales_Data";
 
/**
 * Main function: fetch sales data, analyze with Gemini, write results.
 * Set a time-based trigger to run this daily at 09:00.
 */
function runGeminiAnalysis() {
  const ss   = SpreadsheetApp.getActiveSpreadsheet();
  const data = fetchSalesData(ss);
 
  if (!data || data.length === 0) {
    Logger.log("No data found for analysis.");
    return;
  }
 
  const summary = buildDataSummary(data);
  Logger.log("Summary built: " + summary);
 
  const analysis = callGeminiApi(summary);
  writeAnalysisResult(ss, analysis);
  Logger.log("✅ Analysis complete — results written to sheet.");
}
 
/**
 * Fetch the last 7 days of data from Sales_Data sheet.
 */
function fetchSalesData(ss) {
  const sheet = ss.getSheetByName(DATA_SHEET);
  if (!sheet) throw new Error("Sheet not found: " + DATA_SHEET);
 
  const rows       = sheet.getDataRange().getValues();
  const today      = new Date();
  const oneWeekAgo = new Date(today - 7 * 24 * 60 * 60 * 1000);
 
  return rows.slice(1).filter(row => {
    const dateVal = row[0];
    if (!dateVal) return false;
    const d = dateVal instanceof Date ? dateVal : new Date(dateVal);
    return d >= oneWeekAgo && d <= today;
  });
}
 
/**
 * Convert raw data into a text summary for the Gemini prompt.
 */
function buildDataSummary(data) {
  let totalRevenue   = 0;
  let totalCustomers = 0;
  const catMap       = {};
 
  data.forEach(row => {
    const revenue   = parseFloat(row[1]) || 0;  // Column B: Revenue
    const category  = String(row[2] || "Unknown"); // Column C
    const customers = parseFloat(row[3]) || 0;  // Column D
 
    totalRevenue   += revenue;
    totalCustomers += customers;
    catMap[category] = (catMap[category] || 0) + revenue;
  });
 
  const catLines = Object.entries(catMap)
    .sort((a, b) => b[1] - a[1])
    .map(([cat, r]) => `  - ${cat}: $${r.toLocaleString()}`)
    .join("\n");
 
  return [
    `[Last 7 Days Sales Summary]`,
    `Total Revenue: $${totalRevenue.toLocaleString()}`,
    `Total Customers: ${totalCustomers}`,
    `Revenue by Category:`,
    catLines,
    `Data Rows: ${data.length}`,
  ].join("\n");
}
 
/**
 * Send the data summary to Gemini API and return the analysis text.
 */
function callGeminiApi(summary) {
  const prompt = `
You are a business analyst. Review the sales data below and respond in this format:
 
[DATA]
${summary}
 
[RESPONSE FORMAT]
1. Current Situation (2–3 sentences summarizing key findings)
2. Key Observations (2–3 bullet points)
3. Recommended Actions (1–2 specific, actionable suggestions)
 
Keep your response concise and practical.
  `.trim();
 
  const url     = `https://generativelanguage.googleapis.com/v1beta/models/${GEMINI_MODEL}:generateContent?key=${GEMINI_API_KEY}`;
  const payload = {
    contents: [{ parts: [{ text: prompt }] }],
    generationConfig: {
      temperature: 0.3,       // Low temperature for consistent analysis
      maxOutputTokens: 1024,
    },
  };
 
  const response = UrlFetchApp.fetch(url, {
    method: "POST",
    contentType: "application/json",
    payload: JSON.stringify(payload),
    muteHttpExceptions: true,
  });
 
  const statusCode = response.getResponseCode();
  if (statusCode !== 200) {
    throw new Error(`Gemini API error (HTTP ${statusCode}): ${response.getContentText()}`);
  }
 
  const result = JSON.parse(response.getContentText());
  const text   = result?.candidates?.[0]?.content?.parts?.[0]?.text;
  if (!text) throw new Error("No text returned from Gemini API.");
 
  return text;
}
 
/**
 * Write the analysis result to the AI_Analysis sheet.
 */
function writeAnalysisResult(ss, analysisText) {
  let sheet = ss.getSheetByName(ANALYSIS_SHEET);
  if (!sheet) {
    sheet = ss.insertSheet(ANALYSIS_SHEET);
    sheet.appendRow(["Timestamp", "Period", "AI Analysis", "Recommended Actions"]);
  }
 
  const now    = new Date();
  const start  = new Date(now - 7 * 24 * 60 * 60 * 1000);
  const period = `${formatDate(start)} – ${formatDate(now)}`;
 
  const lines       = analysisText.split("\n").filter(l => l.trim());
  const mainComment = lines.slice(0, Math.ceil(lines.length * 0.7)).join("\n");
  const actionPart  = lines.slice(Math.ceil(lines.length * 0.7)).join("\n");
 
  sheet.appendRow([now, period, mainComment, actionPart]);
}
 
/** Format a date as YYYY-MM-DD */
function formatDate(d) {
  return Utilities.formatDate(d, "America/New_York", "yyyy-MM-dd");
}

Key Points

Replace YOUR_GEMINI_API_KEY with the key from AI Studio. The script uses gemini-2.5-flash for fast, cost-efficient analysis. Setting temperature: 0.3 produces consistent, focused analysis rather than creative responses.

Step 3: Set Up a Daily Trigger

To automate daily execution, click the clock icon in the Apps Script editor and create a trigger:

• Function to run: runGeminiAnalysis
• Event source: Time-driven
• Type: Day timer
• Time: 9am – 10am

The script will now run every morning, pulling fresh data and writing AI analysis to the sheet automatically.

Step 4: Display AI Comments in Looker Studio

In Looker Studio, click Add data and connect the same Google Sheets file using the AI_Analysis tab as the source.

Add a Text widget to your canvas and bind it to the AI Analysis field. Sort by the Timestamp column in descending order and limit display to one row so the most recent comment always appears.

Dashboard Design Tip

Place the AI analysis panel directly beside your main charts. When sharing weekly or monthly reports with stakeholders, having AI-generated interpretation alongside the graphs significantly reduces the time spent explaining the data.

Common Errors and Fixes

Here are the most frequent issues and how to resolve them.

Error 1: QUOTA_EXCEEDED

If you hit the rate limit on the free tier (60 requests per minute), add Utilities.sleep(2000) before the API call to introduce a delay. For large datasets, split your data into smaller batches.

Error 2: "The caller does not have permission"

This almost always means an issue with the API key. Check for leading or trailing spaces in the key string. For better security, store the key in Script Properties (Project Settings → Script Properties) rather than hardcoding it.

Error 3: Looker Studio data not refreshing

Looker Studio caches data from Google Sheets. Click the Refresh data button in the top-right corner of your report, or adjust the data source's cache refresh interval in the connector settings.

Looking back

Connecting Google Looker Studio with the Gemini API makes it possible to add AI-generated interpretation to your business reports — automatically, every day.

Here's what we covered:

• A simple pipeline: Apps Script fetches Sheets data → Gemini API analyzes it → results written back to Sheets
• Fully automated with a daily time-based trigger
• Works with Looker Studio's existing Sheets connector — no plugins needed
• gemini-2.5-flash provides fast, cost-efficient analysis at scale

This same architecture can power automated Google Analytics interpretation, inventory report suggestions, or customer behavior segmentation. To take your Workspace automation further, explore the Gemini Apps Script Cross-Product Automation Guide.