Updated: Expanding A1Notations using Google Apps Script

submit.md

Updated: Expanding A1Notations using Google Apps Script

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".