Search
 
SCRIPT & CODE EXAMPLE
 

JAVASCRIPT

apps script convert a1notation to row column

/**
 * 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;
}
Comment

PREVIOUS NEXT
Code Example
Javascript :: enable bootrstrap duellistbox from my own js 
Javascript :: upload file javascript mdn 
Javascript :: props with ternary in react 
Javascript :: add active class to button onclick react 
Javascript :: angular data table push not working 
Javascript :: remove undefined from object javascript 
Javascript :: Raphael JS store arbitrary data 
Javascript :: print array elements in new line javascript 
Javascript :: how to set up click event listeners javascript 
Javascript :: Scratch Addon userscript 
Javascript :: javascript categories input object example 
Javascript :: window location host vs origin 
Javascript :: react native parent opcaity not affecting text 
Javascript :: alpine js x:bind href link 
Javascript :: Subscription field must return Async Iterable. Received: undefined. 
Javascript :: unable to get local issuer certificate npm 
Javascript :: Jboss heap dump 
Javascript :: convert javascript to jquery converter online tool 
Javascript :: javascript get distance bwetween elements 
Javascript :: read pdf as array bytes using fetch 
Javascript :: firebase check if key exists javascript 
Javascript :: javascript nested arrays stackoverflow 
Javascript :: mongo look for substring of field 
Javascript :: Could not parse as expression: "1, "desc" DataTable 
Javascript :: how to change in website with node js 
Javascript :: nestjs multer file upload delay 
Javascript :: simple form in react native with code 
Javascript :: input should reject non-alphabetical input reacj js 
Javascript :: Check if something is a function 
Javascript :: redwood toaster 
ADD CONTENT
Topic
Content
Source link
Name
6+5 =