Converting Columns between a Number or a Letter

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.

1. To convert from a Letter to a Number, specify the "target" as "number"
   • convertColumn('number', 'G')
2. 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 } }