Accessing Google Sheets using Python

Published in

Analytics Vidhya

5 min readJul 29, 2020

Read/Write Google Sheet using Pandas DataFrame

In this article, I would like to explain the options to read and write google sheets using python. The Google Sheets API lets us read and modify any aspect of a spreadsheet. Accessing this functionality using Python and linking to Python DataFrames helps us to automate and do more data analysis.

Install Required Software and Libraries:

Before jumping into the steps to achieve this, we need to have the following:

Python 2.6 or greater: I prefer to install the Anaconda Individual Edition, as it has Python distribution and also pip package management tool. Instructions to create Python Development Environment can be found in my previous article.
Pandas and Google Client Libraries: Once we have Python and pip, we can install the required libraries in a terminal/command prompt as below:

pip install --upgrade pandas
pip install google_spreadsheet google-auth-oauthlib

Google Cloud Platform and Google Sheets API

As we need to enable Google Sheets API which is part of Google Cloud Platform (GCP), first we need to activate GCP. Visit GCP console page, login with your google account and accept the terms to activate your account. After activating your GCP account, click on “APIs & Services” in the left menu and select OAuth consent screen. Select either Internal or External depending upon your choice and click Create. Enter the Application name and Save. We need to create credentials to use the API. Click “Credentials” in the left menu, then click “CREATE CREDENTIALS” and select “OAuth client ID”. Select “Desktop app” as Application type and enter proper name for the app. When you press Create button, it will show the Client ID and Client Secret which we will be used later. Click the download button in Credentials page and save the JSON file.

Get Google Sheet ID

We installed the required libraries and enabled the Google Sheets API. We need to get the Google Sheet ID that we want to access in our Python code. Open the Google Sheet in browser window and click the green share button.

This will open the permissions page, change the permissions to allow anyone with the link to either view or edit as per your need. Copy the Sheet ID from the generated link which is highlighted (yellow color) in below picture.

We completed all the required steps, now we can start writing the Python code.

Reading Google Sheet using Python

Copy the below code and update the code in between the USER INPUT section accordingly.

import os
import sys
import pickle
import pandas as pd
from __future__ import print_function
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request# If modifying these scopes, delete the file token.pickle.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']# Get the script path
srcDir = os.path.abspath(os.path.dirname(sys.argv[0]))#### USER INPUT ####
credentialFile = os.path.join(srcDir, 'credentials.json')
# The ID and range of a sample spreadsheet.
sheetID = '1KuUE8Ifhh6HoOg_tXGIlc3_LcZyrHkTMBLvlFffqMTM'
dataRange = 'Sheet1!A1:F31'
#### END USER INPUT ####def Create_Service(client_secret_file, api_service_name, api_version, *scopes):
    SCOPES = [scope for scope in scopes[0]]
    print(SCOPES)
    
    cred = None
    # The file token.pickle stores the user's access and refresh tokens, and is
    # created automatically when the authorization flow completes for the first
    # time.
    if os.path.exists('token.pickle'):
        with open('token.pickle', 'rb') as token:
            cred = pickle.load(token)
    # If there are no (valid) credentials available, let the user log in.
    if not cred or not cred.valid:
        if cred and cred.expired and cred.refresh_token:
            cred.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(client_secret_file, SCOPES)
            cred = flow.run_local_server()with open('token.pickle', 'wb') as token:
            pickle.dump(cred, token)try:
        service = build(api_service_name, api_version, credentials=cred)
        print(api_service_name, 'service created successfully')
        return service
    except Exception as e:
        print(e)
        return Nonedef get_google_sheet_data():
    """Shows basic usage of the Sheets API.
    Retruns the Google Sheet data as Pandas DataFrame.
    """
    service = Create_Service(credentialFile, 'sheets', 'v4', SCOPES)# Call the Sheets API
    sheet = service.spreadsheets()
    result = sheet.values().get(spreadsheetId=sheetID, range=dataRange).execute()
    values = result.get('values', [])if not values:
        print('No data found.')
        return None
    else:
        df=pd.DataFrame(values[1:], columns=values[0])
        return dfif __name__ == '__main__':
    sheet_df = get_google_sheet_data()

Running this code in your python environment will read the data from the Google Sheet and create a dataframe ‘sheet_df’. We can analyze the data using the amazing Pandas DataFrame. If we observe the code, all we are doing in this code as follows:

Import necessary modules/libraries.
Defining the scope, which will let us either only read or read/write the Sheet.
Define user inputs for the code to run such as Google Sheet API credentials JSON file, Google Sheet ID and the data range in the Google Sheet.
Authenticate Google API. When we run the code the ‘Create_Service’ function will open the Google Authentication URL in a browser, ask us to login with our account and Accept the permissions. The credentials are saved in ‘token.pickle’ file for next use and this file will be updated whenever they are expired.
Call the Google Sheet API to read the required data.
Create the Pandas DataFrame and return it for further analysis.

Writing Google Sheet using Python

In order to write to Google Sheet, first we need to make the sheet Editable by changing the share preferences and select “Editor” instead of “Viewer” and copy the Sheet ID.

Add below code to the python file and update the values based on your requirement.

def append_google_sheet_data(values, value_input_option):
    body = {
        'values': values
    }
    service = Create_Service(credentialFile, 'sheets', 'v4', SCOPES)# Call the Sheets API
    sheet = service.spreadsheets()
    result = sheet.values().append(spreadsheetId=sheetID, range=dataRange, valueInputOption=value_input_option, body=body).execute()
    print('{0} cells appended.'.format(result \
                                           .get('updates') \
                                           .get('updatedCells')))values = [
    ["Name1", "Male", "4. Senior", "CA", "English", "Basketball"],
    ["Name2", "Female", "3. Junior", "MA", "Math", "Baseball"],
]
append_google_sheet_data(values, "RAW")

Calling the “append_google_sheet_data” function will append the values to the sheet at the end of the existing data. Please visit the Google Sheet API documentation for more options.