How to import a CSV file using Google Sheets API V

问题:

Background

I'm developing a Python 2.7 script that analyzes data from an SQL table and at the end, generates a CSV file.

Once the file is generated, I'm logging into my google sheet account and use the import option to import my CSV file into the google spreadsheet

The manual labor is kinda stupid and I wish to add this ability to my script.

Google Sheets API V4

So, I followed this guide, Python Quickstart and was able to complete all the steps.

Then I followed Google Sheets API reference and looked into Method: spreadsheets.create. If I understand correctly, it does not provides the options to import from a file.

It seems like there is no API for the import functionality.

Question

How to import a CSV file using Google Sheets API V4? Is their an example/reference that I'm missing?

回答1:

You have two options for importing g CSV file. You can use the Drive API to create a spreadsheet from a CSV, or you can use the Sheets API to create an empty spreadsheet and then use spreadsheets.batchUpdate with a PasteDataRequest to add CSV data.

回答2:

I like Burnash's gspread library, but the import_csv function in his answer is limited. It always starts the paste at A1 of the first worksheet (tab) and deletes all other tabs.

I needed to paste starting at a particular tab and cell, so I took Sam Berlin's suggestion to use a PasteDataRequest. Here's my function:

def pasteCsv(csvFile, sheet, cell):
    '''
    csvFile - path to csv file to upload
    sheet - a gspread.Spreadsheet object
    cell - string giving starting cell, optionally including sheet/tab name
      ex: 'A1', 'MySheet!C3', etc.
    '''
    if '!' in cell:
        (tabName, cell) = cell.split('!')
        wks = sheet.worksheet(tabName)
    else:
        wks = sheet.sheet1
    (firstRow, firstColumn) = gspread.utils.a1_to_rowcol(cell)

    with open(csvFile, 'r') as f:
        csvContents = f.read()
    body = {
        'requests': [{
            'pasteData': {
                "coordinate": {
                    "sheetId": wks.id,
                    "rowIndex": firstRow-1,
                    "columnIndex": firstColumn-1,
                },
                "data": csvContents,
                "type": 'PASTE_NORMAL',
                "delimiter": ',',
            }
        }]
    }
    return sheet.batch_update(body)

Note that I used a raw pasteData request rather than the higher-level update_cells method to take advantage of Google's automatic (correct) handling of input data that contains quoted strings, which may contain non-delimeter commas.

回答3:

An alternative to Sam Berlin's answer, you can turn your CSV into a list of lists and set that to your POST payload.

Such a function looks something like this:

def preprocess(table):
    table.to_csv('pivoted.csv') # I use Pandas but use whatever you'd like
    _file = open('pivoted.csv')
    contents = _file.read()
    array = contents.split('\n')
    master_array = []
    for row in array:
        master_array.append(row.split(','))
    return master_array

That master array gets thrown into the following:

body = {
      'values': newValues
}

    result2 = service.spreadsheets().values().update(spreadsheetId=spreadsheetId, range=rangeName + str(len(values) + start + 1), valueInputOption="USER_ENTERED", body=body).execute()

It works just fine for me.

回答4:

Another alternative to Sam Berlin's answer. If you're using Python, you can use the Drive API via gspread to import a CSV file. Here's an example:

import gspread

# Check how to get `credentials`:
# https://github.com/burnash/gspread

gc = gspread.authorize(credentials)

# Read CSV file contents
content = open('file_to_import.csv', 'r').read()

gc.import_csv('<SPREADSHEET_ID>', content)

Related question: Upload CSV to Google Sheets using gspread