This sample script is for expanding a1Notations using Google Apps Script. This was updated from this sample script.
Sample script
function expandA1Notations_(a1Notations, maxRow, maxColumn) {
maxRow = maxRow || "1000";
maxColumn = maxColumn || "Z";
// 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;
};
const letterToColumn = letter => {
let column = 0,
length = letter.length;
for (let i = 0; i < length; i++) {
column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
}
return column;
};
const reg1 = new RegExp("^([A-Z]+)([0-9]+)$");
const reg2 = new RegExp("^([A-Z]+)$");
const reg3 = new RegExp("^([0-9]+)$");
return a1Notations.map(e => {
const a1 = e.split("!");
const r = a1.length > 1 ? a1[1] : a1[0];
const [r1, r2] = r.split(":");
if (!r2) return [r1];
let rr;
if (reg1.test(r1) && reg1.test(r2)) {
rr = [r1.toUpperCase().match(reg1), r2.toUpperCase().match(reg1)];
} else if (reg2.test(r1) && reg2.test(r2)) {
rr = [
[null, r1, 1],
[null, r2, maxRow]
];
} else if (reg1.test(r1) && reg2.test(r2)) {
rr = [r1.toUpperCase().match(reg1), [null, r2, maxRow]];
} else if (reg2.test(r1) && reg1.test(r2)) {
rr = [[null, r1, maxRow], r2.toUpperCase().match(reg1)];
} else if (reg3.test(r1) && reg3.test(r2)) {
rr =
Number(r1) > Number(r2)
? [
[null, "A", r2],
[null, maxColumn, r1]
]
: [
[null, "A", r1],
[null, maxColumn, r2]
];
} else if (reg1.test(r1) && reg3.test(r2)) {
rr = [r1.toUpperCase().match(reg1), [null, maxColumn, r2]];
} else if (reg3.test(r1) && reg1.test(r2)) {
let temp = r2.toUpperCase().match(reg1);
rr =
Number(temp[2]) > Number(r1)
? [
[null, temp[1], r1],
[null, maxColumn, temp[2]]
]
: [temp, [null, maxColumn, r1]];
} else {
throw new Error(`Wrong a1Notation: ${r}`);
}
const obj = {
startRowIndex: Number(rr[0][2]),
endRowIndex: rr.length == 1 ? Number(rr[0][2]) + 1 : Number(rr[1][2]) + 1,
startColumnIndex: letterToColumn(rr[0][1]),
endColumnIndex:
rr.length == 1
? letterToColumn(rr[0][1]) + 1
: letterToColumn(rr[1][1]) + 1
};
let temp = [];
for (let i = obj.startRowIndex; i < obj.endRowIndex; i++) {
for (let j = obj.startColumnIndex; j < obj.endColumnIndex; j++) {
temp.push(columnToLetter(j) + i);
}
}
return temp;
});
}
// When you use this script, please run main().
function main() {
const a1Notations = ["A1:E3", "B10:W13", "EZ5:FA8", "AAA1:AAB3"];
const res = expandA1Notations_(a1Notations);
console.log(res);
}
Result
[
["A1","B1","C1","D1","E1","A2","B2","C2","D2","E2","A3","B3","C3","D3","E3"],
["B10","C10","D10","E10","F10","G10","H10","I10","J10","K10","L10","M10","N10","O10","P10","Q10","R10","S10","T10","U10","V10","W10","B11","C11","D11","E11","F11","G11","H11","I11","J11","K11","L11","M11","N11","O11","P11","Q11","R11","S11","T11","U11","V11","W11","B12","C12","D12","E12","F12","G12","H12","I12","J12","K12","L12","M12","N12","O12","P12","Q12","R12","S12","T12","U12","V12","W12","B13","C13","D13","E13","F13","G13","H13","I13","J13","K13","L13","M13","N13","O13","P13","Q13","R13","S13","T13","U13","V13","W13"],
["EZ5","FA5","EZ6","FA6","EZ7","FA7","EZ8","FA8"],
["AAA1","AAB1","AAA2","AAB2","AAA3","AAB3"]
]
Retrieve duplicated cells
When this script is used, the duplicated cells can be retrieved as follows.
Sample script
function myFunction() {
const a1Notations = ["B3:C7", "D6:E9", "A2:B3", "C7:D10"];
const res = expandA1Notations_(a1Notations)
.reduce((m, e) => {
e.forEach(f => m.set(...(m.has(f) ? ["dup", [...m.get("dup"), f]] : [f, null])));
return m;
}, new Map([["dup", []]]))
.get("dup");
console.log(res); // <--- ["B3","C7","D7","D8","D9"]
}
When this script is run, ["B3","C7","D7","D8","D9"]
is retrieved. These are the duplicated cells in "B3:C7", "D6:E9", "A2:B3", "C7:D10"
.