Python(Google Sheets API)-搜索特定字符串并返回整行

问题描述

现在，我有一个电子表格，其中包含我的不和谐服务器中被禁止的玩家的列表.现在，我正在尝试创建一个python脚本，该脚本可让您输入一个字符串/整数，如果找到匹配项，则返回整行.

电子表格示例:(会有更多列，因为这只是一个示例)

注意:.find()仅返回字符串的第一个匹配项，如果要搜索所有匹配项，请使用.findall()all遍历列表，并对每个元素使用row_values.

.findall()示例:

 导入gspread从oauth2client.service_account导入ServiceAccountCredentialsscope = [" https://spreadsheets.google.com/feeds，'https://www.googleapis.com/auth/spreadsheets'、" https://www.googleapis.com/auth/drive.文件"，"https://www.googleapis.com/auth/drive"]creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json"，作用域)客户端= gspread.authorize(信用)sheet = client.open("MRP黑名单数据").sheet1值= sh.findall("ABC")对于r的值:print('，'.join(sh.row_values(r.row))) 

输出应如下所示:

  Test1，TestID1，ABC，Coastal Craft，无，仅因为Test3，TestID3，ABC，Coastal Craft，无，无 

测试数据:

参考:

查找单元格

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!

解决方案

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

这篇关于Python(Google Sheets API)-搜索特定字符串并返回整行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！