Skip to main content
 首页 » 编程设计

python之有人可以用 python 和 google sheets api 帮助我解决这个问题吗

2024年11月24日9tintown

问题

我正在测试谷歌工作表并尝试制作一个删除所有代表的过滤器,只在工作表上留下参议员。我正在使用此 sheet 中的信息:

代码

import gspread 
from oauth2client.service_account import ServiceAccountCredentials 
from pprint import pprint 
 
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("Congress").sheet1  # Open the spreadhseet 
 
data = sheet.get_all_records()  # Get a list of all records 
 
rowCount = sheet.row_count 
 
val = sheet.col_values(1) 
 
 
i = 1 
for value in val: 
    if value == 'rep': 
        print('Deleted:', sheet.cell(i,1).value, sheet.cell(i,5).value) 
        sheet.delete_row(i) 
    i = i +1 

我得到:

Deleted: rep Young 
Deleted: rep Roby 
Deleted: rep Aderholt 
Deleted: rep Palmer 
Deleted: sen Jones 
Deleted: rep Crawford 
Deleted: rep Womack 

它基本上只删除了我在下面加粗的那些:

它应该删除所有具有“rep”的行,但最终并没有删除所有行,它还删除了一些具有“sen”的行。 我该如何解决这个问题?

请您参考如下方法:

您在遍历行时正在删除行。剩余的行向上移动 - 这会扰乱你的索引 - 你跳过一些行并删除其他行:

例子:

line 0   rep     someone 
line 1   rep     someone 
line 2   sen     someone 

你 for 循环遍历你的行: - 你测试第 0 行,你删除第 0 行,第 1 行变成第 0 行 - 你接下来测试第 1 行 - 太棒了,它是一个 sen - 你跳过了(原来是 1,现在是 0 行)

等等


改为从后面删除:

import gspread 
from oauth2client.service_account import ServiceAccountCredentials 
from pprint import pprint 
 
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("Congress").sheet1  # Open the spreadhseet 
 
data = sheet.get_all_records()  # Get a list of all records 
 
rowCount = sheet.row_count 
 
val = sheet.col_values(1) 
 
for idx,value in reversed(list(enumerate(val,1))): 
    if value == 'rep': 
        print('Deleted:', sheet.cell(idx,1).value, sheet.cell(idx,5).value) 
        sheet.delete_row(idx)