Building an AI Presentation Pipeline with Gemini API and Google Slides Apps Script — A

// config.gs — Pipeline configuration
const CONFIG = {
  GEMINI_API_KEY: PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY'),
  GEMINI_MODEL: 'gemini-2.5-pro',
  GEMINI_ENDPOINT: 'https://generativelanguage.googleapis.com/v1beta/models/',
  TEMPLATE_FOLDER_ID: 'YOUR_TEMPLATE_FOLDER_ID',
  OUTPUT_FOLDER_ID: 'YOUR_OUTPUT_FOLDER_ID',
  MAX_SLIDES: 20,
  DEFAULT_SLIDES: 10,
};
 
// input.gs — Receive input from the sidebar
function showInputSidebar() {
  const html = HtmlService.createHtmlOutputFromFile('Sidebar')
    .setTitle('AI Presentation Generator')
    .setWidth(350);
  SpreadsheetApp.getUi().showSidebar(html);
}
 
// Called from the sidebar UI
function generatePresentation(params) {
  const { theme, audience, slideCount, tone, language } = params;
 
  // Validation
  if (!theme || theme.trim().length < 5) {
    throw new Error('Topic must be at least 5 characters long');
  }
  const count = Math.min(
    Math.max(parseInt(slideCount) || CONFIG.DEFAULT_SLIDES, 3),
    CONFIG.MAX_SLIDES
  );
 
  // Execute the pipeline
  const outline = generateOutline(theme, audience, count, tone, language);
  const contents = generateSlideContents(outline, tone, language);
  const presentation = buildPresentation(contents, language);
  const result = postProcess(presentation, language);
 
  return {
    presentationUrl: result.url,
    pdfUrl: result.pdfUrl,
    slideCount: contents.slides.length,
  };
}

// outline.gs — Outline generation
function generateOutline(theme, audience, slideCount, tone, language) {
  const systemPrompt = `You are an expert presentation architect.
Design a professional slide deck outline based on the given theme.
 
Constraints:
- Slide count: ${slideCount} slides (including title and closing slides)
- Target audience: ${audience}
- Tone: ${tone}
- Assign an appropriate layout type to each slide
- Layout types: title_slide, section_header, content_with_bullets,
  content_with_image, two_column, chart_suggestion, quote, closing`;
 
  const userPrompt = `Theme: ${theme}
 
Output in the following JSON format:
{
  "title": "Main presentation title",
  "subtitle": "Subtitle",
  "slides": [
    {
      "slideNumber": 1,
      "layout": "title_slide",
      "title": "Slide title",
      "keyPoints": ["Point 1", "Point 2"],
      "speakerNotes": "Speaker notes summary"
    }
  ]
}`;
 
  const response = callGeminiAPI(systemPrompt, userPrompt, {
    responseMimeType: 'application/json',
  });
 
  const outline = JSON.parse(response);
 
  // Validation: check slide count
  if (!outline.slides || outline.slides.length < 3) {
    throw new Error('Outline generation failed. Please retry.');
  }
 
  // Log for debugging and auditing
  logToSheet('Outline', JSON.stringify(outline, null, 2));
 
  return outline;
}
 
// gemini-api.gs — Shared Gemini API caller
function callGeminiAPI(systemPrompt, userPrompt, options = {}) {
  const url = `${CONFIG.GEMINI_ENDPOINT}${CONFIG.GEMINI_MODEL}:generateContent?key=${CONFIG.GEMINI_API_KEY}`;
 
  const payload = {
    system_instruction: {
      parts: [{ text: systemPrompt }],
    },
    contents: [
      {
        role: 'user',
        parts: [{ text: userPrompt }],
      },
    ],
    generationConfig: {
      temperature: options.temperature || 0.7,
      topP: 0.95,
      maxOutputTokens: options.maxTokens || 8192,
    },
  };
 
  // Enable Structured Output (JSON Mode)
  if (options.responseMimeType) {
    payload.generationConfig.responseMimeType = options.responseMimeType;
  }
 
  const fetchOptions = {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
    muteHttpExceptions: true,
  };
 
  // Retry logic with exponential backoff
  let lastError;
  for (let attempt = 0; attempt < 3; attempt++) {
    try {
      const res = UrlFetchApp.fetch(url, fetchOptions);
      const status = res.getResponseCode();
 
      if (status === 200) {
        const json = JSON.parse(res.getContentText());
        return json.candidates[0].content.parts[0].text;
      }
 
      if (status === 429) {
        // Rate limited: exponential backoff
        const waitMs = Math.pow(2, attempt) * 1000 + Math.random() * 1000;
        Utilities.sleep(waitMs);
        continue;
      }
 
      throw new Error(`Gemini API error: ${status} - ${res.getContentText()}`);
    } catch (e) {
      lastError = e;
      if (attempt < 2) {
        Utilities.sleep(2000 * (attempt + 1));
      }
    }
  }
  throw lastError;
}

// content-gen.gs — Slide content generation
function generateSlideContents(outline, tone, language) {
  const batchSize = 3; // Process 3 slides per batch
  const allSlides = [];
 
  for (let i = 0; i < outline.slides.length; i += batchSize) {
    const batch = outline.slides.slice(i, i + batchSize);
    const batchContents = generateBatchContents(batch, outline.title, tone, language);
    allSlides.push(...batchContents);
 
    // Rate limit prevention: short pause between batches
    if (i + batchSize < outline.slides.length) {
      Utilities.sleep(500);
    }
  }
 
  return {
    title: outline.title,
    subtitle: outline.subtitle,
    slides: allSlides,
  };
}
 
function generateBatchContents(slides, presentationTitle, tone, language) {
  const systemPrompt = `You are a presentation content writer.
Generate detailed content for each slide as part of the presentation titled "${presentationTitle}".
 
Rules:
- Tone: ${tone}
- 3-5 bullet points per slide
- Each bullet should be concise and specific (15-30 words)
- Speaker notes: 100-200 words with detailed context
- For chart_suggestion layouts, include chart specs with sample data`;
 
  const slidesInfo = slides.map(s =>
    `Slide ${s.slideNumber}: layout=${s.layout}, title="${s.title}", keyPoints=${JSON.stringify(s.keyPoints)}`
  ).join('\n');
 
  const userPrompt = `Generate detailed content for the following slides as a JSON array:
 
${slidesInfo}
 
Output format:
[
  {
    "slideNumber": 1,
    "title": "Slide title",
    "body": "Body text (used for content_with_image, etc.)",
    "bullets": ["Bullet 1", "Bullet 2"],
    "speakerNotes": "Detailed speaker notes",
    "chartSpec": null
  }
]`;
 
  const response = callGeminiAPI(systemPrompt, userPrompt, {
    responseMimeType: 'application/json',
    temperature: 0.6, // Lower temperature for more consistent content
  });
 
  return JSON.parse(response);
}

// slide-builder.gs — Slide construction
function buildPresentation(contents, language) {
  // Copy the template to create a new presentation
  const templateId = getTemplateId(language);
  const template = DriveApp.getFileById(templateId);
  const newFile = template.makeCopy(
    `${contents.title} — ${new Date().toISOString().slice(0, 10)}`,
    DriveApp.getFolderById(CONFIG.OUTPUT_FOLDER_ID)
  );
 
  const presentation = SlidesApp.openById(newFile.getId());
  const masterSlides = presentation.getMasters()[0].getLayouts();
 
  // Remove all existing slides (keep layout masters)
  const existingSlides = presentation.getSlides();
  existingSlides.forEach(slide => slide.remove());
 
  // Generate slides from content
  contents.slides.forEach((slideData, index) => {
    const layout = findLayout(masterSlides, slideData.layout);
    const slide = presentation.appendSlide(layout);
 
    populateSlide(slide, slideData);
 
    // Set speaker notes
    if (slideData.speakerNotes) {
      slide.getNotesPage()
        .getSpeakerNotesShape()
        .getText()
        .setText(slideData.speakerNotes);
    }
  });
 
  presentation.saveAndClose();
 
  return {
    id: newFile.getId(),
    url: newFile.getUrl(),
    name: newFile.getName(),
  };
}
 
// Map layout types to Google Slides layout names
function findLayout(masterLayouts, layoutType) {
  const layoutMap = {
    'title_slide': 'TITLE',
    'section_header': 'SECTION_HEADER',
    'content_with_bullets': 'TITLE_AND_BODY',
    'content_with_image': 'TITLE_AND_BODY',
    'two_column': 'TITLE_AND_TWO_COLUMNS',
    'chart_suggestion': 'TITLE_AND_BODY',
    'quote': 'TITLE_ONLY',
    'closing': 'TITLE',
  };
 
  const targetName = layoutMap[layoutType] || 'TITLE_AND_BODY';
 
  for (const layout of masterLayouts) {
    if (layout.getLayoutName() === targetName) {
      return layout;
    }
  }
  // Fallback: use the first available layout
  return masterLayouts[0];
}
 
// Populate a slide with content
function populateSlide(slide, data) {
  const placeholders = slide.getPlaceholders();
 
  placeholders.forEach(placeholder => {
    const type = placeholder.getPlaceholderType();
 
    switch (type) {
      case SlidesApp.PlaceholderType.TITLE:
      case SlidesApp.PlaceholderType.CENTER_TITLE:
        placeholder.asShape().getText().setText(data.title);
        break;
 
      case SlidesApp.PlaceholderType.BODY:
      case SlidesApp.PlaceholderType.SUBTITLE:
        if (data.bullets && data.bullets.length > 0) {
          const textRange = placeholder.asShape().getText();
          textRange.clear();
          data.bullets.forEach((bullet, i) => {
            const paragraph = i === 0
              ? textRange.setText(bullet)
              : textRange.appendParagraph(bullet);
          });
        } else if (data.body) {
          placeholder.asShape().getText().setText(data.body);
        }
        break;
    }
  });
}

// post-process.gs — Post-processing pipeline
function postProcess(presentation, language) {
  const result = {
    url: presentation.url,
    pdfUrl: null,
    translations: [],
  };
 
  // 1. PDF export
  result.pdfUrl = exportToPDF(presentation.id, presentation.name);
 
  // 2. Record metadata to spreadsheet
  logPresentationMetadata(presentation);
 
  // 3. Completion notification (optional)
  sendNotification(presentation, result.pdfUrl);
 
  return result;
}
 
function exportToPDF(fileId, fileName) {
  const url = `https://docs.google.com/presentation/d/${fileId}/export/pdf`;
  const token = ScriptApp.getOAuthToken();
 
  const response = UrlFetchApp.fetch(url, {
    headers: { Authorization: `Bearer ${token}` },
    muteHttpExceptions: true,
  });
 
  if (response.getResponseCode() !== 200) {
    Logger.log(`PDF export failed: ${response.getResponseCode()}`);
    return null;
  }
 
  const pdfBlob = response.getBlob().setName(`${fileName}.pdf`);
  const outputFolder = DriveApp.getFolderById(CONFIG.OUTPUT_FOLDER_ID);
  const pdfFile = outputFolder.createFile(pdfBlob);
 
  return pdfFile.getUrl();
}
 
// Generate translated version automatically
function generateTranslatedVersion(presentationId, targetLanguage) {
  const original = SlidesApp.openById(presentationId);
  const slides = original.getSlides();
 
  // Extract all text from slides
  const texts = slides.map(slide => {
    const elements = slide.getPageElements();
    return elements
      .filter(e => e.getPageElementType() === SlidesApp.PageElementType.SHAPE)
      .map(e => e.asShape().getText().asString())
      .filter(t => t.trim().length > 0);
  });
 
  // Batch translate with Gemini API
  const systemPrompt = `You are a professional translator.
Translate the following presentation text into ${targetLanguage}.
- Maintain bullet point structure
- Keep technical terms in their original language
- Use natural ${targetLanguage} expressions`;
 
  const userPrompt = `Translate the following slide texts.
Maintain the structure of each slide and output as a JSON array:
 
${JSON.stringify(texts, null, 2)}`;
 
  const response = callGeminiAPI(systemPrompt, userPrompt, {
    responseMimeType: 'application/json',
    maxTokens: 16384,
  });
 
  const translations = JSON.parse(response);
 
  // Create a copy and apply translated text
  const file = DriveApp.getFileById(presentationId);
  const copy = file.makeCopy(
    file.getName().replace(/(.+)/, `$1 (${targetLanguage})`),
    DriveApp.getFolderById(CONFIG.OUTPUT_FOLDER_ID)
  );
 
  const translatedPres = SlidesApp.openById(copy.getId());
  const translatedSlides = translatedPres.getSlides();
 
  translations.forEach((slideTexts, slideIndex) => {
    if (slideIndex >= translatedSlides.length) return;
 
    const elements = translatedSlides[slideIndex].getPageElements()
      .filter(e => e.getPageElementType() === SlidesApp.PageElementType.SHAPE);
 
    let textIndex = 0;
    elements.forEach(element => {
      const shape = element.asShape();
      const currentText = shape.getText().asString().trim();
      if (currentText.length > 0 && textIndex < slideTexts.length) {
        shape.getText().setText(slideTexts[textIndex]);
        textIndex++;
      }
    });
  });
 
  translatedPres.saveAndClose();
  return copy.getUrl();
}

// template-manager.gs — Template management
const TEMPLATES = {
  corporate: {
    ja: 'TEMPLATE_ID_JA_CORPORATE',
    en: 'TEMPLATE_ID_EN_CORPORATE',
    description: 'Internal meetings and executive reports',
  },
  pitch: {
    ja: 'TEMPLATE_ID_JA_PITCH',
    en: 'TEMPLATE_ID_EN_PITCH',
    description: 'Investor pitch decks',
  },
  workshop: {
    ja: 'TEMPLATE_ID_JA_WORKSHOP',
    en: 'TEMPLATE_ID_EN_WORKSHOP',
    description: 'Workshops and study sessions',
  },
  minimal: {
    ja: 'TEMPLATE_ID_JA_MINIMAL',
    en: 'TEMPLATE_ID_EN_MINIMAL',
    description: 'Minimal design (general purpose)',
  },
};
 
function getTemplateId(language, templateType = 'corporate') {
  const template = TEMPLATES[templateType];
  if (!template) {
    throw new Error(`Unknown template type: ${templateType}`);
  }
 
  const lang = language === 'en' ? 'en' : 'ja';
  return template[lang];
}
 
// Get template preview information
function getTemplateList() {
  return Object.entries(TEMPLATES).map(([key, value]) => ({
    id: key,
    description: value.description,
    thumbnailUrl: getThumbnail(value.ja),
  }));
}
 
function getThumbnail(presentationId) {
  try {
    const presentation = SlidesApp.openById(presentationId);
    const firstSlide = presentation.getSlides()[0];
    const thumbnail = firstSlide.getThumbnail();
    return thumbnail.getUrl();
  } catch (e) {
    return null;
  }
}

// error-handler.gs — Error handling
class PipelineError extends Error {
  constructor(stage, message, retryable = false) {
    super(`[Stage ${stage}] ${message}`);
    this.stage = stage;
    this.retryable = retryable;
  }
}
 
function executePipelineWithRetry(params, maxRetries = 2) {
  let lastError;
 
  for (let attempt = 0; attempt <= maxRetries; attempt++) {
    try {
      if (attempt > 0) {
        Logger.log(`Retry attempt ${attempt}/${maxRetries}`);
        Utilities.sleep(3000 * attempt); // Exponential backoff
      }
 
      return generatePresentation(params);
    } catch (error) {
      lastError = error;
 
      // Non-retryable errors: abort immediately
      if (error instanceof PipelineError && !error.retryable) {
        break;
      }
 
      // Approaching Apps Script time limit (6 min): abort
      const elapsed = (new Date() - scriptStartTime) / 1000;
      if (elapsed > 300) { // 5 minutes elapsed
        Logger.log('Approaching execution time limit. Aborting retry.');
        break;
      }
 
      logError(error, attempt);
    }
  }
 
  // All retries failed: save partial results and notify
  savePartialResult(lastError);
  sendErrorNotification(lastError);
  throw lastError;
}
 
function logError(error, attempt) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('ErrorLog') ||
    SpreadsheetApp.getActiveSpreadsheet().insertSheet('ErrorLog');
 
  sheet.appendRow([
    new Date(),
    error.stage || 'unknown',
    error.message,
    attempt,
    JSON.stringify(error.stack || ''),
  ]);
}

// batch.gs — Batch processing
function setupWeeklyReportTrigger() {
  // Remove existing triggers
  ScriptApp.getProjectTriggers().forEach(trigger => {
    if (trigger.getHandlerFunction() === 'generateWeeklyReport') {
      ScriptApp.deleteTrigger(trigger);
    }
  });
 
  // Run every Monday at 9:00 AM
  ScriptApp.newTrigger('generateWeeklyReport')
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(9)
    .create();
}
 
function generateWeeklyReport() {
  // Fetch latest data from the spreadsheet
  const dataSheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('WeeklyData');
  const data = dataSheet.getDataRange().getValues();
 
  // Format data as context for the presentation
  const dataContext = formatDataForPrompt(data);
 
  const params = {
    theme: `Weekly Performance Report (${getWeekRange()})`,
    audience: 'Leadership team',
    slideCount: 8,
    tone: 'professional',
    language: 'en',
    additionalContext: dataContext,
  };
 
  try {
    const result = executePipelineWithRetry(params);
 
    // Success: notify via Slack
    sendSlackNotification({
      text: `✅ Weekly report generated automatically\n📊 ${result.presentationUrl}`,
    });
  } catch (error) {
    sendSlackNotification({
      text: `❌ Weekly report generation failed: ${error.message}`,
    });
  }
}
 
function formatDataForPrompt(sheetData) {
  const headers = sheetData[0];
  const rows = sheetData.slice(1);
 
  return rows.map(row => {
    const obj = {};
    headers.forEach((h, i) => { obj[h] = row[i]; });
    return obj;
  });
}
 
function getWeekRange() {
  const now = new Date();
  const weekAgo = new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000);
  const fmt = d => `${d.getMonth() + 1}/${d.getDate()}`;
  return `${fmt(weekAgo)}–${fmt(now)}`;
}

// cost-optimization.gs — Model selection
function callGeminiAPIWithModel(model, systemPrompt, userPrompt, options = {}) {
  const url = `${CONFIG.GEMINI_ENDPOINT}${model}:generateContent?key=${CONFIG.GEMINI_API_KEY}`;
  // ... (same implementation as callGeminiAPI, only the model in the URL changes)
}
 
// Stage 2: Use Pro for structural design (higher quality)
function generateOutlineOptimized(theme, audience, slideCount, tone) {
  return callGeminiAPIWithModel('gemini-2.5-pro', /* ... */);
}
 
// Stage 3: Use Flash for content filling (faster, lower cost)
function generateSlideContentsOptimized(outline, tone) {
  return callGeminiAPIWithModel('gemini-2.5-flash', /* ... */);
}