BroadOakData.UK

Google Sheets in CoLab using service account key file

—

by

broadoakdata
in

Using Google Drive, Google Sheets and Python in Google Colaboratory to access Google Sheet using service account json key file.

Useful links

Prerequisites

  • Service Account exists and key file available as json format
  • Google Sheets with appropriate share permission

Steps

  • Start Google Colaboratory (CoLab)
  • Install the following packages if needed
    • oauth2client
    • PyOpenSSL (not needed – please check)
    • gspread
    • pydrive
  • upload service key file to session storage
  • use ServiceAccountCredentials.from_json_keyfile_name method for credentials

Code Snippets

# Init Google (with Auth)
file_path = r"service.json" 
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
 
credentials = ServiceAccountCredentials.from_json_keyfile_name(file_path, scope)
 
# Used to auth Google Sheets
gc = gspread.authorize(credentials)
 
# Used to get all files in Google Drive folder
gauth = GoogleAuth()
gauth.credentials = credentials
drive = GoogleDrive(gauth)

# get data from a google sheet and save to pandas dataframe
# Load your dataframe
import pandas as pd
        
wb = gc.open_by_key('19tywfbmzF-hi1lLiUgc4cB6VCIGZrZXJ9cAd3pufitNYY')
sheet1 = wb.worksheet('Sheet1') # Enter your sheet name.
original_df = sheet1.get_all_values()
df = pd.DataFrame(original_df)
df.head()
google colab and sheet