Pinter Computing

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 gspread, oauth2client, 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)