Using Python to Write a Dataframe to a Google Spreadsheet

 I had all sort of issues with this one.  Multiple searches, and trying many variations of the Google API access code modules to find one that worked.  Even the one that works omitted some salient details.

Reading data from a Google sheet was straightforward, and required the setting up of a project in the Google Cloud console.  My assumption had been that the JSON credential file that enabled the read would work for the write.

Nope.

First, the code that did (ultimately) allow me to write to the same sheet is straight off this website:

HOTLINK:  medium.com

The first few steps on the page relate to creating Google Cloud project credentials from scratch.  In the Python code, the first step is to install the required libraries:

Their next step (#4) is the following code:

This is how it looks in my app code:

NOTE the two different JSON files.  We'll get back to those later.

And the code that writes the dataframe to the Google sheet:

And as it appears in my code:

Running a test gave standard errors referring to email address and access.  A fair bit of rummaging and targeted searching resulted in an elegant solution.

Simply, within the Google Cloud portal for the project I had just the one Service Account, which I had used to generate the JSON credential file used to read data.  What is needed is a SECOND service account, used to write.

When setting up the second account Google creates a secure email address - here is a snapshot of my accounts:

Create and download the JSON credential file for the new account - this is the one to use for writing. The first service account is used to read.

The last thing to do is to open the Google sheet and share it to the new service account email.

Having done that, the code works smoothly and writes the entire Python dataframe to the nominated worksheet in the spreadsheet.