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

user_13117792 0
Viewed 1516

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)

google-sheets-api google-sheets python
3 Answers

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.

user_118619540

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.

user_8439530

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.

user_131177920
Related