Google Apps Script snippets ᕦʕ •ᴥ•ʔᕤ

Column names to numbers and vice versa

Define Base 26 ABC

There are several base 26 types. Apps Scripts works with Sheets. Therefore, let’s add some rules:

  1. A26 is 110
  2. Z26 is 2610
  3. Ignore case: A26 is equal a26

The code below shows how you can get base 10 from base 26 and vice versa. In other words it helps you know the column number by letters or column letters by number.

Snippet

index.js 
/**
 *
 */
function run() {
  console.log(base26ABCto10('ABC'));
  console.log(base26ABCfrom10(731));
}

/**
 * @see {@link https://t.me/googleappsscriptrc/37031}
 *
 * @param {string} string Any string as a column name 'A', 'Z', 'ABC'
 * @returns {number} The column number
 */
function base26ABCto10(string) {
  return [...string].reduceRight(
    (p, c, i, a) =>
      p + (c.toUpperCase().charCodeAt(0) - 64) * 26 ** (a.length - i - 1),
    0
  );
}

/**
 * @see {@link https://stackoverflow.com/a/45789255/1393023}
 *
 * @param {number} number Positive integer. A column number
 * @returns {string} The column name
 */
function base26ABCfrom10(number) {
  let num = number;
  let sfx = '';
  while (num > 0) {
    const cd = (num - 1) % 26;
    sfx = String.fromCharCode(65 + cd) + sfx;
    num = Math.floor((num - cd) / 26);
  }
  return sfx;
}

Test

test.js 
/**
 *
 */
function runTest() {
  let n = 0;
  while (n <= 1000) {
    const curN = n;
    const nextCol = base26ABCfrom10(curN + 1);
    n = base26ABCto10(nextCol);
    console.log(`${n} ~ ${nextCol}`);
    if (n !== curN + 1) console.error(`Unexpected output: "${n} ~ ${nextCol}"`);
  }
}

/**
 *
 */
function runTestArt() {
  const strings = ['ABC', 'AF', 'ALEX', 'ALL', 'COFE', 'GAS', 'TEA', 'UI'];
  console.log(strings.map((string) => [string, base26ABCto10(string)]));

  const numbers = [255, 1000, 2048, new Date().getTime()];
  console.log(numbers.map((number) => [number, base26ABCfrom10(number)]));
}

The short list of the first 250 numbers

You can see the list for quick access Conversion list of 250 first numbers

Art

Some examples of relating column names and numbers are shown below

Column names to number

[
  ['ABC', 731],
  ['AF', 32],
  ['ALEX', 25842],
  ['ALL', 1000],
  ['COFE', 63029],
  ['GAS', 4777],
  ['TEA', 13651],
  ['UI', 555],
];

Numbers to column name

[
  [255, 'IU'],
  [1000, 'ALL'],
  [2048, 'BZT'],
  [1613808548650, 'GRXBSEBKL'],
];

#js #common