How to search specific columns and return specific values in python using Google spreadsheet?

Question

I’m having trouble getting this to work. Essentially I want a user to input a keyword and then get a question and answer pair as a return. The first, second, and third columns in the the spreadsheet has the keywords, while the fourth and fifth columns have the question and answer respectively. I want to have it so that the program only searches the first three columns for the keyword, and then only returns the 4th and 5th column from any row that contains that keyword.

So a user will put in the word “VPN”, the program will search the first 3 columns to see if any of them equal VPN. Let’s say the 1st row and 1st column equals VPN, then the program should return the question and answer columns from the first row.

I have code that searches all columns and returns all values from that column, but I’m having trouble with the specific rows and columns portion. Any help would be greatly appreciated!

scope =["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://ww$
creds = ServiceAccountCredentials.from_json_keyfile_name("key.json", scope)
client = gspread.authorize(creds)
sheet = client.open("Questionnaire").sheet1

userInput = input('Ask your question: ')

cell = sheet.find(userInput, in_column=[1-3]) #Not sure how to search multiple columns
 
rownum = cell.index(userInput) + 1
row = sheet.row_values(rownum) #Not sure how to return specific columns within this row

if cell != None:
        print(row)
else:
        print("I don't know the answer to that question")

I have to apologize for my poor English skill. Unfortunately, I cannot imagine your situation. In order to correctly understand your question, can you provide the sample input and output situations you expect? First, I would like to correctly understand your question. — Tanaike
– Tanaike, Commented Jun 27, 2024 at 23:34

Adam DS · Accepted Answer · 2024-06-28 13:33:28Z

2

According to Gspread's documentation, the find() function can only search one column at a time. Hence, I suggest having a while loop to loop over the first three columns and exits once the cell variable is not None or the counter goes over three as you don't want it to iterate over columns four or five.

column = 1
cell = None

while cell = None and column <= 3:
    cell = sheet.find(userInput, in_column=column)
    column += 1 

if cell != None: 
    question_col = sheet.acell(f'D{cell.row}').value
    answer_col = sheet.acell(f'E{cell.row}').value
    print(f'{question_col}: {answer_col}')

else:
    print("I don't know the answer to that question")

edited Jun 28, 2024 at 13:33

answered Jun 28, 2024 at 13:16

Adam DS

7536 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to search specific columns and return specific values in python using Google spreadsheet?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related