Can't read data simultaneously from the first three columns of a google sheet

Question

I'm trying to read data from the first three columns of a google sheet using gspread. The three columns that I'm interested in are ID,NAME and SYMBOL. The name of the sheet is testFile.

If I try like the following, I can get data from the first column.

client = authenticate()
sh = client.open("testFile")
worksheet = sh.get_worksheet(0)

for item in worksheet.col_values(1):
    print(item)

However, I wish to parse the data from the three columns simultaneously. It would be better If I could read the values using column headers.

I know I colud try like this to get the values, but this way I will end up getting quotaExceeded errors or something similar because of it's slow pacing.

for i in range(1, worksheet.row_count + 1):
   row = worksheet.row_values(i)
   if row[0]=='ID':continue
   print(row[0],row[1],row[2])

How can I read data from the first three columns of a google sheet?

Answer 1

In your situation, how about the following modification?

From:

for item in worksheet.col_values(1):
    print(item)

To:

values = worksheet.get_all_values()
obj = {}
for e in zip(*values):
    obj[e[0]] = list(e[1:])

print(obj)

• In this modification, all values are retrieved by one API call. And, the retrieved values are converted to an object as the key of the header title.
  • For example, when your sample Spreadsheet is used, you can retrieve the values of column "NAME" by obj["NAME"].

Or,

To:

values = worksheet.get_all_records()
res = [e["NAME"] for e in values] # or e["ID"] or e["SYMBOL"]
print(res)

• In this modification, you can retrieve the values of column "NAME" can be retrieved.

References:

• get_all_values
• get_all_records