Survey Response Normalization

Expand survey responses with multiple answers per row into normalized one-answer-per-row format.

User Story

You run a survey where respondents rate multiple products (Product A, Product B, Product C) on a single form submission. The Google Form automatically stores results as side-by-side column groups:

timestamp	email	product_a_name	product_a_rating	product_b_name	product_b_rating	product_c_name	product_c_rating
2024-03-25	a@example.com	Product A	5	Product B	3	Product C	4
2024-03-26	b@example.com	Product A	4	Product B	5	Product C	2

This wide format (one row per respondent) is problematic for analysis:

• Each product rating is in a different column (product_a_rating, product_b_rating, product_c_rating)
• Analysis tools expect long format (one row per rating)
• Adding new products requires changing column names and formulas
• Aggregations (average rating, rating distribution) require manual column operations

This is where unfoldColumns() shines — it's the gaslamp equivalent of reshape/melt in pandas/polars, but designed specifically for Google Sheets and side-by-side column groups (a pattern pandas doesn't handle as elegantly).

You need to:

• Normalize the data so each product rating becomes its own row
• Keep survey metadata (timestamp, email) with each rating
• Validate normalized data types
• Export to a new sheet for analysis
• Count total ratings per product

With gaslamp

JavaScript

function cookbook_surveyNormalization() {
  // Step 1: Define the schema for survey metadata (fixed columns)
  const metadataSchema = {
    timestamp: gaslamp.FlameGuards.isString,
    email:     gaslamp.FlameGuards.isString,
  };

  const sourceSheet = SpreadsheetApp.getActiveSheet();
  const workbook    = SpreadsheetApp.getActiveSpreadsheet();

  // Step 2: Load survey responses
  const df = gaslamp.BareFrame.fromSheet(sourceSheet);
  const { passed, failed } = gaslamp.FlameFrame.from(df, metadataSchema);

  if (failed.length > 0) {
    console.error(failed.length + ' validation error(s) found:');
    console.error(failed.display());
    return;
  }

  // Step 3: Unfold product rating groups into separate rows
  // Each product group has: product_name, rating columns
  const normalized = passed.unfoldColumns({
    fixedHeaders: ['timestamp', 'email'],
    groups: [
      ['product_a_name', 'product_a_rating'],
      ['product_b_name', 'product_b_rating'],
      ['product_c_name', 'product_c_rating'],
    ],
    groupHeaders: ['product', 'rating'],
  });

  console.log(`Expanded ${passed.length} responses → ${normalized.length} ratings`);

  // Step 4: Validate normalized data types
  const normalizedSchema = {
    timestamp: gaslamp.FlameGuards.isString,
    email:     gaslamp.FlameGuards.isString,
    product:   gaslamp.FlameGuards.isString,
    rating:    gaslamp.FlameGuards.isNumber,
  };

  const { passed: validNormalized, failed: invalidNormalized } =
    gaslamp.FlameFrame.from(normalized, normalizedSchema);

  if (invalidNormalized.length > 0) {
    console.error('Invalid normalized rows found:');
    console.error(invalidNormalized.display());
    return;
  }

  console.log(`All ${validNormalized.length} ratings are valid`);

  // Step 5: Export normalized data
  const normalizedSheet = workbook.getSheetByName('Normalized Ratings')
    ?? workbook.insertSheet('Normalized Ratings');

  validNormalized.toSheet(normalizedSheet);

  // Step 6: Generate summary statistics by product
  const ratingByProduct = validNormalized
    .groupBy(['product'])
    .count();

  console.log('Ratings per product:');
  console.log(ratingByProduct.display());

  const summarySheet = workbook.getSheetByName('Product Summary')
    ?? workbook.insertSheet('Product Summary');

  ratingByProduct.toSheet(summarySheet);

  console.log('Survey normalization complete');
}

Key gaslamp features used:

• .unfoldColumns() — gaslamp's unique reshape/melt operation
• Expands side-by-side column groups into rows (wide → long format)
• Declares groups by column names, not indexes (resilient to layout changes)
• Repeats fixed metadata (timestamp, email) for each unfolded row
• No direct equivalent in vanilla Google Apps Script or standard library operations
• fixedHeaders and groups — declarative schema for unfolding pattern (unlike pandas/polars which use column selection)
• Schema validation with FlameFrame.from() — validate both input and output data
• .groupBy() for aggregation — count and summarize per product after unfolding
• .toSheet() — directly write normalized data and summaries

Without gaslamp

JavaScript

function cookbook_surveyNormalization_vanilla() {
  const sourceSheet = SpreadsheetApp.getActiveSheet();
  const workbook    = SpreadsheetApp.getActiveSpreadsheet();

  // Step 1: Load survey responses
  const range = sourceSheet.getDataRange();
  const values = range.getValues();
  const headers = values[0];
  const rows = values.slice(1);

  // Step 2: Find column indexes manually
  const timestampIdx = headers.indexOf('timestamp');
  const emailIdx = headers.indexOf('email');
  const productAIdx = headers.indexOf('product_a_name');
  const ratingAIdx = headers.indexOf('product_a_rating');
  const productBIdx = headers.indexOf('product_b_name');
  const ratingBIdx = headers.indexOf('product_b_rating');
  const productCIdx = headers.indexOf('product_c_name');
  const ratingCIdx = headers.indexOf('product_c_rating');

  // Step 3: Manually unfold column groups into rows
  const normalized = [];
  for (const row of rows) {
    // Validate metadata (timestamp, email)
    if (typeof row[timestampIdx] !== 'string' || typeof row[emailIdx] !== 'string') {
      console.warn('Invalid metadata in row:', row);
      continue;
    }

    // Product A rating
    if (typeof row[productAIdx] === 'string' && typeof row[ratingAIdx] === 'number') {
      normalized.push([row[timestampIdx], row[emailIdx], row[productAIdx], row[ratingAIdx]]);
    }

    // Product B rating
    if (typeof row[productBIdx] === 'string' && typeof row[ratingBIdx] === 'number') {
      normalized.push([row[timestampIdx], row[emailIdx], row[productBIdx], row[ratingBIdx]]);
    }

    // Product C rating
    if (typeof row[productCIdx] === 'string' && typeof row[ratingCIdx] === 'number') {
      normalized.push([row[timestampIdx], row[emailIdx], row[productCIdx], row[ratingCIdx]]);
    }
  }

  console.log(`Expanded ${rows.length} responses → ${normalized.length} ratings`);

  // Step 4: Write normalized data to sheet
  const normalizedSheet = workbook.getSheetByName('Normalized Ratings')
    ?? workbook.insertSheet('Normalized Ratings');

  if (normalized.length > 0) {
    const normalizedHeaders = ['timestamp', 'email', 'product', 'rating'];
    normalizedSheet.getRange(1, 1, 1, normalizedHeaders.length).setValues([normalizedHeaders]);
    normalizedSheet.getRange(2, 1, normalized.length, normalizedHeaders.length).setValues(normalized);
  }

  // Step 5: Manually group and count by product
  const productCounts = {};
  const productOrder = [];

  for (const row of normalized) {
    const product = row[2]; // product column
    if (!productCounts[product]) {
      productCounts[product] = 0;
      productOrder.push(product);
    }
    productCounts[product] += 1;
  }

  // Step 6: Write summary statistics
  const summarySheet = workbook.getSheetByName('Product Summary')
    ?? workbook.insertSheet('Product Summary');

  const summaryData = productOrder.map((product) => [product, productCounts[product]]);

  if (summaryData.length > 0) {
    summarySheet.getRange(1, 1, 1, 2).setValues([['Product', 'Count']]);
    summarySheet.getRange(2, 1, summaryData.length, 2).setValues(summaryData);
  }

  console.log('Survey normalization complete');
}

Without gaslamp, you need to:

• Manually find and track column indexes for each product group (6 columns × 3 products = 18 indexes)
• Manually validate both metadata and each product rating separately
• Write nested conditions for each product group (Product A, B, C with separate checks)
• Manually create and validate normalized output headers
• Manually implement grouping and counting logic for summaries
• Track insertion order of unique products manually
• Duplicate validation logic for metadata and each rating group
• Manually construct range dimensions for writing multiple sheets

---

Key Techniques

• Unfold column groups with unfoldColumns(): gaslamp's unique method for reshape/melt on Google Sheets
• More elegant than manual index tracking and nested loops
• Declarative: declare groups by column names, not positions
• Automatic repetition of fixed columns for each unfolded row
• Validate input and output: Check original metadata and normalized data types separately
• Resilient to layout changes: Column name-based declaration means reordering or adding columns doesn't break the code
• Summary after normalization: Use .groupBy() on normalized data for instant aggregation
• Wide-to-long conversion: Transform survey responses from 1 row (many columns) to N rows (fewer columns) without manual loops

Why This Matters for Google Sheets

Unlike pandas/polars, Google Sheets lacks reshape operations. unfoldColumns() fills this gap with a Google Sheets-native solution:

• No temporary columns needed
• No pivot table workarounds
• Column names are the source of truth (like SQL or pandas, not index-based)
• Seamless integration with schema validation and aggregation

See Unfolding Guide for more column group patterns and groupBy Guide for advanced aggregation after unfolding.