Converting cell coordinates without using a column number to column letter method?

Question

I'm trying to figure out what my options are here when I need to use a column number in a formula, and if I really need to write a column number to column letter method to accomplish what I'm trying to do.

See this method I have here:

createFormulas(lookupField, lookupColumns) {
  // Iterate through the lookupColumn array 
  lookupColumns.forEach(value => {
    let columnNumber = this.getColumn(this.headers, value);
    let range = this.sheet.getRange(2, columnNumber, this.lastRow - 1, 1);
    // range.setFormula('=$A2');
    range.setFormula('=' + columnNumber + '2' ); // doesn't work obviously
  })
}

I'm trying to add formulas in a column based on the column.

• this.getColumn() returns the column number based on the column name being passed in.
• let range sets the range I want to set the formula in
• range.setFormula('=$A2') pastes this formula into range and updates the reference accordingly (i.e., $A3, $A4, etc.). This isn't the formula I ultimately want to use, just a simplified example.

I need to set the column in the reference dynamically, however.

What I have obviously won't work: range.setFormula('=' + columnNumber + '2' );. That would just result in something like 72 where 7 is the column number.

I know I can write a method that will convert the column number into a letter. I'm just surprised there isn't a built in method for doing that or some other native way of accomplishing this.

For example, in Excel VBA I think you can do something like "=" & Cells(2, columnNumber).Address or something like that (been a while, I could be wrong), which should equate to =A2, =A3, =A4, etc. in the range.

So before writing this column number to letter method, I just wanted to check: is that the only way to accomplish what I'm after or is there a native way of handling this that I'm just not seeing?

Answer 1

Actually, was able to do this using .getA1Notation().

Refactored to the following and it works as expected:

createFormulas(lookupField, lookupColumns) {
  // Iterate through the lookupColumn array 
  lookupColumns.forEach(value => {
    let columnNumber = this.getColumn(this.headers, value);
    let formulaRange = this.sheet.getRange(2, columnNumber, this.lastRow - 1, 1);
    let referenceRange = this.sheet.getRange(2, this.idColumn, this.lastRow - 1, 1);
    formulaRange.setFormula("=" + referenceRange.getCell(1, 1).getA1Notation());
  })
}

Answer 2

Just in case. Based on https://stackoverflow.com/a/64456745/14265469

function numberToLetters(num) {
  // num--; // if you need 1 --> A, 2 --> B, 26 --> Z
  let letters = '';
  while (num >= 0) {
      letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'[num % 26] + letters;
      num = (num - num % 26) / 26 - 1;
  }
  return letters;
}

console.log(numberToLetters(0));  // --> A
console.log(numberToLetters(25)); // --> Z
console.log(numberToLetters(26)); // --> AA

Answer 3

Column To Letters

I followed Yuri's path to the numbers to letter functions and I'm a bit baffled that we have forgotten that there are 26 letters in the alphabet and so after looking at the various functions at that reference none of them seem to have worked for me. So here's my replacement:

function colToletters(num) {
  let a = " ABCDEFGHIJKLMNOPQRSTUVWXYZ";
  if (num < 27) return a[num % a.length];
  if (num > 26) {
    num--;
    let letters = '';
    while (num >= 0) {
      letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'[num % 26] + letters;
      num = Math.floor(num / 26) - 1;
    }
    return letters;
  }
}

This will calculate the column letters for 1 to 1000 and I check all the way to 703 where the letters go to AAA and they look good all the way.