python, pygsheets question - how to gain the color of each cell in a column, fast?

Question

i have this code, that checks color of each cell in a google sheets worksheet. that would be ok, but for 1200 rows, it takes 400 seconds to do so, so i wanted to ask if someone know of a better way to check color of a each cell in a column(i couldnt find how to check only 1 column, and not the whole sheet), and put it in a list? can i use get_all_values() for only 1 column?

import pygsheets
cells = cyber_worksheet.get_all_values(returnas='cell',include_tailing_empty=False, include_tailing_empty_rows=False)
color_code = []
for r in cells:
    for c in r:
        color_code.append(c.color)
return color_code

this worked, but very very slowly.... i was wondering if there was

Answer 1

I believe your goal is as follows.

• You want to retrieve the values from only one column instead of all cells.
• You want to achieve this using pygsheets.

In this case, how about using get_col instead of get_all_values? When this is reflected in your script, how about the following modification?

From:

cells = cyber_worksheet.get_all_values(returnas='cell',include_tailing_empty=False, include_tailing_empty_rows=False)
color_code = []
for r in cells:
    for c in r:
        color_code.append(c.color)

To:

cells = cyber_worksheet.get_col(1, returnas="cell", include_tailing_empty=False)
color_code = []
for r in cells:
    color_code.append(r.color)

• In this case, the values are retrieved from column "A". When you want to retrieve the values from the column "B", please modify cyber_worksheet.get_col(1, returnas="cell", include_tailing_empty=False) to cyber_worksheet.get_col(2, returnas="cell", include_tailing_empty=False).
• In this case, it seems that the value of cells is a one-dimensional array, and each element is each row.

Note:

• If you use get_all_values, how about the following modification?

    col = 1  # Column "A"
    cells = cyber_worksheet.get_all_values(returnas="cell", include_tailing_empty=False, include_tailing_empty_rows=False)
    color_code = []
    for r in cells:
        color_code.append(r[col - 1].color)
  

Reference:

• get_col(col, returnas='matrix', include_tailing_empty=True, **kwargs)