0

I have several columns in my google sheet. The number of columns varies over time (new columns may get added in future). I want to get a map or something similar like this if my sheets look like this :

View of my sheet

Response of an API call:

{
    "timestamp" : "A",
    "Col1" : "B",
    "Col2" : "C",
    "Col3" : "D"
}

In other words, I want to get column name and its label in the sheet in api response. Or, anything similar will also work. I don't know what API call to make. I'm aware about .get call of the sheet, but I don't know how to get labels of the column in this. I can only get data in certain range. I don't know how to get labels of the columns.

Russ
  • 5
  • 1
  • 4
  • 1
    The headers seem to be the values on row 1. Getting the values of that row in order would be enough for your use case? If no, could you explain what is your use case? – Martí Apr 07 '21 at 13:33
  • I have a script, and in that script I use query language to query my sheets. I execute queries like `select A, B, C` on my sheets. However, A,B,C do not look like any meaningful names. I want to query sheets by headers. To query by headers, I need to keep this kind of mapping, so that I can convert my query like `select Row_num` to select A – Russ Apr 07 '21 at 15:48

1 Answers1

-1

You can simply use the API to get the values and then map then generate the letter of the columns:

def main():
    # Build the service (see python quickstart)
    # [...] 

    # Get the values on row 1
    result = service.spreadsheets().values().get(spreadsheetId=SPREADSHEET_ID, range='1:1').execute()
    values = result.get('values', [[]])[0]

    # Generate the headers mapping
    headers = {}
    for i, value in enumerate(values, start=1):
        if value is not None and value != '':
            headers[value] = col_to_letter(i)
    
    print(headers)

def col_to_letter(col):
    '''Gets the letter of a column number'''
    r = ''
    while col > 0:
        v = (col - 1) % 26
        r = chr(v + 65) + r
        col = (col - v - 1) // 26
    return r

col_to_letter was adapted from this JavaScript answer.

References

Martí
  • 2,651
  • 1
  • 4
  • 11