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

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?

CodePudding user response：

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

CodePudding user response：

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

function numberToLetters(num) {
  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