Skip to content

Instantly share code, notes, and snippets.

@emmaly
Created December 4, 2023 23:41
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save emmaly/d7ee29f0f795f6a1a3c3e2fbe67cc6a4 to your computer and use it in GitHub Desktop.
Apps Script Helper Functions for Google Sheets
/** @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;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment