Google Sheets Named Range Format Patroller

Code.gs

// An example of how something like this could be used:
//
// Set some ranges as NamedRanges named "ColumnFormat_PhoneNumber" and "ColumnFormat_DateTime"
// and those will end up processed by the functions named "Format_PhoneNumber" and "Format_DateTime"
// which should enforce a value format or number format or possibly even a visual format upon edit
// and then set occasionalSheetFormatPatrol as a scheduled trigger to catch the items that were
// updated by API/scripts or other things that couldn't have been caught by the onEdit function.
// **

function occasionalSheetFormatPatrol() {
  SpreadsheetApp
  .getActiveSpreadsheet()
  .getSheets()
  .forEach((sheet) => formatProcessor(sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn())));
}


/**
 * @param {object} e
 * @param {SpreadsheetApp.Range} e.range
 */
function onEdit(e) {
  handleEditEvent(e.range, e);
}


/**
 * @param {SpreadsheetApp.Range} range
 * @param {object} e
 */
function handleEditEvent(range, e) {
  if (!e) e = {};
  if (!range) range = SpreadsheetApp.getActiveRange();

  formatProcessor(range);
}


/**
 * @param {SpreadsheetApp.Range} range
 */
function formatProcessor(range) {
  if (!range) range = SpreadsheetApp.getActiveRange();

  const nonHeaderRange = range.getSheet().getRange(
    range.getSheet().getFrozenRows()+1,
    range.getSheet().getFrozenColumns()+1,
    range.getSheet().getLastRow()-range.getSheet().getFrozenRows(),
    range.getSheet().getLastColumn()-range.getSheet().getFrozenColumns(),
  );

  const columnFormatMatch = /^ColumnFormat_/i;

  range
  .getSheet()
  .getNamedRanges()
  .filter((namedRange) => columnFormatMatch.test(namedRange.getName()))
  .filter((namedRange) => DoRangesHaveOverlap(range, nonHeaderRange, namedRange.getRange()))
  .map((namedRange) => {
    return {
      rangeName: namedRange.getName(),
      union: GetRangeUnion(range, nonHeaderRange, namedRange.getRange())
    };
  })
  .forEach((v) => {
    const s = v.rangeName.replace(columnFormatMatch, "");
    // Logger.log("%s\t%s\t%s", v.rangeName, s, v.union.getA1Notation());
    const fxn = globalThis["Format_"+s];
    if (typeof fxn === "function") fxn(v.union);
  });
}


/**
 * @param {SpreadsheetApp.Range} range
 */
function Format_DateTime(range) {
  Logger.log("Format_DateTime: %s", range.getA1Notation());
  range.setNumberFormat("YYYY-MM-DD h:mm am/pm");
}


/**
 * @param {SpreadsheetApp.Range} range
 */
function Format_PhoneNumber(range) {
  Logger.log("Format_PhoneNumber: %s", range.getA1Notation());

  const formatter = (input) => {
    if (/^\+\d+$/.test(input)) return null; // no change
    if (/^\s*$/.test(input)) return ""; // make it empty string
    if (/^[\(\s]*\d{3}[\)/\-\.\s]*\d{3}[/\-\.\s+]?\d{4}\s*$/) {
      return "+1" + input.replace(/[-\.\s+]+/g, "");
    }
    Logger.log("Probably invalid format: [%s]", input);
    return null; // no change
  };

  const values = range.getValues();
  let hasChanges = false;
  for (let i=0; i<values.length; i++) {
    for (let j=0; j<values[i].length; j++) {
      const was = values[i][j].toString();
      const now = formatter(was);
      if (now === null) continue;
      Logger.log("[%s] => [%s]", was, now);
      hasChanges = hasChanges || was !== now;
      values[i][j] = now;
    }
  }
  if (hasChanges) range.setValues(values);
}

RangeUtils.gs

/**
 * @param {SpreadsheetApp.Range[]} ranges
 * @returns {SpreadsheetApp.Range}
 */
function GetRangeUnion(...ranges) {
  const unionPair = (rangeA, rangeB) => {
    const union = {
      left: -1,
      right: -1,
      top: -1,
      bottom: -1,
    };

    // if
    //   both range's left edge is at/before both range's right edge
    // then
    //   union.left  is the rightest left  side
    //   union.right is the leftest  right side
    if (
      rangeA.getColumn() <= rangeB.getLastColumn() && // rangeA's left edge is at/before rangeB's right edge
      rangeB.getColumn() <= rangeA.getLastColumn()    // rangeB's left edge is at/before rangeA's right edge
    ) {
      union.left  = Math.max(rangeA.getColumn()    , rangeB.getColumn()    ); // left  is the rightest left  side
      union.right = Math.min(rangeA.getLastColumn(), rangeB.getLastColumn()); // right is the leftest  right side
    }

    // if
    //   both range's top edge is at/above both range's bottom edge
    // then
    //   union.top    is the bottomest top    side
    //   union.bottom is the toppest   bottom side
    if (
      rangeA.getRow() <= rangeB.getLastRow() && // rangeA's top edge is at/before rangeB's bottom edge
      rangeB.getRow() <= rangeA.getLastRow()    // rangeB's top edge is at/before rangeA's bottom edge
    ) {
      union.top    = Math.max(rangeA.getRow()    , rangeB.getRow()    ); // top    is the bottomest top    side
      union.bottom = Math.min(rangeA.getLastRow(), rangeB.getLastRow()); // bottom is the toppest   bottom side
    }

    if (
      union.left   < 0 ||
      union.right  < 0 ||
      union.top    < 0 ||
      union.bottom < 0    
    ) return null; // no union occurred.

    return rangeA.getSheet().getRange(union.top, union.left, union.bottom-union.top+1, union.right-union.left+1);
  };

  return ranges.reduce((rangeA, rangeB) => unionPair(rangeA, rangeB));
}


/**
 * @param {SpreadsheetApp.Range} ranges
 * @returns {boolean}
 * - `true` indicates all ranges share the same overlapping point(s) with all others
 * - `false` indicates no shared overlap point, even if they all overlap individually
 */
function DoRangesHaveOverlap(...ranges) {
  return GetRangeUnion(...ranges) !== null;
}