I'm trying to develop a Python script to search a sheet for a particular row, matching multiple criteria. I'll start with a sample scenario, a fruits menu in Google Sheets. See screenshot below. Note, this example might or might not reflect actual prices.
In gspread, I would normally use the find function to search for a particular item (e.g. cell = worksheet.find("Apple")) and from that I can find out which row the item is located in. However, this might only find the row for the first "Apple" entry that appears. In the sample below, it'll find the "green apple" and its corresponding row - Row 2.
What if I'm interested in the row number of a fruit matching a certain criteria? For example, Apple, Red, which is located in Row 6. How do I search a sheet using gspread (find function) to find a particular row of something matching 2 or more criteria? In the example, I want to return the row number containing the red apple (Row 6), which seems to appear after the first apple entry (green apple).
EDIT:
- Solved / Answered (Code Snippet Below)
- Thanks @Tanaike
# OUTPUT: Code below updates Cell D6 with the letter "X"
def itemRow(sheet):
searchColA = "Apple"
searchColB = "Red"
row = [i 1 for i, r in enumerate(sheet.get_all_values()) if
r[0] == searchColA and r[1] == searchColB][0]
sheet.update("D" str(row), "X")
CodePudding user response：
When I saw the document of find of gspread, it says in_column (int) – (optional) One-based column number to scope the search.. In this case, unfortunately, it seems that only one column is used for searching values. And, when I saw the script of gspread, in the case of find method, it seems that a value is searched by retrieving all values from the sheet. Ref When find is used 2 times, your goal can be achieved. But, in this case, 2 API calls are required. So, I thought that in your situation, when the above flow is reflected for achieving your goal, your goal can be achieved by one API call. In this case, how about the following sample script?
Sample script:
client = ### # Please use your client.
searchColA = "Apple" # This is used for searching column "A".
searchColB = "Red" # This is used for searching column "B".
spreadsheetId = "###" # Please set your spreadsheet ID.
sheetName = "Sheet1" # Please set your sheet name.
spreadsheet = client.open_by_key(spreadsheetId)
sheet = spreadsheet.worksheet(sheetName)
res = [i 1 for i, r in enumerate(sheet.get_all_values()) if r[0] == searchColA and r[1] == searchColB]
print(res) # Found row numbers. In this case, 1st row number is 1.