#sheet #convert #js

.md

###Convert a sheet column letter to dec-number

code.gs

/*
 * to26()
 * 26-positional notation numeral system
 * A to dec 0
 * B to dec 1
 * ....
 * Z to dec 25
 *
 * to10() is revert
 */

Number.prototype.to26 = function(suffix) {
  suffix = String.fromCharCode((this % 26) + 65) + (suffix || '');
  return (this >= 26) ? (Math.floor(this / 26) - 1).to26(suffix) : suffix;
}

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;
}

examples.gs

function example() {
  var str = 'A';
  var column = str.to10(1);
  Logger.log(column);

  var num = 29;
  var name = (num - 1).to26();
  Logger.log(name);

  /*
  [17-01-04 22:41:08:540 PST] 1.0
  [17-01-04 22:41:08:540 PST] AE
  */
}

tests.gs

function gast() {

  if ('undefined' == typeof GasTap) {
    var cs = CacheService.getScriptCache().get('gast');
    cs || (cs = UrlFetchApp.fetch('https://raw.githubusercontent.com/zixia/gast/master/src/gas-tap-lib.js').getContentText(),
      CacheService.getScriptCache().put('gast', cs, 21600));
    eval(cs);
  }

  var test = new GasTap();
  var pairs = [{
    num: 0,
    chr: 'A'
  }, {
    num: 13,
    chr: 'N'
  }, {
    num: 25,
    chr: 'Z'
  }, {
    num: 26,
    chr: 'AA'
  }, {
    num: 27,
    chr: 'AB'
  }, {
    num: 53,
    chr: 'BB'
  }, {
    num: 701,
    chr: 'ZZ'
  }, {
    num: 702,
    chr: 'AAA'
  }, {
    num: 18277,
    chr: 'ZZZ'
  }];
  test('to26', function(t) {
    for (var i = 0; i < pairs.length; i++)
      t.equal(pairs[i].num.to26(), pairs[i].chr, pairs[i].num + ' is ' + pairs[i].chr);
  });
  test('to10', function(t) {
    for (var i = 0; i < pairs.length; i++)
      t.equal(pairs[i].chr.to10(), pairs[i].num, pairs[i].chr + ' is ' + pairs[i].num);
  });

  test.finish();
}

/* exported numberTo26_ */
/**
 *
 * @param {number} number
 * @param {string} suffix
 */
function numberTo26_(number, suffix) {
  suffix = String.fromCharCode((number % 26) + 65) + (suffix || '');
  return number >= 26
    ? numberTo26_(Math.floor(number / 26) - 1, suffix)
    : suffix;
}

https://stackoverflow.com/questions/45787459/convert-number-to-alphabet-string-javascript/45787487

function numberTo26_(number, suffix = undefined) {
  return number > 0
    ? numberTo26_(
        Math.floor((number - ((number - 1) % 26)) / 26),
        String.fromCharCode(65 + ((number - 1) % 26)) + (suffix || '')
      )
    : suffix;
}