Python & Google Sheets - Check in which Row the data is

Question

So I have something like that:

scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name(file_path, scope)
client = gspread.authorize(creds)
sheet = client.open('Test').sheet1

username = input('Username: ')

test = sheet.col_values(1)

if username in test:
    print(username.row) # This won't work

Explanation: I want it to "scan" the Sheet for the username, if it exists it should tell me in which row it is. Example: print(username.row) = 5 (5 = 5. Row)

Does this answer your question? [Finding the index of an item in a list](https://stackoverflow.com/questions/176918/finding-the-index-of-an-item-in-a-list) — Pranav Hosangadi, Jul 23 '20 at 15:30

score 1 · Answer 1 · answered Jul 22 '20 at 12:29

I understand that you got an username (known string) and you want to know in which cell it is written if any. In that case, you would need to read every cell and iterate the data until a match is found. To do that you could use spreadsheets.values.get. You can see an example in the linked documentation. If you find a match, the cell would be on the natural order of the range array (that is if the match is in array[4][2], then the A1 notation would be fifth row and third column). Please, ask me any question if you have doubts.

So maybe I understood something wrong but this works: if username in test:, It finds the Username. I just need to look in which Row it is so I can check the Password or anything else in this Row. — Idunno, Jul 22 '20 at 12:32

score 1 · Accepted Answer · answered Jul 22 '20 at 22:07

You almost had it.

test = sheet.col_values(1)
rownum = test.index(username) + 1
row = sheet.row_values(rownum)

test.index(username) gives the first occurrence of username in test. Because row numbers are 1-based but list indices are 0-based, you need to add 1 to the index to get the row number. Then, you get the values in that row.

score 0 · Answer 3 · answered Jul 22 '20 at 15:30

0

So I found a solution:

scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name(file_path, scope)
client = gspread.authorize(creds)
sheet = client.open('Test').sheet1

username = input('Username: ')

i = 1
ii = 1

while True:
    usernames = sheet.row_values(i)
    if not usernames:
        ii += 1
    if ii == 5:
        print('The Username is wrong.')
        sleep(2)
        sys.exit()
    if username in usernames:
        break
    i += 1

passwords = sheet.row_values(i)
password = input('Password: ')
if password in passwords:

And if you have anything else in that row you would just add more etc.

answered Jul 22 '20 at 15:30

Idunno

57
6

This is a bad way to do it because you're going to use up way too many API calls if you ever decide to scale your spreadsheet beyond 5 usernames. Even now, you could end up using 5 API calls where one is sufficient. You should use `spreadsheets.values.get` like Jacques-Guzel suggested to get the entire column first, then iterate over the values in the retrieved the column. – Pranav Hosangadi Jul 22 '20 at 19:46
Would it be a problem if I would use way too many API calls? – Idunno Jul 22 '20 at 19:55
It's wasteful. You could be rate limited. Since each API call has some network overhead in sending and receiving the request/response, your code will take longer to run. – Pranav Hosangadi Jul 22 '20 at 20:48
Alright, could you give me an example because I don't really get it how to do what you said @PranavHosangadi – Idunno Jul 22 '20 at 21:04
I realized you're using the [gspread](https://github.com/burnash/gspread) package and not the [google api client](https://github.com/googleapis/google-api-python-client). – Pranav Hosangadi Jul 22 '20 at 21:59
For your package, you should use [`col_values()`](https://gspread.readthedocs.io/en/latest/api.html?highlight=column_values#gspread.models.Worksheet.col_values) to get all the values in the column and then find the username in the list you get. Once you have found it, you can use [`row_values()`](https://gspread.readthedocs.io/en/latest/api.html?highlight=column_values#gspread.models.Worksheet.row_values) to get the values in the correct row. – Pranav Hosangadi Jul 22 '20 at 21:59
But I already tried it, I can't get the row value if I only have the col value. – Idunno Jul 22 '20 at 22:52

Python & Google Sheets - Check in which Row the data is

3 Answers3