fetching color code for each cell of a column in google sheets

Question

I am trying to get the color code for each cell of a google sheet using pygsheets. Code block being used:

import pygsheets
gc = pygsheets.authorize(service_file='credentials.json')
sh = gc.open('golden dataset')
wks = sh[0]

count = 1
color_code = []
while count<27628:
        cell = pygsheets.Cell('J'+str(count),worksheet=wks)
        color_code.append(cell.color)

where 27628 is the column length & 'golden dataset' is the sheet name & credentials.json helps in connecting google_sheets with python. Though, this works fine but is extremely slow (taking ~7-8 hours) for just one column. Is there a faster way to do this? Thanks

Answer 1

You can use get_all_values with cells as return type.

import pygsheets
gc = pygsheets.authorize(service_file='credentials.json')
sh = gc.open('golden dataset')
wks = sh[0]

cells = wks.get_all_values(returnas='cell', include_tailing_empty=False, include_tailing_empty_rows=False)
# adjust if you need trailing empty cells too

color_code = []
for r in cells:
  for c in r:
    color_code.append(c.color)

if you want to fetch data in custom batches, you can use get_values along with wks.rows and wks.cols.