Gspread: search a column and get the corresponding row in different column

Question

I have a spreadsheet that's like a company directory with Column A being names and the other columns having data points like email, location, manager, etc. What I want to do is search column A and when I find what I'm looking for, get the corresponding cell value in another column.

I don't see a way to search just a column and get back a cell object. If I use the col_values() method to get back a list, can I be guaranteed that the list returned will be ordered by row (i.e. array position 0 = row1, position 1 = row2, etc.). Is there a better way to do this?

Answer 1

wks = gc.open("YourSpread").sheet1
for k in range(1, wks.row_count + 1):
   row=wks.row_values(k)    #this reads all columns in each row                  
   if row[0] == "name":        #value 0 reads column A
      return row[YourIndex]  

Have tested a similar thing and it works. You might want to exchange .row_count if you have a fixed number of entries, as this accounts for the whole spreadsheet.

Answer 2

I'm improving the answer given by user2822564 The for in the answer above gives a non indentable error because the wks.row_count returns a single number not a list. This is the final code that worked for me. Although the while iterates starting from the last row to the first so the returned data in case there are multiple results will be ordered from last to first.

wks = gc.open("YourSpread").sheet1    
x = wks.row_count
while x > 0:
   row=wks.row_values(x, value_render_option='UNFORMATTED_VALUE')                    
   if row[0] == "name": #replace name by the desired keyword
      return row[4] #replace 4 for the number of the column. ie 4 is column E
   x = x - 1

Answer 3

As per their documentation you can use,

To find and get the first match

find(query, in_row=None, in_column=None, case_sensitive=True)

To find and get all of the matches

findall(query, in_row=None, in_column=None, case_sensitive=True