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

您可以使用 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)

This recent answer 类似的问题似乎简单得多:

my_list = [['a', 'b'], ['c', 'd'], ['e', 'f'], ['g', 'h']]

sh.values_update(
    'Sheet1!A1', 
    params={'valueInputOption': 'RAW'}, 
    body={'values': my_list}
)

顺便说一句,代码由@Burnash提供( gspread 开发者)

您可以使用以下内容 update_sheet 功能与前一个流程相结合,以获得更简洁的解决方案:

def update_sheet(ws, table, rangeStart='A', rangeEnd='C')

  for index, row in enumerate(table):

    range = '{start}{i}:{end}{i}'.format(
      start=rangeStart, end=rangeEnd, i=index+1
    )
    cell_list = worksheet.range(range)

    for i, cell in enumerate(cell_list):
      cell.value = row[i]

    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)