Skip to content

Practical Workflows

A core GAS workflow: validate event registration data from Google Forms, detect duplicates, and generate reports by attendance mode and dietary restrictions.

User Story

You're organizing an event and created a Google Form to collect registrations. The form automatically records a timestamp and collects: name, kana (reading), email, department, position, nationality, attendance mode (in-person/online), dinner preference, dietary restrictions, and notes.

Some registrations have invalid data (malformed emails, wrong attendance mode values). You also need to detect duplicate registrations (same email submitted twice).

You need to:

  • Validate all registrations against the expected schema
  • Detect and report duplicate emails
  • Generate attendance lists filtered by mode and dinner preference
  • Provide summary statistics (registrations per time period, unique dietary restrictions)

Step 1: Validate and Load Registration Data

First, load the Google Form responses into a DataFrame and validate each row against the schema. Any rows with missing or malformed data are separated into a failed DataFrame and exported to a new sheet for manual review. Only rows that pass validation move to the next step.

Sample Input Data (Expected Sheet Structure)

The Google Form automatically creates a sheet with these columns. Column names must match the keys used in the code examples below:

timestamp name email department position attendanceMode dinnerPreference dietaryRestrictions notes
2026-04-01 10:05 Taro Yamada taro@example.com Science Faculty in-person yes vegetarian Interested in afternoon sessions
2026-04-02 15:20 Hanako Suzuki hanako@example.com Engineering Student online no
2026-04-03 09:30 Yuki Tanaka yuki@example.com Agriculture Faculty in-person yes vegan Prefer vegetarian menu options
2026-04-04 14:45 Kenji Nakamura kenji@example.com Science Student online no gluten-free
2026-04-05 11:15 Sakura Ito sakura@example.com Engineering Faculty in-person yes Will bring a guest if possible

Key points:

  • Column names must exactly match the keys in the schema (email, attendanceMode, dinnerPreference)
  • Empty cells are allowed for optional columns (notes, dietaryRestrictions)
  • Timestamps are auto-generated by Google Forms
JavaScript
function validateRegistrations() {
  // Define the schema for registration data
  const schema = {
    timestamp: gaslamp.FlameGuards.isString,
    name: gaslamp.FlameGuards.isString,
    kana: gaslamp.FlameGuards.isString,
    email: gaslamp.FlameGuards.isString,
    department: gaslamp.FlameGuards.isString,
    position: gaslamp.FlameGuards.isString,
    nationality: gaslamp.FlameGuards.isString,
    attendanceMode: gaslamp.FlameGuards.isString,
    dinnerPreference: gaslamp.FlameGuards.isString,
    dietaryRestrictions: gaslamp.FlameGuards.isString,
    notes: gaslamp.FlameGuards.isString,
  };

  // Load data from Google Form responses
  const sheet = SpreadsheetApp.getActiveSheet();
  const df = gaslamp.BareFrame.fromSheet(sheet);

  // Validate against schema
  const { passed, failed } = gaslamp.FlameFrame.from(df, schema);

  console.log(`Total registrations: ${df.length}`);
  console.log(`Validation errors: ${failed.length}`);

  // Export validation error report
  if (failed.length > 0) {
    const workbook = SpreadsheetApp.getActiveSpreadsheet();
    const errorSheet = workbook.getSheetByName('Validation Errors')
      ?? workbook.insertSheet('Validation Errors');
    failed.toSheet(errorSheet);
    console.warn(`Found ${failed.length} validation errors. See "Validation Errors" sheet.`);
  }

  return passed;
}

Example Output:

Text Only
Total registrations: 10
Validation errors: 2
Error report created in "Validation Errors" sheet

The failed DataFrame exported to "Validation Errors" sheet will contain rows with invalid data, with an additional invalid_message column explaining what validation failed (e.g., "email: expected string but got undefined").

Step 2: Normalize Data

Before applying business rules, normalize the email and name fields (convert to lowercase, trim whitespace) for consistent comparison. Since we only need the normalized values (not the original), we replace the original columns in-place using withColumn().

JavaScript
function normalizeData(validData) {
  // Normalize email and name columns for consistent comparison
  // Replace original columns with normalized values (trim and lowercase)
  const normalized = validData
    .withColumn('email', (row) =>
      row.get('email').trim().toLowerCase()
    )
    .withColumn('name', (row) =>
      row.get('name').trim()
    );

  return normalized;
}

Step 3: Identify Duplicates for Review

Find duplicate registrations by checking multiple columns (name, email, department) and export them to a separate sheet for manual review. This allows you to understand the duplicate issue before removing any records.

JavaScript
function identifyDuplicates(normalizedData) {
  // Find all rows with duplicate combinations of name, email, and department
  // This catches registrations from the same person at the same company
  const duplicates = normalizedData.findDuplicatedRows({
    headers: ['name', 'email', 'department']
  });

  console.log(`Duplicate registrations found: ${duplicates.length}`);

  // Export duplicates to a separate sheet for manual review
  if (duplicates.length > 0) {
    const workbook = SpreadsheetApp.getActiveSpreadsheet();
    const dupSheet = workbook.getSheetByName('Duplicates')
      ?? workbook.insertSheet('Duplicates');
    duplicates.toSheet(dupSheet);
    console.warn(`Found ${duplicates.length} duplicate registrations. See "Duplicates" sheet.`);
  }

  return duplicates.length;
}

Example Output:

Text Only
Duplicate registrations found: 0

In this example, all registrations are unique (no matching combinations of name, email, and department), so no duplicates are found. If duplicates were present, the "Duplicates" sheet would contain all columns with rows matching name, email, AND department:

timestamp name email department position attendance dinner dietary notes
2026-04-01 10:05 Taro Yamada taro@example.com Science Faculty in-person yes vegetarian Interested in afternoon sessions
2026-04-01 11:30 Taro Yamada taro@example.com Science Faculty online no vegetarian

Step 4: Remove Duplicates and Keep Unique Registrations

Remove duplicate registrations from the dataset, keeping only the first occurrence based on name, email, and department. This ensures clean data for downstream processing.

Note: Whether to keep the first or last occurrence should be decided based on your Step 3 review (e.g., which registration has more complete data, or which was submitted first). In this example, we keep first to preserve the earliest registration timestamp.

JavaScript
function removeDuplicates(normalizedData) {
  // Remove duplicates based on the same columns we checked (name, email, department)
  // keep: 'first' — keep the earliest registration for each unique combination
  const unique = normalizedData.dropDuplicatedRows({
    headers: ['name', 'email', 'department'],
    keep: 'first'
  });

  console.log(`After removing duplicates: ${unique.length} unique registrations`);

  return unique;
}

Step 5: Apply Business Rules

With validated, normalized, and deduplicated data, apply business-rule filters to confirm attendance mode and dinner preference are valid enum values.

JavaScript
function applyBusinessRules(uniqueData) {
  // Filter for valid registrations (enum checks and email format)
  // Note: duplicates have already been removed in Step 4
  const validRegistrations = uniqueData.filter((row) => {
    const email = row.get('email');
    const mode = row.get('attendanceMode');
    const dinner = row.get('dinnerPreference');

    const validMode = ['in-person', 'online'].includes(mode);
    const validDinner = ['yes', 'no'].includes(dinner);
    const validEmail = email.includes('@') && email.includes('.');

    return validMode && validDinner && validEmail;
  });

  console.log(`Valid registrations: ${validRegistrations.length}`);

  return validRegistrations;
}

Example Output:

Text Only
Valid registrations: 5

After this step, you have 5 registrations that passed all validation checks (valid email, valid attendance mode, valid dinner preference).

Step 6: Generate and Export Reports

Create focused reports for different stakeholders: in-person attendees, online attendees, and dinner attendees. Export each report to its own sheet for easy distribution to different teams (venue, catering, etc.).

JavaScript
function generateAndExportReports(validRegistrations) {
  // Generate in-person attendance list
  const inPersonList = validRegistrations
    .filter((row) => row.get('attendanceMode') === 'in-person')
    .select(['name', 'department', 'position']);

  // Generate online attendance list
  const onlineList = validRegistrations
    .filter((row) => row.get('attendanceMode') === 'online')
    .select(['name', 'department', 'position']);

  // Extract dinner attendees (for catering planning)
  const dinnerAttendees = validRegistrations
    .filter((row) => row.get('dinnerPreference') === 'yes')
    .select(['name', 'department', 'dietaryRestrictions']);

  console.log(`In-person: ${inPersonList.length}, Online: ${onlineList.length}, Dinner: ${dinnerAttendees.length}`);

  // Export reports to new sheets
  const workbook = SpreadsheetApp.getActiveSpreadsheet();

  const inPersonSheet = workbook.getSheetByName('In-Person') ?? workbook.insertSheet('In-Person');
  inPersonList.toSheet(inPersonSheet);

  const onlineSheet = workbook.getSheetByName('Online') ?? workbook.insertSheet('Online');
  onlineList.toSheet(onlineSheet);

  const dinnerSheet = workbook.getSheetByName('Dinner') ?? workbook.insertSheet('Dinner');
  dinnerAttendees.toSheet(dinnerSheet);

  console.log('Reports exported to In-Person, Online, and Dinner sheets');
}

Example Output:

Text Only
In-person: 3, Online: 2, Dinner: 3
Reports exported to In-Person, Online, and Dinner sheets

In-Person Sheet (3 attendees):

name department position
Taro Yamada Science Faculty
Yuki Tanaka Agriculture Faculty
Sakura Ito Engineering Faculty

Online Sheet (2 attendees):

name department position
Hanako Suzuki Engineering Student
Kenji Nakamura Science Student

Dinner Sheet (3 attendees) — for catering planning:

name department dietaryRestrictions
Taro Yamada Science vegetarian
Yuki Tanaka Agriculture vegan
Sakura Ito Engineering

Step 7: Generate Summary Statistics

Generate summary statistics to understand registration patterns and requirements.

JavaScript
function generateSummaryStatistics(validRegistrations) {
  // Count registrations by department
  const byDepartment = validRegistrations.groupBy(['department']).count();

  console.log('Registrations by department:');
  // Display as formatted table (up to 100 rows, 30 chars per column)
  console.log(byDepartment.display(100, 30));

  // Extract unique dietary restrictions (excluding empty values)
  const dietaryCol = validRegistrations.toColumns().dietaryRestrictions;
  const uniqueDietary = [...new Set(dietaryCol.filter((d) => d && d.trim()))];

  console.log('Unique dietary restrictions:');
  uniqueDietary.forEach((dietary) => {
    const count = dietaryCol.filter((d) => d === dietary).length;
    console.log(`  ${dietary}: ${count} person(s)`);
  });

  // Count registrations by date using resampleBy
  // Add a date column by converting timestamp string to Date object
  const withDateCol = validRegistrations.withColumn('date', (row) => {
    const timestampStr = row.get('timestamp');
    return new Date(timestampStr);
  });

  // Resample by day and count registrations
  const byDate = withDateCol.resampleBy('date', '1d').count();

  console.log('Registrations by date:');
  // Display as formatted table (up to 100 rows, 30 chars per column)
  console.log(byDate.display(100, 30));
}

Example Output:

Text Only
Registrations by department:
  Science: 2
  Engineering: 2
  Agriculture: 1

Unique dietary restrictions:
  vegetarian: 1 person(s)
  vegan: 1 person(s)
  gluten-free: 1 person(s)

Registrations by date:
  2026-04-01: 1
  2026-04-02: 1
  2026-04-03: 1
  2026-04-04: 1
  2026-04-05: 1

Main Workflow

Putting it all together:

JavaScript
// Main workflow: tie everything together
function workflow_eventRegistration() {
  // Step 1: Validate input
  const validated = validateRegistrations();

  // Step 2: Normalize data
  const normalized = normalizeData(validated);

  // Step 3: Identify duplicates for review
  identifyDuplicates(normalized);

  // Step 4: Remove duplicates and keep unique registrations
  const unique = removeDuplicates(normalized);

  // Step 5: Apply business rules
  const valid = applyBusinessRules(unique);

  // Step 6: Generate and export reports
  generateAndExportReports(valid);

  // Step 7: Generate summary statistics
  generateSummaryStatistics(valid);
}

Key gaslamp Features Demonstrated

This workflow demonstrates core gaslamp operations:

  • Schema validation (FlameFrame.from)
  • Data normalization (.withColumn)
  • Duplicate detection and removal (.findDuplicatedRows, .dropDuplicatedRows)
  • Aggregation (.groupBy, .resampleBy)
  • Data export (.toSheet)

See the User Guides for detailed explanation of each feature.

Alternative: Expression API for Cleaner Validation

For complex validation logic, consider using the Expression API:

JavaScript
const emailIsValid = Col('email').contains('@');
const attendanceModeIsValid = Col('attendanceMode').eq('in-person')
  .or(Col('attendanceMode').eq('online'));
const registrationIsValid = emailIsValid.and(attendanceModeIsValid);

return uniqueData.filter(registrationIsValid.toFunction());

Learn more in the Expressions and Predicates conceptual guide.

Pro Tips

  1. Validate early — always run schema validation on input data to catch malformed records
  2. Normalize before filtering — standardize email/phone/text fields before checking business rules
  3. Check duplicates twice — first review them with .findDuplicatedRows(), then remove with .dropDuplicatedRows() (with keep: 'first' to preserve registration order)
  4. Export error reports — use .toSheet() to export validation failures and duplicate reports for manual review
  5. Use convenience methods — .groupBy().count() and .resampleBy().count() are simpler than .agg() for basic aggregation
  6. Chain transformations — validation (Step 1) → normalization (Step 2) → duplicate identification (Step 3) → duplicate removal (Step 4) → business-rule filtering (Step 5) → report generation (Step 6) → summary statistics (Step 7) forms a natural workflow

Next Steps

Ready to explore more patterns? Check out the Cookbooks section for specialized workflows:

For more advanced topics: