Project

Google Apps Script: Spreadsheet Rule Engine for Process Tagging, for a small enterprise

Overview

This project implements a rule-based tagging engine using Google Apps Script that processes spreadsheet records by scanning multiple text columns, detecting prioritized keywords, mapping them to predefined process conditions, and automatically writing the results to target columns.

The goal was to replace manual, error-prone visual checks with a data-driven and reproducible workflow, where business rules are maintained in spreadsheets while execution is fully automated.

Problem

  • Decision-relevant information was scattered across multiple columns (D/E/F/I/J/O)
  • Business rules kept growing and became:
    • Error-prone when handled manually
    • Slow to execute
    • Difficult for new team members to understand
  • A strict rule priority was required:
    once the highest-priority keyword matches, no further checks should be performed

Goal

  1. Scan multiple columns in the homepage sheet for keywords
  2. Evaluate keywords in priority order defined in keyList
  3. Resolve matched keywords into process conditions using ProcessList
  4. Automatically write output values to Q and R columns
  5. Stop processing a row immediately after the first valid match

Design (Data-Driven Rules)

Sheets

  • homepage
    Source data. Relevant text values are distributed across several columns.
  • keyList
    Maps keyword → processName
    • Priority is implicitly controlled by row order
  • ProcessList
    Maps processCondition → output values (B/C)
    • Determines what values should be written once a process is identified

Implementation Highlights

  • Multi-column scanning
    Ensures that relevant signals are detected regardless of where they appear
  • Priority-based matching
    Keywords are evaluated top-down, stopping at the first match
  • Rule externalization
    Business logic is managed in sheets instead of hardcoded values
  • Fail-safe checks
    Script exits gracefully if required sheets are missing

Code (Google Apps Script)

/**
 * Rule-based tagging:
 * - Scan multiple columns in `homepage` for prioritized keywords (top-down in `keyList`)
 * - Resolve keyword -> processName -> processCondition -> (Q, R output values)
 * - Write outputs to homepage columns Q/R in batch
 */
function processConditionsOptimized() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const wsHome = ss.getSheetByName("homepage");
  const wsKey = ss.getSheetByName("keyList");
  const wsProcess = ss.getSheetByName("ProcessList");

  if (!wsHome || !wsKey || !wsProcess) {
    Logger.log("Required sheet not found.");
    return;
  }

  const homeRange = wsHome.getDataRange();
  const homeData = homeRange.getValues(); // includes header row
  const keyData = wsKey.getDataRange().getValues();
  const processData = wsProcess.getDataRange().getValues();

  // Columns to scan in homepage (0-based): D,E,F,I,J,O
  const scanColIdx = [3, 4, 5, 8, 9, 14];

  // Output columns: Q and R (1-based for Range)
  const OUT_Q_COL = 17;
  const OUT_R_COL = 18;

  // Build a lookup: processCondition -> [valueForQ, valueForR]
  // Assumption: ProcessList columns: A=condition, B=Q value, C=R value
  const processLookup = new Map();
  for (let i = 1; i < processData.length; i++) {
    const cond = normalize(processData[i][0]);
    if (!cond) continue;
    processLookup.set(cond, [processData[i][1], processData[i][2]]);
  }

  // Normalize key rules (priority is row order in the sheet)
  // Assumption: keyList columns: A=keyword, B=processName
  const keyRules = [];
  for (let i = 1; i < keyData.length; i++) {
    const keyword = normalize(keyData[i][0]);
    const processName = normalize(keyData[i][1]);
    if (!keyword || !processName) continue;
    keyRules.push({ keyword, processName });
  }

  // Prepare batched outputs for Q/R (exclude header row)
  const outQR = Array.from({ length: Math.max(0, homeData.length - 1) }, () => ["", ""]);

  // Main loop over homepage rows (skip header)
  for (let row = 1; row < homeData.length; row++) {
    const match = findFirstMatchForRow_(homeData[row], scanColIdx, keyRules, processLookup);
    if (match) {
      outQR[row - 1][0] = match[0]; // Q
      outQR[row - 1][1] = match[1]; // R
    }
  }

  // Batch write Q/R results in a single call
  if (outQR.length > 0) {
    wsHome.getRange(2, OUT_Q_COL, outQR.length, 2).setValues(outQR);
  }

  SpreadsheetApp.getUi().alert("Processing completed.");
}

/**
 * Returns [valueForQ, valueForR] for the first match in the row, or null.
 * Priority:
 * 1) scan columns in order (D,E,F,I,J,O)
 * 2) within a cell, apply keyRules top-down (keyList order)
 * 3) resolve processName by checking which processCondition it includes (ProcessList)
 */
function findFirstMatchForRow_(rowValues, scanColIdx, keyRules, processLookup) {
  for (const col of scanColIdx) {
    const cellText = normalize(rowValues[col]);
    if (!cellText) continue;

    for (const rule of keyRules) {
      if (!cellText.includes(rule.keyword)) continue;

      // Resolve processName -> processCondition -> output values
      for (const [cond, out] of processLookup.entries()) {
        if (rule.processName.includes(cond)) return out;
      }
      // If keyword matched but no processCondition matched, continue searching
    }
  }
  return null;
}

/** Normalize values to safe comparable strings. */
function normalize(v) {
  if (v === null || v === undefined) return "";
  return String(v).trim();
}

Impact

  • Replaced manual tagging with a fully automated, repeatable process
  • Reduced operational errors caused by subjective judgment and rule misinterpretation
  • Enabled non-engineers to maintain business rules directly in spreadsheets
  • Encoded priority logic explicitly, ensuring consistent decision-making

Next Improvements

  • Batch write results using setValues() to reduce execution time
  • Support regular expressions for more flexible keyword matching
  • Add execution logs (matched rules, processed rows) for auditing and monitoring