python - 在添加数据之前使用 Python gdata 清除工作表中的行

Question

我有一个 Google 电子表格，我正在使用 python 脚本和 gdata 库填充值。如果我多次运行该脚本，它会将新行附加到工作表，我希望脚本在填充它之前先清除行中的所有数据，这样我每次运行时都会有一组新数据脚本。我试过使用:

UpdateCell(row, col, value, spreadsheet_key, worksheet_id)

但是如果没有像这样运行两个 for 循环，有没有更简洁的方法？此外，这个循环似乎慢得可怕:

for x in range(2, 45):
      for i in range(1, 5):
        self.GetGDataClient().UpdateCell(x, i, '', 
                                         self.spreadsheet_key,
                                         self.worksheet_id)

Answer 1

不确定您是否解决了这个问题，但是关于加速清除当前数据，请尝试使用 batch request .例如，要清除工作表中的每个单元格，您可以这样做:

cells = client.GetCellsFeed(key, wks_id)
batch_request = gdata.spreadsheet.SpreadsheetsCellsFeed()

# Iterate through every cell in the CellsFeed, replacing each one with ''
# Note that this does not make any calls yet - it all happens locally
for i, entry in enumerate(cells.entry):
  entry.cell.inputValue = ''
  batch_request.AddUpdate(cells.entry[i])

# Now send the entire batchRequest as a single HTTP request
updated = client.ExecuteBatch(batch_request, cells.GetBatchLink().href)

如果你想做一些事情，比如保存列标题(假设它们在第一行)，你可以使用 CellQuery:

# Set up a query that starts at row 2
query = gdata.spreadsheet.service.CellQuery()
query.min_row = '2'

# Pull just those cells
no_headers = client.GetCellsFeed(key, wks_id, query=query)

batch_request = gdata.spreadsheet.SpreadsheetsCellsFeed()

# Iterate through every cell in the CellsFeed, replacing each one with ''
# Note that this does not make any calls yet - it all happens locally
for i, entry in enumerate(no_headers.entry):
  entry.cell.inputValue = ''
  batch_request.AddUpdate(no_headers.entry[i])

# Now send the entire batchRequest as a single HTTP request
updated = client.ExecuteBatch(batch_request, no_headers.GetBatchLink().href)

或者，您也可以使用它来更新您的单元格(也许更符合您的需要)。文档链接提供了一种基本方法，即(从文档中复制以防链接发生变化):

import gdata.spreadsheet
import gdata.spreadsheet.service

client = gdata.spreadsheet.service.SpreadsheetsService()
# Authenticate ...

cells = client.GetCellsFeed('your_spreadsheet_key', wksht_id='your_worksheet_id')

batchRequest = gdata.spreadsheet.SpreadsheetsCellsFeed()

cells.entry[0].cell.inputValue = 'x'
batchRequest.AddUpdate(cells.entry[0])
cells.entry[1].cell.inputValue = 'y'
batchRequest.AddUpdate(cells.entry[1])
cells.entry[2].cell.inputValue = 'z'
batchRequest.AddUpdate(cells.entry[2])
cells.entry[3].cell.inputValue = '=sum(3,5)'
batchRequest.AddUpdate(cells.entry[3])

updated = client.ExecuteBatch(batchRequest, cells.GetBatchLink().href)