如何从A1notation获得范围号？

Question

这是一个从数字中获取a1notation的脚本。相反，我不知道如何从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());

我想从"A1:E2“中得到(1，1，2，5)

Answer 1

下面是如何从A1符号中获取行和列的示例。

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