Converting a1Notation to GridRange for Google Sheets API

submit.md

Converting a1Notation to GridRange for Google Sheets API

When it uses Google Sheets API v4, GridRange is used for it as the range property. These sample scripts are for converting from a1Notation to GridRange. You can chose from following 2 scripts. Both scripts can retrieve the same result.

Script 1 :

This is from me.

function a1notation2gridrange1(sheetid, a1notation) {
  var data = a1notation.match(/(^.+)!(.+):(.+$)/);
  var ss = SpreadsheetApp.openById(sheetid).getSheetByName(data[1]);
  var range = ss.getRange(data[2] + ":" + data[3]);
  var gridRange = {
    sheetId: ss.getSheetId(),
    startRowIndex: range.getRow() - 1,
    endRowIndex: range.getRow() - 1 + range.getNumRows(),
    startColumnIndex: range.getColumn() - 1,
    endColumnIndex: range.getColumn() - 1 + range.getNumColumns(),
  };
  if (!data[2].match(/[0-9]/)) delete gridRange.startRowIndex;
  if (!data[3].match(/[0-9]/)) delete gridRange.endRowIndex;
  return gridRange;
}

Script 2 :

String.prototype.to10 was used for this script. String.prototype.to10 is from Alexander Ivanov. I think that String.prototype.to10 is a clever solution.

String.prototype.to10 = function(base) {
  var lvl = this.length - 1;
  var val = (base || 0) + Math.pow(26, lvl) * (this[0].toUpperCase().charCodeAt() - 64 - (lvl ? 0 : 1));
  return (this.length > 1) ? (this.substr(1, this.length - 1)).to10(val) : val;
}

function a1notation2gridrange2(sheetid, a1notation) {
  var data = a1notation.match(/(^.+)!(.+):(.+$)/);
  var ss = SpreadsheetApp.openById(sheetid).getSheetByName(data[1]);
  var co1 = data[2].match(/(\D+)(\d+)/);
  var co2 = data[3].match(/(\D+)(\d+)/);
  var gridRange = {
    sheetId: ss.getSheetId(),
    startRowIndex: co1 ? parseInt(co1[2], 10) - 1 : null,
    endRowIndex: co2 ? parseInt(co2[2], 10) : null,
    startColumnIndex: co1 ? co1[1].to10() : data[2].to10(),
    endColumnIndex: co2 ? co2[1].to10(1) : data[3].to10(1),
  };
  if (gridRange.startRowIndex == null) delete gridRange.startRowIndex;
  if (gridRange.endRowIndex == null) delete gridRange.endRowIndex;
  return gridRange;
}

Result :

Following sample results are from the document for GridRange at Google Sheets API v4.

For example, if "Sheet1" is sheet ID 0, then:

Sheet1!A1:A1 == sheetId: 0, startRowIndex: 0, endRowIndex: 1, startColumnIndex: 0, endColumnIndex: 1

Sheet1!A3:B4 == sheetId: 0, startRowIndex: 2, endRowIndex: 4, startColumnIndex: 0, endColumnIndex: 2

Sheet1!A:B == sheetId: 0, startColumnIndex: 0, endColumnIndex: 2

Sheet1!A5:B == sheetId: 0, startRowIndex: 4, startColumnIndex: 0, endColumnIndex: 2

What about Sheet1 == sheetId:0 ?