Expanding A1Notations using Google Apps Script

submit.md

Expanding A1Notations using Google Apps Script

This is a sample script for expanding a1Notations using Google Apps Script (GAS). In this script, for example, "A1:E3" is expanded to "A1, B1, C1, D1, E1, A2, B2, C2, D2, E2, A3, B3, C3, D3, E3". When each cell in "A1:E3" is checked, this script might be able to be used. If this was useful for your situation, I'm glad.

Script:

function expandA1Notation(a1Notations) {
  var columnToLetter = function(column) {
    var temp, letter = '';
    while (column > 0) {
      temp = (column - 1) % 26;
      letter = String.fromCharCode(temp + 65) + letter;
      column = (column - temp - 1) / 26;
    }
    return letter;
  };

  var letterToColumn = function(letter) {
    var column = 0, length = letter.length;
    for (var i = 0; i < length; i++) {
      column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
    }
    return column;
  };

  var reg = new RegExp("([A-Z]+)([0-9]+)");
  var res = a1Notations.map(function(e) {
    var a1 = e.split("!");
    var r = a1.length > 1 ? a1[1] : a1[0];
    var rr = r.split(":").map(function(f) {return f.toUpperCase().match(reg)});
    var 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,
    };
    var temp = [];
    for (var i = obj.startRowIndex; i < obj.endRowIndex; i++) {
      for (var j = obj.startColumnIndex; j < obj.endColumnIndex; j++) {
        temp.push(columnToLetter(j) + i);
      }
    }
    return temp;
  });
  return res;
}

// When you use this script, please run main().
function main() {
  var a1Notations = ["A1:E3", "B10:W13", "EZ5:FA8", "AAA1:AAB3"];
  var res = expandA1Notation(a1Notations);
  Logger.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"]
]

This script uses 2 methods (https://stackoverflow.com/a/21231012/7108653) for converting from index to letter and from letter to index.