Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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

This comment has been minimized.

Copy link

@brainysmurf brainysmurf commented May 1, 2018

What about Sheet1 == sheetId:0 ?

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.