google apps script to generate text slides from any 2 col csv in google sheets

gistfile1.txt

/**
 * CONFIGURATION & STYLING
 */
const CONFIG = {
  MENU_NAME: "🎲 SLIDE APP",
  TAGS: {
    BTN: "nav_btn_v7",
    PHRASE: "phrase_txt_v7",
    FOCUS: "focus_txt_v7"
  },
  STYLES: {
    BTN_BG: "#efefef",
    BTN_BORDER: "#333333",
    TEXT_COLOR: "#000000",
    BTN_FONT_SIZE: 14,
    PHRASE_FONT_SIZE: 42,
    FOCUS_FONT_SIZE: 28,
    LABEL_FONT_SIZE: 11,
    LABEL_COLOR: "#888888",
    FONT_FAMILY: "Arial"
  }
};

function onOpen() {
  SlidesApp.getUi().createMenu(CONFIG.MENU_NAME)
    .addItem('🚀 Create New Randomized Deck', 'startWorkflow')
    .addSeparator()
    .addItem('❓ Help & Instructions', 'showHelp')
    .addToUi();
}

function showHelp() {
  const html = HtmlService.createHtmlOutput(`
    <div style="font-family: sans-serif; padding: 15px; font-size: 14px; line-height: 1.6;">
      <h2 style="color: #202124;">Documentation</h2>
      <p>This tool generates a randomized deck from a Google Sheet.</p>
      <hr>
      <ol>
        <li><b>Prepare Sheet:</b> Row 1 = headers (any names). Column A = question/prompt, Column B = answer/detail.</li>
        <li><b>Run:</b> Click the menu and paste your Sheet URL.</li>
        <li><b>Flow:</b> Each card = 2 slides. Slide 1 shows Col A only. Click REVEAL to see Col B. Click RANDOM ▶ to move to the next card.</li>
      </ol>
    </div>
  `)
  .setWidth(450)
  .setHeight(320);
  SlidesApp.getUi().showModalDialog(html, 'Slide App Help');
}

function startWorkflow() {
  const ui = SlidesApp.getUi();

  const response = ui.prompt(
    'New Slide Deck Generation',
    'Please paste the full URL of your Google Sheet:',
    ui.ButtonSet.OK_CANCEL
  );

  if (response.getSelectedButton() !== ui.Button.OK) return;
  const url = response.getResponseText().trim();

  if (!url.includes("docs.google.com/spreadsheets")) {
    ui.alert("Invalid URL", "Please provide a valid Google Sheets URL.", ui.ButtonSet.OK);
    return;
  }

  try {
    const ss = SpreadsheetApp.openByUrl(url);
    const ssName = ss.getName();
    const { headers, data } = fetchDataFromSheet(ss);

    if (data.length === 0) {
      throw new Error("No data found. Ensure Column A has data rows below the header.");
    }

    const currentFileId = SlidesApp.getActivePresentation().getId();
    const newFile = DriveApp.getFileById(currentFileId).makeCopy(`Randomized - ${ssName}`);
    const newDeck = SlidesApp.openById(newFile.getId());

    const shuffled = shuffleArray(data).slice(0, 50);
    const slidePairs = rebuildSlides(newDeck, shuffled, headers);
    wireButtons(newDeck, slidePairs);

    const htmlOutput = HtmlService.createHtmlOutput(`
      <p>Successfully created: <b>${ssName}</b></p>
      <p>Columns: <b>${headers.col1}</b> (prompt) → <b>${headers.col2}</b> (reveal)</p>
      <p>${shuffled.length} cards = ${shuffled.length * 2} slides total.</p>
      <p><a href="${newDeck.getUrl()}" target="_blank" style="color: #1a73e8; font-weight: bold;">Click here to open your new deck</a></p>
    `)
    .setWidth(400)
    .setHeight(200);
    ui.showModalDialog(htmlOutput, 'Process Complete');

  } catch (e) {
    ui.alert("Execution Error", "Details: " + e.message, ui.ButtonSet.OK);
  }
}

/**
 * Reads headers from Row 1, then data from remaining rows.
 */
function fetchDataFromSheet(ss) {
  const sheet = ss.getSheets()[0];
  const rows = sheet.getDataRange().getValues();

  if (rows.length === 0) return { headers: { col1: "Column A", col2: "Column B" }, data: [] };

  const headerRow = rows[0];
  const headers = {
    col1: headerRow[0] ? headerRow[0].toString().trim() : "Column A",
    col2: headerRow[1] ? headerRow[1].toString().trim() : "Column B"
  };

  const data = [];
  for (let i = 1; i < rows.length; i++) {
    const row = rows[i];
    if (row[0] && row[0].toString().trim() !== "") {
      data.push({
        col1: row[0].toString().trim(),
        col2: row[1] ? row[1].toString().trim() : ""
      });
    }
  }

  return { headers, data };
}

/**
 * Builds PAIRS of slides per data item.
 * Each text box gets a small label (header name) in the top-left.
 */
function rebuildSlides(deck, data, headers) {
  deck.getSlides().forEach(s => s.remove());

  const pWidth = deck.getPageWidth();
  const pHeight = deck.getPageHeight();
  const slidePairs = [];

  data.forEach((item) => {
    // --- Slide A: Question (col1 only) ---
    const questionSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
    addLabeledTextBox(
      questionSlide, headers.col1, item.col1,
      pWidth, 60, 650, 160,
      CONFIG.STYLES.PHRASE_FONT_SIZE, true, CONFIG.TAGS.PHRASE
    );

    // --- Slide B: Reveal (col1 + col2) ---
    const revealSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
    addLabeledTextBox(
      revealSlide, headers.col1, item.col1,
      pWidth, 60, 650, 160,
      CONFIG.STYLES.PHRASE_FONT_SIZE, true, CONFIG.TAGS.PHRASE
    );
    addLabeledTextBox(
      revealSlide, headers.col2, item.col2,
      pWidth, pHeight - 120 - 120, 650, 120,
      CONFIG.STYLES.FOCUS_FONT_SIZE, false, CONFIG.TAGS.FOCUS
    );

    slidePairs.push({ questionSlide, revealSlide });
  });

  return slidePairs;
}

/**
 * Inserts a text box with a small header label in the top-left,
 * and the main value text below it — all in one shape.
 */
function addLabeledTextBox(slide, label, value, pWidth, yPos, boxWidth, boxHeight, fontSize, bold, tag) {
  const box = slide.insertShape(
    SlidesApp.ShapeType.TEXT_BOX,
    (pWidth / 2) - (boxWidth / 2),
    yPos,
    boxWidth,
    boxHeight
  );
  box.setTitle(tag);

  const t = box.getText();

  // Line 1: small label (header name)
  t.setText(label + "\n" + value);

  // Style the label portion (first line)
  const labelRange = t.getRange(0, label.length);
  labelRange.getTextStyle()
    .setFontSize(CONFIG.STYLES.LABEL_FONT_SIZE)
    .setBold(false)
    .setForegroundColor(CONFIG.STYLES.LABEL_COLOR)
    .setFontFamily(CONFIG.STYLES.FONT_FAMILY);

  // Style the value portion (after newline)
  const valueRange = t.getRange(label.length + 1, label.length + 1 + value.length);
  valueRange.getTextStyle()
    .setFontSize(fontSize)
    .setBold(bold)
    .setForegroundColor(CONFIG.STYLES.TEXT_COLOR)
    .setFontFamily(CONFIG.STYLES.FONT_FAMILY);

  // Align label left, value center
  t.getParagraphs()[0].getRange().getParagraphStyle()
    .setParagraphAlignment(SlidesApp.ParagraphAlignment.START);
  t.getParagraphs()[1].getRange().getParagraphStyle()
    .setParagraphAlignment(SlidesApp.ParagraphAlignment.CENTER);

  box.setContentAlignment(SlidesApp.ContentAlignment.MIDDLE);
  return box;
}

/**
 * Wires buttons across all slide pairs.
 */
function wireButtons(deck, slidePairs) {
  const pWidth = deck.getPageWidth();
  const pHeight = deck.getPageHeight();

  slidePairs.forEach(({ questionSlide, revealSlide }, index) => {
    const isLast = index === slidePairs.length - 1;
    const nextQuestionSlide = isLast ? slidePairs[0].questionSlide : slidePairs[index + 1].questionSlide;

    // Question slide: REVEAL ▼ → reveal slide
    const revealBtn = createButton(questionSlide, "REVEAL ▼", (pWidth - 220), (pHeight - 80), "#fff2cc");
    revealBtn.setLinkSlide(revealSlide);

    // Reveal slide: RANDOM ▶ or FINISH ↺ → next question slide
    const nextLabel = isLast ? "FINISH ↺" : "RANDOM ▶";
    const nextBg = isLast ? "#ccffcc" : CONFIG.STYLES.BTN_BG;
    const nextBtn = createButton(revealSlide, nextLabel, (pWidth - 220), (pHeight - 80), nextBg);
    nextBtn.setLinkSlide(nextQuestionSlide);

    // Reveal slide: ◀ BACK → question slide
    const backBtn = createButton(revealSlide, "◀ BACK", 50, (pHeight - 80), CONFIG.STYLES.BTN_BG);
    backBtn.setLinkSlide(questionSlide);
  });
}

/**
 * Helper: styled rectangle button.
 */
function createButton(slide, label, x, y, bgColor) {
  const shape = slide.insertShape(SlidesApp.ShapeType.RECTANGLE, x, y, 170, 50);
  shape.getFill().setSolidFill(bgColor);
  shape.getBorder().setWeight(1).getLineFill().setSolidFill(CONFIG.STYLES.BTN_BORDER);
  const txt = shape.getText();
  txt.setText(label);
  txt.getTextStyle()
    .setFontSize(CONFIG.STYLES.BTN_FONT_SIZE)
    .setFontFamily(CONFIG.STYLES.FONT_FAMILY)
    .setForegroundColor(CONFIG.STYLES.TEXT_COLOR);
  txt.getParagraphStyle().setParagraphAlignment(SlidesApp.ParagraphAlignment.CENTER);
  shape.setContentAlignment(SlidesApp.ContentAlignment.MIDDLE);
  return shape;
}

/**
 * Fisher-Yates Shuffle.
 */
function shuffleArray(array) {
  for (let i = array.length - 1; i > 0; i--) {
    const j = Math.floor(Math.random() * (i + 1));
    [array[i], array[j]] = [array[j], array[i]];
  }
  return array;
}