Building a Cross-Product AI Workflow with Gemini API and Apps Script — Gmail, Calendar, and Drive Automation

// Store the API key securely in PropertiesService
function setApiKey() {
  PropertiesService.getScriptProperties().setProperty(
    'GEMINI_API_KEY',
    'YOUR_GEMINI_API_KEY' // ← Replace with your key from Google AI Studio
  );
}
 
function getApiKey() {
  return PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
}

// appsscript.json
{
  "timeZone": "America/New_York",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/gmail.modify",
    "https://www.googleapis.com/auth/calendar",
    "https://www.googleapis.com/auth/drive.readonly",
    "https://www.googleapis.com/auth/documents",
    "https://www.googleapis.com/auth/script.external_request"
  ]
}

/**
 * Fetch unread emails and triage them using Gemini API
 * @returns {Array<Object>} Array of triage results
 */
function triageEmails() {
  const threads = GmailApp.search('is:unread -category:promotions -category:social', 0, 10);
  const results = [];
 
  for (const thread of threads) {
    const messages = thread.getMessages();
    const latest = messages[messages.length - 1];
 
    const emailData = {
      subject: latest.getSubject(),
      from: latest.getFrom(),
      body: latest.getPlainBody().substring(0, 2000), // Limit to 2000 chars to save tokens
      date: latest.getDate().toISOString()
    };
 
    const triage = callGeminiForTriage(emailData);
    if (triage) {
      results.push({
        threadId: thread.getId(),
        messageId: latest.getId(),
        ...triage,
        originalEmail: emailData
      });
      applyTriageActions(thread, triage);
    }
 
    // Rate limit: wait 4 seconds between requests (15 RPM = 4s/request)
    Utilities.sleep(4000);
  }
 
  return results;
}

/**
 * Send an email to Gemini API for triage analysis
 * @param {Object} email - Email data object
 * @returns {Object|null} Triage result
 */
function callGeminiForTriage(email) {
  const apiKey = getApiKey();
  const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent?key=${apiKey}`;
 
  const prompt = `You are an expert email triage assistant. Analyze the following email and return a structured JSON result.
 
Email details:
- Subject: ${email.subject}
- From: ${email.from}
- Date: ${email.date}
- Body: ${email.body}
 
Return JSON matching this schema:
{
  "urgency": "high" | "medium" | "low",
  "category": "meeting" | "deadline" | "request" | "info" | "spam",
  "action": "reply_needed" | "calendar_entry" | "archive" | "follow_up",
  "summary": "1-2 sentence summary",
  "suggested_reply": "Draft reply if needed, otherwise null",
  "calendar_info": {
    "needed": true/false,
    "title": "Event title",
    "datetime": "ISO 8601 datetime",
    "duration_minutes": 60,
    "attendees": ["email1@example.com"]
  }
}`;
 
  const payload = {
    contents: [{ parts: [{ text: prompt }] }],
    generationConfig: {
      responseMimeType: 'application/json',
      temperature: 0.1 // Keep temperature low for classification tasks
    }
  };
 
  try {
    const response = UrlFetchApp.fetch(url, {
      method: 'post',
      contentType: 'application/json',
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    });
 
    const status = response.getResponseCode();
    if (status !== 200) {
      console.error(`Gemini API error: ${status} - ${response.getContentText()}`);
      return null;
    }
 
    const json = JSON.parse(response.getContentText());
    const text = json.candidates[0].content.parts[0].text;
    return JSON.parse(text);
  } catch (e) {
    console.error(`Triage failed: ${e.message}`);
    return null;
  }
}

/**
 * Apply labels, archive, and create draft replies based on triage results
 */
function applyTriageActions(thread, triage) {
  // Get or create urgency label
  const labelName = `AI-Triage/${triage.urgency}`;
  let label = GmailApp.getUserLabelByName(labelName);
  if (!label) {
    label = GmailApp.createLabel(labelName);
  }
  thread.addLabel(label);
 
  // Apply category label
  const catLabel = GmailApp.getUserLabelByName(`AI-Category/${triage.category}`)
    || GmailApp.createLabel(`AI-Category/${triage.category}`);
  thread.addLabel(catLabel);
 
  // Auto-archive low-priority informational emails
  if (triage.urgency === 'low' && triage.category === 'info') {
    thread.moveToArchive();
    thread.markRead();
  }
 
  // Create draft reply when needed
  if (triage.suggested_reply && triage.action === 'reply_needed') {
    const messages = thread.getMessages();
    const latest = messages[messages.length - 1];
    latest.createDraftReply(triage.suggested_reply);
  }
}

/**
 * Check if the proposed time slot is available, suggest alternatives if not
 * @param {string} dateTimeStr - ISO 8601 datetime string
 * @param {number} durationMin - Duration in minutes
 * @returns {Object} { available: boolean, suggestedTime: Date }
 */
function checkCalendarAvailability(dateTimeStr, durationMin) {
  const startTime = new Date(dateTimeStr);
  const endTime = new Date(startTime.getTime() + durationMin * 60 * 1000);
 
  const calendar = CalendarApp.getDefaultCalendar();
  const events = calendar.getEvents(startTime, endTime);
 
  if (events.length === 0) {
    return { available: true, suggestedTime: startTime };
  }
 
  // Conflict detected: search for free slots within business hours
  const dayStart = new Date(startTime);
  dayStart.setHours(9, 0, 0, 0);
  const dayEnd = new Date(startTime);
  dayEnd.setHours(18, 0, 0, 0);
 
  const allEvents = calendar.getEvents(dayStart, dayEnd);
  const busySlots = allEvents.map(e => ({
    start: e.getStartTime().getTime(),
    end: e.getEndTime().getTime()
  })).sort((a, b) => a.start - b.start);
 
  // Find first available slot
  let candidateStart = dayStart.getTime();
  for (const slot of busySlots) {
    if (slot.start - candidateStart >= durationMin * 60 * 1000) {
      return { available: false, suggestedTime: new Date(candidateStart) };
    }
    candidateStart = Math.max(candidateStart, slot.end);
  }
 
  // No availability today — suggest next business day at 9 AM
  const nextDay = new Date(startTime);
  nextDay.setDate(nextDay.getDate() + 1);
  if (nextDay.getDay() === 0) nextDay.setDate(nextDay.getDate() + 1);
  if (nextDay.getDay() === 6) nextDay.setDate(nextDay.getDate() + 2);
  nextDay.setHours(9, 0, 0, 0);
 
  return { available: false, suggestedTime: nextDay };
}

/**
 * Create a Calendar event from triage results
 * @param {Object} triageResult - Phase 1 triage result
 * @returns {Object|null} Created event information
 */
function createCalendarEvent(triageResult) {
  const calInfo = triageResult.calendar_info;
  if (!calInfo || !calInfo.needed) return null;
 
  const duration = calInfo.duration_minutes || 60;
  const availability = checkCalendarAvailability(calInfo.datetime, duration);
 
  const eventStart = availability.suggestedTime;
  const eventEnd = new Date(eventStart.getTime() + duration * 60 * 1000);
 
  const calendar = CalendarApp.getDefaultCalendar();
  const event = calendar.createEvent(
    calInfo.title || triageResult.summary,
    eventStart,
    eventEnd,
    {
      description: `[AI Auto-Created]\n\nEmail summary: ${triageResult.summary}\n\nFrom: ${triageResult.originalEmail.from}`,
      guests: (calInfo.attendees || []).join(',')
    }
  );
 
  // Add note if the time was rescheduled
  if (!availability.available) {
    event.setDescription(
      event.getDescription() + `\n\n⚠️ Rescheduled from original time: ${calInfo.datetime}`
    );
  }
 
  // Set 30-minute reminder
  event.addPopupReminder(30);
 
  return {
    eventId: event.getId(),
    title: event.getTitle(),
    start: eventStart.toISOString(),
    end: eventEnd.toISOString(),
    rescheduled: !availability.available
  };
}

/**
 * Search Drive for documents related to a calendar event
 * @param {Object} eventInfo - Event from Phase 2
 * @param {Object} triageResult - Triage from Phase 1
 * @returns {Array<Object>} List of related documents
 */
function searchRelatedDocs(eventInfo, triageResult) {
  const searchQuery = generateSearchQuery(eventInfo, triageResult);
 
  const files = DriveApp.searchFiles(
    `fullText contains '${searchQuery}' and mimeType != 'application/vnd.google-apps.folder' and trashed = false`
  );
 
  const results = [];
  let count = 0;
  while (files.hasNext() && count < 5) {
    const file = files.next();
    results.push({
      id: file.getId(),
      name: file.getName(),
      mimeType: file.getMimeType(),
      url: file.getUrl(),
      lastUpdated: file.getLastUpdated().toISOString()
    });
    count++;
  }
 
  return results;
}
 
/**
 * Use Gemini API to generate optimal search keywords
 */
function generateSearchQuery(eventInfo, triageResult) {
  const apiKey = getApiKey();
  const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent?key=${apiKey}`;
 
  const prompt = `Generate a single search phrase (maximum 5 words) to find relevant documents in Google Drive for the following meeting. Return only the phrase, nothing else.
 
Meeting title: ${eventInfo.title}
Email summary: ${triageResult.summary}
From: ${triageResult.originalEmail.from}`;
 
  const payload = {
    contents: [{ parts: [{ text: prompt }] }],
    generationConfig: { temperature: 0.2, maxOutputTokens: 50 }
  };
 
  const response = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  });
 
  const json = JSON.parse(response.getContentText());
  return json.candidates[0].content.parts[0].text.trim();
}

/**
 * Summarize related documents and create a Google Doc prep note
 * @param {Object} eventInfo - Event information
 * @param {Array<Object>} relatedDocs - Related document list
 * @param {Object} triageResult - Triage result
 * @returns {string} URL of the created Doc
 */
function createPrepNote(eventInfo, relatedDocs, triageResult) {
  // Extract content from each document (text-based files only)
  const docContents = relatedDocs.map(doc => {
    try {
      if (doc.mimeType === 'application/vnd.google-apps.document') {
        const content = DocumentApp.openById(doc.id).getBody().getText();
        return { name: doc.name, content: content.substring(0, 3000) };
      }
      if (doc.mimeType === 'application/pdf' || doc.mimeType.startsWith('text/')) {
        const blob = DriveApp.getFileById(doc.id).getBlob();
        return { name: doc.name, content: blob.getDataAsString().substring(0, 3000) };
      }
      return { name: doc.name, content: '(Binary file — content extraction unavailable)' };
    } catch (e) {
      return { name: doc.name, content: `(Read error: ${e.message})` };
    }
  });
 
  // Generate prep note content via Gemini API
  const noteContent = callGeminiForPrepNote(eventInfo, docContents, triageResult);
 
  // Create Google Doc
  const doc = DocumentApp.create(`📋 Meeting Prep: ${eventInfo.title}`);
  const body = doc.getBody();
 
  body.appendParagraph(`Meeting Prep: ${eventInfo.title}`)
    .setHeading(DocumentApp.ParagraphHeading.HEADING1);
  body.appendParagraph(`Date: ${new Date(eventInfo.start).toLocaleString('en-US')}`);
  body.appendParagraph(`Generated: ${new Date().toLocaleString('en-US')} (AI-generated)`);
  body.appendHorizontalRule();
  body.appendParagraph(noteContent);
 
  // Add links to source documents
  body.appendParagraph('Reference Documents').setHeading(DocumentApp.ParagraphHeading.HEADING2);
  for (const d of relatedDocs) {
    body.appendListItem(d.name).setLinkUrl(d.url);
  }
 
  doc.saveAndClose();
  return doc.getUrl();
}

/**
 * Generate prep note content using Gemini API
 */
function callGeminiForPrepNote(eventInfo, docContents, triageResult) {
  const apiKey = getApiKey();
  const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-pro:generateContent?key=${apiKey}`;
 
  const docsSection = docContents.map(d =>
    `【${d.name}】\n${d.content}`
  ).join('\n\n---\n\n');
 
  const prompt = `You are a skilled executive assistant. Based on the meeting details and related documents below, create a concise meeting prep note that helps attendees prepare effectively.
 
## Meeting Information
- Title: ${eventInfo.title}
- Date: ${eventInfo.start}
- Context: ${triageResult.summary}
 
## Related Documents
${docsSection}
 
## Output Format
1. **Meeting Purpose & Context** (2-3 sentences)
2. **Key Discussion Points** (3-5 bullet items)
3. **Document Highlights** (2-3 sentences per document)
4. **Decisions & Action Items Needed** (bullet list)
5. **Materials to Prepare in Advance** (if any)
 
Keep it concise and actionable.`;
 
  const payload = {
    contents: [{ parts: [{ text: prompt }] }],
    generationConfig: { temperature: 0.3, maxOutputTokens: 2000 }
  };
 
  const response = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  });
 
  const json = JSON.parse(response.getContentText());
  return json.candidates[0].content.parts[0].text;
}

/**
 * Main orchestrator: Execute Phase 1 → Phase 2 → Phase 3 sequentially
 */
function runWorkflowPipeline() {
  const startTime = new Date();
  console.log(`=== AI Workflow Pipeline started: ${startTime.toISOString()} ===`);
 
  // Phase 1: Email Triage
  console.log('Phase 1: Starting email triage');
  const triageResults = triageEmails();
  console.log(`Phase 1 complete: ${triageResults.length} emails processed`);
 
  // Phase 2: Calendar Event Creation
  console.log('Phase 2: Starting calendar event creation');
  const calendarEntries = [];
  for (const result of triageResults) {
    if (result.calendar_info && result.calendar_info.needed) {
      const eventInfo = createCalendarEvent(result);
      if (eventInfo) {
        calendarEntries.push({ event: eventInfo, triage: result });
      }
      Utilities.sleep(4000); // Rate limit protection
    }
  }
  console.log(`Phase 2 complete: ${calendarEntries.length} events created`);
 
  // Phase 3: Prep Note Generation
  console.log('Phase 3: Starting prep note generation');
  const prepNotes = [];
  for (const entry of calendarEntries) {
    const docs = searchRelatedDocs(entry.event, entry.triage);
    if (docs.length > 0) {
      const noteUrl = createPrepNote(entry.event, docs, entry.triage);
      prepNotes.push({ event: entry.event.title, noteUrl });
    }
    Utilities.sleep(4000);
  }
  console.log(`Phase 3 complete: ${prepNotes.length} notes created`);
 
  // Output execution summary
  const elapsed = (new Date() - startTime) / 1000;
  const summary = {
    executionTime: `${elapsed}s`,
    emailsProcessed: triageResults.length,
    eventsCreated: calendarEntries.length,
    prepNotesGenerated: prepNotes.length,
    notes: prepNotes
  };
  console.log(`=== Pipeline complete ===\n${JSON.stringify(summary, null, 2)}`);
 
  // Optional: email execution report to yourself
  if (prepNotes.length > 0) {
    sendExecutionReport(summary);
  }
 
  return summary;
}
 
/**
 * Send execution report via email
 */
function sendExecutionReport(summary) {
  const notesList = summary.notes.map(n => `• ${n.event}: ${n.noteUrl}`).join('\n');
  GmailApp.sendEmail(
    Session.getActiveUser().getEmail(),
    `[AI Workflow] ${summary.eventsCreated} events / ${summary.prepNotesGenerated} prep notes`,
    `AI Workflow Pipeline Results\n\nEmails processed: ${summary.emailsProcessed}\nEvents created: ${summary.eventsCreated}\nPrep notes: ${summary.prepNotesGenerated}\nExecution time: ${summary.executionTime}\n\nCreated notes:\n${notesList}`
  );
}

/**
 * Initial setup: register triggers
 * Run this once manually from the Apps Script editor
 */
function setupTriggers() {
  // Remove existing triggers to prevent duplicates
  const triggers = ScriptApp.getProjectTriggers();
  for (const trigger of triggers) {
    if (trigger.getHandlerFunction() === 'runWorkflowPipeline') {
      ScriptApp.deleteTrigger(trigger);
    }
  }
 
  // Run at 8 AM daily
  ScriptApp.newTrigger('runWorkflowPipeline')
    .timeBased()
    .everyDays(1)
    .atHour(8)
    .nearMinute(0)
    .create();
 
  // Run again at noon to catch morning emails
  ScriptApp.newTrigger('runWorkflowPipeline')
    .timeBased()
    .everyDays(1)
    .atHour(12)
    .nearMinute(0)
    .create();
 
  console.log('Triggers configured (daily at 8:00 and 12:00)');
}

const MAX_EXECUTION_MS = 5 * 60 * 1000; // 5 minutes (with safety margin)
 
function isTimeUp(startTime) {
  return (new Date() - startTime) > MAX_EXECUTION_MS;
}
 
/**
 * Time-aware email triage (improved version)
 */
function triageEmailsWithTimeLimit() {
  const startTime = new Date();
  const threads = GmailApp.search('is:unread -category:promotions -category:social', 0, 20);
  const results = [];
  let processedCount = 0;
 
  for (const thread of threads) {
    if (isTimeUp(startTime)) {
      console.log(`Time limit reached: ${processedCount}/${threads.length} processed`);
      // Unprocessed emails remain unread and will be picked up next run
      break;
    }
 
    const messages = thread.getMessages();
    const latest = messages[messages.length - 1];
    const emailData = {
      subject: latest.getSubject(),
      from: latest.getFrom(),
      body: latest.getPlainBody().substring(0, 2000),
      date: latest.getDate().toISOString()
    };
 
    const triage = callGeminiForTriage(emailData);
    if (triage) {
      results.push({ threadId: thread.getId(), ...triage, originalEmail: emailData });
      applyTriageActions(thread, triage);
    }
 
    processedCount++;
    Utilities.sleep(4000);
  }
 
  return results;
}

/**
 * Retry wrapper with exponential backoff
 * @param {Function} fn - Function to execute
 * @param {number} maxRetries - Maximum retry count
 * @returns {any} Function return value
 */
function withRetry(fn, maxRetries = 3) {
  let lastError;
  for (let attempt = 0; attempt <= maxRetries; attempt++) {
    try {
      return fn();
    } catch (e) {
      lastError = e;
      console.warn(`Attempt ${attempt + 1}/${maxRetries + 1} failed: ${e.message}`);
 
      if (attempt < maxRetries) {
        const backoff = Math.pow(2, attempt) * 1000 + Math.random() * 1000;
        console.log(`Retrying in ${Math.round(backoff / 1000)}s...`);
        Utilities.sleep(backoff);
      }
    }
  }
  throw new Error(`All ${maxRetries + 1} attempts failed: ${lastError.message}`);
}
 
// Usage example
function callGeminiWithRetry(email) {
  return withRetry(() => callGeminiForTriage(email), 3);
}

/**
 * Track daily API usage via PropertiesService
 */
function trackApiUsage() {
  const props = PropertiesService.getScriptProperties();
  const today = new Date().toISOString().split('T')[0];
  const key = `api_usage_${today}`;
 
  const current = parseInt(props.getProperty(key) || '0');
  props.setProperty(key, String(current + 1));
 
  // Warn when approaching daily limit (free tier: 1500 RPD)
  if (current + 1 >= 1400) {
    console.warn(`⚠️ API usage warning: ${current + 1}/1500 RPD`);
  }
 
  return current + 1;
}

/**
 * Filter out confidential emails from processing
 */
function isConfidential(email) {
  const confidentialDomains = ['legal@', 'hr@', 'finance@'];
  const confidentialKeywords = ['confidential', 'privileged', 'under nda'];
 
  if (confidentialDomains.some(d => email.from.includes(d))) return true;
  if (confidentialKeywords.some(k =>
    email.subject.toLowerCase().includes(k) || email.body.toLowerCase().includes(k)
  )) return true;
 
  return false;
}

function parseGeminiJson(rawText) {
  // Strip leading/trailing code fences before JSON.parse
  const cleaned = rawText
    .replace(/^\s*```(?:json)?\s*/i, '')
    .replace(/\s*```\s*$/i, '')
    .trim();
  return JSON.parse(cleaned);
}

// Update the search query in triageEmails
const threads = GmailApp.search(
  'is:unread -category:promotions -category:social -label:AI-Triage-Done', 0, 10
);
 
// Add to the end of applyTriageActions
const doneLabel = GmailApp.getUserLabelByName('AI-Triage-Done')
  || GmailApp.createLabel('AI-Triage-Done');
thread.addLabel(doneLabel);

function safeFullTextQuery(keyword) {
  // Escape single quotes to avoid corrupting the Drive query
  const escaped = keyword.replace(/'/g, "\\'");
  return `fullText contains '${escaped}' and mimeType != 'application/vnd.google-apps.folder' and trashed = false`;
}