Writing a dataframe to google sheets using python/pandas

Question

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)

Tanaike · Accepted Answer · 2020-06-24 01:01:40Z

8

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:

From:

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

To:

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:

answered Jun 24, 2020 at 1:01

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

HamishCrichton Over a year ago

Thanks, Tanaike, that's perfect.

Tanaike Over a year ago

@HamishCrichton Thank you for replying. I'm glad your issue was resolved.

conor Over a year ago

Note that if you have datetimes and get the error TypeError: Object of type Timestamp is not JSON serializable, simply convert the relevant fields to 'str' first: df = df.astype('str') (will convert all columns).

Collectives™ on Stack Overflow

Writing a dataframe to google sheets using python/pandas

1 Answer 1

Modification points:

Modified script:

Note:

References:

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Modified script:

Note:

References:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related