How to get Row & Column values from A1Notation

Question

Writing scripts for Google Spreadsheets can be difficult sometimes, because the Google Spreadsheet methods that take row and column numbers use 1-based indexing, while Javascript arrays use 0-based.

In this example, cell A2 has a row == 2 and column == 1. The SpreadsheetApp methods reverse column & row from those in A1Notation, so these two ranges are equivalent:

var range1 = sheet.getRange("A2");
var range2 = sheet.getRange(2, 1);

Once I read the contents of a sheet into an array, things are different again.

var ss = SpreadsheetApp.getActive();
var sheet = ss.getActiveSheet();
var data = sheet.getDataRange().getValues();

After that, the value that was in cell A2 in my spreadsheet is in data[1][0]. The row & column are in the same order as the SpreadsheetApp API, but each is 1 less.

The answers to lots of questions here (example) have boiled down to mismatching of these different forms of indexing. Code that is full of row+1 and col-1 statements can be hard to debug.

Finally: If I know the reference for a cell in A1Notation, say AZ342, how can I find out the correct index values correspond to that cell in the 2D array, data, obtained from the full Data Range?

Answer 1

Obviously, you can just be very careful about keeping track of the places where you're using either type of indexing, and you'll be fine.

But it would be easier to do something like this:

var importantCell = "AZ342";
var cellIndexConverted = cellA1ToIndex( importantCell );
var data[cellIndexConverted.row][cellIndexConverted.col] = "Some new value";

ConvertA1.gs

Here are three helper functions to simplify conversion from A1Notation.

These helper functions are also available as a gist.

/**
 * Convert a cell reference from A1Notation to 0-based indices (for arrays)
 * or 1-based indices (for Spreadsheet Service methods).
 *
 * @param {String}    cellA1   Cell reference to be converted.
 * @param {Number}    index    (optional, default 0) Indicate 0 or 1 indexing
 *
 * @return {object}            {row,col}, both 0-based array indices.
 *
 * @throws                     Error if invalid parameter
 */
function cellA1ToIndex( cellA1, index ) {
  // Ensure index is (default) 0 or 1, no other values accepted.
  index = index || 0;
  index = (index == 0) ? 0 : 1;

  // Use regex match to find column & row references.
  // Must start with letters, end with numbers.
  // This regex still allows induhviduals to provide illegal strings like "AB.#%123"
  var match = cellA1.match(/(^[A-Z]+)|([0-9]+$)/gm);

  if (match.length != 2) throw new Error( "Invalid cell reference" );

  var colA1 = match[0];
  var rowA1 = match[1];

  return { row: rowA1ToIndex( rowA1, index ),
           col: colA1ToIndex( colA1, index ) };
}

/**
 * Return a 0-based array index corresponding to a spreadsheet column
 * label, as in A1 notation.
 *
 * @param {String}    colA1    Column label to be converted.
 *
 * @return {Number}            0-based array index.
 * @param {Number}    index    (optional, default 0) Indicate 0 or 1 indexing
 *
 * @throws                     Error if invalid parameter
 */
function colA1ToIndex( colA1, index ) {
  if (typeof colA1 !== 'string' || colA1.length > 2) 
    throw new Error( "Expected column label." );

  // Ensure index is (default) 0 or 1, no other values accepted.
  index = index || 0;
  index = (index == 0) ? 0 : 1;

  var A = "A".charCodeAt(0);

  var number = colA1.charCodeAt(colA1.length-1) - A;
  if (colA1.length == 2) {
    number += 26 * (colA1.charCodeAt(0) - A + 1);
  }
  return number + index;
}


/**
 * Return a 0-based array index corresponding to a spreadsheet row
 * number, as in A1 notation. Almost pointless, really, but maintains
 * symmetry with colA1ToIndex().
 *
 * @param {Number}    rowA1    Row number to be converted.
 * @param {Number}    index    (optional, default 0) Indicate 0 or 1 indexing
 *
 * @return {Number}            0-based array index.
 */
function rowA1ToIndex( rowA1, index ) {
  // Ensure index is (default) 0 or 1, no other values accepted.
  index = index || 0;
  index = (index == 0) ? 0 : 1;

  return rowA1 - 1 + index;
}