Using Google Drive, Google Sheets and Python in Google Colaboratory to access Google Sheet using service account json key file.
Useful links
- https://broadoakdata.uk/google-colab-pyspark/
- Google CoLab and relational database
- https://colab.research.google.com/
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()