Gemini Workspace AI Expanded Access: Advanced Automation Guide 2026

/**
 * Advanced Sales Pipeline Analysis with Gemini 3 Pro
 * Triggered daily to evaluate all open deals
 */
 
class SalesPipelineAnalyzer {
  constructor() {
    this.ss = SpreadsheetApp.getActiveSpreadsheet();
    this.dealsSheet = this.ss.getSheetByName("Sales Pipeline");
    this.resultsSheet = this.ss.getSheetByName("AI Analysis");
  }
 
  run() {
    // Step 1: Extract complex deals
    const deals = this.extractDeals();
    const complexDeals = deals.filter(d => this.isComplex(d));
 
    Logger.log(`Found ${complexDeals.length} complex deals for analysis`);
 
    // Step 2: Analyze each deal with Gemini 3 Pro
    const analyses = complexDeals.map(deal => {
      const analysis = this.analyzeWithGemini(deal);
      return {
        dealId: deal.id,
        dealName: deal.company,
        analysis: analysis,
        timestamp: new Date().toISOString()
      };
    });
 
    // Step 3: Log results to Sheets
    this.logResults(analyses);
 
    // Step 4: Alert on high-risk deals
    this.alertHighRisk(analyses);
  }
 
  extractDeals() {
    const data = this.dealsSheet.getDataRange().getValues();
    const headers = data[0];
    const deals = [];
 
    for (let i = 1; i < data.length; i++) {
      const obj = {};
      headers.forEach((h, idx) => {
        obj[h] = data[i][idx];
      });
      if (obj.Status !== "LOST" && obj.Status !== "WON") {
        deals.push(obj);
      }
    }
 
    return deals;
  }
 
  isComplex(deal) {
    const amountThreshold = 100000;
    const daysThreshold = 60;
    const daysOpen = Math.floor((Date.now() - deal.Created) / (1000 * 60 * 60 * 24));
 
    return deal.Amount > amountThreshold || daysOpen > daysThreshold;
  }
 
  analyzeWithGemini(deal) {
    const prompt = `
You are an expert sales director. Analyze this complex deal:
 
DEAL SUMMARY:
- Prospect: ${deal.company}
- Amount: $${deal.Amount.toLocaleString()}
- Stage: ${deal.Stage}
- Days Open: ${Math.floor((Date.now() - deal.Created) / (1000 * 60 * 60 * 24))}
- Last Activity: ${deal.LastActivity}
- Key Stakeholders: ${deal.Stakeholders}
- Competitive Situation: ${deal.Competition}
 
ANALYSIS REQUIRED:
1. Close probability (0-100%)
2. Top 3 risk factors (with mitigation)
3. Immediate next steps (max 5)
4. Recommended closing strategy
 
Respond in JSON format:
{
  "close_probability": number,
  "risk_level": "LOW" | "MEDIUM" | "HIGH",
  "risk_factors": [{ factor: string, mitigation: string }],
  "next_steps": [string],
  "recommended_strategy": string
}
`;
 
    try {
      const response = this.callGemini3Pro(prompt, {
        temperature: 0.3,  // Lower temp for analytical consistency
        max_tokens: 1500
      });
 
      // Try to parse JSON response
      const jsonMatch = response.match(/\{[\s\S]*\}/);
      if (jsonMatch) {
        return JSON.parse(jsonMatch[0]);
      }
 
      // Fallback: return raw response
      return {
        close_probability: 0,
        risk_level: "UNKNOWN",
        analysis_text: response
      };
    } catch (e) {
      Logger.log(`Gemini error for deal ${deal.id}: ${e.message}`);
      return {
        close_probability: 0,
        risk_level: "ERROR",
        error: e.message
      };
    }
  }
 
  callGemini3Pro(prompt, options = {}) {
    const apiKey = PropertiesService.getScriptProperties()
      .getProperty("GEMINI_API_KEY");
 
    if (!apiKey) {
      throw new Error("GEMINI_API_KEY not found in Script Properties");
    }
 
    const payload = {
      contents: [{
        parts: [{
          text: prompt
        }]
      }],
      generationConfig: {
        temperature: options.temperature || 0.7,
        maxOutputTokens: options.max_tokens || 2000
      }
    };
 
    const requestOptions = {
      method: "post",
      contentType: "application/json",
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    };
 
    const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-3-pro:generateContent?key=${apiKey}`;
 
    const response = UrlFetchApp.fetch(url, requestOptions);
    const result = JSON.parse(response.getContentText());
 
    if (result.candidates?.[0]?.content?.parts?.[0]?.text) {
      return result.candidates[0].content.parts[0].text;
    }
 
    if (response.getResponseCode() !== 200) {
      throw new Error(`API error: ${response.getContentText()}`);
    }
 
    throw new Error("No response text from Gemini");
  }
 
  logResults(analyses) {
    const resultsData = analyses.map(a => [
      a.dealId,
      a.dealName,
      a.analysis.close_probability,
      a.analysis.risk_level,
      a.analysis.risk_factors?.map(f => f.factor).join("; "),
      a.analysis.next_steps?.join("; "),
      a.timestamp
    ]);
 
    this.resultsSheet.getRange(2, 1, resultsData.length, 7)
      .setValues(resultsData);
  }
 
  alertHighRisk(analyses) {
    const highRisk = analyses.filter(a => a.analysis.risk_level === "HIGH");
 
    if (highRisk.length > 0) {
      const recipients = PropertiesService.getScriptProperties()
        .getProperty("ALERT_EMAILS").split(",");
 
      const message = `High-risk deals detected (${highRisk.length}):\n\n` +
        highRisk.map(a =>
          `${a.dealName}: ${a.analysis.risk_level} risk, ${a.analysis.close_probability}% close prob`
        ).join("\n");
 
      recipients.forEach(email => {
        GmailApp.sendEmail(email, "Sales Pipeline Alert", message);
      });
    }
  }
}
 
// Trigger: Daily at 8am
function scheduledPipelineAnalysis() {
  const analyzer = new SalesPipelineAnalyzer();
  analyzer.run();
}

/**
 * Automated Customer Success Reporting Pipeline
 * Runs weekly; generates Docs report + Slides deck + Email distribution
 */
 
class CustomerSuccessPipeline {
  constructor() {
    this.driveFolder = DriveApp.getFolderById(
      PropertiesService.getScriptProperties().getProperty("REPORTS_FOLDER_ID")
    );
    this.dataSheet = SpreadsheetApp.getSheetByName("Customer Metrics");
    this.docTemplate = DocumentApp.openById(
      PropertiesService.getScriptProperties().getProperty("DOC_TEMPLATE_ID")
    );
  }
 
  run() {
    // Step 1: Extract and aggregate customer data
    const customers = this.getCustomerMetrics();
    const aggregatedData = this.aggregateMetrics(customers);
 
    // Step 2: Analyze with Gemini 3 Pro
    const analysis = this.performAnalysis(aggregatedData);
 
    // Step 3: Generate Docs report
    const docUrl = this.generateDocReport(analysis);
 
    // Step 4: Create Slides presentation
    const slidesUrl = this.generateSlidesDeck(analysis);
 
    // Step 5: Send distribution
    this.sendDistribution(docUrl, slidesUrl, analysis);
  }
 
  getCustomerMetrics() {
    const data = this.dataSheet.getDataRange().getValues();
    const headers = data[0];
 
    return data.slice(1).map(row => {
      const obj = {};
      headers.forEach((h, i) => obj[h] = row[i]);
      return obj;
    }).filter(c => c.Status !== "INACTIVE");
  }
 
  aggregateMetrics(customers) {
    // Group by segment
    const bySegment = {};
 
    customers.forEach(c => {
      const seg = c.Segment;
      if (!bySegment[seg]) bySegment[seg] = [];
      bySegment[seg].push(c);
    });
 
    // Calculate metrics per segment
    const aggregated = {};
    Object.entries(bySegment).forEach(([seg, custs]) => {
      aggregated[seg] = {
        count: custs.length,
        avgUsage: custs.reduce((sum, c) => sum + c.UsageScore, 0) / custs.length,
        churnRisk: custs.filter(c => c.NPS < 5).length / custs.length,
        mrrContribution: custs.reduce((sum, c) => sum + c.MRR, 0),
        topCustomers: custs.sort((a, b) => b.MRR - a.MRR).slice(0, 3)
      };
    });
 
    return aggregated;
  }
 
  performAnalysis(aggregatedData) {
    const prompt = `
Analyze our customer success metrics and provide strategic recommendations:
 
SEGMENT PERFORMANCE:
${JSON.stringify(aggregatedData, null, 2)}
 
Provide:
1. Growth opportunities (fastest-growing segments)
2. Churn risks (segments needing intervention)
3. Upsell recommendations (under-utilizing features)
4. Strategic actions (top 5 priorities)
 
Return JSON with keys: growth_segs, churn_risks, upsell_opps, priorities
`;
 
    const response = this.callGemini3Pro(prompt);
 
    try {
      const json = JSON.parse(response);
      return {
        ...aggregatedData,
        analysis: json
      };
    } catch (e) {
      Logger.log(`JSON parse failed: ${e.message}`);
      return { ...aggregatedData, analysis: { raw: response } };
    }
  }
 
  generateDocReport(analysis) {
    // Copy template
    const doc = this.docTemplate.copy(
      `CS Report - ${new Date().toLocaleDateString('en-US', { weekday: 'long', month: 'long', day: 'numeric' })}`
    );
    const body = doc.getBody();
 
    // Clear template placeholders
    const paragraphs = body.getParagraphs();
    paragraphs.forEach(p => {
      if (p.getText().includes("{{")) {
        p.setText("");
      }
    });
 
    // Add content
    body.appendParagraph("Executive Summary").setHeading(DocumentApp.ParagraphHeading.HEADING_1);
    body.appendParagraph(analysis.analysis.priorities?.[0] || "Analysis complete");
 
    Object.entries(analysis).forEach(([segment, metrics]) => {
      if (segment !== "analysis") {
        body.appendParagraph(`${segment} - ${metrics.count} customers`)
          .setHeading(DocumentApp.ParagraphHeading.HEADING_2);
        body.appendParagraph(`Avg Usage: ${metrics.avgUsage?.toFixed(2)}, Churn Risk: ${(metrics.churnRisk * 100).toFixed(1)}%`);
      }
    });
 
    doc.saveAndClose();
    return doc.getUrl();
  }
 
  generateSlidesDeck(analysis) {
    const pres = SlidesApp.create(
      `Customer Success Report - ${new Date().toLocaleDateString('en-US')}`
    );
 
    // Slide 1: Title
    const slide1 = pres.getSlides()[0];
    slide1.getShapes()[0].getText().setText("Customer Success Report");
    slide1.getShapes()[1].getText().setText(new Date().toLocaleDateString('en-US'));
 
    // Slide 2: Key Findings
    const slide2 = pres.appendSlide(SlidesApp.PredefinedLayout.TITLE_AND_BODY);
    slide2.getShapes()[0].getText().setText("Key Findings");
    const body = slide2.getShapes()[1].getText();
    analysis.analysis.priorities?.forEach((p, i) => {
      if (i < 3) body.appendText(`• ${p}\n`);
    });
 
    // Slide 3: Segment Breakdown
    const slide3 = pres.appendSlide(SlidesApp.PredefinedLayout.TITLE_AND_BODY);
    slide3.getShapes()[0].getText().setText("Segment Performance");
 
    pres.saveAndClose();
    return pres.getUrl();
  }
 
  sendDistribution(docUrl, slidesUrl, analysis) {
    const recipients = PropertiesService.getScriptProperties()
      .getProperty("CS_DISTRIBUTION_LIST").split(",");
 
    const subject = `Customer Success Report - ${new Date().toLocaleDateString('en-US')}`;
    const body = `
Weekly Customer Success Report
 
Executive Summary:
${analysis.analysis.priorities?.[0] || "See attached reports"}
 
Report: ${docUrl}
Presentation: ${slidesUrl}
 
---
Generated on ${new Date().toISOString()}
`;
 
    recipients.forEach(email => {
      GmailApp.sendEmail(email.trim(), subject, body);
    });
  }
 
  callGemini3Pro(prompt) {
    const apiKey = PropertiesService.getScriptProperties()
      .getProperty("GEMINI_API_KEY");
 
    const payload = {
      contents: [{
        parts: [{
          text: prompt
        }]
      }],
      generationConfig: {
        temperature: 0.5,
        maxOutputTokens: 2000
      }
    };
 
    const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-3-pro:generateContent?key=${apiKey}`;
 
    const response = UrlFetchApp.fetch(url, {
      method: "post",
      contentType: "application/json",
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    });
 
    const result = JSON.parse(response.getContentText());
    return result.candidates?.[0]?.content?.parts?.[0]?.text || "";
  }
}
 
// Trigger: Every Monday at 9am
function weeklyCSReport() {
  const pipeline = new CustomerSuccessPipeline();
  pipeline.run();
}

/**
 * Sheet menu item: Analyze This Row
 * Runs Gemini 3 Pro on the selected row data
 */
 
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("AI Analyze")
    .addItem("Analyze row with Gemini", "analyzeSelectedRow")
    .addToUi();
}
 
function analyzeSelectedRow() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getActiveRange();
  const rowNum = range.getRow();
 
  // Get entire row
  const rowData = sheet.getRange(rowNum, 1, 1, sheet.getLastColumn())
    .getValues()[0];
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn())
    .getValues()[0];
 
  // Format as key-value
  const rowObj = {};
  headers.forEach((h, i) => {
    rowObj[h] = rowData[i];
  });
 
  // Call Gemini
  const prompt = `Analyze this data row and provide a one-line insight:\n${JSON.stringify(rowObj)}`;
  const insight = callGemini3Pro(prompt);
 
  // Write to column next to row
  sheet.getRange(rowNum, sheet.getLastColumn() + 1)
    .setValue(insight);
 
  SpreadsheetApp.getUi().alert("Analysis complete!");
}