Writing a dataframe to google sheets using python/pandas

2
votes

I am using google sheets to hold data for a shared project. Using Google's Sheets API I access the data, process it in python, and I am trying to update the Sheets file using batchUpdate, in the function writer.

  • If I pass this function data as a list, it works as expected.
  • If I pass a dataframe (as I would like to do) I get: TypeError: Object of type DataFrame is not JSON serializable
  • If I use .to_json(), I get this:

googleapiclient.errors.HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/XXX/values:batchUpdate?alt=json returned "Invalid value at 'data[0].values' (type.googleapis.com/google.protobuf.ListValue), "{"0":{"0":1},"1":{"0":2},"2":{"0":3},"3":{"0":4}}"". Details: "[{'@type': 'type.googleapis.com/google.rpc.BadRequest', 'fieldViolations': [{'field': 'data[0].values', 'description': 'Invalid value at 'data[0].values' (type.googleapis.com/google.protobuf.ListValue), "{"0":{"0":1},"1":{"0":2},"2":{"0":3},"3":{"0":4}}"'}]}]">

Any pointers would be much appreciated.

import pickle
import os.path
import pandas as pd
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
from pprint import pprint

# If modifying these scopes, delete the file token.pickle.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

# Spreadsheet ID: https://docs.google.com/spreadsheets/d/XXX/edit#gid=0
SPREADSHEET_ID = 'XXX'
RANGE_NAME = 'contacts'

def writer(df):
    service = build('sheets', 'v4', credentials=gsheet_api(SCOPES))
    sheet_name = 'contacts'
    data = [{'range' : sheet_name, 'values' : df}]
    batch_update_values_request_body = {
        'value_input_option': 'RAW',
        'data': data }

    request = service.spreadsheets().values().batchUpdate(spreadsheetId=SPREADSHEET_ID,
                                                          body=batch_update_values_request_body)
    response = request.execute()
    pprint(response)

df = [[1, 2, 3, 4]]
writer(df)
1
pythonpandasgoogle-sheetsgoogle-sheets-api

1 Answers

4
votes

I believe your goal and situation as follows.

  • You want to put the dataframe to Google Spreadsheet using googleapis with Python.
  • You have already been able to get and put values for Google Spreadsheet using Sheets API.

For this, how about this answer?

Modification points:

  • I'm not sure about the values of the dataframe. So in this answer, I would like to explain about the modification points using the following sample dataframe.

         A  B  C
  0  1  2  3
  1  4  5  6
  2  7  8  9

  • Unfortunately, the dataframe cannot be directly used for the request body of the method "spreadsheets.values.batchUpdate". So in this case, it is required to convert from the dataframe to the 2 dimensional array. For this, I used tolist().

When your script is modified using the sample dataframe, it becomes as follows.

Modified script:

df = [[1, 2, 3, 4]]
writer(df)

sampleValue = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
df = pd.DataFrame(sampleValue, columns=list('ABC'))
values = [df.columns.values.tolist()]
values.extend(df.values.tolist())
writer(values)

Note:

  • If you don't want to include the header row, please modify as follows.

    • From

        values = [df.columns.values.tolist()]
  values.extend(df.values.tolist())

    • To

        values = df.values.tolist()

References: