Backup Automation¶

Create timestamped backups of important data with a master log.

User Story¶

Your team maintains critical budget data in Google Sheets collected through a form. Before major updates or reconciliations, you want to:

Validate data types before creating backups
Create a timestamped snapshot of the current data
Add metadata (backup date) to each row for traceability
Maintain an audit log of all backups with status (Success, Failed, Partial)
Ensure backups are only created if data validates

With gaslamp¶

JavaScript

function cookbook_createBackup() {
  // Step 1: Define the schema for data validation and valid backup statuses
  const schema = {
    name:  gaslamp.FlameGuards.isString,
    value: gaslamp.FlameGuards.isNumber,
  };

  const validStatuses = ['Success', 'Failed', 'Partial'];

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

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

  if (failed.length > 0) {
    console.warn('Cannot back up: ' + failed.length + ' validation error(s) found');
    console.error(failed.display());
    console.error('→ Fix these rows in the source sheet before backup');

    // Record failed backup attempt in audit log
    recordBackupStatus(workbook, sourceSheet.getName(), 'Failed', failed.length);
    return;
  }

  // Step 3: Create backup timestamp (used for both data and sheet name)
  const now = new Date();
  const timestamp = Utilities.formatDate(now, Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss');
  const backupNameTimestamp = Utilities.formatDate(now, Session.getScriptTimeZone(), 'yyyy-MM-dd-HHmmss');

  // Add backup timestamp metadata to each row
  const withBackupDate = passed.withColumn('backup_date', () => timestamp);

  // Step 4: Create a timestamped backup sheet
  const backupName = `Backup_${backupNameTimestamp}`;
  const backupSheet = workbook.insertSheet(backupName);
  withBackupDate.toSheet(backupSheet);

  const rowCount = passed.length;
  console.log(`Backup created: ${backupName} (${rowCount} rows)`);

  // Step 5: Record backup metadata in the audit log
  recordBackupStatus(workbook, sourceSheet.getName(), 'Success', rowCount, backupName);
}

// Helper function to record backup status in audit log with enum validation
function recordBackupStatus(workbook, sourceSheetName, status, rowCount, backupName = '') {
  const validStatuses = ['Success', 'Failed', 'Partial'];

  // Validate status enum
  if (!validStatuses.includes(status)) {
    console.warn('Invalid backup status: ' + status);
    return;
  }

  const logSheet = workbook.getSheetByName('Backup Log')
    ?? workbook.insertSheet('Backup Log');

  // Create DataFrame for the log entry
  const now = new Date();
  const logEntry = gaslamp.BareFrame.fromArrays([
    ['Timestamp', 'Source Sheet', 'Row Count', 'Backup Name', 'Status'],
    [now, sourceSheetName, rowCount, backupName, status],
  ]);

  // Initialize headers if log sheet is empty, then append entry
  if (logSheet.getLastRow() === 0) {
    logEntry.toSheet(logSheet);
  } else {
    logEntry.toSheet(logSheet, {
      clear: false,
      header: false,
      startRow: logSheet.getLastRow() + 1,
    });
  }
}

Key gaslamp features used:

Schema validation with FlameFrame.from() — only backup valid data
.withColumn() for metadata — add backup timestamp to each row
.toSheet() with options — write DataFrame to new sheet or append to existing
.fromArrays() — construct DataFrame from 2D array for log entries
Readable variable names — withBackupDate, logEntry clearly indicate transformations

Without gaslamp¶

JavaScript

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

  // Step 1: Define valid backup statuses and fetch data
  const validStatuses = ['Success', 'Failed', 'Partial'];

  const range = sourceSheet.getDataRange();
  const values = range.getValues();
  const headers = values[0];
  const rows = values.slice(1);

  const nameIdx = headers.indexOf('name');
  const valueIdx = headers.indexOf('value');

  // Step 2: Validate rows by manually type-checking each value
  const validated = [];
  const invalid = [];
  for (const row of rows) {
    if (typeof row[nameIdx] !== 'string' || typeof row[valueIdx] !== 'number') {
      console.warn('Type error in row:', row);
      invalid.push(row);
      continue;
    }
    validated.push(row);
  }

  if (validated.length === 0) {
    console.warn('Cannot back up: no valid rows');
    recordBackupStatus(workbook, sourceSheet.getName(), 'Failed', invalid.length);
    return;
  }

  // Step 3: Create backup timestamp (used for both data and sheet name)
  const now = new Date();
  const timestamp = Utilities.formatDate(now, Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss');
  const backupNameTimestamp = Utilities.formatDate(now, Session.getScriptTimeZone(), 'yyyy-MM-dd-HHmmss');

  // Add backup timestamp column to each row
  const withBackupDate = validated.map((row) => [...row, timestamp]);

  // Step 4: Create a timestamped backup sheet
  const backupName = `Backup_${backupNameTimestamp}`;
  const backupSheet = workbook.insertSheet(backupName);

  // Add headers with backup_date column
  const headers_with_backup = [...headers, 'backup_date'];
  backupSheet.getRange(1, 1, 1, headers_with_backup.length)
    .setValues([headers_with_backup]);

  // Write backup data (batch write for efficiency)
  backupSheet.getRange(2, 1, withBackupDate.length, withBackupDate[0].length)
    .setValues(withBackupDate);

  const rowCount = validated.length;
  console.log(`Backup created: ${backupName} (${rowCount} rows)`);

  // Step 5: Record backup status in the audit log
  recordBackupStatus(workbook, sourceSheet.getName(), 'Success', rowCount, backupName);
}

// Helper function to record backup status in audit log with enum validation
function recordBackupStatus(workbook, sourceSheetName, status, rowCount, backupName = '') {
  const validStatuses = ['Success', 'Failed', 'Partial'];

  // Validate status enum
  if (!validStatuses.includes(status)) {
    console.warn('Invalid backup status: ' + status);
    return;
  }

  // Initialize or get the audit log sheet
  let logSheet = workbook.getSheetByName('Backup Log');
  if (!logSheet) {
    logSheet = workbook.insertSheet('Backup Log');
    logSheet.getRange(1, 1, 1, 5).setValues([[
      'Timestamp', 'Source Sheet', 'Row Count', 'Backup Name', 'Status',
    ]]);
  }

  // Append an entry to the audit log
  const now = new Date();
  const lastRow = logSheet.getLastRow();
  logSheet.getRange(lastRow + 1, 1, 1, 5).setValues([[
    now, sourceSheetName, rowCount, backupName, status,
  ]]);
}

Without gaslamp, you need to:

Manually define and validate enum constraints (e.g., validStatuses array)
Manually index columns and type-check each value
Manually loop and filter valid rows before backup
Manually validate status enum values before logging
Manually append timestamp to each row with .map()
Manually construct headers with the new backup_date column
Manually construct range dimensions for setValues()
Manually check if log sheet exists and create with headers if needed
Write separate helper functions to avoid code duplication in status logging

Key Techniques¶

Validate before backup: Ensure data integrity with schema validation before creating backup
Timestamp naming: Use ISO format, sanitized for sheet names (e.g., Backup_2024-01-15T10-30-45)
Metadata columns: Add backup_date to track when each row was captured
Batch sheet writes: Separate headers and data rows, write in one operation for efficiency
Audit logging: Maintain a master log with timestamp, source, row count, and status
Check and initialize: Check if log sheet exists; create with headers on first use

See GAS Utilities for scheduling this function with ScriptApp.newTrigger().