Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Expanding A1Notations using Google Apps Script

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.