Python (Google Sheets API) - Searching for a certain string and returning the whole row

Question

so right now I have a spreadsheet that contains a list of banned players from my discord server. Right now I'm trying to create a python script that allows you to input a string/int and if it finds a match it returns the whole row.

Example of the Spreadsheet: (There will be more columns as this is just an example)

https://gyazo.com/82aee173103cbb77cff067a9e28c5fc1

So as you can see, if the user gives the discord ID, it should return the whole row which the value is on.

So far my setup:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]

creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)

client = gspread.authorize(creds)

sheet = client.open("MRP Blacklist Data").sheet1

values = result.get('values', [])

    if not values:
        print('No data found.')
    else:
        print('Name, Major:')
        for row in values:
            print('%s, %s' % (row[0], row[4]))

The code is incomplete and I have no clue how I could complete it. Any tips or suggestions would help greatly!

Answer 1

You can use gspread .find("string") and .row_values(n) function.

.find("string") - Get the position of the cell that matches the string. It has .row and .cell properties which you can use locate the cell.

row_values(n) - Get all values from the given row number:

In your code:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)
client = gspread.authorize(creds)
sheet = client.open("MRP Blacklist Data").sheet1

print(sheet.row_values(sheet.find("ABC").row))

It will print something like this:

['Test1', 'TestID1', 'ABC', 'Costal Craft', 'None', 'Just Because']

Here's my sample data:

Note: .find() will only return the first occurrence of the string, if you want to search all the occurrence, use .findall() all loop through the list and use row_values to each element.

.findall() example:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)
client = gspread.authorize(creds)
sheet = client.open("MRP Blacklist Data").sheet1
values = sh.findall("ABC")
for r in values:
    print(', '.join(sh.row_values(r.row)))

Output should look like this:

Test1, TestID1, ABC, Costal Craft, None, Just Because
Test3, TestID3, ABC, Costal Craft, None, None

Test Data:

Reference:

Finding a cell