How can I pull data from rows based on the presence of a specific character in a specific column with Google Sheets?

I’m sending API calls to Google sheets to retrieve information like so:

gc = gspread.authorize(credentials)

def grab_available_row(wks):
    str_list = list(filter(None, wks.col_values(17)))
    return str(len(str_list)+1) 

wks = gc.open("test").worksheet("Logs")
grab_row = grab_available_row(wks)

try:
    GrabRequestTest = wks.acell("B{}".format(grab_row)).value
except:
    pass

try:
    print(GrabRequestTest)
    ctypes.windll.user32.MessageBoxW(0, "DONE!!!", "DONE!!!", 1)
    sys.exit()
except:
    pass

With this, I can retrieve information in any row if there is no value present in column #17. In other words, this essentially reads from the first available row without anything in column #17. If I put an X in column 17, it will read the row below it. This isn’t exactly what I’m looking for.

I’d like to be able to print all values in a row where a specific character like X is present in column 17, and ignore all other rows. I’d then take the data from each row with X present in column 17 and use mail merge to generate a bunch of .docx files. I can easily figure out the second part. Anybody know how to accomplish the first part? (print values in a specific row where X is present in column 17)

Answer

From I'd like to be able to print all values in a row where a specific character like X is present in column 17, and ignore all other rows. I'd then take the data from each row with X present in column 17 and use mail merge to generate a bunch of .docx files. I can easily figure out the second part. Anybody know how to accomplish the first part? (print values in a specific row where X is present in column 17), I believe your goal in this question is as follows.

• You want to retrieve the filtered rows by the specific value at the column 17 (it’s column “Q”.).
• You want to achieve this using gspread for python.

In this case, how about the following modification?

Modified script:

gc = gspread.authorize(credentials)
wks = gc.open("test").worksheet("Logs")

search = "X" # Please set the search value you expect.
values = [r for r in wks.get_all_values() if r[16] == search]
print(values)

• When this script is run, the rows that the column “Q” is the value of search are retrieved as a 2-dimensional array.

Added:

From the following reply,

This is almost it! How can you print by column only? like, I only want the value from column 3 from the array.. print(values[4]) doesn’t seem to work.

In this case, how about the following sample script?

Sample script:

gc = gspread.authorize(credentials)
wks = gc.open("test").worksheet("Logs")

search = "X" # Please set the search value you expect.
col = 3 # From your reply, the values of the column "C" is retrieved.
values = [r[col - 1] for r in wks.get_all_values() if r[16] == search]
print(values)