Apps Script Helper Functions for Google Sheets

code.gs

/** @OnlyCurrentDoc */


/**
 * @param {any} value - value to test
 * @returns {boolean} - `true` if a number, `false` otherwise
 */
function isNumber(value) {
  return typeof value == 'number' && isFinite(value);
}


/**
 * @param {number} sheetId
 * @returns {SpreadsheetApp.Sheet|void}
 */
function getSheetById(sheetId) {
  return SpreadsheetApp
  .getActiveSpreadsheet()
  .getSheets()
  .filter((sheet) => sheet.getSheetId() === sheetId)
  .shift();
}


/**
 * Source: https://gist.github.com/emmaly/2c10ba34a3616f8aa9989aa7694ad5b1
 * 
 * @param {SpreadsheetApp.Sheet} sheet
 * @param {string} name
 * @returns {SpreadsheetApp.Range}
 */
function getNamedRange(sheet, name) {
  return sheet.getNamedRanges().filter((namedRange) => namedRange.getName() === name)?.shift()?.getRange();
}


/**
 * @param {SpreadsheetApp.Sheet} sheet
 * @param {boolean?} obeyFrozenRows - default `true`
 * @param {boolean?} obeyFrozenCols - default `false`
 * @returns {SpreadsheetApp.Range}
 */
function getDataRangeFromSheet(sheet, obeyFrozenRows=true, obeyFrozenCols=false) {
  const firstRow = 1 + (!!obeyFrozenRows ? sheet.getFrozenRows()    : 0);
  const firstCol = 1 + (!!obeyFrozenCols ? sheet.getFrozenColumns() : 0);
  const numRows = sheet.getMaxRows() - (firstRow-1);
  const numCols = sheet.getMaxColumns() - (firstCol-1);
  return sheet.getRange(firstRow, firstCol, numRows, numCols);
}


/**
 * Source: https://gist.github.com/emmaly/2522d2e12157c4c3ebefe32d409ca996
 * 
 * @param {SpreadsheetApp.Range} ranges
 * @returns {boolean}
 * - `true` indicates all ranges share the same overlapping area with all others.
 * - `false` indicates no shared overlap area, even if they all overlap individually.
 */
function doRangesIntersect(...ranges) {
  return getRangeIntersection(...ranges) !== null;
}


/**
 * Calculates the intersection of multiple given ranges within a Google Sheet. 
 * 
 * The function determines the single range that encompasses the overlapping area of the 
 * provided ranges. It attempts to return as early as possible when a failure condition is 
 * met, such as in cases where an intersection is impossible or input is invalid, or where an 
 * intersection can be quickly resolved. 
 * 
 * In cases where ranges do not overlap or where any ranges are on a different sheet, then an 
 * intersection is not found, and therefore will return `null`. 
 * 
 * Returns a single `Range` encompassing the overlapping area common to all provided ranges. 
 * 
 * Source: Emmaly - https://gist.github.com/emmaly/2522d2e12157c4c3ebefe32d409ca996
 * 
 * @param {SpreadsheetApp.Range[]} ranges - An array of `Range` objects from Google Sheets.
 *                                         
 *                                          The ranges should be from the same sheet.
 *                                          The ranges must be supplied as arguments, not as 
 *                                          `Range[]` but as `...ranges` (using the spread 
 *                                          operator) or `range1, range2, range3` (as 
 *                                          individual `Range` arguments).
 *                                         
 *                                          To aid in debugging, this will throw a helpful 
 *                                          error if an array of `Range`s is mistakenly passed 
 *                                          in as the first argument, rather than giving false 
 *                                          negative results.
 *                                         
 *                                          If *any* arguments are not a `Range`, this will 
 *                                          throw an error indicating the argument number 
 *                                          at fault. Take care to never pass in a non-`Range`,
 *                                          value as an argument. But if you might, then you 
 *                                          will need to catch the exception when it occurs.
 * @returns {SpreadsheetApp.Range} - A single `Range` object representing the intersection of 
 *                                   all provided ranges.
 *                                   
 *                                   Returns `null` if there is no intersection across *all* 
 *                                   provided ranges or if any ranges are from different sheets.
 */
function getRangeIntersection(...ranges) {
  if (!ranges || !Array.isArray(ranges) || ranges.length < 1) {
    throw new Error("expected `...ranges`");
  }
  if (ranges.length === 1 && !!ranges[0].getA1Notation) {
    // only one range passed in?  this is already resolved.
    return ranges[0];
  }
  if (Array.isArray(ranges[0])) {
    // if first argument is an array...
    // then it's likely programmer error.
    throw new Error("expected `...ranges` not `ranges[]`");
  }
  for (let i=0; i<ranges.length; i++) {
    if (typeof ranges[i].getA1Notation !== "function") {
      throw new Error("expected `Range` at argument #" + (i+1).toFixed(0));
    }
    // Logger.log("[%s] %s", i.toFixed(0), ranges[i].getA1Notation());
  }

  const intersectionOf = (rangeA, rangeB) => {
    // missing ranges can't have an intersection
    if (!rangeA || !rangeB) return null;

    // sheet containing the potential intersection
    const sheet = rangeA.getSheet();

    // no intersection if ranges are from different sheets
    if (sheet.getSheetId() !== rangeB.getSheet().getSheetId()) return null;

    // find our intersection box
    const left   = Math.max(rangeA.getColumn(),     rangeB.getColumn());
    const right  = Math.min(rangeA.getLastColumn(), rangeB.getLastColumn());
    const top    = Math.max(rangeA.getRow(),        rangeB.getRow());
    const bottom = Math.min(rangeA.getLastRow(),    rangeB.getLastRow());

    // test for failed intersection
    if (left > right || top > bottom) return null;

    // return the intersection's range
    return sheet.getRange(
      top,
      left,
      bottom - top + 1,
      right - left + 1
    );
  };
  
  return ranges.slice(1).reduce((acc, curr) => acc && intersectionOf(acc, curr), ranges[0]);
}


/**
 * @param {number} column
 * @returns {string}
 */
function columnToLetter(column) {
  let temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}