Python Function to Get the 0-based Index of a Column in a Google Sheet

Sheet columns are named A-Z and then AA, AB and so on. To get the 0-based index of the first 26 letters, make an array of letters A-Z. Begin by creating a range of the hexidecimal values between A and Z. Then map that back to the character. Turn the iterator into a list and assign it to the variable alphabet. Get the index value of a specific letter in the array. This will be the same as its 0-based index position in the column headers.

>>> alphabet = [_ for _ in map(chr, range(ord('a'), ord('z')+1))]
# get the index of a 'a'
>>> alphabet.index('a')
0
>>> alphabet.index('t')
19

If the columns go past Z, the following lambda will return the correct index. We have to add an additional "1" because the index for "a" is "0" so when we add 25 (the index for "z") + the index for "a", we need an additional "1" to get to "26".

>>>alphabet_2 = lambda char: alphabet.index('z') + alphabet.index(char) + 1
>>> alphabet_2('a')  # 'AA', but we are just referencing the 2nd 'a'
26
>>> alphabet_2('r')  # 'AR', but we are just referencing the 2nd value, the 'r'
43

Feedback?

Email us at enquiries@kinsa.cc.