Dataframe to Google Sheet

Setup in Google Console

• Create a new project in Google Console.
• You have to enable google services in order to get access to google sheet. Under API & Services Click on Enable API and Services
  • Enable Google Drive API
  • Enable Google Sheets API
• Now go to Credentials menu and click on Manage service accounts.
  • Click on Create Service Account
  • Optionally you can also add roles but we are going to skip that since we need to access files for different users.
  • Inside Service Account Details click on Add key. Download it and store it we will need it later while setting up functionality in jupyter.

Setup in Jupyter

• Place the service key in the root of your jupyter folder. (You can change the path too, but you have to modify it inside the function.)
• To get access of google drive and google sheet we need to install some libraries. Install the following libraries:

    pip install pandas gspread google-api-python-client

• The following function takes dataframe and two optional arguments sheet_name and email. sheet_name will rename the sheet to a given name and email will add permission to add the created file into the given account's google drvive. Copy the following function:

    import pandas as pd
    from numpy.random import randint
    import gspread
    from google.oauth2 import service_account
    from googleapiclient.discovery import build

    def df_to_sheet(df, sheet_name, email_address):
        """
        Connects with google drive. Creates a sheet and copy data from dataframe, stores and  create a link.

            :param df: dataframe you want to store.
            :param sheet_name: Name of the sheet you want to save in google drive.
            :param email_address: of the user of google drive.
            :return: returns URL of the file saved 
        """

        scopes = [ "https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive"]
        credentials = service_account.Credentials.from_service_account_file("./credentials.json",scopes = scopes)
        permission = { "role": 'writer', "type": 'user', "emailAddress": email_address}

        try:
            # connecting to sheets and google drive
            sheet_service = gspread.authorize(credentials) 
            drive_service = build("drive", "v3", credentials=credentials)

            # creating sheet and copying data in worksheet
            sh = sheet_service.create(sheet_name)
            worksheet = sh.get_worksheet(0)
            worksheet.update([df.columns.values.tolist()]+df.values.tolist())
            drive_service.permissions().create(fileId=sh.id, body=permission).execute()

        except Exception as e: 
            print('Error creating google sheet: ',e)
            return None
        return "https://docs.google.com/spreadsheets/d/%s" % sh.id

• Now you can create and store any dataframe you want. Note: make sure there are no null values inside dataframe df.fillna('',inplace=True).

    ## creating a dataframe
    df = pd.DataFrame({'x':randint(5, size=10),'y':randint(5, size=10)})

    ## storing dataframe to google sheet 
    df_to_sheet(df, sheet_name='New Sheet', email_address='[e-mail]')