如何使用gspread将表格（列表列表）写入Google Spreadsheet [英] How to write a table (list of lists) to Google Spreadsheet using gspread

问题描述

我有一张以Python列表的形式列出的表格，我想用 gspread 库将其写入一些Google Spreadsheet。但是，似乎 gspread 没有这种功能。当然，我可以使用循环和更新特定的单元格，但这是非常低效的解决方案，因为它必须执行多个请求（每个单元一个请求）。如何做得更好？

I have a table which is presented as Python's list of lists and I'd like to write it down to some Google Spreadsheet using gspread library. However, it seems that gspread does not have such function out of the box. Of course I can use loop and update particular cells but it is very inefficient solution because it have to perform multiple requests (one request per cell). How to do it better?

推荐答案

您可以使用 Worksheet.range 选择要更新的范围，然后将表格内容写入此范围，并使用 Worksheet.update_cells 批量更新它们。

You can use Worksheet.range to select the range you want to update, then write down the contents of your table to this range and use Worksheet.update_cells to update them in a batch.

以下代码片段修改自
$ b

The following code snipped is adapted from 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)

您可以按以下方式使用它：

You can use it in the following way:

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)

这篇关于如何使用gspread将表格（列表列表）写入Google Spreadsheet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！