When you're scripting in Google Apps Script to automate data in a Google Sheet, you'll be working with Columns a lot. Sometimes you'll get those columns in Letters and sometimes you'll get them in Numbers. Most of the time, it's a pain to have to convert Numbers to Letters or the other way around.
This code snippet makes it easy to do that by giving you a simple function that'll convert your input both ways, depending on what you need.
- To convert from a Letter to a Number, specify the "target" as "number"
convertColumn('number', 'G')
- To convert from a Number to a Letter, specify the "target" as "Letter"
convertColumn('letter', '6')
function convertColumn(target, value) {
if (target == 'letter') {
let temp, letter = ''
while (value > 0) {
temp = (value - 1) % 26
letter = String.fromCharCode(temp + 65) + letter
value = (value - temp - 1) / 26
}
return letter
} else if (target == 'number') {
let column = 0, length = value.length;
for (i=0; i<length; i++) {
column += (value.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
}
return column
} else {
return 0
}
}
