Time-Series Aggregation by Window

Generate weekly and monthly summaries from daily time-series data.

User Story

You collect daily sales data (date, amount, category) from a Google Form. You need to:

• Validate date and amount values
• Aggregate daily data into weekly summaries (sum of sales by week)
• Aggregate daily data into monthly summaries (sum of sales by month)
• Export summaries to separate sheets
• Track which dates fall into which aggregation windows

With gaslamp

JavaScript

function cookbook_timeSeriesAggregation() {
  // Step 1: Define schema for daily data
  const schema = {
    date:     gaslamp.FlameGuards.isString,
    amount:   gaslamp.FlameGuards.isNumber,
    category: gaslamp.FlameGuards.isString,
  };

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

  // Step 2: Load and validate daily sales data
  const df = gaslamp.BareFrame.fromSheet(sourceSheet);
  const { passed, failed } = gaslamp.FlameFrame.from(df, schema);

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

  // Step 3: Helper function to calculate week start date
  const getWeekStart = (dateString) => {
    const date = new Date(dateString);
    const weekStart = new Date(date);
    weekStart.setDate(date.getDate() - date.getDay());
    return Utilities.formatDate(weekStart, Session.getScriptTimeZone(), 'yyyy-MM-dd');
  };

  // Step 4: Add week and month columns for grouping
  const withTimeWindows = passed
    .withColumn('week', (row) => getWeekStart(row.get('date')))
    .withColumn('month', (row) => {
      const date = new Date(row.get('date'));
      return Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM');
    });

  // Step 5: Generate weekly summary
  const weeklySummary = withTimeWindows
    .groupBy(['week', 'category'])
    .sum(['amount']);

  console.log('Weekly summary:');
  console.log(weeklySummary.display());

  const weeklySheet = workbook.getSheetByName('Weekly Summary')
    ?? workbook.insertSheet('Weekly Summary');
  weeklySummary.toSheet(weeklySheet);

  // Step 6: Generate monthly summary
  const monthlySummary = withTimeWindows
    .groupBy(['month', 'category'])
    .sum(['amount']);

  console.log('Monthly summary:');
  console.log(monthlySummary.display());

  const monthlySheet = workbook.getSheetByName('Monthly Summary')
    ?? workbook.insertSheet('Monthly Summary');
  monthlySummary.toSheet(monthlySheet);

  // Step 7: Calculate totals across all periods
  const weeklyTotal = weeklySummary
    .groupBy([])
    .sum(['amount']);

  const monthlyTotal = monthlySummary
    .groupBy([])
    .sum(['amount']);

  console.log('Weekly total:', weeklyTotal.toRows()[0].get('amount'));
  console.log('Monthly total:', monthlyTotal.toRows()[0].get('amount'));
}

Key gaslamp features used:

• Schema validation with FlameFrame.from() — validate date and numeric values
• .withColumn() for computed windows — add week/month columns derived from date
• Chainable .groupBy() — group by multiple dimensions (week + category, month + category)
• .sum() on grouped data — aggregate amounts per group
• Multiple aggregations — reuse the same DataFrame to generate multiple summary views
• Readable column names — withTimeWindows makes the transformation intent clear

Without gaslamp

JavaScript

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

  // Step 1: Load and validate daily sales data
  const range = sourceSheet.getDataRange();
  const values = range.getValues();
  const headers = values[0];
  const rows = values.slice(1);

  const dateIdx = headers.indexOf('date');
  const amountIdx = headers.indexOf('amount');
  const categoryIdx = headers.indexOf('category');

  const validated = [];
  for (const row of rows) {
    if (typeof row[dateIdx] !== 'string' || typeof row[amountIdx] !== 'number' || typeof row[categoryIdx] !== 'string') {
      console.warn('Type error in row:', row);
      continue;
    }
    validated.push(row);
  }

  if (validated.length === 0) {
    console.warn('Cannot aggregate: no valid rows');
    return;
  }

  // Step 2: Helper function to calculate week start date
  const getWeekStart = (dateString) => {
    const date = new Date(dateString);
    const weekStart = new Date(date);
    weekStart.setDate(date.getDate() - date.getDay());
    return Utilities.formatDate(weekStart, Session.getScriptTimeZone(), 'yyyy-MM-dd');
  };

  // Step 3: Add week and month columns to each row
  const withTimeWindows = validated.map((row) => {
    const date = new Date(row[dateIdx]);
    const week = getWeekStart(row[dateIdx]);
    const month = Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM');
    return [...row, week, month];
  });

  // Step 4: Manually group by week and category, then sum amounts
  const weeklyMap = {};
  const weeklyOrder = [];
  for (const row of withTimeWindows) {
    const week = row[row.length - 2];
    const category = row[categoryIdx];
    const amount = row[amountIdx];
    const key = `${week}|${category}`;

    if (!weeklyMap[key]) {
      weeklyMap[key] = { week, category, amount: 0 };
      weeklyOrder.push(key);
    }

    weeklyMap[key].amount += amount;
  }

  const weeklySummary = weeklyOrder.map((key) => {
    const data = weeklyMap[key];
    return [data.week, data.category, data.amount];
  });

  console.log('Weekly summary:', weeklySummary);

  const weeklySheet = workbook.getSheetByName('Weekly Summary')
    ?? workbook.insertSheet('Weekly Summary');

  if (weeklySummary.length > 0) {
    weeklySheet.getRange(1, 1, 1, 3).setValues([['Week', 'Category', 'Amount']]);
    weeklySheet.getRange(2, 1, weeklySummary.length, 3).setValues(weeklySummary);
  }

  // Step 5: Manually group by month and category, then sum amounts
  const monthlyMap = {};
  const monthlyOrder = [];
  for (const row of withTimeWindows) {
    const month = row[row.length - 1];
    const category = row[categoryIdx];
    const amount = row[amountIdx];
    const key = `${month}|${category}`;

    if (!monthlyMap[key]) {
      monthlyMap[key] = { month, category, amount: 0 };
      monthlyOrder.push(key);
    }

    monthlyMap[key].amount += amount;
  }

  const monthlySummary = monthlyOrder.map((key) => {
    const data = monthlyMap[key];
    return [data.month, data.category, data.amount];
  });

  console.log('Monthly summary:', monthlySummary);

  const monthlySheet = workbook.getSheetByName('Monthly Summary')
    ?? workbook.insertSheet('Monthly Summary');

  if (monthlySummary.length > 0) {
    monthlySheet.getRange(1, 1, 1, 3).setValues([['Month', 'Category', 'Amount']]);
    monthlySheet.getRange(2, 1, monthlySummary.length, 3).setValues(monthlySummary);
  }

  // Step 6: Calculate grand totals
  let weeklyTotal = 0;
  let monthlyTotal = 0;
  for (const summary of weeklySummary) {
    weeklyTotal += summary[2];
  }
  for (const summary of monthlySummary) {
    monthlyTotal += summary[2];
  }

  console.log('Weekly total:', weeklyTotal);
  console.log('Monthly total:', monthlyTotal);
}

Without gaslamp, you need to:

• Manually validate types for each column
• Manually compute time windows (week start, month) for each row
• Manually create separate aggregation maps for each window type (weekly vs monthly)
• Manually track aggregation order to preserve insertion order
• Manually iterate through grouped data to sum amounts
• Manually reconstruct 2D arrays from grouped maps
• Duplicate grouping logic for each aggregation window
• Manually compute grand totals by iterating through summaries
• Manually write headers and data separately for each output sheet
• Manually handle empty result cases

---

Key Techniques

• Validate before aggregation: Always check data types before time-window computation
• Computed window columns: Use .withColumn() to add week/month derived from date
• Multi-dimensional grouping: Group by multiple columns (window + category) for cross-tabulation
• Chainable aggregations: Reuse the enriched DataFrame for multiple summary views
• Date window calculation: Use getDate() - getDay() to find week start, format with Utilities.formatDate()
• Aggregation preservation: Object maps preserve order by tracking insertion order (or use Map for guaranteed order)

See groupBy Guide for more aggregation patterns and resampleBy Guide for advanced time-series operations.