Retrieving Overwrapped Cells Between 2 Ranges on Google Spreadsheet using Google Apps Script
This is a sample script for retrieving the overwrapped cells between 2 ranges on Google Spreadsheet using Google Apps Script. Please use this with enabling V8.
const getOverwrappedRanges_ = (rangeList1, rangeList2) => {
if (
rangeList1.toString() != "RangeList" ||
rangeList2.toString() != "RangeList"
) {
throw new Error("Input RangeList object.");
}
// Ref: https://stackoverflow.com/a/21231012/7108653
const 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;
};
// Expand range1.
const ar = rangeList1.getRanges().reduce((ar, r) => {
const startRow1 = r.getRow();
const endRow1 = startRow1 + r.getNumRows();
const startColumn1 = r.getColumn();
const endColumn1 = startColumn1 + r.getNumColumns();
for (let j = startRow1; j < endRow1; j++) {
for (let k = startColumn1; k < endColumn1; k++) {
ar.push(columnToLetter(k) + j);
}
}
return ar;
}, []);
// Expand range2.
const map = rangeList2.getRanges().reduce((m, r) => {
const startRow2 = r.getRow();
const endRow2 = startRow2 + r.getNumRows();
const startColumn2 = r.getColumn();
const endColumn2 = startColumn2 + r.getNumColumns();
for (let j = startRow2; j < endRow2; j++) {
for (let k = startColumn2; k < endColumn2; k++) {
m.set(columnToLetter(k) + j, null);
}
}
return m;
}, new Map());
return ar.filter(e => map.has(e));
};
const main = () => {
const range1 = ["B3:C7", "D6:E9"]; // Please input range1 as a1Notation.
const range2 = ["A2:B3", "C7:D10"]; // Please input range2 as a1Notation.
const sheet = SpreadsheetApp.getActiveSheet();
const res = getOverwrappedRanges_(
sheet.getRangeList(range1),
sheet.getRangeList(range2)
);
console.log(res); // <--- ["B3","C7","D7","D8","D9"]
};
-
In this sample script, from the ranges of
"B3:C7", "D6:E9"and"A2:B3", "C7:D10", the overwrapped cells are returned. In this case,["B3","C7","D7","D8","D9"]is returned.
