Google Forms → Narrative Doc Generator

/****  Google Forms → Narrative Doc Generator
 *    Author: You :)
 *    WARNING: all risks assumed by the person using this script
 *    What it does:
 *      - Reads a Google Sheets "Form responses" sheet
 *      - Builds a readable Google Doc
 *      - Two layouts:
 *          1) GROUP_BY: 'question'  (sections per question, all answers underneath)
 *          2) GROUP_BY: 'response'  (sections per respondent, Q→A in order)
 *    Notes:
 *      - No third-party add-ons required.
 *      - Safe defaults; tweak CONFIG to taste.
 ****/

const CONFIG = {
  SHEET_NAME: 'Form Responses 1',     // ← change if yours is different
  GROUP_BY: 'question',               // 'question' or 'response'
  DOC_TITLE_PREFIX: 'Feedback: DIM SUM  X VIBE CODING (Responses)',
  EXCLUDE_COLUMNS: [                  // columns to ignore (edit as needed)
    // 'Timestamp', 'Email Address', 'Score'
  ],
  ANONYMIZE_RESPONDENTS: false,        // hides respondent details if columns like "Name", "Email Address" exist
  PAGE_BREAK_EVERY_N_QUESTIONS: 0,    // 0 = no forced breaks; or e.g. 3 = page break after every 3 questions
  PAGE_BREAK_EVERY_N_RESPONSES: 0,    // only used when GROUP_BY === 'response'
  INCLUDE_SUMMARY_HEADER: true,       // adds doc header with counts & date
  TRIM_WHITESPACE: true,              // trims answers
  SKIP_BLANK_ANSWERS: false,           // skip empty answers
  MAX_ANSWERS_PER_QUESTION: null,     // e.g. 50 to limit; null = no limit
  // If your multi-select answers use a delimiter, you can split & bullet them:
  MULTISELECT_SPLIT: null             // e.g. ',' or ';' | null = don't split
};

function generateNarrativeDoc() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(CONFIG.SHEET_NAME);
  if (!sheet) throw new Error(`Sheet "${CONFIG.SHEET_NAME}" not found.`);

  const values = sheet.getDataRange().getValues();
  if (!values || values.length < 2) throw new Error('No response rows found.');

  const headers = values[0].map(String);
  const dataRows = values.slice(1).filter(row => row.some(cell => String(cell).trim() !== ''));

  // Build a working set of columns we'll include
  const includeIdx = headers
    .map((h, i) => ({ i, h }))
    .filter(col => !CONFIG.EXCLUDE_COLUMNS.includes(col.h));

  if (includeIdx.length === 0) throw new Error('After exclusions, no columns remain to format.');

  // Prepare the doc
  const docTitle = `${CONFIG.DOC_TITLE_PREFIX} — ${Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm')}`;
  const doc = DocumentApp.create(docTitle);
  const body = doc.getBody();
  body.clear();

  // Header / Summary
  if (CONFIG.INCLUDE_SUMMARY_HEADER) {
    body.appendParagraph(CONFIG.DOC_TITLE_PREFIX).setHeading(DocumentApp.ParagraphHeading.HEADING1);
    body.appendParagraph(`Generated: ${Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'EEE, MMM d, yyyy h:mm a')}`)
        .setSpacingAfter(4);
    body.appendParagraph(`Responses: ${dataRows.length}`).setSpacingAfter(14);
    body.appendHorizontalRule();
  }

  if (CONFIG.GROUP_BY.toLowerCase() === 'question') {
    buildByQuestion(body, headers, includeIdx, dataRows);
  } else if (CONFIG.GROUP_BY.toLowerCase() === 'response') {
    buildByResponse(body, headers, includeIdx, dataRows);
  } else {
    throw new Error(`Unknown GROUP_BY "${CONFIG.GROUP_BY}". Use "question" or "response".`);
  }

  // Tidy formatting
  body.editAsText().setFontFamily('Arial');
  doc.saveAndClose();

  Logger.log(`✅ Created: ${doc.getUrl()}`);
}

/** GROUPING: One section per question, with all answers listed beneath (fixed) */
function buildByQuestion(body, headers, includeIdx, dataRows) {
  // Map question -> array of answers
  const qMap = new Map();
  includeIdx.forEach(col => qMap.set(col.h, []));

  dataRows.forEach((row) => {
    includeIdx.forEach(({ i, h }) => {
      let val = String(row[i] ?? '');
      if (CONFIG.TRIM_WHITESPACE) val = val.trim();
      if (CONFIG.SKIP_BLANK_ANSWERS && !val) return;

      // Anonymize respondent-identifying questions if desired
      if (CONFIG.ANONYMIZE_RESPONDENTS && looksLikePIIHeader(h)) {
        val = '[redacted]';
      }

      qMap.get(h).push(val);
    });
  });

  let questionCounter = 0;
  includeIdx.forEach(({ h }) => {
    questionCounter++;
    const answers = qMap.get(h) || [];
    body.appendParagraph(h).setHeading(DocumentApp.ParagraphHeading.HEADING2);

    if (answers.length === 0) {
      body.appendParagraph('— No responses —').setItalic(true).setSpacingAfter(12);
    } else {
      const max = CONFIG.MAX_ANSWERS_PER_QUESTION ?? answers.length;
      const limit = Math.min(answers.length, max);

      for (let idx = 0; idx < limit; idx++) {
        const ans = formatAnswer(answers[idx]);
        // Guard: Apps Script throws if you pass an empty string
        const safe = (ans && String(ans).trim().length > 0) ? String(ans) : '—';
        body.appendListItem(safe).setGlyphType(DocumentApp.GlyphType.BULLET);
      }
      body.appendParagraph('').setSpacingAfter(14);
    }

    // Optional page breaks
    if (CONFIG.PAGE_BREAK_EVERY_N_QUESTIONS > 0 &&
        questionCounter % CONFIG.PAGE_BREAK_EVERY_N_QUESTIONS === 0) {
      body.appendPageBreak();
    }
  });
}

/** GROUPING: One section per respondent, listing Q→A in order */
function buildByResponse(body, headers, includeIdx, dataRows) {
  dataRows.forEach((row, rIdx) => {
    body.appendParagraph(`Response #${rIdx + 1}`).setHeading(DocumentApp.ParagraphHeading.HEADING2);

    includeIdx.forEach(({ i, h }) => {
      let val = String(row[i] ?? '');
      if (CONFIG.TRIM_WHITESPACE) val = val.trim();
      if (CONFIG.SKIP_BLANK_ANSWERS && !val) return;

      // Anonymize typical PII column headers
      const displayVal = (CONFIG.ANONYMIZE_RESPONDENTS && looksLikePIIHeader(h)) ? '[redacted]' : val;

      // Question
      const qPara = body.appendParagraph(h);
      qPara.setHeading(DocumentApp.ParagraphHeading.HEADING3);

      // Answer (as paragraph or split bullets if MULTISELECT_SPLIT is set)
      if (CONFIG.MULTISELECT_SPLIT && displayVal.includes(CONFIG.MULTISELECT_SPLIT)) {
        const parts = displayVal.split(CONFIG.MULTISELECT_SPLIT).map(s => s.trim()).filter(Boolean);
        parts.forEach((p, idx) => {
          if (idx === 0) {
            const li = body.appendListItem(p).setGlyphType(DocumentApp.GlyphType.BULLET);
          } else {
            body.appendListItem(p).setGlyphType(DocumentApp.GlyphType.BULLET);
          }
        });
      } else {
        body.appendParagraph(displayVal).setSpacingAfter(10);
      }
    });

    // Optional page breaks
    if (CONFIG.PAGE_BREAK_EVERY_N_RESPONSES > 0 &&
        (rIdx + 1) % CONFIG.PAGE_BREAK_EVERY_N_RESPONSES === 0) {
      body.appendPageBreak();
    } else {
      body.appendParagraph('').setSpacingAfter(16);
      body.appendHorizontalRule();
    }
  });
}

/** Helpers **/

function looksLikePIIHeader(header) {
  const h = String(header).toLowerCase();
  const hints = ['email', 'name', 'first name', 'last name', 'phone', 'contact'];
  return hints.some(k => h.includes(k));
}

function formatAnswer(answer) {
  if (!CONFIG.MULTISELECT_SPLIT || !String(answer).includes(CONFIG.MULTISELECT_SPLIT)) {
    return String(answer);
  }
  // If you wanted to inline-summarize multi-selects, you could do it here.
  return String(answer);
}

/** Optional: time-based trigger to regenerate a fresh doc daily at ~2am
 *  1) Run once to create trigger; 2) Manage in Triggers UI to remove if needed.
 */
function installDailyTrigger() {
  ScriptApp.newTrigger('generateNarrativeDoc')
    .timeBased()
    .atHour(2)
    .everyDays(1)
    .create();
}