时间戳

首页 > 解决方案 > Uploading .csv to Google Sheets via Gspread

python - Uploading .csv to Google Sheets via Gspread

问题描述

I am using gspread to refresh a worksheet in Google Sheets with data from a .CSV file. As much as I've looked around the web, I'm having a hard time finding what I think should be a clean answer to my question.

I have the .CSV file in my project directory. Here's my code thus far:

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import csv

scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)
sheet = client.open_by_key('Spreadsheet Key')
worksheet = sheet.worksheet('Worksheet Name')
worksheet.clear()

At this point, I'm not sure what method I should use to mass-upload all the data in the .CSV. I've read that .update_cells() in gspread will only use a single call to the Google Sheets API and is the fastest method, so my question is this:

Using .update_cells(), how can I iterate through my .CSV to be able to post to Sheets?

Some info about my .CSV file is that it has 9 columns, but I need the code to handle any changes in the quantity of rows. Any help is greatly appreciated!

标签： pythoncsvgoogle-sheets-apigspread

解决方案

首先，如果您对进入A1第一个电子表格的数据感到满意，您可以使用gspread.Client.import_csv(), 作为一个非常简单的选项。

否则，请继续阅读。

要使用update_cells()，请将Cell()对象列表传递给它。

您可以从worksheet.range()、worksheet.cell()、worksheet.find()或中获得 gspread 对象worksheet.findall()。（这些函数中的每一个在您访问它们时都会进行网络调用，因此请尽量压缩调用次数。）

然后，对于每个单元格对象，将cell.value字段更改为您的 csv 数据。

for data,cell in zip(csv_data,worksheet.range()): # Whatever range you need.
    cell.value=data # Sets the cell in *local* sheet to the specified data. (String)

因此，这将更改该单元格的所有本地引用以显示您的 csv 数据。（值得指出的是，您的数据在上传时将被转换为字符串。

如果您想将单元格数据读取为数字，我记得会cell.numeric_value显示出来，尽管我在docs中没有看到任何参考。

然后您将所有现在修改的单元格项目传递给update_cells()然后谷歌电子表格将反映您的更改。

您可以在此处查看其他参考资料：https ://github.com/burnash/gspread#updating-cells

此外，在过去不得不解决一个非常相似的挑战（json，不是 csv，但足够接近），这里有一个很好的辅助函数，它将占用一堆列，并完成获取单元对象的繁忙工作，然后发送update_cells()请求。代码可以在 GitHub.com 上找到。

def update_columns(sheet, row, col, columns, execute = True):
    """Update the specified columns. Row and col are the starting most top left
       cell. Each column should be a list of values. Each list should be the
       same length.
    """
    # Step one, no columns is an error.
    if not columns:
        raise ValueError("Please specify at least one column to update.")

    # Otherwise, get that column length.
    r_len = len(columns[0])
    # First check that all columns are the same length.
    for column in columns[1:]:
        if len(column) != r_len:
            # Variable length.
            raise ValueError("Columns are of varying length.")

    # Start making lists.
    update_cells = []

    # Expand the sheet size if needed.
    if col + len(columns) > sheet.col_count:
        sheet.add_cols(col + len(columns) - sheet.col_count)

    if row + r_len > sheet.row_count:
       sheet.add_rows(row + r_len - sheet.row_count)

    # Get the range of cells to be updated.
    print("Range %s %s %s %s" % (row, col, row + r_len - 1 , col + len(columns) - 1))
    update_range = sheet.range (row, col, row + r_len - 1 , col + len(columns) - 1)

    for c, column in enumerate(columns):

        # Get the range on the sheet for the column.
##        column_range = sheet.range(row, col + c, row + len(column), col + c)
        column_range = (update_range[i] for i in range(c, len(update_range), len(columns)))

        for cell, value in zip(column_range, column):
            # Boolean rational.
            if isinstance(value, bool):
                if str(value).upper() != cell.value:
                    # So its NOT the same.
                    cell.value = value
                    update_cells.append(cell)

            # Use numerical_value for numbers.
            elif isinstance(value, (int, float)):
                # For whatever reason, it looks like gsheets
                # truncates to the 10th place.
                # It seems that 11th & 12th place is almost always correct but
                # can actually differ slightly???
                if cell.numeric_value is None or \
                   truncate(value, 10) != truncate(cell.numeric_value, 10):
                    cell.value = value
                    update_cells.append(cell)

            # And for everything else, string handling.
            elif isinstance(value, basestring):
                if value != cell.value:
                    cell.value = value
                    update_cells.append(cell)

            # Handle None
            elif value is None:
                if '' != cell.value:
                    # Set to ''
                    cell.value = ''
                    update_cells.append(cell)

            else:
                # Other type, error.
                raise ValueError("Cell value %r must be of type string, number, "
                                 "or boolean. Not %s." % (value, type(value)))

    # Now take the list of cells and call an update.
    if execute:
        print("Updating %d cells." % len(update_cells))
        if update_cells:
            sheet.update_cells(update_cells)
        return len(update_cells)
    else:
        return update_cells

推荐阅读