How to Export Pandas Data Frame to Google Sheets Using Python

Sync project with Google API console.

To sync the Project with the Google API console, first, we need to create a project via Google Cloud Console.

To create credentials, we need to enable the below two APIs by searching in the search bar:

• Google Drive API
• Google Sheet API

Now how to create credentials? To do that, we are required to follow the below steps:

1. Click on Create Credentials.
2. Select Service Account, enter Service Account name, and then press Done (rest of the information is optional)
3. Once it is created, click on it, go to the Keys tab, and download the key in JSON format.

The downloaded JSON file content (saved in the service_account.json file) may look like this:

{
  "type": "service_account",
  "project_id": "some-project-id",
  "private_key_id": "eb...25",
  "private_key": "-----BEGIN PRIVATE KEY-----\nNrDyLw...jINQh/9\n-----END PRIVATE KEY-----\n",
  "client_email": "123...999-yourclientemail@projectid.iam.gserviceaccount.com",
  "client_id": "473...hd.apps.googleusercontent.com",
  ...
}

Access the Google Sheet API.

Install the below Python libraries to connect and interact with Google Sheets.

• gspread

PS C:\> pip install gspread

• oauth2client

PS C:\> pip install oauth2client

Let’s create a client through the code using the downloaded API to connect the Google Sheets.

Example Code (saved in demo.py):

import gspread
from oauth2client.service_account import ServiceAccountCredentials


def create_connection(service_file):
    client = None
    scope = [
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/drive.file",
    ]
    try:
        credentials = ServiceAccountCredentials.from_json_keyfile_name(
            service_file, scope
        )
        client = gspread.authorize(credentials)
        print("Connection established successfully...")
    except Exception as e:
        print(e)
    return client


service = "service_account.json"  # downloaded Credential file in JSON format
client = create_connection(service)

First, we imported gspread, and from Python Package outh2client, we imported a class ServiceAccountCredentials stored in the service_account.py file.

The function create_connection() connects client to the Google Sheets. It takes the service_file as an argument where the credential key is stored.

Don’t forget to keep the service_account.json in the same directory as demo.py or store its path in the service variable. Inside this function, we create the credentials using ServiceAccountCredentials.from_json_keyfile_name().

Then we provide the credentials to gspread.authorize() that checks its authenticity and creates a client if the key stored in service_file is valid.

In case of any error, this function will print the Exception without breaking the program.

Export Pandas data frame to the created Google Sheet.

To export Pandas data frame to a Google sheet, first, we have to create it. Let’s write a code that creates a Google sheet.

Example Code (saved in demo.py):

def create_google_sheet(client, sheet_name):
    google_sheet = None
    try:
        google_sheet = client.create(sheet_name)
        google_sheet.share(
            "@gmail.com",  # enter the email you want to create a google sheet on
            perm_type="user",
            role="writer",
        )
        print("Google Sheet created successfully...")
    except Exception as e:
        print(e)
    return google_sheet


service = "service_account.json"  # downloaded Credential file in JSON format
client = create_connection(service)

sheet_name = "50_Startups"  # google sheet name of your choice
google_sheet = create_google_sheet(client, sheet_name)

The function create_google_sheet takes the already created client and sheet_name as arguments and uses the client.create() function to create a Google sheet.

Note that this google sheet is created on the client built using the downloaded API. So we must share it with the email we used to create a service account, giving the writing permissions using the google_sheet.share() function.

Here, the final step is exporting data to Google Sheets. To do this, we must install the Python library pandas using the following:

PS C:\> pip install pandas

Let’s run that code that finally exports the Pandas data frame to Google sheets.

Example Code (saved in demo.py):

import pandas as pd


def export_dataframe_to_google_sheet(worksheet, dataframe):
    try:
        worksheet.update(
            [dataframe.columns.values.tolist()] + dataframe.values.tolist()
        )
        print("DataFrame exported successfully...")
    except Exception as e:
        print(e)


df_file = "50_Startups.csv"
dataframe = pd.read_csv(df_file)
worksheet = google_sheet.get_worksheet(0)
export_dataframe_to_google_sheet(worksheet, dataframe)