Skip to content
Skip to chapter

Chapter 12

Reliable automations

11 min read

Write Sheets formulas, rules, and rich-text links

Write row-independent R1C1 formulas, idempotent conditional format rules, and multi-link RichTextValue cells to a Google Sheets table.

By the end

Write R1C1 line-total and summary formulas without string-concatenating row numbers, replace managed conditional format rules in priority order without stacking duplicates, and attach multiple validated https:// links to a single cell with RichTextValue.

Chapter navigation

Calculate line totals and a summary row with row-independent R1C1 formulas, highlight rows with ordered conditional format rules that replace prior script rules on rerun, and turn pipe-separated documentation URLs into multi-link RichTextValue cells. Complete Read and write Sheets in batches and Validate, format, and track Sheets edits before starting.

Set up the disposable LineItems workbook

Create an isolated spreadsheet so you can test formula writes, rule replacement, and summary-row cleanup without affecting shared team data:

  1. Create a new Google Sheet and rename its first tab LineItems.

  2. Copy sample.csv below into LineItems!A1, select column A, click Data > Split text to columns, and choose Comma as the separator.

  3. Confirm that LineItems!A1:G5 holds one header row and four item rows (REQ-201 through REQ-204), with column E (Line Total) blank.

  4. Open Extensions > Apps Script and replace Code.gs with the example file below.

  5. In Project Settings, check Show "appsscript.json" manifest file in editor, replace appsscript.json with the included manifest, and save both files.

Spreadsheet.getSheetByName('LineItems') returns null when the tab name does not match, so getLineItemsSheet_ checks the result before calling getRange (see Cannot read properties of null (reading 'getRange')).

sample.csv
Request ID,Item,Quantity,Unit Cost,Line Total,Status,Reference Paths
REQ-201,Laptop dock,6,180,,Approved,https://docs.example.com/specs/req-201|https://docs.example.com/runbooks/dock-setup
REQ-202,Monitor arm,4,95,,Approved,https://docs.example.com/specs/req-202|https://docs.example.com/runbooks/ergonomics
REQ-203,GPU workstation,1,2400,,Blocked,https://docs.example.com/specs/req-203|https://docs.example.com/runbooks/security-review
REQ-204,USB-C cable pack,10,18,,Pending,https://docs.example.com/specs/req-204

Download sample.csv
Code.gs
const SHEET_NAME = 'LineItems';
const HEADERS = [
  'Request ID',
  'Item',
  'Quantity',
  'Unit Cost',
  'Line Total',
  'Status',
  'Reference Paths'
];
const FIRST_DATA_ROW = 2;
const MAX_ITEM_ROWS = 200;
const BUDGET_LIMIT = 1000;
const SUMMARY_LABEL = 'TOTAL';
const ALLOWED_STATUSES = ['Approved', 'Pending', 'Blocked'];
const LINK_LABELS = ['Spec', 'Runbook'];

const REQUEST_ID_COLUMN = 1;
const ITEM_COLUMN = 2;
const QUANTITY_COLUMN = 3;
const UNIT_COST_COLUMN = 4;
const LINE_TOTAL_COLUMN = 5;
const STATUS_COLUMN = 6;
const REFERENCE_COLUMN = 7;

const LINE_TOTAL_R1C1 = '=RC[-2]*RC[-1]';
const SUMMARY_TOTAL_R1C1 = '=SUM(R2C5:R[-1]C5)';
const CURRENCY_FORMAT = '$#,##0.00';
const FORMULA_INJECTION_PREFIX = /^[=+\-@]/;
const HTTPS_URL_PATTERN = /^https:\/\/[a-zA-Z0-9-]+(?:\.[a-zA-Z0-9-]+)+(?::\d+)?(?:\/[^\s<>|"]*)?$/;
const MANAGED_CUSTOM_FORMULAS = [
  '=$F2="Blocked"',
  '=$E2>' + BUDGET_LIMIT,
  '=$F2="Approved"'
];

/**
 * @typedef {{
 *   requestId: string,
 *   urls: string[]
 * }} ValidatedLineItem
 */

/**
 * Validates the LineItems table, writes row-independent R1C1 line-total formulas
 * and a TOTAL summary row, converts Reference Paths into multi-link RichTextValue
 * cells, and replaces this script's ordered conditional format rules idempotently.
 *
 * @returns {void}
 */
function applyFormulasAndFormatting() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = getLineItemsSheet_(spreadsheet);
  const validated = readAndValidateLineItems_(sheet);
  const itemCount = validated.items.length;
  const summaryRow = FIRST_DATA_ROW + itemCount;

  const lineTotalFormulas = validated.items.map(function () {
    return [LINE_TOTAL_R1C1];
  });
  const richTextGrid = validated.items.map(function (item) {
    return [buildReferenceRichText_(item.requestId, item.urls)];
  });

  const dataRange = sheet.getRange(FIRST_DATA_ROW, 1, itemCount, HEADERS.length);
  const lineTotalRange = sheet.getRange(FIRST_DATA_ROW, LINE_TOTAL_COLUMN, itemCount, 1);
  const referenceRange = sheet.getRange(FIRST_DATA_ROW, REFERENCE_COLUMN, itemCount, 1);
  const summaryRowRange = sheet.getRange(summaryRow, 1, 1, HEADERS.length);
  const summaryTotalCell = sheet.getRange(summaryRow, LINE_TOTAL_COLUMN);

  lineTotalRange.setFormulasR1C1(lineTotalFormulas);
  referenceRange.setRichTextValues(richTextGrid);

  summaryRowRange.setValues([[SUMMARY_LABEL, '', '', '', '', '', '']]).setFontWeight('bold');
  summaryTotalCell.setFormulaR1C1(SUMMARY_TOTAL_R1C1);
  sheet
    .getRange(FIRST_DATA_ROW, UNIT_COST_COLUMN, itemCount + 1, 2)
    .setNumberFormat(CURRENCY_FORMAT);

  const ruleCounts = replaceManagedFormatRules_(sheet, dataRange);

  // Flush pending formula writes so getValue() returns the recalculated summary total.
  SpreadsheetApp.flush();
  const evaluatedSummaryTotal = summaryTotalCell.getValue();

  Logger.log(
    'Wrote R1C1 formulas to ' +
      SHEET_NAME +
      '!' +
      lineTotalRange.getA1Notation() +
      ' and summary row ' +
      summaryRow +
      ' (evaluated total: ' +
      evaluatedSummaryTotal +
      ').'
  );
  Logger.log(
    'Built rich-text reference links in ' +
      SHEET_NAME +
      '!' +
      referenceRange.getA1Notation() +
      '.'
  );
  Logger.log(
    'Kept ' +
      ruleCounts.keptCount +
      ' unmanaged conditional format rule(s); replaced ' +
      ruleCounts.replacedCount +
      ' prior script rule(s) with ' +
      ruleCounts.appliedCount +
      ' ordered rule(s) on ' +
      SHEET_NAME +
      '!' +
      dataRange.getA1Notation() +
      '.'
  );
}

/**
 * Read-only check that logs evaluated values alongside A1 and R1C1 formulas
 * in Line Total, plus the current conditional format rule count on LineItems.
 *
 * @returns {void}
 */
function inspectSheetFormulasAndRules() {
  const sheet = getLineItemsSheet_(SpreadsheetApp.getActiveSpreadsheet());
  checkHeaders_(sheet);

  const lastRow = sheet.getLastRow();
  if (lastRow < FIRST_DATA_ROW) {
    throw new Error(SHEET_NAME + ' has no rows below the header row.');
  }

  const rowCount = lastRow - FIRST_DATA_ROW + 1;
  const labels = sheet.getRange(FIRST_DATA_ROW, REQUEST_ID_COLUMN, rowCount, 1).getValues();
  const totalRange = sheet.getRange(FIRST_DATA_ROW, LINE_TOTAL_COLUMN, rowCount, 1);
  const evaluatedValues = totalRange.getValues();
  const a1Formulas = totalRange.getFormulas();
  const r1c1Formulas = totalRange.getFormulasR1C1();
  const rules = sheet.getConditionalFormatRules();

  for (let i = 0; i < rowCount; i += 1) {
    const sheetRow = FIRST_DATA_ROW + i;
    const label = String(labels[i][0]).trim() || '(blank)';
    const value = evaluatedValues[i][0];
    const a1 = a1Formulas[i][0] || '(plain value)';
    const r1c1 = r1c1Formulas[i][0] || '(plain value)';
    Logger.log(
      'Row ' +
        sheetRow +
        ' (' +
        label +
        '): value=' +
        value +
        ' | A1=' +
        a1 +
        ' | R1C1=' +
        r1c1
    );
  }
  Logger.log('Conditional format rules on ' + SHEET_NAME + ': ' + rules.length + '.');
}

/**
 * Removes this script's conditional format rules on LineItems and deletes the
 * TOTAL summary row while leaving item rows intact.
 *
 * @returns {void}
 */
function resetGeneratedFormatting() {
  const sheet = getLineItemsSheet_(SpreadsheetApp.getActiveSpreadsheet());
  checkHeaders_(sheet);

  const existingRules = sheet.getConditionalFormatRules();
  const keptRules = existingRules.filter(function (rule) {
    return !isManagedLineItemRule_(rule);
  });
  sheet.setConditionalFormatRules(keptRules);
  const removedRuleCount = existingRules.length - keptRules.length;

  let removedSummaryRow = false;
  const lastRow = sheet.getLastRow();
  if (lastRow >= FIRST_DATA_ROW) {
    const bottomLabel = String(sheet.getRange(lastRow, REQUEST_ID_COLUMN).getValue()).trim();
    if (bottomLabel === SUMMARY_LABEL) {
      sheet.deleteRow(lastRow);
      removedSummaryRow = true;
    }
  }

  Logger.log(
    'Removed ' +
      removedRuleCount +
      ' script conditional format rule(s) (kept ' +
      keptRules.length +
      ') and ' +
      (removedSummaryRow ? 'deleted the TOTAL summary row.' : 'found no TOTAL summary row.')
  );
}

/**
 * @param {SpreadsheetApp.Spreadsheet | null} spreadsheet
 * @returns {SpreadsheetApp.Sheet}
 */
function getLineItemsSheet_(spreadsheet) {
  if (!spreadsheet) {
    throw new Error('Open this project from its bound Google Sheet with Extensions > Apps Script.');
  }
  const sheet = spreadsheet.getSheetByName(SHEET_NAME);
  if (!sheet) {
    throw new Error(
      SHEET_NAME +
        ' sheet is missing. Rename the first tab ' +
        SHEET_NAME +
        ' and import sample.csv.'
    );
  }
  return sheet;
}

/**
 * @param {SpreadsheetApp.Sheet} sheet
 * @returns {void}
 */
function checkHeaders_(sheet) {
  if (sheet.getLastRow() < 1 || sheet.getLastColumn() < HEADERS.length) {
    throw new Error(
      SHEET_NAME + ' must contain all seven columns: ' + HEADERS.join(', ') + '.'
    );
  }
  const headerRow = sheet.getRange(1, 1, 1, HEADERS.length).getValues()[0];
  const matches = HEADERS.every(function (expected, index) {
    return String(headerRow[index]).trim() === expected;
  });
  if (!matches) {
    throw new Error(SHEET_NAME + ' row 1 must match: ' + HEADERS.join(', ') + '.');
  }
}

/**
 * Reads and validates all line-item rows in memory before any sheet write runs.
 * Recognizes an existing TOTAL summary row so reruns stay idempotent.
 *
 * @param {SpreadsheetApp.Sheet} sheet
 * @returns {{items: ValidatedLineItem[], hasSummaryRow: boolean}}
 */
function readAndValidateLineItems_(sheet) {
  checkHeaders_(sheet);

  const lastRow = sheet.getLastRow();
  if (lastRow < FIRST_DATA_ROW) {
    throw new Error(SHEET_NAME + ' needs at least one line-item row below row 1.');
  }

  const allValues = sheet.getRange(1, 1, lastRow, HEADERS.length).getValues();
  const bottomRow = allValues[allValues.length - 1];
  const hasSummaryRow = String(bottomRow[REQUEST_ID_COLUMN - 1]).trim() === SUMMARY_LABEL;
  const itemCount = hasSummaryRow ? allValues.length - 2 : allValues.length - 1;

  if (itemCount < 1) {
    throw new Error(SHEET_NAME + ' needs at least one line-item row above TOTAL.');
  }
  if (itemCount > MAX_ITEM_ROWS) {
    throw new Error(
      SHEET_NAME +
        ' has ' +
        itemCount +
        ' item rows, exceeding MAX_ITEM_ROWS (' +
        MAX_ITEM_ROWS +
        ').'
    );
  }

  const existingRichTexts = sheet
    .getRange(FIRST_DATA_ROW, REFERENCE_COLUMN, itemCount, 1)
    .getRichTextValues();

  /** @type {ValidatedLineItem[]} */
  const items = [];
  for (let i = 0; i < itemCount; i += 1) {
    const row = allValues[i + 1];
    const sheetRow = FIRST_DATA_ROW + i;
    if (String(row[REQUEST_ID_COLUMN - 1]).trim() === SUMMARY_LABEL) {
      throw new Error(
        'Row ' +
          sheetRow +
          ' is the TOTAL summary row. Insert new items above TOTAL or run resetGeneratedFormatting first.'
      );
    }
    const requestId = validateSafeLabel_(row[REQUEST_ID_COLUMN - 1], 'Request ID', sheetRow);
    validateSafeLabel_(row[ITEM_COLUMN - 1], 'Item', sheetRow);

    const quantity = row[QUANTITY_COLUMN - 1];
    if (typeof quantity !== 'number' || !Number.isFinite(quantity) || quantity < 0) {
      throw new Error('Row ' + sheetRow + ': Quantity must be a non-negative number.');
    }

    const unitCost = row[UNIT_COST_COLUMN - 1];
    if (typeof unitCost !== 'number' || !Number.isFinite(unitCost) || unitCost < 0) {
      throw new Error('Row ' + sheetRow + ': Unit Cost must be a non-negative number.');
    }

    const status = String(row[STATUS_COLUMN - 1]).trim();
    if (ALLOWED_STATUSES.indexOf(status) === -1) {
      throw new Error(
        'Row ' +
          sheetRow +
          ': Status "' +
          status +
          '" must be one of ' +
          ALLOWED_STATUSES.join(', ') +
          '.'
      );
    }

    const urls = resolveRowReferenceUrls_(
      String(row[REFERENCE_COLUMN - 1]),
      existingRichTexts[i][0],
      sheetRow
    );
    items.push({ requestId: requestId, urls: urls });
  }

  return { items: items, hasSummaryRow: hasSummaryRow };
}

/**
 * Rejects blank text and leading characters (=, +, -, @, tab, CR, LF) that
 * Google Sheets can interpret as formulas.
 *
 * @param {unknown} rawValue
 * @param {string} fieldName
 * @param {number} sheetRow
 * @returns {string}
 */
function validateSafeLabel_(rawValue, fieldName, sheetRow) {
  const rawString = String(rawValue);
  if (/^[\t\r\n]/.test(rawString)) {
    throw new Error(
      'Row ' + sheetRow + ': ' + fieldName + ' cannot start with a tab or line break.'
    );
  }
  const trimmed = rawString.trim();
  if (trimmed === '') {
    throw new Error('Row ' + sheetRow + ': ' + fieldName + ' cannot be blank.');
  }
  if (FORMULA_INJECTION_PREFIX.test(trimmed)) {
    throw new Error(
      'Row ' +
        sheetRow +
        ': ' +
        fieldName +
        ' "' +
        trimmed +
        '" cannot start with =, +, -, or @.'
    );
  }
  return trimmed;
}

/**
 * Extracts 1 or 2 validated https:// URLs from either raw pipe-separated text
 * or an existing RichTextValue created by an earlier run.
 *
 * @param {string} rawText
 * @param {SpreadsheetApp.RichTextValue | null} existingRichText
 * @param {number} sheetRow
 * @returns {string[]}
 */
function resolveRowReferenceUrls_(rawText, existingRichText, sheetRow) {
  const trimmed = rawText.trim();
  if (trimmed === '') {
    throw new Error('Row ' + sheetRow + ': Reference Paths cannot be blank.');
  }

  if (existingRichText && trimmed.indexOf('https://') !== 0 && trimmed.indexOf(' (') !== -1) {
    /** @type {string[]} */
    const existingUrls = [];
    const runs = existingRichText.getRuns();
    for (let i = 0; i < runs.length; i += 1) {
      const linkUrl = runs[i].getLinkUrl();
      if (linkUrl !== null) {
        validateHttpsUrl_(linkUrl, sheetRow);
        existingUrls.push(linkUrl);
      }
    }
    if (existingUrls.length > 0 && existingUrls.length <= LINK_LABELS.length) {
      return existingUrls;
    }
  }

  const parts = trimmed.split('|').map(function (part) {
    return part.trim();
  });
  if (parts.length < 1 || parts.length > LINK_LABELS.length) {
    throw new Error(
      'Row ' +
        sheetRow +
        ': Reference Paths must list 1 or ' +
        LINK_LABELS.length +
        ' pipe-separated https:// URLs.'
    );
  }
  parts.forEach(function (url) {
    validateHttpsUrl_(url, sheetRow);
  });
  return parts;
}

/**
 * @param {string} url
 * @param {number} sheetRow
 * @returns {void}
 */
function validateHttpsUrl_(url, sheetRow) {
  if (!HTTPS_URL_PATTERN.test(url)) {
    throw new Error(
      'Row ' + sheetRow + ': Reference URL "' + url + '" must use https:// and a valid host.'
    );
  }
}

/**
 * Builds one RichTextValue such as "REQ-201 (Spec | Runbook)" using 0-based
 * UTF-16 start offsets and exclusive end offsets.
 *
 * @param {string} requestId
 * @param {string[]} urls
 * @returns {SpreadsheetApp.RichTextValue}
 */
function buildReferenceRichText_(requestId, urls) {
  let fullText = requestId + ' (';
  /** @type {Array<{start: number, end: number, url: string}>} */
  const spans = [];

  for (let i = 0; i < urls.length; i += 1) {
    if (i > 0) {
      fullText += ' | ';
    }
    const label = LINK_LABELS[i] || 'Link ' + (i + 1);
    const startOffset = fullText.length;
    fullText += label;
    const endOffset = fullText.length;
    spans.push({
      start: startOffset,
      end: endOffset,
      url: urls[i]
    });
  }
  fullText += ')';

  const builder = SpreadsheetApp.newRichTextValue().setText(fullText);
  for (let i = 0; i < spans.length; i += 1) {
    builder.setLinkUrl(spans[i].start, spans[i].end, spans[i].url);
  }
  return builder.build();
}

/**
 * Builds the three ordered conditional format rules for the item rows:
 * 1. Blocked rows (red fill, dark red bold text)
 * 2. Over budget rows where Line Total > 1000 (amber fill, dark amber text)
 * 3. Approved rows (green fill, dark green text)
 *
 * @param {SpreadsheetApp.Range} dataRange
 * @returns {SpreadsheetApp.ConditionalFormatRule[]}
 */
function buildManagedFormatRules_(dataRange) {
  const blockedRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(MANAGED_CUSTOM_FORMULAS[0])
    .setBackground('#fce8e6')
    .setFontColor('#b3261e')
    .setBold(true)
    .setRanges([dataRange])
    .build();

  const overBudgetRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(MANAGED_CUSTOM_FORMULAS[1])
    .setBackground('#fef7e0')
    .setFontColor('#b06000')
    .setRanges([dataRange])
    .build();

  const approvedRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(MANAGED_CUSTOM_FORMULAS[2])
    .setBackground('#e6f4ea')
    .setFontColor('#137333')
    .setRanges([dataRange])
    .build();

  return [blockedRule, overBudgetRule, approvedRule];
}

/**
 * Replaces conditional format rules targeting the managed LineItems table
 * while preserving any manual rules targeting other columns or ranges.
 *
 * @param {SpreadsheetApp.Sheet} sheet
 * @param {SpreadsheetApp.Range} dataRange
 * @returns {{keptCount: number, replacedCount: number, appliedCount: number}}
 */
function replaceManagedFormatRules_(sheet, dataRange) {
  const existingRules = sheet.getConditionalFormatRules();
  const keptRules = existingRules.filter(function (rule) {
    return !isManagedLineItemRule_(rule);
  });
  const managedRules = buildManagedFormatRules_(dataRange);
  sheet.setConditionalFormatRules(keptRules.concat(managedRules));
  return {
    keptCount: keptRules.length,
    replacedCount: existingRules.length - keptRules.length,
    appliedCount: managedRules.length
  };
}

/**
 * Identifies rules owned by this script: custom-formula rules carrying one of
 * MANAGED_CUSTOM_FORMULAS whose target ranges span columns A:G from row 2 downward.
 *
 * @param {SpreadsheetApp.ConditionalFormatRule} rule
 * @returns {boolean}
 */
function isManagedLineItemRule_(rule) {
  const ranges = rule.getRanges();
  const spansManagedColumns =
    ranges.length > 0 &&
    ranges.every(function (range) {
      return (
        range.getRow() >= FIRST_DATA_ROW &&
        range.getColumn() === 1 &&
        range.getNumColumns() === HEADERS.length
      );
    });
  if (!spansManagedColumns) {
    return false;
  }

  const booleanCondition = rule.getBooleanCondition();
  if (!booleanCondition) {
    return false;
  }
  if (booleanCondition.getCriteriaType() !== SpreadsheetApp.BooleanCriteria.CUSTOM_FORMULA) {
    return false;
  }
  const values = booleanCondition.getCriteriaValues();
  return values.length > 0 && MANAGED_CUSTOM_FORMULAS.indexOf(String(values[0])) !== -1;
}

Download Code.gs
appsscript.json
{
  "timeZone": "Etc/UTC",
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ]
}

Download appsscript.json

The manifest requests https://www.googleapis.com/auth/spreadsheets.currentonly, which restricts access to the bound spreadsheet (Authorization for Google services). The script runs as the signed-in user who clicks Run. Select applyFormulasAndFormatting in the toolbar, click Run, and approve access when prompted.

Verify four changes on LineItems:

  1. Execution log:

    Source
    Wrote R1C1 formulas to LineItems!E2:E5 and summary row 6 (evaluated total: 4040).
    Built rich-text reference links in LineItems!G2:G5.
    Kept 0 unmanaged conditional format rule(s); replaced 0 prior script rule(s) with 3 ordered rule(s) on LineItems!A2:G5.

  2. Calculated totals: E2:E5 displays $1,080.00, $380.00, $2,400.00, and $180.00. Row 6 displays TOTAL in A6 and $4,040.00 in E6.

  3. Clickable reference links: G2:G4 displays REQ-201 (Spec | Runbook) through REQ-203 (Spec | Runbook) with separate links on Spec and Runbook. G5 displays REQ-204 (Spec).

  4. Ordered row highlights: Row 4 (REQ-203) turns red, row 2 (REQ-201) turns amber, row 3 (REQ-202) turns green, and rows 5 and 6 remain unhighlighted.

Protect formulas and write row-independent R1C1 formulas

Range.getValues() evaluates every formula cell to its current value (number, string, boolean, or Date). Reading a multi-column table with getValues(), editing one field in JavaScript, and writing the entire 2D array back with setValues() replaces every formula in that block with a static value.

warning

Two rerun mistakes corrupt automated spreadsheets silently. First, calling Range.setValues() across columns that hold formulas overwrites those formulas with plain cached results. Target setValues() only at plain-data columns, or inspect Range.getFormulas() first. Second, calling rules.push(...) on sheet.getConditionalFormatRules() and passing the array to setConditionalFormatRules(rules) without removing prior script rules appends duplicate rules on every run.

To inspect whether cells hold formulas or static values, pair getValues() with Range.getFormulas() and Range.getFormulasR1C1(). Both formula methods return a string starting with '=' for formula cells and '' for plain value or blank cells. applyFormulasAndFormatting reads A1:G5 for validation, leaves columns A through D and F untouched, and writes formulas only to E2:E5 and E6.

Avoid string-concatenated row numbers with R1C1 notation

Scripts that build A1 formulas in a loop often concatenate row numbers into strings, such as '=C' + row + '*D' + row for line totals and '=SUM(E2:E' + lastItemRow + ')' for a summary row. That pattern forces your code to track column letters and row numbers manually, and a fixed =SUM(E2:E5) formula in E6 misses a new item inserted immediately above row 6.

Range.setFormulasR1C1(formulas) expresses cell coordinates relative to each formula cell or as 1-based sheet coordinates:

  • Square brackets mark relative offsets. In '=RC[-2]*RC[-1]', R without an offset refers to the current row, C[-2] refers to two columns to the left (Quantity), and C[-1] refers to one column to the left (Unit Cost). Every row in lineTotalFormulas receives the exact same string '=RC[-2]*RC[-1]'. Google Sheets stores the formula and displays =C2*D2 in E2, =C3*D3 in E3, and =C5*D5 in E5.

  • Unbracketed integers mark absolute coordinates. In '=SUM(R2C5:R[-1]C5)' on E6, R2C5 anchors the top of the sum to row 2, column 5 ($E$2), while R[-1]C5 points one row above the summary cell in column 5 ($E5). Whether the table holds 4 items or 150 items, the summary cell receives the same constant string.

As with setValues(), the 2D array passed to setFormulasR1C1(formulas) must match the row and column counts of the target Range (4 rows by 1 column for E2:E5), or Apps Script throws a row count mismatch. After writing the summary formula, Code.gs calls SpreadsheetApp.flush() before summaryTotalCell.getValue() so Google Sheets recalculates E2:E6 before returning 4040.

Compare value, A1 formula, and R1C1 formula writes

Choose the Range write method that matches how your coordinates are defined:

Range methodsInput formatHow Google Sheets interprets coordinatesBest fit
setValue(value) and setValues(values)Typed primitives (number, boolean, Date, string) in a single cell or 2D arrayStrings starting with '=' or '+' parse as A1 formulas unless escaped with a leading apostrophe ('). Other values store as literals.Writing plain data columns, timestamps, or labels such as TOTAL
setFormula(formula) and setFormulas(formulas)Formula strings in A1 notation ('=C2*D2', '=SUM(E2:E5)')Column letters and row numbers (C2), where $ locks absolute axes ($E$2). On a multi-cell range, setFormula shifts relative references from the top-left cell.Named-range formulas (=SUM(LineTotals)) or fixed single-cell formulas
setFormulaR1C1(formula) and setFormulasR1C1(formulas)Formula strings in R1C1 notation ('=RC[-2]*RC[-1]', '=SUM(R2C5:R[-1]C5)')R[offset]C[offset] is relative to each cell, and R2C5 without brackets is absolute ($E$2).Column-offset row formulas and summary rows that should not concatenate row numbers
note

In Apps Script, setFormula, setFormulas, setFormulaR1C1, setFormulasR1C1, and whenFormulaSatisfied always expect English function names (such as SUM, IF, and AND) and comma argument separators (,), even when the spreadsheet's File > Settings > Locale uses semicolons (;) and translated function names in the formula bar. Google Sheets translates the stored formula into each collaborator's display locale automatically.

Manage ConditionalFormatRule pipelines idempotently

Sheet.getConditionalFormatRules() returns an immutable snapshot array of ConditionalFormatRule objects. Calling builder methods or mutating that JavaScript array in memory does not change the sheet until you pass the replacement array to Sheet.setConditionalFormatRules(rules).

Because setConditionalFormatRules(rules) replaces the sheet's entire rule list, an idempotent pipeline follows three steps in replaceManagedFormatRules_:

  1. Read the current snapshot with const existingRules = sheet.getConditionalFormatRules().

  2. Filter out rules owned by this script (!isManagedLineItemRule_(rule)), keeping manual rules that collaborators created on individual columns or notes columns outside A:G.

  3. Build fresh ConditionalFormatRule objects with SpreadsheetApp.newConditionalFormatRule() and ConditionalFormatRuleBuilder, then call sheet.setConditionalFormatRules(keptRules.concat(managedRules)).

isManagedLineItemRule_ requires both conditions together: every target range on the rule must start at or below row 2 and span columns A through G (getColumn() === 1 && getNumColumns() === HEADERS.length), and its boolean condition must be a custom formula in MANAGED_CUSTOM_FORMULAS. That keeps manual rules on individual columns or notes columns outside A:G untouched even when a collaborator reuses a formula such as =$F2="Blocked", while still replacing prior script rules when dataRange grows from A2:G5 to A2:G6.

How rule order decides which color wins

Google Sheets evaluates conditional format rules from index 0 upward, and the first matching rule controls the formatting properties it sets (Use conditional formatting rules in Google Sheets). buildManagedFormatRules_ returns three custom-formula rules on dataRange (A2:G5) in priority order:

  1. Blocked (=$F2="Blocked"): light red fill (#fce8e6), dark red bold text (#b3261e).

  2. Over budget (=$E2>1000): light amber fill (#fef7e0), dark amber text (#b06000).

  3. Approved (=$F2="Approved"): light green fill (#e6f4ea), dark green text (#137333).

Each custom formula is written relative to A2, the top-left cell of A2:G5, with $F2 and $E2 locking columns F (Status) and E (Line Total) across all seven columns in each row. Compare how the four sample rows evaluate against that order:

  • Row 4 (REQ-203, Blocked, $2,400.00): satisfies both rule 1 (Blocked) and rule 2 ($E2>1000). Rule 1 comes first, so row 4 renders in bold red.

  • Row 2 (REQ-201, Approved, $1,080.00): satisfies both rule 2 ($E2>1000) and rule 3 (Approved). Because Over budget precedes Approved, row 2 renders in amber rather than green so an over-budget approval is never hidden behind a green highlight. $E2>1000 evaluates the numeric result of the =RC[-2]*RC[-1] formula in E2.

  • Row 3 (REQ-202, Approved, $380.00): fails rules 1 and 2, satisfies rule 3, and renders in green.

  • Row 5 (REQ-204, Pending, $180.00): satisfies none of the three rules and keeps its default background.

  • Row 6 (TOTAL, $4,040.00): sits below dataRange (A2:G5), so the $4,040.00 grand total is not flagged as over budget.

Attaching links with =HYPERLINK("https://...", "Label") turns the cell into a formula. That approach has two limitations: any later getValues() and setValues() pass strips the link down to its plain label, and =HYPERLINK(...) can bind only one URL to the entire cell.

SpreadsheetApp.newRichTextValue() returns a RichTextValueBuilder that stores plain text plus per-substring link runs without creating a formula. buildReferenceRichText_ attaches one or two links inside a single cell such as 'REQ-201 (Spec | Runbook)':

  1. Assemble the complete display string fullText first while recording { start, end, url } for each label (Spec and Runbook).

  2. Call builder.setText(fullText) before calling setLinkUrl. In RichTextValueBuilder, setText resets existing text styles and link runs on the builder, so calling setText second erases links added earlier.

  3. Call builder.setLinkUrl(span.start, span.end, span.url) for each recorded span, then call builder.build() to produce an immutable RichTextValue.

  4. Collect all rows into richTextGrid (N rows by 1 column) and write the column in one batch call with Range.setRichTextValues(richTextGrid).

tip

RichTextValueBuilder.setLinkUrl(startOffset, endOffset, linkUrl) uses 0-based UTF-16 code-unit offsets where startOffset is inclusive and endOffset is exclusive, matching fullText.slice(startOffset, endOffset) (RichTextValueBuilder reference). In 'REQ-201 (Spec | Runbook)' (length 24), 'Spec' spans 9 to 13 and 'Runbook' spans 16 to 23. Passing startOffset < 0, startOffset >= endOffset, or endOffset > fullText.length throws an exception. Computing startOffset = fullText.length immediately before appending label and endOffset = fullText.length immediately after keeps offsets exact even when requestId changes length or contains characters encoded as UTF-16 surrogate pairs.

Guard URLs and cell text against formula injection

Even though setRichTextValues stores text rather than formulas, untrusted spreadsheet strings and URLs still require two input checks before building cells:

  • Allowlist https:// URLs. validateHttpsUrl_ tests every URL against HTTPS_URL_PATTERN, requiring an https:// scheme, a valid hostname, and no whitespace, pipe characters, or quotes. That check rejects http://, javascript:, data:, and malformed strings before setLinkUrl runs.

  • Reject formula-triggering prefixes. validateSafeLabel_ rejects leading tabs, line breaks, and the characters =, +, -, and @ on Request ID and Item. Google Sheets parses strings starting with =, +, or - as formulas when written with setValues(), and spreadsheet CSV exports opened in desktop spreadsheet programs also evaluate @. When a workflow must display arbitrary external text rather than rejecting it, prefix strings matching /^[=+\-@\t\r\n]/ with a leading apostrophe (') before passing them to setValues().

To keep reruns idempotent after G2:G5 changes from raw pipe-separated URLs to 'REQ-201 (Spec | Runbook)', readAndValidateLineItems_ calls getRichTextValues() once on G2:G5. When a cell already holds formatted label text from a prior run, resolveRowReferenceUrls_ reads existingRichText.getRuns(), extracts each non-null run.getLinkUrl(), re-validates every URL with validateHttpsUrl_, and rebuilds the RichTextValue.

Inspect formulas and clean up

Test formula inspection, rerun idempotency, and cleanup on your practice sheet:

  1. Compare values, A1 formulas, and R1C1 formulas. Select inspectSheetFormulasAndRules and click Run. Check the Execution log:

    Source
    Row 2 (REQ-201): value=1080 | A1==C2*D2 | R1C1==RC[-2]*RC[-1]
    Row 3 (REQ-202): value=380 | A1==C3*D3 | R1C1==RC[-2]*RC[-1]
    Row 4 (REQ-203): value=2400 | A1==C4*D4 | R1C1==RC[-2]*RC[-1]
    Row 5 (REQ-204): value=180 | A1==C5*D5 | R1C1==RC[-2]*RC[-1]
    Row 6 (TOTAL): value=4040 | A1==SUM($E$2:$E5) | R1C1==SUM(R2C5:R[-1]C5)
    Conditional format rules on LineItems: 3.

  2. Verify idempotent reruns. Run applyFormulasAndFormatting a second time. Confirm that row 6 remains the only TOTAL row and the Execution log reports replaced 3 prior script rule(s) with 3 ordered rule(s).

  3. Reset formatting and summary row. Select resetGeneratedFormatting and click Run. Confirm that row 6 (TOTAL) is deleted, Format > Conditional formatting lists no rules on LineItems, and the four item rows in A2:G5 remain intact.

Common failures

  • TypeError: Cannot read properties of null (reading 'getRange') or LineItems sheet is missing. The active spreadsheet has no tab named LineItems. Rename the tab to LineItems and guard getSheetByName results before calling Sheet methods (null reading getRange).

  • Exception: The number of rows in the data does not match the number of rows in the range. The outer length of lineTotalFormulas or richTextGrid did not match itemCount on getRange(FIRST_DATA_ROW, column, itemCount, 1) (row count mismatch).

  • Row N is the TOTAL summary row. A new item was pasted below TOTAL instead of above it. Insert new item rows above TOTAL, or run resetGeneratedFormatting before adding rows at the bottom.

  • Exception: Illegal argument on setLinkUrl. startOffset was negative, equal to endOffset, or greater than fullText.length, or setText was called after setLinkUrl. Build fullText completely and call setText(fullText) first.

  • Duplicate rules appear in Conditional formatting. A script pushed onto sheet.getConditionalFormatRules() without filtering out prior rules on its managed range. Replace matching entries before calling sheet.setConditionalFormatRules(nextRules).

  • Formula shows #ERROR! after setFormula on a European-locale sheet. The script passed semicolons (=SUM(E2;E5)) or a localized function name (=SUMME(...)). Use English function names and commas in Apps Script calls regardless of spreadsheet locale.

Exercise

Extend Code.gs to support an optional third reference link (Changelog) and test how rule order changes row 2 (REQ-201):

  1. Change LINK_LABELS to ['Spec', 'Runbook', 'Changelog']. On row 2 (REQ-201), paste https://docs.example.com/specs/req-201|https://docs.example.com/runbooks/dock-setup|https://docs.example.com/changelogs/req-201 into LineItems!G2.

  2. Run applyFormulasAndFormatting and confirm G2 displays REQ-201 (Spec | Runbook | Changelog) with three independent clickable links.

  3. In buildManagedFormatRules_, swap approvedRule ahead of overBudgetRule (return [blockedRule, approvedRule, overBudgetRule];) and run applyFormulasAndFormatting again.

  4. Confirm that row 2 (REQ-201, $1,080.00) turns green while row 4 (REQ-203, $2,400.00) stays red, and inspectSheetFormulasAndRules still reports 3 rules.

  5. Restore [blockedRule, overBudgetRule, approvedRule], run resetGeneratedFormatting, and move the disposable spreadsheet to Trash when finished.

Next, read Named ranges, filters, and pivots.

Project files

Complete source files for this chapter's examples.

Write R1C1 formulas, rich-text reference links, and ordered conditional formats

Code.gs
const SHEET_NAME = 'LineItems';
const HEADERS = [
  'Request ID',
  'Item',
  'Quantity',
  'Unit Cost',
  'Line Total',
  'Status',
  'Reference Paths'
];
const FIRST_DATA_ROW = 2;
const MAX_ITEM_ROWS = 200;
const BUDGET_LIMIT = 1000;
const SUMMARY_LABEL = 'TOTAL';
const ALLOWED_STATUSES = ['Approved', 'Pending', 'Blocked'];
const LINK_LABELS = ['Spec', 'Runbook'];

const REQUEST_ID_COLUMN = 1;
const ITEM_COLUMN = 2;
const QUANTITY_COLUMN = 3;
const UNIT_COST_COLUMN = 4;
const LINE_TOTAL_COLUMN = 5;
const STATUS_COLUMN = 6;
const REFERENCE_COLUMN = 7;

const LINE_TOTAL_R1C1 = '=RC[-2]*RC[-1]';
const SUMMARY_TOTAL_R1C1 = '=SUM(R2C5:R[-1]C5)';
const CURRENCY_FORMAT = '$#,##0.00';
const FORMULA_INJECTION_PREFIX = /^[=+\-@]/;
const HTTPS_URL_PATTERN = /^https:\/\/[a-zA-Z0-9-]+(?:\.[a-zA-Z0-9-]+)+(?::\d+)?(?:\/[^\s<>|"]*)?$/;
const MANAGED_CUSTOM_FORMULAS = [
  '=$F2="Blocked"',
  '=$E2>' + BUDGET_LIMIT,
  '=$F2="Approved"'
];

/**
 * @typedef {{
 *   requestId: string,
 *   urls: string[]
 * }} ValidatedLineItem
 */

/**
 * Validates the LineItems table, writes row-independent R1C1 line-total formulas
 * and a TOTAL summary row, converts Reference Paths into multi-link RichTextValue
 * cells, and replaces this script's ordered conditional format rules idempotently.
 *
 * @returns {void}
 */
function applyFormulasAndFormatting() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = getLineItemsSheet_(spreadsheet);
  const validated = readAndValidateLineItems_(sheet);
  const itemCount = validated.items.length;
  const summaryRow = FIRST_DATA_ROW + itemCount;

  const lineTotalFormulas = validated.items.map(function () {
    return [LINE_TOTAL_R1C1];
  });
  const richTextGrid = validated.items.map(function (item) {
    return [buildReferenceRichText_(item.requestId, item.urls)];
  });

  const dataRange = sheet.getRange(FIRST_DATA_ROW, 1, itemCount, HEADERS.length);
  const lineTotalRange = sheet.getRange(FIRST_DATA_ROW, LINE_TOTAL_COLUMN, itemCount, 1);
  const referenceRange = sheet.getRange(FIRST_DATA_ROW, REFERENCE_COLUMN, itemCount, 1);
  const summaryRowRange = sheet.getRange(summaryRow, 1, 1, HEADERS.length);
  const summaryTotalCell = sheet.getRange(summaryRow, LINE_TOTAL_COLUMN);

  lineTotalRange.setFormulasR1C1(lineTotalFormulas);
  referenceRange.setRichTextValues(richTextGrid);

  summaryRowRange.setValues([[SUMMARY_LABEL, '', '', '', '', '', '']]).setFontWeight('bold');
  summaryTotalCell.setFormulaR1C1(SUMMARY_TOTAL_R1C1);
  sheet
    .getRange(FIRST_DATA_ROW, UNIT_COST_COLUMN, itemCount + 1, 2)
    .setNumberFormat(CURRENCY_FORMAT);

  const ruleCounts = replaceManagedFormatRules_(sheet, dataRange);

  // Flush pending formula writes so getValue() returns the recalculated summary total.
  SpreadsheetApp.flush();
  const evaluatedSummaryTotal = summaryTotalCell.getValue();

  Logger.log(
    'Wrote R1C1 formulas to ' +
      SHEET_NAME +
      '!' +
      lineTotalRange.getA1Notation() +
      ' and summary row ' +
      summaryRow +
      ' (evaluated total: ' +
      evaluatedSummaryTotal +
      ').'
  );
  Logger.log(
    'Built rich-text reference links in ' +
      SHEET_NAME +
      '!' +
      referenceRange.getA1Notation() +
      '.'
  );
  Logger.log(
    'Kept ' +
      ruleCounts.keptCount +
      ' unmanaged conditional format rule(s); replaced ' +
      ruleCounts.replacedCount +
      ' prior script rule(s) with ' +
      ruleCounts.appliedCount +
      ' ordered rule(s) on ' +
      SHEET_NAME +
      '!' +
      dataRange.getA1Notation() +
      '.'
  );
}

/**
 * Read-only check that logs evaluated values alongside A1 and R1C1 formulas
 * in Line Total, plus the current conditional format rule count on LineItems.
 *
 * @returns {void}
 */
function inspectSheetFormulasAndRules() {
  const sheet = getLineItemsSheet_(SpreadsheetApp.getActiveSpreadsheet());
  checkHeaders_(sheet);

  const lastRow = sheet.getLastRow();
  if (lastRow < FIRST_DATA_ROW) {
    throw new Error(SHEET_NAME + ' has no rows below the header row.');
  }

  const rowCount = lastRow - FIRST_DATA_ROW + 1;
  const labels = sheet.getRange(FIRST_DATA_ROW, REQUEST_ID_COLUMN, rowCount, 1).getValues();
  const totalRange = sheet.getRange(FIRST_DATA_ROW, LINE_TOTAL_COLUMN, rowCount, 1);
  const evaluatedValues = totalRange.getValues();
  const a1Formulas = totalRange.getFormulas();
  const r1c1Formulas = totalRange.getFormulasR1C1();
  const rules = sheet.getConditionalFormatRules();

  for (let i = 0; i < rowCount; i += 1) {
    const sheetRow = FIRST_DATA_ROW + i;
    const label = String(labels[i][0]).trim() || '(blank)';
    const value = evaluatedValues[i][0];
    const a1 = a1Formulas[i][0] || '(plain value)';
    const r1c1 = r1c1Formulas[i][0] || '(plain value)';
    Logger.log(
      'Row ' +
        sheetRow +
        ' (' +
        label +
        '): value=' +
        value +
        ' | A1=' +
        a1 +
        ' | R1C1=' +
        r1c1
    );
  }
  Logger.log('Conditional format rules on ' + SHEET_NAME + ': ' + rules.length + '.');
}

/**
 * Removes this script's conditional format rules on LineItems and deletes the
 * TOTAL summary row while leaving item rows intact.
 *
 * @returns {void}
 */
function resetGeneratedFormatting() {
  const sheet = getLineItemsSheet_(SpreadsheetApp.getActiveSpreadsheet());
  checkHeaders_(sheet);

  const existingRules = sheet.getConditionalFormatRules();
  const keptRules = existingRules.filter(function (rule) {
    return !isManagedLineItemRule_(rule);
  });
  sheet.setConditionalFormatRules(keptRules);
  const removedRuleCount = existingRules.length - keptRules.length;

  let removedSummaryRow = false;
  const lastRow = sheet.getLastRow();
  if (lastRow >= FIRST_DATA_ROW) {
    const bottomLabel = String(sheet.getRange(lastRow, REQUEST_ID_COLUMN).getValue()).trim();
    if (bottomLabel === SUMMARY_LABEL) {
      sheet.deleteRow(lastRow);
      removedSummaryRow = true;
    }
  }

  Logger.log(
    'Removed ' +
      removedRuleCount +
      ' script conditional format rule(s) (kept ' +
      keptRules.length +
      ') and ' +
      (removedSummaryRow ? 'deleted the TOTAL summary row.' : 'found no TOTAL summary row.')
  );
}

/**
 * @param {SpreadsheetApp.Spreadsheet | null} spreadsheet
 * @returns {SpreadsheetApp.Sheet}
 */
function getLineItemsSheet_(spreadsheet) {
  if (!spreadsheet) {
    throw new Error('Open this project from its bound Google Sheet with Extensions > Apps Script.');
  }
  const sheet = spreadsheet.getSheetByName(SHEET_NAME);
  if (!sheet) {
    throw new Error(
      SHEET_NAME +
        ' sheet is missing. Rename the first tab ' +
        SHEET_NAME +
        ' and import sample.csv.'
    );
  }
  return sheet;
}

/**
 * @param {SpreadsheetApp.Sheet} sheet
 * @returns {void}
 */
function checkHeaders_(sheet) {
  if (sheet.getLastRow() < 1 || sheet.getLastColumn() < HEADERS.length) {
    throw new Error(
      SHEET_NAME + ' must contain all seven columns: ' + HEADERS.join(', ') + '.'
    );
  }
  const headerRow = sheet.getRange(1, 1, 1, HEADERS.length).getValues()[0];
  const matches = HEADERS.every(function (expected, index) {
    return String(headerRow[index]).trim() === expected;
  });
  if (!matches) {
    throw new Error(SHEET_NAME + ' row 1 must match: ' + HEADERS.join(', ') + '.');
  }
}

/**
 * Reads and validates all line-item rows in memory before any sheet write runs.
 * Recognizes an existing TOTAL summary row so reruns stay idempotent.
 *
 * @param {SpreadsheetApp.Sheet} sheet
 * @returns {{items: ValidatedLineItem[], hasSummaryRow: boolean}}
 */
function readAndValidateLineItems_(sheet) {
  checkHeaders_(sheet);

  const lastRow = sheet.getLastRow();
  if (lastRow < FIRST_DATA_ROW) {
    throw new Error(SHEET_NAME + ' needs at least one line-item row below row 1.');
  }

  const allValues = sheet.getRange(1, 1, lastRow, HEADERS.length).getValues();
  const bottomRow = allValues[allValues.length - 1];
  const hasSummaryRow = String(bottomRow[REQUEST_ID_COLUMN - 1]).trim() === SUMMARY_LABEL;
  const itemCount = hasSummaryRow ? allValues.length - 2 : allValues.length - 1;

  if (itemCount < 1) {
    throw new Error(SHEET_NAME + ' needs at least one line-item row above TOTAL.');
  }
  if (itemCount > MAX_ITEM_ROWS) {
    throw new Error(
      SHEET_NAME +
        ' has ' +
        itemCount +
        ' item rows, exceeding MAX_ITEM_ROWS (' +
        MAX_ITEM_ROWS +
        ').'
    );
  }

  const existingRichTexts = sheet
    .getRange(FIRST_DATA_ROW, REFERENCE_COLUMN, itemCount, 1)
    .getRichTextValues();

  /** @type {ValidatedLineItem[]} */
  const items = [];
  for (let i = 0; i < itemCount; i += 1) {
    const row = allValues[i + 1];
    const sheetRow = FIRST_DATA_ROW + i;
    if (String(row[REQUEST_ID_COLUMN - 1]).trim() === SUMMARY_LABEL) {
      throw new Error(
        'Row ' +
          sheetRow +
          ' is the TOTAL summary row. Insert new items above TOTAL or run resetGeneratedFormatting first.'
      );
    }
    const requestId = validateSafeLabel_(row[REQUEST_ID_COLUMN - 1], 'Request ID', sheetRow);
    validateSafeLabel_(row[ITEM_COLUMN - 1], 'Item', sheetRow);

    const quantity = row[QUANTITY_COLUMN - 1];
    if (typeof quantity !== 'number' || !Number.isFinite(quantity) || quantity < 0) {
      throw new Error('Row ' + sheetRow + ': Quantity must be a non-negative number.');
    }

    const unitCost = row[UNIT_COST_COLUMN - 1];
    if (typeof unitCost !== 'number' || !Number.isFinite(unitCost) || unitCost < 0) {
      throw new Error('Row ' + sheetRow + ': Unit Cost must be a non-negative number.');
    }

    const status = String(row[STATUS_COLUMN - 1]).trim();
    if (ALLOWED_STATUSES.indexOf(status) === -1) {
      throw new Error(
        'Row ' +
          sheetRow +
          ': Status "' +
          status +
          '" must be one of ' +
          ALLOWED_STATUSES.join(', ') +
          '.'
      );
    }

    const urls = resolveRowReferenceUrls_(
      String(row[REFERENCE_COLUMN - 1]),
      existingRichTexts[i][0],
      sheetRow
    );
    items.push({ requestId: requestId, urls: urls });
  }

  return { items: items, hasSummaryRow: hasSummaryRow };
}

/**
 * Rejects blank text and leading characters (=, +, -, @, tab, CR, LF) that
 * Google Sheets can interpret as formulas.
 *
 * @param {unknown} rawValue
 * @param {string} fieldName
 * @param {number} sheetRow
 * @returns {string}
 */
function validateSafeLabel_(rawValue, fieldName, sheetRow) {
  const rawString = String(rawValue);
  if (/^[\t\r\n]/.test(rawString)) {
    throw new Error(
      'Row ' + sheetRow + ': ' + fieldName + ' cannot start with a tab or line break.'
    );
  }
  const trimmed = rawString.trim();
  if (trimmed === '') {
    throw new Error('Row ' + sheetRow + ': ' + fieldName + ' cannot be blank.');
  }
  if (FORMULA_INJECTION_PREFIX.test(trimmed)) {
    throw new Error(
      'Row ' +
        sheetRow +
        ': ' +
        fieldName +
        ' "' +
        trimmed +
        '" cannot start with =, +, -, or @.'
    );
  }
  return trimmed;
}

/**
 * Extracts 1 or 2 validated https:// URLs from either raw pipe-separated text
 * or an existing RichTextValue created by an earlier run.
 *
 * @param {string} rawText
 * @param {SpreadsheetApp.RichTextValue | null} existingRichText
 * @param {number} sheetRow
 * @returns {string[]}
 */
function resolveRowReferenceUrls_(rawText, existingRichText, sheetRow) {
  const trimmed = rawText.trim();
  if (trimmed === '') {
    throw new Error('Row ' + sheetRow + ': Reference Paths cannot be blank.');
  }

  if (existingRichText && trimmed.indexOf('https://') !== 0 && trimmed.indexOf(' (') !== -1) {
    /** @type {string[]} */
    const existingUrls = [];
    const runs = existingRichText.getRuns();
    for (let i = 0; i < runs.length; i += 1) {
      const linkUrl = runs[i].getLinkUrl();
      if (linkUrl !== null) {
        validateHttpsUrl_(linkUrl, sheetRow);
        existingUrls.push(linkUrl);
      }
    }
    if (existingUrls.length > 0 && existingUrls.length <= LINK_LABELS.length) {
      return existingUrls;
    }
  }

  const parts = trimmed.split('|').map(function (part) {
    return part.trim();
  });
  if (parts.length < 1 || parts.length > LINK_LABELS.length) {
    throw new Error(
      'Row ' +
        sheetRow +
        ': Reference Paths must list 1 or ' +
        LINK_LABELS.length +
        ' pipe-separated https:// URLs.'
    );
  }
  parts.forEach(function (url) {
    validateHttpsUrl_(url, sheetRow);
  });
  return parts;
}

/**
 * @param {string} url
 * @param {number} sheetRow
 * @returns {void}
 */
function validateHttpsUrl_(url, sheetRow) {
  if (!HTTPS_URL_PATTERN.test(url)) {
    throw new Error(
      'Row ' + sheetRow + ': Reference URL "' + url + '" must use https:// and a valid host.'
    );
  }
}

/**
 * Builds one RichTextValue such as "REQ-201 (Spec | Runbook)" using 0-based
 * UTF-16 start offsets and exclusive end offsets.
 *
 * @param {string} requestId
 * @param {string[]} urls
 * @returns {SpreadsheetApp.RichTextValue}
 */
function buildReferenceRichText_(requestId, urls) {
  let fullText = requestId + ' (';
  /** @type {Array<{start: number, end: number, url: string}>} */
  const spans = [];

  for (let i = 0; i < urls.length; i += 1) {
    if (i > 0) {
      fullText += ' | ';
    }
    const label = LINK_LABELS[i] || 'Link ' + (i + 1);
    const startOffset = fullText.length;
    fullText += label;
    const endOffset = fullText.length;
    spans.push({
      start: startOffset,
      end: endOffset,
      url: urls[i]
    });
  }
  fullText += ')';

  const builder = SpreadsheetApp.newRichTextValue().setText(fullText);
  for (let i = 0; i < spans.length; i += 1) {
    builder.setLinkUrl(spans[i].start, spans[i].end, spans[i].url);
  }
  return builder.build();
}

/**
 * Builds the three ordered conditional format rules for the item rows:
 * 1. Blocked rows (red fill, dark red bold text)
 * 2. Over budget rows where Line Total > 1000 (amber fill, dark amber text)
 * 3. Approved rows (green fill, dark green text)
 *
 * @param {SpreadsheetApp.Range} dataRange
 * @returns {SpreadsheetApp.ConditionalFormatRule[]}
 */
function buildManagedFormatRules_(dataRange) {
  const blockedRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(MANAGED_CUSTOM_FORMULAS[0])
    .setBackground('#fce8e6')
    .setFontColor('#b3261e')
    .setBold(true)
    .setRanges([dataRange])
    .build();

  const overBudgetRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(MANAGED_CUSTOM_FORMULAS[1])
    .setBackground('#fef7e0')
    .setFontColor('#b06000')
    .setRanges([dataRange])
    .build();

  const approvedRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(MANAGED_CUSTOM_FORMULAS[2])
    .setBackground('#e6f4ea')
    .setFontColor('#137333')
    .setRanges([dataRange])
    .build();

  return [blockedRule, overBudgetRule, approvedRule];
}

/**
 * Replaces conditional format rules targeting the managed LineItems table
 * while preserving any manual rules targeting other columns or ranges.
 *
 * @param {SpreadsheetApp.Sheet} sheet
 * @param {SpreadsheetApp.Range} dataRange
 * @returns {{keptCount: number, replacedCount: number, appliedCount: number}}
 */
function replaceManagedFormatRules_(sheet, dataRange) {
  const existingRules = sheet.getConditionalFormatRules();
  const keptRules = existingRules.filter(function (rule) {
    return !isManagedLineItemRule_(rule);
  });
  const managedRules = buildManagedFormatRules_(dataRange);
  sheet.setConditionalFormatRules(keptRules.concat(managedRules));
  return {
    keptCount: keptRules.length,
    replacedCount: existingRules.length - keptRules.length,
    appliedCount: managedRules.length
  };
}

/**
 * Identifies rules owned by this script: custom-formula rules carrying one of
 * MANAGED_CUSTOM_FORMULAS whose target ranges span columns A:G from row 2 downward.
 *
 * @param {SpreadsheetApp.ConditionalFormatRule} rule
 * @returns {boolean}
 */
function isManagedLineItemRule_(rule) {
  const ranges = rule.getRanges();
  const spansManagedColumns =
    ranges.length > 0 &&
    ranges.every(function (range) {
      return (
        range.getRow() >= FIRST_DATA_ROW &&
        range.getColumn() === 1 &&
        range.getNumColumns() === HEADERS.length
      );
    });
  if (!spansManagedColumns) {
    return false;
  }

  const booleanCondition = rule.getBooleanCondition();
  if (!booleanCondition) {
    return false;
  }
  if (booleanCondition.getCriteriaType() !== SpreadsheetApp.BooleanCriteria.CUSTOM_FORMULA) {
    return false;
  }
  const values = booleanCondition.getCriteriaValues();
  return values.length > 0 && MANAGED_CUSTOM_FORMULAS.indexOf(String(values[0])) !== -1;
}

appsscript.json

Download file
appsscript.json
{
  "timeZone": "Etc/UTC",
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ]
}

sample.csv

Download file
sample.csv
Request ID,Item,Quantity,Unit Cost,Line Total,Status,Reference Paths
REQ-201,Laptop dock,6,180,,Approved,https://docs.example.com/specs/req-201|https://docs.example.com/runbooks/dock-setup
REQ-202,Monitor arm,4,95,,Approved,https://docs.example.com/specs/req-202|https://docs.example.com/runbooks/ergonomics
REQ-203,GPU workstation,1,2400,,Blocked,https://docs.example.com/specs/req-203|https://docs.example.com/runbooks/security-review
REQ-204,USB-C cable pack,10,18,,Pending,https://docs.example.com/specs/req-204