Converting A1Notation to GridRange and vice versa using Google Apps Script without any Scopes

submit.md

Converting A1Notation to GridRange and vice versa using Google Apps Script without any Scopes

This is a sample script for converting A1Notation to GridRange and vice versa using Google Apps Script without any scopes.

A1Notation and GridRange are often used with Sheets API. I have posted a sample script for converting A1Notation to GridRange before. Ref But, in that case, I used the method of Spreadsheet service (SpreadsheetApp). By this, in order to use the script, it is required to authorize the scopes. In this sample script, A1Notation can be converted to GridRange and vice versa with no scopes. Also, this sample script can be used for Javascript and Node.js.

Sample script

/**
 * Converting colum letter to column index. Start of column index is 0.
 * Ref: https://tanaikech.github.io/2022/05/01/increasing-column-letter-by-one-using-google-apps-script/
 * @param {String} letter Column letter.
 * @return {Number} Column index.
 */
function columnLetterToIndex_(letter = null) {
  letter = letter.toUpperCase();
  return [...letter].reduce(
    (c, e, i, a) =>
      (c += (e.charCodeAt(0) - 64) * Math.pow(26, a.length - i - 1)),
    -1
  );
}

/**
 * Converting colum index to column letter. Start of column index is 0.
 * Ref: https://stackoverflow.com/a/53678158
 * @param {Number} index Column index.
 * @return {String} Column letter.
 */
function columnIndexToLetter_(index = null) {
  return (a = Math.floor(index / 26)) >= 0
    ? columnIndexToLetter_(a - 1) + String.fromCharCode(65 + (index % 26))
    : "";
}

/**
 * Converting a1Notation to gridrange.
 * Ref: https://tanaikech.github.io/2017/07/31/converting-a1notation-to-gridrange-for-google-sheets-api/
 * @param {String} a1Notation A1Notation of range.
 * @param {Number} sheetId Sheet ID of the range.
 * @return {Object} Gridrange.
 */
function convA1NotationToGridRange_(a1Notation, sheetId) {
  const { col, row } = a1Notation
    .toUpperCase()
    .split("!")
    .map((f) => f.split(":"))
    .pop()
    .reduce(
      (o, g) => {
        var [r1, r2] = ["[A-Z]+", "[0-9]+"].map((h) => g.match(new RegExp(h)));
        o.col.push(r1 && columnLetterToIndex_(r1[0]));
        o.row.push(r2 && Number(r2[0]));
        return o;
      },
      { col: [], row: [] }
    );
  col.sort((a, b) => (a > b ? 1 : -1));
  row.sort((a, b) => (a > b ? 1 : -1));
  const [start, end] = col.map((e, i) => ({ col: e, row: row[i] }));
  const gridrange = {
    sheetId,
    startRowIndex: start?.row && start.row - 1,
    endRowIndex: end?.row ? end.row : start.row,
    startColumnIndex: start && start.col,
    endColumnIndex: end ? end.col + 1 : 1,
  };
  if (gridrange.startRowIndex === null) {
    gridrange.startRowIndex = 0;
    delete gridrange.endRowIndex;
  }
  if (gridrange.startColumnIndex === null) {
    gridrange.startColumnIndex = 0;
    delete gridrange.endColumnIndex;
  }
  return gridrange;
}

/**
 * Converting gridrange to a1Notation.
 * Ref: https://tanaikech.github.io/2017/07/31/converting-a1notation-to-gridrange-for-google-sheets-api/
 * @param {Object} gridrange Gridrange of range.
 * @param {String} sheetName Sheet name of the range.
 * @return {Object} A1Notation.
 */
function convGridRangeToA1Notation_(gridrange, sheetName) {
  const start = {};
  const end = {};
  if (gridrange.hasOwnProperty("startColumnIndex")) {
    start.col = columnIndexToLetter_(gridrange.startColumnIndex);
  } else if (
    !gridrange.hasOwnProperty("startColumnIndex") &&
    gridrange.hasOwnProperty("endColumnIndex")
  ) {
    start.col = "A";
  }
  if (gridrange.hasOwnProperty("startRowIndex")) {
    start.row = gridrange.startRowIndex + 1;
  } else if (
    !gridrange.hasOwnProperty("startRowIndex") &&
    gridrange.hasOwnProperty("endRowIndex")
  ) {
    start.row = gridrange.endRowIndex;
  }
  if (gridrange.hasOwnProperty("endColumnIndex")) {
    end.col = columnIndexToLetter_(gridrange.endColumnIndex - 1);
  } else if (!gridrange.hasOwnProperty("endColumnIndex")) {
    end.col = "{Here, please set the max column letter.}";
  }
  if (gridrange.hasOwnProperty("endRowIndex")) {
    end.row = gridrange.endRowIndex;
  }
  const k = ["col", "row"];
  const st = k.map((e) => start[e]).join("");
  const en = k.map((e) => end[e]).join("");
  const a1Notation =
    st == en ? `'${sheetName}'!${st}` : `'${sheetName}'!${st}:${en}`;
  return a1Notation;
}

// When you test this script, please run this function.
// You can test this sample script.
function main() {
  const sheetId = 0;
  const sheetName = "Sheet1";
  const sampleValues = [
    "Sheet1!B3:D5",
    "'Sheet1'!B7:D9",
    "AB25:AD51",
    "11:15",
    "G:H",
    "A1",
    "A20:A20",
    "C1:D1",
    "B22:A25",
  ];
  const res1 = sampleValues.map((e) => convA1NotationToGridRange_(e, sheetId));
  const res2 = res1.map((e) => convGridRangeToA1Notation_(e, sheetName));

  console.log(res1);
  console.log(res2);
}

• When main() is run, the following results are obtained.

  [
    {
      "sheetId": 0,
      "startRowIndex": 2,
      "endRowIndex": 5,
      "startColumnIndex": 1,
      "endColumnIndex": 4
    },
    {
      "sheetId": 0,
      "startRowIndex": 6,
      "endRowIndex": 9,
      "startColumnIndex": 1,
      "endColumnIndex": 4
    },
    {
      "sheetId": 0,
      "startRowIndex": 24,
      "endRowIndex": 51,
      "startColumnIndex": 27,
      "endColumnIndex": 30
    },
    {
      "sheetId": 0,
      "startRowIndex": 10,
      "endRowIndex": 15,
      "startColumnIndex": 0
    },
    {
      "sheetId": 0,
      "startRowIndex": 0,
      "startColumnIndex": 6,
      "endColumnIndex": 8
    },
    {
      "sheetId": 0,
      "startRowIndex": 0,
      "endRowIndex": 1,
      "startColumnIndex": 0,
      "endColumnIndex": 1
    },
    {
      "sheetId": 0,
      "startRowIndex": 19,
      "endRowIndex": 20,
      "startColumnIndex": 0,
      "endColumnIndex": 1
    },
    {
      "sheetId": 0,
      "startRowIndex": 0,
      "endRowIndex": 1,
      "startColumnIndex": 2,
      "endColumnIndex": 4
    },
    {
      "sheetId": 0,
      "startRowIndex": 21,
      "endRowIndex": 25,
      "startColumnIndex": 0,
      "endColumnIndex": 2
    }
  ]

  [
    "'Sheet1'!B3:D5",
    "'Sheet1'!B7:D9",
    "'Sheet1'!AB25:AD51",
    "'Sheet1'!A11:{Here, please set the max column letter.}15",
    "'Sheet1'!G1:H",
    "'Sheet1'!A1",
    "'Sheet1'!A20",
    "'Sheet1'!C1:D1",
    "'Sheet1'!A22:B25"
  ]

jsfiddle.net

In this case, you can also test the script with jsfiddle.net as follows.

https://jsfiddle.net/et4fg1k2/

Note

• In this sample script, for example, when the a1Notation of "11:15" with Sheet1 is converted to the gridrange, 'Sheet1'!A11:{Here, please set the max column letter.}15 is returned. Because, the max column cannot be written as an A1Notation. Of course, the max column can be retrieved by using Spreadsheet service like SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getMaxColumns(). But, the direction of this sample script is not to use any scopes. So, I used {Here, please set the max column letter.}. If you know the a1Notation for the max column, when you tell me it, I'm glad.