Export pandas data frames to Google Sheet

This module allows you to export a pandas data frame into Google Sheet in just two lines of code.

Table of Contents

• 1. Overview.
  • 1.1. Goal of the project
  • 1.2. Code structure
• 2. Usage examples
  • 2.1. Usage with implicit json oauth file call
  • 2.2. Usage with explicit json oauth file call
• 3. Required packages
• 4. Class elements
  • 4.1. Arguments
  • 4.2. Attributes
  • 4.3. Properties
  • 4.4. Methods
    • 4.4.1. to_google_sheet()

1. Overview.

The GoogleSheet class allows the user to rapidly export a pandas data frame to a sheet within a Google Sheet workbook. It relies mostly on the gspread module which works as an intermediary with the Google Sheet API.

To be able to complete these operations, some quick steps on the Google Console should be carried out. This article from the gspread library provides a detailed explanation on what to do. In summary:

• Obtain a json file with the credentials through the Google Developers Console.
• Place the file in the right folder of your device if you want to call it freely from any script.

CustomExcel is a subclass of the Spreadsheet class, whose code and doc can be found at this link.

1.1. Goal of the project

The aim of this class is to speed up the upload process to Google Sheet, making it as similar as possible to a regular output to excel, as it happens in pandas through the built-in method dataframe.to_excel().

2. Usage examples

As an example, suppose that you want to send the pandas data frame df to the first sheet of the Google Sheet workbook named "MyWork". For this to work correctly, the workbook needs to be shared with edit permission with the email specified in the json authentication file.

2.1. Usage with implicit json oauth file call

If you choose to save the json oauth file as "credentials.json" in the default folder specified by the gspread library (doc) - in my case it was "C:\Users\MyUser\AppData\Roaming\gspread" - the usage is as follows:

sheet = GoogleSheet(dataframe=df, google_workbook_id="MyWork")
sheet.to_google_sheet()

2.2. Usage with explicit json oauth file call

The json file can also be kept in any other arbitrary folder. In this case, the path to the json file needs to be passed for the parameter auth_keys. For the following example suppose that the json file was named "credentials.json" and placed in the "auth" folder inside the working directory. The the usage is the following:

sheet = GoogleSheet(dataframe=df, google_workbook_id="MyWork", auth_keys="auth\credentials.json")
sheet.to_google_sheet()

3. Required packages

CustomExcel requires the following custom module created by me:

• Spreadsheet link

This class relies on the following built-in packages:

• typing
• string [by Spreadsheet class]

And on the following additional packages:

• gspread
• numpy [by Spreadsheet class]

Documentation

Consult the documentation here.