Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Last active September 3, 2019 14:05
Show Gist options
  • Save tanaikech/95c7cd650837f33a564babcaf013cae0 to your computer and use it in GitHub Desktop.
Save tanaikech/95c7cd650837f33a564babcaf013cae0 to your computer and use it in GitHub Desktop.
Converting a1Notation to GridRange for Google Sheets API

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
@brainysmurf
Copy link

What about Sheet1 == sheetId:0 ?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment