Read and Update Google Sheet data with Python

In this example we will use python to read data from Google Sheets and update another one using the Google API.

Enable the Google API

  • Navigate to https://console.developers.google.com/
  • Log in with your Google account
  • Click the Create Project link
  • Enter a project name, you can have 12 free project.
  • Click the API panel
  • Click the ENABLE APIS AND SERVICES link
  • In the G Suite section click theVIEW ALL link
  • Click the Google Sheets API panel
  • Click the Enable button
  • Navigate back to the API panel list and click the Google Drive API panel
  • Click the Enable button

Create API credentials

OAuth 2.0 Client credentials

Create the Consent Screen

First, we need to create a Consent Screen before we create our first OAuth 2.0 credential

  • On the left side select Credentials, click the CREATE CREDENTIALS button and select OAuth client ID
  • Click the CONFIGURE CONSENT SCREEN
  • Select External and click the CREATE button
  • Enter the application name, select an email address from the list, enter the developer’s email address and click the SAVE AND CONTINUE button
  • Add a Space
  • Select ../auth/drive.file
  • Click SAVE AND CONTINUE
  • Save again

Create the OAuth 2.0 credential

  • Create the credential
  • In our case, we build a desktop app
  • Enter a Name and click the CREATE button
  • Close the popup

Download the OAuth2 credentials

  • On the Credentials page click the name of the OAuth 2.0 credential name
  • Click the DOWNLOAD JSON button

Read data from Google Sheets with Python

We will use the Google tutorial at https://developers.google.com/sheets/api/quickstart/python

  • Copy the downloaded OAuth 2.0 credentials file to the application directory.
  • Add the file name to the .gitignore file to avoid committing it to GitHub
  • Enter the downloaded credentials file name in the line
            flow = InstalledAppFlow.from_client_secrets_file(
                'MY_OAUTH_CRED_FILE.json', SCOPES)
  • Set the Google sheet ID and data range in
SAMPLE_SPREADSHEET_ID = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
SAMPLE_RANGE_NAME = 'Class Data!A2:E'
  • The rows of your Google sheet are stored in rows of the values[] array. Adjust the row displayed column numbers in the line
print('%s, %s' % (row[0], row[4]))

Run the application

  • The app will open a page in the default web browser and will ask for a Google login
  • Click Advanced
  • Click the Go to … link
  • Grant the permission
  • Allow the access
  • Confirmation is displayed
  • Your application should display the data from the Google spreadsheet

To use a service account to read Google Sheets

Create a Google Service account

  • Click the CREATE CREDENTIALS button
  • Select the Google Drive API, Other non-UI, Appication data, No, I’m not using them options
  • Click the What credentials do I need? button
  • For the Role select Project, Owner
  • Keep JSON as the private key format, click the Continue button
  • The JSON private key downloads, save it to be able to access the API

Share the Google sheet with the Service Account

  • Open the downloaded JSON key file and copy the client_email value
  • Open the Google Sheet you want to read from
  • Click the Share button
  • Make the service account a Viewer to read the data, but if you later want to update the sheet with the same account make the service account an Editor

Reading Google sheets with a Service Account

Install the gspreadoauth2client, and pandas Python libraries on your workstation

  • Open a terminal window and execute the commands
pip3 install gspread
pip3 install --upgrade google-api-python-client oauth2client
pip3 install pandas
  • Save the code in a .py file
# import the libraries
import gspread
import pandas as pd
from oauth2client.service_account import ServiceAccountCredentials

# define the scope
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']

# get the credentials from the JSON file
creds = ServiceAccountCredentials.from_json_keyfile_name('MY_CREDENTIALS_FILE.json', scope)

# authorize service account in the the clientsheet 
client = gspread.authorize(creds)

Leave a comment

Leave a Reply