Google Sheets column alias capability

column-alias.gs

/**
 * SEMANTIC COLUMN TOOLKIT v1.0
 * * Provides a set of custom Google Sheets functions to reference data by column name 
 * rather than static column letters. Designed to survive column reordering, row 
 * insertions/deletions, and to handle date-formatted headers (e.g., "Jan").
 * * Functions included:
 * - NAMED_COLUMN_CELL: Fetches a single cell value from a column by name, not letter.
 * - NAMED_COLUMN_RANGE: Fetches an entire vertical array (useful for SUM/AVERAGE) by column name, not letter.
 * - COLUMN_NAMED: Returns the current column letter of a named header.
 * - DEREF: Helper to resolve a cell value from a column letter and row number.
 * * @license MIT
 * Copyright (c) 2026
 * * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 * * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 * * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 */

/**
 * Returns a cell value by column name, using row references and an optional sheet name.
 * Survives column moves and row insertions.
 * * Example with literal column name, row containing header names, and row whose value you want:
 *  * =NAMED_COLUMN_CELL("Jan", 1, 3)
 * * Example as above but that survives refactoring:
 *  * =NAMED_COLUMN_CELL("Jan", ROW(A$1), ROW(A3))
 *
 * @param {"Jan"} name The header name to find.
 * @param {1} headerRowRef The header row number (best used with ROW(A$1)).
 * @param {3} dataRowRef The data row number (best used with ROW(A3)).
 * @param {"Sheet1"} sheetName [Optional] The name of the sheet.
 * @return The value of the cell.
 * @customfunction
 */
function NAMED_COLUMN_CELL(name, headerRowRef, dataRowRef, sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = sheetName ? ss.getSheetByName(sheetName) : ss.getActiveSheet();
  if (!sheet) throw new Error("Sheet '" + sheetName + "' not found.");

  const headerRow = Number(headerRowRef);
  const dataRow = Number(dataRowRef);
  
  const colLetter = COLUMN_NAMED(name, headerRow, sheetName);
  return sheet.getRange(colLetter + dataRow).getValue();
}

/**
 * Returns a range of data below a named header.
 * * Example: =SUM(NAMED_COLUMN_RANGE("Jan", ROW(A$1), 12))
 *
 * @param {"Jan"} name The header name to find.
 * @param {1} headerRowRef The header row number (use ROW(A$1)).
 * @param {12} numRows [Optional] The number of rows to return. If omitted, returns until the last row.
 * @param {"Sheet1"} sheetName [Optional] The name of the sheet.
 * @return The range of data below the header.
 * @customfunction
 */
function NAMED_COLUMN_RANGE(name, headerRowRef, numRows, sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = sheetName ? ss.getSheetByName(sheetName) : ss.getActiveSheet();
  if (!sheet) throw new Error("Sheet '" + sheetName + "' not found.");

  const headerRow = Number(headerRowRef);
  const colLetter = COLUMN_NAMED(name, headerRow, sheetName);
  
  const startRow = headerRow + 1;
  const rowsToFetch = numRows ? Number(numRows) : (sheet.getLastRow() - headerRow);
  
  if (rowsToFetch <= 0) return [];

  return sheet.getRange(colLetter + startRow + ":" + colLetter + (startRow + rowsToFetch - 1)).getValues();
}

/**
 * Returns the column letter (e.g., "B") based on the header name.
 * Matches displayed text (works with date-formatted cells).
 * * @param {"Jan"} name The header text to search for.
 * @param {1} oneBasedHeaderRowIndex The row number where headers live.
 * @param {"Sheet1"} sheetName [Optional] The name of the sheet.
 * @return The column letter.
 * @customfunction
 */
function COLUMN_NAMED(name, oneBasedHeaderRowIndex, sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = sheetName ? ss.getSheetByName(sheetName) : ss.getActiveSheet();
  if (!sheet) throw new Error("Sheet '" + sheetName + "' not found.");

  const lastCol = sheet.getLastColumn();
  const headerRowData = sheet.getRange(oneBasedHeaderRowIndex, 1, 1, lastCol).getDisplayValues()[0];
  
  const colIndex = headerRowData.findIndex(cellText => 
    cellText.toString().trim().toLowerCase() === name.toString().trim().toLowerCase()
  );
  
  if (colIndex === -1) throw new Error("Header '" + name + "' not found.");

  return sheet.getRange(1, colIndex + 1).getA1Notation().replace(/[0-9]/g, '');
}

/**
 * Returns the value of a cell given its column letter and row number.
 * * @param {"A"} colLetter The column letter.
 * @param {5} oneBasedRowIndex The row number.
 * @param {"Sheet1"} sheetName [Optional] The name of the sheet.
 * @return The value of the cell.
 * @customfunction
 */
function DEREF(colLetter, oneBasedRowIndex, sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = sheetName ? ss.getSheetByName(sheetName) : ss.getActiveSheet();
  if (!sheet) throw new Error("Sheet '" + sheetName + "' not found.");
  
  return sheet.getRange(colLetter + oneBasedRowIndex).getValue();
}