CODEWITHSUNDEEP
google-apps-script
Bjorn Behrendt
Bjorn Behrendt

Reputation: 1264

How to convert column number into a letter

I am writing a query formula via script. I have a dialogue which I would like to list all the available columns that have data in them. I can easily get the number of columns that the sheet has using sheet.getLastColumn();, but how can I easily convert that to the column format A,B,C...AA,BB,CC,... ?

So if the number of columns is 5 I am trying to get an array [A,B,C,D,E] If it is 30 then I would expect an array of [A,B,C,...,CC].

Not sure why it is not letting me post, but I thought I would write some more to see if it helps.

Upvotes: 3

Views: 5197

Answers (1)

Kriggs
Kriggs

Reputation: 3778

Found this code somewhere:

function NUM_RETURN_LETRA(column){
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

Then for your case:

var columns = [];
for( i = 0; i< 10; i++)
  columns.push(NUM_RETURN_LETRA(+i));

return columns

Upvotes: 7

Related Questions