Skip to content

Instantly share code, notes, and snippets.

@erickoledadevrel
Last active May 1, 2024 10:08
Show Gist options
  • Save erickoledadevrel/6fa9b256f41b5d7646ba6ed37e0f8018 to your computer and use it in GitHub Desktop.
Save erickoledadevrel/6fa9b256f41b5d7646ba6ed37e0f8018 to your computer and use it in GitHub Desktop.
[Apps Script] Getting the unmerged ranges within a range
/**
* Gets all the unmerged ranges within a range.
* @param {SpreadsheetApp.Range} range The range to evaluate.
* @returns {SpreadsheetApp.Range[]} The unmerged ranges.
*/
function getUnmergedRanges(range) {
if (!range.isPartOfMerge()) {
return [range];
}
var mergedRanges = range.getMergedRanges();
var ranges = [range];
mergedRanges.forEach(function(mergedRange) {
ranges = ranges.reduce(function(result, range) {
return result.concat(subtractRange(range, mergedRange));
}, []);
});
return ranges;
}
/**
* Subtracts one range from another.
* @param {SpreadsheetApp.Range} minuend The range to subtract from.
* @param {SpreadsheetApp.Range} subtrahend The range to subtract.
* @returns {SpreadsheetApp.Range[]} The resulting ranges.
*/
function subtractRange(minuend, subtrahend) {
var intersection = getIntersection(minuend, subtrahend);
if (!intersection) {
return [minuend];
}
var sheet = minuend.getSheet();
var result = [];
// Top slice.
if (subtrahend.getRow() > minuend.getRow()) {
var top = getRangeWithStartAndEnd(sheet, minuend.getRow(), minuend.getColumn(),
subtrahend.getRow() - 1, minuend.getLastColumn());
result.push(top);
}
// Bottom slice.
if (subtrahend.getLastRow() < minuend.getLastRow()) {
var bottom = getRangeWithStartAndEnd(sheet, subtrahend.getLastRow() + 1, minuend.getColumn(),
minuend.getLastRow(), minuend.getLastColumn());
result.push(bottom);
}
// Left slice.
if (subtrahend.getColumn() > minuend.getColumn()) {
var left = getRangeWithStartAndEnd(sheet,
Math.max(minuend.getRow(), subtrahend.getRow()),
minuend.getColumn(),
Math.min(minuend.getLastRow(),subtrahend.getLastRow()),
subtrahend.getColumn() - 1);
result.push(left);
}
// Right slice.
if (subtrahend.getLastColumn() < minuend.getLastColumn()) {
var right = getRangeWithStartAndEnd(sheet,
Math.max(minuend.getRow(), subtrahend.getRow()),
subtrahend.getLastColumn() + 1,
Math.min(minuend.getLastRow(), subtrahend.getLastRow()),
minuend.getLastColumn());
result.push(right);
}
return result;
}
/**
* Gets the intersection of two ranges.
* @param {SpreadsheetApp.Range} range1 One range.
* @param {SpreadsheetApp.Range} range2 The other range.
* @returns {SpreadsheetApp.Range} A range representing the intersection, or null if they don't intersect.
*/
function getIntersection(range1, range2) {
if (range1.getSheet().getSheetId() != range2.getSheet().getSheetId()) {
return null;
}
var sheet = range1.getSheet();
var startRow = Math.max(range1.getRow(), range2.getRow());
var endRow = Math.min(range1.getLastRow(), range2.getLastRow());
var startColumn = Math.max(range1.getColumn(), range2.getColumn());
var endColumn = Math.min(range1.getLastColumn(), range2.getLastColumn());
if (startRow > endRow || startColumn > endColumn) {
return null;
}
return getRangeWithStartAndEnd(sheet, startRow, startColumn, endRow, endColumn);
}
/**
* Gets the difference between two ranges.
* @param {SpreadsheetApp.Range} range1 One range.
* @param {SpreadsheetApp.Range} range2 The other range.
* @returns {SpreadsheetApp.Range[]} The ranges representing the difference.
*/
function getDifference(range1, range2) {
var intersection = getIntersection(range1, range2);
if (!intersection) {
return [range1, range2];
}
return [range1, range2].reduce(function(result, range) {
return result.concat(subtractRange(range, intersection));
}, []);
}
/**
* Gets a range specified using end rows and columns, instead of widths and heights.
* @param {SpreadsheetApp.Sheet} sheet The sheet containing the range.
* @param {Number} startRow The start row (1-indexed).
* @param {Number} startColumn The start column (1-indexed).
* @param {Number} endRow The end row (1-indexed).
* @param {Number} endColumn The end column (1-indexed).
* @returns {SpreadsheetApp.Range} The range.
*/
function getRangeWithStartAndEnd(sheet, startRow, startColumn, endRow, endColumn) {
return sheet.getRange(startRow, startColumn, endRow - startRow + 1, endColumn - startColumn + 1);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment