sheetAPI-test.py

import pickle
from pprint import pprint

from googleapiclient.discovery import build

# con = cg.ConnectGoogle()

TOKEN_FILENAME = "token.pickle"
creds = None
with open(TOKEN_FILENAME, "rb") as token:
    creds = pickle.load(token)

service = build("sheets", "v4", credentials=creds)

request_doby = {
    "dataFilters": [{"gridRange": {"startColumnIndex": 0, "startRowIndex": 1}}],
    "majorDimension": "COLUMNS",
}

request = (
    service.spreadsheets()
    .values()
    .batchGetByDataFilter(
        spreadsheetId="1iung0Vi3DNKOlb_IIV2oYya_0YjUsZaP2oBkjKekvbI", body=request_doby
    )
)

response = request.execute()
pprint(response)
values_list = response.get("valueRanges")[0].get("valueRange").get("values")
id2name_dict = dict(zip(values_list[0], values_list[1]))
pprint(id2name_dict["U1ENCSW65"])