GAS Abstraction Layer

Intro

Creating abstraction layers in a system built on top of Google Sheets.

“I say what I want, not how Sheets does it.”

Abstraction Layer

Wrap raw APIs like

• SpreadsheetApp.getRange()
• getUi()
• Logger.log()

into:

• debugNotify()
• applyStatusRules()
• etc

SharedUtils

// =========================================================
// 📊 Data utilities
// =========================================================
 
/**
 * Builds a header dictionary mapping column names → column index.
 */
function getHeaderDict(sheet, headerRow = 1, debug = false) {
  const lastCol = sheet.getRange(headerRow, 1, 1, sheet.getMaxColumns())
    .getValues()[0]
    .reduceRight((acc, val, i) => {
      if (acc !== -1) return acc;
      return val ? i + 1 : -1;
    }, -1);
 
  const headers = sheet.getRange(headerRow, 1, 1, lastCol).getValues()[0];
 
  const headerDict = {};
  const issues = [];
 
  Logger.log(`===== HEADER DUMP: ${sheet.getName()} (row ${headerRow}) =====`);
 
  headers.forEach((h, i) => {
    const col = i + 1;
 
    Logger.log(`${col}: ${JSON.stringify(h)}`);
 
    if (!h) {
      issues.push(`Empty header at column ${col}`);
      return;
    }
 
    const key = h.toString().trim();
 
    if (!key) {
      issues.push(`Whitespace-only header at column ${col}`);
      return;
    }
 
    if (headerDict[key]) {
      issues.push(`Duplicate header: "${key}"`);
    }
 
    headerDict[key] = col;
  });
 
  if (debug && issues.length > 0) {
    try {
      SpreadsheetApp.getUi().alert(
        `⚠️ Header issues in "${sheet.getName()}" (row ${headerRow}):\n\n` +
        issues.join("\n")
      );
    } catch (e) {
      Logger.log("UI alert failed.");
    }
  }
 
  return headerDict;
}
 
 
// =========================================================
// 📊 Data utilities
// =========================================================
 
/**
 * Finds last non-empty row in a column.
 */
function getLastDataRowByColumn(sheet, col, startRow = 1) {
  const values = sheet
    .getRange(startRow, col, sheet.getLastRow() - startRow + 1)
    .getValues();
 
  for (let i = values.length - 1; i >= 0; i--) {
    if (values[i][0] !== "" && values[i][0] != null) {
      return i + startRow;
    }
  }
 
  return startRow;
}
 
 
// =========================================================
// 🧭 Debug / observability
// =========================================================
 
/**
 * Universal debug notifier (toast / alert / log fallback)
 */
function debugNotify(message, title = "Debug") {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
 
  try {
    const ui = SpreadsheetApp.getUi();
    if (ui) {
      ui.alert(`${title}\n\n${message}`);
      return;
    }
  } catch (e) {}
 
  try {
    if (ss) {
      ss.toast(message, title, 5);
      return;
    }
  } catch (e) {}
 
  Logger.log(`[${title}] ${message}`);
}
 
 
// =========================================================
// ⚙️ Formatting / helpers
// =========================================================
 
/**
 * Strip seconds from Date
 */
function stripSeconds(dateVal) {
  if (dateVal instanceof Date) {
    dateVal.setSeconds(0, 0);
  }
  return dateVal;
}
 
 
/**
 * Write time into cell with formatting
 */
function setTimeCellBestFormat(cell, dateVal = new Date()) {
  cell.setValue(stripSeconds(new Date(dateVal)));
  cell.setNumberFormat("h:mm:ss AM/PM");
}