How can i get range number from A1notation?-CodePudding

This is a script to get a1notation from a number. Conversely, I don't know how to get the number from a1notation !

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getRange(1, 1, 2, 5);

// Logs "A1:E2"
Logger.log(range.getA1Notation());

i want to get (1, 1, 2, 5) from "A1:E2"

CodePudding user response：

Here is an example of how to get the row and column from A1 notation.

function test() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Sheet1");
    let range = "A1:E2";
    range = getRange(range);
    console.log(range);
    console.log( sheet.getRange(range[0][0],range[0][1],(range[1][0]-range[0][0] 1),(range[1][1]-range[0][1] 1)).getValues() );
    range = "F3";
    range = getRange(range);
    console.log(range);
    console.log( sheet.getRange(range[0][0],range[0][1]).getValue() );
  }
  catch(err) {
    throw "Error in test: " err;
  }
}


function getRange(range) {
  try {
    let ranges = range.split(":");
    ranges = ranges.map( text => {
        let cell = text.toUpperCase();
        let column = 0;
        let row = 0;
        let char = null;
        for( let i=0; i<cell.length; i   ) {
          char = cell.charCodeAt(i);
          if( ( char > 64 ) && ( char < 91 ) ) {
            if( row > 0 ) return null;
            column = (26*column) char-64;
          }
          else if( ( char > 47 ) && ( char < 58 ) ) {
            if( column === 0 ) return null;
            row = (10*row) char-48;
          }
        }
        if( ( column === 0 ) || ( row === 0 ) ) throw "Incorrect range";
        return [row,column];
      }
    );
    return ranges;
  }
  catch(err) {
    throw "\nError in getRange: " err;
  }
};

8:47:47 AM  Notice  Execution started
8:47:49 AM  Info    [ [ 1, 1 ], [ 2, 5 ] ]
8:47:49 AM  Info    [ [ 'Goodbye World', 'Date/Time', 'a', 'b', 'c' ],
  [ 1,
    Mon Nov 07 2022 06:34:37 GMT-0800 (Pacific Standard Time),
    1,
    101,
    201 ] ]
8:47:49 AM  Info    [ [ 3, 6 ] ]
8:47:49 AM  Info    302
8:47:48 AM  Notice  Execution completed