Uploading Pandas Dataframes to Google Sheets

Intro and Motivation

Recently during my work I often find myself in need of reading/writing to Google Sheets. The later is a very convenient environment to show off your work, if it is in the table format.
Then you can simply drop off the link and do not worry that info will get leaked, due to Google Drive's built in authentication mechanism. You could of course upload your tables to GitHub, for example, but then:
1. you may find that your manager does not have (and does not want to have) a GitHub account;
2. Google Sheets have convenient stack of mobile/desktop/web apps built around them, so you can inspect the tables virtually on any device, perhaps except your watch (so far)

Now, there is a handy tutorial showing how to use Google Sheets Python API, however I always find it a bit tedious to use. After some time it occurred to me
that it would be nice to have a set of routines, which would allow one to upload/download Google Sheets from/to Pandas Dataframes (the later being, to my knowledge, the de-facto standard library for tables manipulation in Python). And so
I did.

Main Part

Before using the routines below, make sure that you enabled Sheets API and installed the required libraries:

pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib

The first routine is used for initialization:

from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
import string
import os

def get_creds(client_secret_file, token_file=".token.json" , create_if_not_exist=False):
    creds = None
    if os.path.exists(token_file):
        creds = Credentials.from_authorized_user_file(token_file, _SCOPES)
    if not creds or not creds.valid:
        if create_if_not_exist:
            if creds and creds.expired and creds.refresh_token:
                creds.refresh(Request())
            else:
                flow = InstalledAppFlow.from_client_secrets_file(
                    client_secret_file, _SCOPES)
                creds = flow.run_local_server(port=0)
            with open(token_file, 'w') as token:
                token.write(creds.to_json())
        else:
            raise Exception(
                f"provide valid credentials file: {token_file} given")

    return creds

You can use it as
py
creds = get_creds("client_secret_file.json", create_if_not_exist=True)


to create (or retreive previously created) credentials object. If create_if_not_exist flag is set to True, it will read the credentials given in argument ("client_secret_file.json" in the example above) and then either retrieve
the token saved in ".token.json" (the name can be overridden) or use client_secret_file.json to create the token, and then save it. Anyhow, what you get in the end is the creds object, which can be used for further requests.

The next subroutine upload_df_to_google_sheets is a bit bigger:

def upload_df_to_google_sheets(creds, df, spreadsheet_title):
    service = build('sheets', 'v4', credentials=creds)
    spreadsheet = {
        'properties': {
            'title': spreadsheet_title
        }
    }
    spreadsheet = service.spreadsheets().create(body=spreadsheet,
                                                fields='spreadsheetId').execute()
    spreadsheet_id = spreadsheet.get('spreadsheetId')

    assert len(list(df)) <= len(string.ascii_uppercase)
    range_ = f"A1:{string.ascii_uppercase[len(list(df))-1]}{len(df)+1}"
    if sheet_name is not None:
        range_ = f"'{sheet_name}'!{range_}"
    header = list(df)
    result = service.spreadsheets().values().update(
        spreadsheetId=spreadsheet_id,
        range=range_,
        valueInputOption="RAW",
        body={
            "values": [
                header,
                *[[r[fn] for fn in header] for r in df.to_dict(orient="records")]
            ]
        },
    ).execute()

    return spreadsheet_id

You can use it as
py
spreadsheet_id = upload_df_to_google_sheets(creds, df, "sheet_name")


to create a Google Sheet named sheet_name and upload your dataframe to it. You can the print out the url of the resulting Google Sheet via

print(f"https://docs.google.com/spreadsheets/d/{spreadsheet_id}/edit#gid=0")