Google Forms × Gemini API: Automated Response Analysis — Sentiment Analysis, Theme Extraction & Report Generation

// Run this function once in the Apps Script editor to store your key
function setApiKey() {
  PropertiesService.getScriptProperties().setProperty(
    'GEMINI_API_KEY',
    'YOUR_GEMINI_API_KEY'  // Replace with your actual key
  );
  Logger.log('API key saved successfully');
}

// ============================================================
// Google Forms × Gemini API Response Analysis System
// ============================================================
 
const GEMINI_API_KEY = PropertiesService.getScriptProperties()
  .getProperty('GEMINI_API_KEY');
const GEMINI_API_URL = 
  'https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent';
 
// Configure sheet names and target columns
const CONFIG = {
  sourceSheetName: 'Form Responses 1',  // Default name created by Forms
  analysisSheetName: 'Analysis Results',
  targetColumns: [3, 4, 5],  // Column numbers (1-indexed) for open-ended fields
  batchSize: 10,  // Number of responses to send to Gemini at once
};
 
/**
 * Main function — processes unanalyzed responses
 */
function analyzeFormResponses() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName(CONFIG.sourceSheetName);
  const analysisSheet = getOrCreateAnalysisSheet(ss);
  
  const lastSourceRow = sourceSheet.getLastRow();
  const lastAnalysisRow = analysisSheet.getLastRow();
  const startRow = Math.max(2, lastAnalysisRow + 1); // Skip header
  
  if (startRow > lastSourceRow) {
    Logger.log('No new responses to analyze');
    return;
  }
  
  Logger.log(`Analyzing rows ${startRow} to ${lastSourceRow}`);
  
  // Process in batches
  for (let row = startRow; row <= lastSourceRow; row += CONFIG.batchSize) {
    const endRow = Math.min(row + CONFIG.batchSize - 1, lastSourceRow);
    const batch = [];
    
    for (let r = row; r <= endRow; r++) {
      const texts = CONFIG.targetColumns.map(col => 
        sourceSheet.getRange(r, col).getValue()
      ).filter(text => text && text.toString().trim().length > 0);
      
      if (texts.length > 0) {
        batch.push({ row: r, texts: texts });
      }
    }
    
    if (batch.length > 0) {
      const results = analyzeBatch(batch);
      writeAnalysisResults(analysisSheet, results);
    }
    
    // Respect API rate limits
    Utilities.sleep(1000);
  }
  
  Logger.log('Analysis complete!');
}
 
/**
 * Send a batch of responses to Gemini for analysis
 */
function analyzeBatch(batch) {
  const prompt = buildAnalysisPrompt(batch);
  
  const payload = {
    contents: [{
      parts: [{ text: prompt }]
    }],
    generationConfig: {
      temperature: 0.1,  // Low temperature for consistent analysis
      responseMimeType: 'application/json',
    }
  };
  
  const options = {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
    muteHttpExceptions: true,
  };
  
  const url = `${GEMINI_API_URL}?key=${GEMINI_API_KEY}`;
  const response = UrlFetchApp.fetch(url, options);
  
  if (response.getResponseCode() !== 200) {
    Logger.log('API error: ' + response.getContentText());
    return batch.map(b => ({ row: b.row, error: 'API Error' }));
  }
  
  const responseData = JSON.parse(response.getContentText());
  const resultText = responseData.candidates[0].content.parts[0].text;
  
  try {
    return JSON.parse(resultText);
  } catch (e) {
    Logger.log('JSON parse error: ' + resultText);
    return batch.map(b => ({ row: b.row, error: 'Parse Error' }));
  }
}
 
/**
 * Build the analysis prompt for a batch of responses
 */
function buildAnalysisPrompt(batch) {
  const responses = batch.map((b, i) => 
    `Response ID: ${b.row}\nContent: ${b.texts.join(' / ')}`
  ).join('\n\n');
  
  return `
Analyze the following open-ended survey responses and return a JSON object.
 
${responses}
 
Return results in this exact format:
{
  "results": [
    {
      "row": <response ID as number>,
      "sentiment": "Positive" or "Negative" or "Neutral",
      "sentiment_score": <number from -1.0 to 1.0>,
      "main_themes": ["theme1", "theme2"],
      "key_insight": "The most important insight from this response (under 100 characters)",
      "action_suggestion": "A specific, actionable improvement suggestion (under 100 characters)"
    }
  ]
}
 
Guidelines:
- Base sentiment on the overall tone of the full response
- main_themes should capture key concepts (maximum 3)
- key_insight should be concrete and immediately useful
- action_suggestion should describe something a team could realistically act on
`;
}
 
/**
 * Write analysis results back to the spreadsheet
 */
function writeAnalysisResults(sheet, results) {
  if (!results.results) return;
  
  results.results.forEach(result => {
    if (result.error) return;
    
    const outputRow = sheet.getLastRow() + 1;
    
    sheet.getRange(outputRow, 1).setValue(result.row);
    sheet.getRange(outputRow, 2).setValue(result.sentiment);
    sheet.getRange(outputRow, 3).setValue(result.sentiment_score);
    sheet.getRange(outputRow, 4).setValue(
      Array.isArray(result.main_themes) ? result.main_themes.join(', ') : ''
    );
    sheet.getRange(outputRow, 5).setValue(result.key_insight);
    sheet.getRange(outputRow, 6).setValue(result.action_suggestion);
    sheet.getRange(outputRow, 7).setValue(new Date());
    
    // Color-code rows by sentiment
    const color = result.sentiment === 'Positive' ? '#e8f5e9' :
                  result.sentiment === 'Negative' ? '#ffebee' : '#f5f5f5';
    sheet.getRange(outputRow, 1, 1, 7).setBackground(color);
  });
  
  SpreadsheetApp.flush();
}
 
/**
 * Get or create the analysis results sheet
 */
function getOrCreateAnalysisSheet(ss) {
  let sheet = ss.getSheetByName(CONFIG.analysisSheetName);
  
  if (!sheet) {
    sheet = ss.insertSheet(CONFIG.analysisSheetName);
    const headers = [
      'Source Row', 'Sentiment', 'Score', 'Themes',
      'Key Insight', 'Action Suggestion', 'Analyzed At'
    ];
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    sheet.getRange(1, 1, 1, headers.length)
      .setBackground('#1a73e8')
      .setFontColor('#ffffff')
      .setFontWeight('bold');
    sheet.setFrozenRows(1);
  }
  
  return sheet;
}

/**
 * Generate a summary report from all analysis results
 */
function generateSummaryReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const analysisSheet = ss.getSheetByName(CONFIG.analysisSheetName);
  
  if (!analysisSheet || analysisSheet.getLastRow() < 2) {
    Logger.log('Insufficient analysis data');
    return;
  }
  
  const data = analysisSheet.getDataRange().getValues();
  const rows = data.slice(1);
  
  const total = rows.length;
  const sentimentCounts = { 'Positive': 0, 'Negative': 0, 'Neutral': 0 };
  const allThemes = [];
  const allInsights = [];
  
  rows.forEach(row => {
    const sentiment = row[1];
    if (sentimentCounts.hasOwnProperty(sentiment)) sentimentCounts[sentiment]++;
    if (row[3]) allThemes.push(...row[3].split(', '));
    if (row[4]) allInsights.push(row[4]);
  });
  
  const themeCounts = {};
  allThemes.forEach(t => { themeCounts[t] = (themeCounts[t] || 0) + 1; });
  const topThemes = Object.entries(themeCounts)
    .sort((a, b) => b[1] - a[1])
    .slice(0, 10)
    .map(([theme, count]) => `${theme} (${count})`)
    .join(', ');
  
  const summaryPrompt = `
You are a business analyst summarizing survey data for executives and team leads.
 
Data:
- Total responses: ${total}
- Sentiment distribution: Positive ${sentimentCounts['Positive']} (${Math.round(sentimentCounts['Positive']/total*100)}%), Negative ${sentimentCounts['Negative']} (${Math.round(sentimentCounts['Negative']/total*100)}%), Neutral ${sentimentCounts['Neutral']} (${Math.round(sentimentCounts['Neutral']/total*100)}%)
- Top themes: ${topThemes}
- Sample insights (up to 20): ${allInsights.slice(0, 20).join(' | ')}
 
Write a concise, actionable summary report with these sections:
1. Executive Summary (3 sentences)
2. Key Findings (3–5 bullet points)
3. Top Issues from Negative Responses (3 specific items)
4. Recommended Actions (3 concrete, implementable suggestions)
 
Write for a decision-maker who needs to act quickly. Be specific, not generic.
`;
  
  const summaryResult = callGeminiAPI(summaryPrompt);
  
  let summarySheet = ss.getSheetByName('Summary Report');
  if (!summarySheet) {
    summarySheet = ss.insertSheet('Summary Report');
  } else {
    summarySheet.clear();
  }
  
  const timestamp = Utilities.formatDate(new Date(), 'GMT', "MMMM d, yyyy 'at' HH:mm 'UTC'");
  summarySheet.getRange('A1').setValue(`Survey Analysis Report — ${timestamp}`);
  summarySheet.getRange('A1').setFontSize(16).setFontWeight('bold');
  summarySheet.getRange('A2').setValue(summaryResult);
  summarySheet.getRange('A2').setWrap(true);
  summarySheet.setColumnWidth(1, 600);
  
  return summaryResult;
}
 
/**
 * Utility: Call Gemini API with a text prompt
 */
function callGeminiAPI(prompt) {
  const payload = {
    contents: [{ parts: [{ text: prompt }] }],
    generationConfig: { temperature: 0.3 }
  };
  
  const options = {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
    muteHttpExceptions: true,
  };
  
  const response = UrlFetchApp.fetch(
    `${GEMINI_API_URL}?key=${GEMINI_API_KEY}`, options
  );
  
  if (response.getResponseCode() !== 200) {
    throw new Error('Gemini API Error: ' + response.getResponseCode());
  }
  
  const data = JSON.parse(response.getContentText());
  return data.candidates[0].content.parts[0].text;
}

/**
 * Email the summary report to stakeholders
 */
function sendReportEmail() {
  const summary = generateSummaryReport();
  
  const recipients = ['manager@example.com', 'team@example.com'];
  const subject = `[Gemini Analysis] Survey Report — ${
    Utilities.formatDate(new Date(), 'GMT', 'MMMM d, yyyy')
  }`;
  
  const htmlBody = `
    <div style="font-family: Arial, sans-serif; max-width: 700px; margin: 0 auto;">
      <h2 style="color: #1a73e8; border-bottom: 2px solid #1a73e8; padding-bottom: 8px;">
        Survey Analysis Report (Gemini AI)
      </h2>
      <div style="background: #f8f9fa; padding: 16px; border-radius: 8px; white-space: pre-wrap;">
        ${summary.replace(/\n/g, '<br>')}
      </div>
      <p style="color: #666; font-size: 12px; margin-top: 16px;">
        This report was generated automatically using the Gemini API.
        View the full dataset in your 
        <a href="${SpreadsheetApp.getActiveSpreadsheet().getUrl()}">Google Sheet</a>.
      </p>
    </div>
  `;
  
  recipients.forEach(recipient => {
    GmailApp.sendEmail(recipient, subject, summary, {
      htmlBody: htmlBody,
      name: 'Gemini Analysis System'
    });
  });
  
  Logger.log(`Report sent to: ${recipients.join(', ')}`);
}

pip install google-generativeai google-auth google-auth-oauthlib \
            google-auth-httplib2 google-api-python-client \
            pandas openpyxl python-dotenv

"""
Google Forms × Gemini API Response Analysis System (Python)
"""
import os
import json
import time
from datetime import datetime
import pandas as pd
from dotenv import load_dotenv
import google.generativeai as genai
from googleapiclient.discovery import build
from google.oauth2.service_account import Credentials
 
load_dotenv()
 
GEMINI_API_KEY = os.getenv('GEMINI_API_KEY')
SPREADSHEET_ID = os.getenv('SPREADSHEET_ID')
SERVICE_ACCOUNT_FILE = 'service_account.json'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
 
genai.configure(api_key=GEMINI_API_KEY)
model = genai.GenerativeModel('gemini-2.5-flash')
 
def get_sheets_service():
    """Initialize Google Sheets API client"""
    creds = Credentials.from_service_account_file(
        SERVICE_ACCOUNT_FILE, scopes=SCOPES
    )
    return build('sheets', 'v4', credentials=creds)
 
def fetch_form_responses(service: object, sheet_name: str = 'Form Responses 1') -> pd.DataFrame:
    """Fetch all form responses from Google Sheets"""
    result = service.spreadsheets().values().get(
        spreadsheetId=SPREADSHEET_ID,
        range=f"'{sheet_name}'!A:Z"
    ).execute()
    
    values = result.get('values', [])
    if not values:
        return pd.DataFrame()
    
    df = pd.DataFrame(values[1:], columns=values[0])
    print(f"📊 Fetched {len(df)} responses")
    return df
 
def analyze_with_gemini(texts: list[str]) -> dict:
    """Analyze a list of response texts with Gemini"""
    if not texts:
        return {}
    
    combined = ' / '.join([t for t in texts if t.strip()])
    
    prompt = f"""
Analyze this survey response and return a JSON object only (no markdown).
 
Response: {combined}
 
Return format:
{{
  "sentiment": "Positive" or "Negative" or "Neutral",
  "sentiment_score": <float from -1.0 to 1.0>,
  "main_themes": ["theme1", "theme2"],
  "key_insight": "Key insight under 100 characters",
  "action_suggestion": "Specific action under 100 characters",
  "urgency": "High" or "Medium" or "Low"
}}
"""
    
    try:
        response = model.generate_content(
            prompt,
            generation_config=genai.GenerationConfig(
                temperature=0.1,
                response_mime_type='application/json'
            )
        )
        return json.loads(response.text)
    except Exception as e:
        print(f"❌ Analysis error: {e}")
        return {
            'sentiment': 'Neutral', 'sentiment_score': 0.0,
            'main_themes': [], 'key_insight': 'Error during analysis',
            'action_suggestion': 'Manual review required', 'urgency': 'Low'
        }
 
def analyze_all_responses(df: pd.DataFrame, text_columns: list[str]) -> pd.DataFrame:
    """Run Gemini analysis on all responses"""
    results = []
    total = len(df)
    
    for i, (idx, row) in enumerate(df.iterrows()):
        print(f"⏳ Processing: {i+1}/{total}...")
        
        texts = [str(row[col]) for col in text_columns 
                 if col in df.columns and str(row[col]).strip()]
        
        analysis = analyze_with_gemini(texts)
        
        results.append({
            'original_index': idx,
            **{col: row[col] for col in df.columns},
            'sentiment': analysis.get('sentiment', ''),
            'sentiment_score': analysis.get('sentiment_score', 0),
            'main_themes': ', '.join(analysis.get('main_themes', [])),
            'key_insight': analysis.get('key_insight', ''),
            'action_suggestion': analysis.get('action_suggestion', ''),
            'urgency': analysis.get('urgency', 'Low'),
            'analyzed_at': datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        })
        
        time.sleep(1)  # Stay within 60 RPM limit
    
    return pd.DataFrame(results)
 
def generate_summary_report(results_df: pd.DataFrame) -> str:
    """Generate an executive summary report using Gemini"""
    total = len(results_df)
    sentiment_dist = results_df['sentiment'].value_counts().to_dict()
    top_themes = (
        results_df['main_themes']
        .str.split(', ').explode()
        .value_counts().head(10)
    )
    urgent_count = len(results_df[results_df['urgency'] == 'High'])
    insights_sample = results_df['key_insight'].dropna().head(20).tolist()
    
    prompt = f"""
You are a business analyst preparing an executive summary of survey results.
 
Data:
- Total responses: {total}
- Sentiment breakdown: {sentiment_dist}
- High-urgency responses: {urgent_count}
- Top themes: {dict(top_themes)}
- Sample insights: {insights_sample[:10]}
 
Write a concise executive summary with:
1. Three-sentence overview
2. Key findings (3–5 points)
3. Priority issues (from high-urgency responses)
4. Recommended actions (3 concrete suggestions)
 
Be specific and actionable. This goes directly to decision-makers.
"""
    
    response = model.generate_content(prompt)
    return response.text
 
def upload_results_to_sheets(service, results_df: pd.DataFrame):
    """Upload analysis results back to Google Sheets"""
    headers = results_df.columns.tolist()
    values = [headers] + results_df.values.tolist()
    
    service.spreadsheets().values().clear(
        spreadsheetId=SPREADSHEET_ID,
        range='Analysis Results!A:Z'
    ).execute()
    
    service.spreadsheets().values().update(
        spreadsheetId=SPREADSHEET_ID,
        range='Analysis Results!A1',
        valueInputOption='RAW',
        body={'values': [[str(v) for v in row] for row in values]}
    ).execute()
    
    print(f"✅ Uploaded {len(results_df)} results to Google Sheets")
 
def main():
    print("🚀 Google Forms × Gemini API Analysis System Starting")
    
    service = get_sheets_service()
    df = fetch_form_responses(service)
    
    if df.empty:
        print("⚠️ No response data found")
        return
    
    # Set the column names for your open-ended questions
    text_columns = ['What did you like?', 'What could be improved?', 'Other comments']
    
    results_df = analyze_all_responses(df, text_columns)
    
    print("\n📝 Generating summary report...")
    summary = generate_summary_report(results_df)
    print("\n=== Summary Report ===")
    print(summary)
    
    upload_results_to_sheets(service, results_df)
    
    output_file = f"analysis_report_{datetime.now().strftime('%Y%m%d_%H%M')}.xlsx"
    results_df.to_excel(output_file, index=False)
    print(f"💾 Excel report saved: {output_file}")
 
if __name__ == '__main__':
    main()

/**
 * Set up all triggers — run this function once manually
 */
function setupTriggers() {
  // Remove existing triggers first
  ScriptApp.getProjectTriggers().forEach(t => ScriptApp.deleteTrigger(t));
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Analyze in real-time when the spreadsheet changes (new form submission)
  ScriptApp.newTrigger('analyzeFormResponses')
    .forSpreadsheet(ss)
    .onChange()
    .create();
  
  // Send daily email report at 9 AM
  ScriptApp.newTrigger('sendReportEmail')
    .timeBased()
    .everyDays(1)
    .atHour(9)
    .create();
  
  Logger.log('Triggers configured successfully');
}

# Add to crontab (runs at 8 AM daily)
0 8 * * * cd /path/to/project && python3 main.py >> logs/analysis.log 2>&1
 
# Google Cloud Scheduler + Cloud Run
gcloud scheduler jobs create http forms-analysis-job \
  --schedule="0 8 * * *" \
  --uri="https://YOUR_CLOUD_RUN_URL/analyze" \
  --time-zone="America/New_York" \
  --http-method=POST

/**
 * Aggregate analysis results from multiple survey sheets
 * Useful for tracking sentiment trends across time periods
 */
function aggregateMultipleSurveys() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Define all survey sheets to aggregate
  const surveySheets = [
    { name: 'Q1 2026 Analysis', period: '2026-Q1' },
    { name: 'Q2 2026 Analysis', period: '2026-Q2' },
    { name: 'Q3 2026 Analysis', period: '2026-Q3' },
  ];
  
  const aggregatedData = [];
  
  surveySheets.forEach(config => {
    const sheet = ss.getSheetByName(config.name);
    if (!sheet) return;
    
    const data = sheet.getDataRange().getValues();
    const rows = data.slice(1); // Skip header
    
    const periodData = {
      period: config.period,
      total: rows.length,
      avgSentimentScore: 0,
      sentimentBreakdown: { Positive: 0, Negative: 0, Neutral: 0 },
      topThemes: {},
    };
    
    rows.forEach(row => {
      const score = parseFloat(row[2]) || 0;
      periodData.avgSentimentScore += score;
      
      const sentiment = row[1];
      if (periodData.sentimentBreakdown.hasOwnProperty(sentiment)) {
        periodData.sentimentBreakdown[sentiment]++;
      }
      
      if (row[3]) {
        row[3].split(', ').forEach(theme => {
          periodData.topThemes[theme] = (periodData.topThemes[theme] || 0) + 1;
        });
      }
    });
    
    if (rows.length > 0) {
      periodData.avgSentimentScore = 
        (periodData.avgSentimentScore / rows.length).toFixed(3);
    }
    
    aggregatedData.push(periodData);
  });
  
  // Ask Gemini to interpret the trend
  const trendPrompt = `
You are a business analyst interpreting multi-quarter survey trend data.
 
Data across periods:
${JSON.stringify(aggregatedData, null, 2)}
 
Write a trend analysis that covers:
1. Overall sentiment trajectory (improving, declining, or stable?)
2. Which themes are gaining or losing prominence?
3. What does the data suggest about the root causes of any changes?
4. What should the team prioritize in the next quarter based on these trends?
 
Be specific about which numbers support each conclusion.
`;
  
  const trendAnalysis = callGeminiAPI(trendPrompt);
  
  // Write trend analysis to a dedicated sheet
  let trendSheet = ss.getSheetByName('Trend Analysis');
  if (!trendSheet) trendSheet = ss.insertSheet('Trend Analysis');
  else trendSheet.clear();
  
  trendSheet.getRange('A1').setValue('Multi-Period Trend Analysis');
  trendSheet.getRange('A1').setFontSize(16).setFontWeight('bold');
  trendSheet.getRange('A2').setValue(trendAnalysis);
  trendSheet.getRange('A2').setWrap(true);
  trendSheet.setColumnWidth(1, 650);
  
  Logger.log('Trend analysis complete');
  return trendAnalysis;
}

/**
 * Compare sentiment across customer segments
 * Assumes a "Segment" column exists in form responses
 */
function compareSegments(segmentColumnIndex = 2) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName(CONFIG.sourceSheetName);
  const analysisSheet = ss.getSheetByName(CONFIG.analysisSheetName);
  
  if (!analysisSheet) {
    Logger.log('Run analyzeFormResponses first');
    return;
  }
  
  // Get segment data from source sheet
  const sourceData = sourceSheet.getDataRange().getValues();
  const analysisData = analysisSheet.getDataRange().getValues();
  
  // Build a map of row -> segment
  const segmentMap = {};
  sourceData.slice(1).forEach((row, i) => {
    segmentMap[i + 2] = row[segmentColumnIndex - 1]; // Adjust for 0-index
  });
  
  // Group analysis results by segment
  const segmentResults = {};
  analysisData.slice(1).forEach(row => {
    const sourceRow = parseInt(row[0]);
    const segment = segmentMap[sourceRow] || 'Unknown';
    
    if (!segmentResults[segment]) {
      segmentResults[segment] = {
        count: 0, totalScore: 0, themes: {}, insights: []
      };
    }
    
    const group = segmentResults[segment];
    group.count++;
    group.totalScore += parseFloat(row[2]) || 0;
    if (row[3]) {
      row[3].split(', ').forEach(t => { group.themes[t] = (group.themes[t] || 0) + 1; });
    }
    if (row[4]) group.insights.push(row[4]);
  });
  
  // Prepare summary for Gemini
  const segmentSummary = Object.entries(segmentResults).map(([seg, data]) => ({
    segment: seg,
    avgSentimentScore: (data.totalScore / data.count).toFixed(2),
    responseCount: data.count,
    topThemes: Object.entries(data.themes)
      .sort((a, b) => b[1] - a[1]).slice(0, 5).map(([k, v]) => `${k}(${v})`),
    sampleInsights: data.insights.slice(0, 5)
  }));
  
  const comparisonPrompt = `
Compare these customer segments based on survey analysis:
 
${JSON.stringify(segmentSummary, null, 2)}
 
Provide:
1. Which segment has the highest/lowest satisfaction and why?
2. What themes are unique to each segment vs. shared across all?
3. What different actions should the team take for each segment?
4. Are there any surprising differences that warrant deeper investigation?
`;
  
  const comparison = callGeminiAPI(comparisonPrompt);
  Logger.log('Segment comparison:\n' + comparison);
  return comparison;
}

function truncateResponse(text, maxChars = 1500) {
  if (!text || text.length <= maxChars) return text;
  return text.substring(0, maxChars) + '... [truncated]';
}

/**
 * Track cumulative API usage in Script Properties
 */
function trackApiUsage(estimatedTokens) {
  const props = PropertiesService.getScriptProperties();
  const current = parseInt(props.getProperty('totalTokensUsed') || '0');
  props.setProperty('totalTokensUsed', (current + estimatedTokens).toString());
  
  const totalTokens = current + estimatedTokens;
  const estimatedCostUSD = (totalTokens / 1000000) * 0.075; // Flash input price
  
  Logger.log(`Total tokens used this session: ${totalTokens} (~$${estimatedCostUSD.toFixed(4)})`);
}