python - 如何使用 gspread 将表格(列表列表)写入 Google 电子表格

Question

我有一个以 Python 的列表形式呈现的表格，我想使用 gspread 库将其写到一些 Google 电子表格中。但是，gspread 似乎没有开箱即用的功能。当然，我可以使用循环并更新特定的单元格，但这是一个非常低效的解决方案，因为它必须执行多个请求(每个单元格一个请求)。如何做得更好？

Answer 1

你可以使用Worksheet.range选择你要更新的范围，然后把你的表格内容写到这个范围内，使用Worksheet.update_cells更新他们成批。

以下截取的代码改编自this tutorial .

def numberToLetters(q):
    """
    Helper function to convert number of column to its index, like 10 -> 'A'
    """
    q = q - 1
    result = ''
    while q >= 0:
        remain = q % 26
        result = chr(remain+65) + result;
        q = q//26 - 1
    return result

def colrow_to_A1(col, row):
    return numberToLetters(col)+str(row)

def update_sheet(ws, rows, left=1, top=1):
    """
    updates the google spreadsheet with given table
    - ws is gspread.models.Worksheet object
    - rows is a table (list of lists)
    - left is the number of the first column in the target document (beginning with 1)
    - top is the number of first row in the target document (beginning with 1)
    """

    # number of rows and columns
    num_lines, num_columns = len(rows), len(rows[0])

    # selection of the range that will be updated
    cell_list = ws.range(
        colrow_to_A1(left,top)+':'+colrow_to_A1(left+num_columns-1, top+num_lines-1)
    )

    # modifying the values in the range

    for cell in cell_list:
        val = rows[cell.row-top][cell.col-left]
        cell.value = val

    # update in batch
    ws.update_cells(cell_list)

您可以通过以下方式使用它:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# your auth here
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)

gc = gspread.authorize(credentials)
# your spreadsheet have to be shared with 'client_email' from credentials.json

gc = gspread.authorize(credentials)
# end of auth

spreadsheet = gc.open_by_url(my_url) # url to your spreadsheet here
ws = spreadsheet.sheet1 # or select any other sheet

table = [['one', 'two', 'three'], [4, 5, 6]]

# you may need to resize your worksheet so it have the neccessary cells
# ws.resize(len(table),len(table[0]))

update_sheet(ws, table)