Programmatically accessing Google Sheets via Service Account

I was working on automation that involves reading Google Sheets from server side and then writing some values back.

Initially this seemed straight forward but led me to waste a couple of hours pursuing the false start of "Domain Wide Delegation" which would allow anyone impersonate as a user without the whole UI based OAuth2 flow. Turns out you don't actually need this.

Instead, simply create a service account and then share your Google Sheet with email address of that service account ([email protected]) just like how you would share a document with an actual person.

Now, the official docs are pretty confusing and vague on how to authenticate Google Sheets using a service account. Ironically, after ton of Googling, I finally manage to include Google Sheet's access scope with the service account in Python API client via from_service_account_file.

from googleapiclient.discovery import build
from google.oauth2 import service_account
import pandas as pd
from datetime import datetime

credentials = service_account.Credentials.from_service_account_file(
    '/tmp/serviceaccount.json', scopes=['https://www.googleapis.com/auth/spreadsheets'])
service = build('sheets', 'v4', credentials=credentials)
sheet = service.spreadsheets()

sheetId = 'XXXXXXXXXXXXXXXXX'
tabName = 'Sheet1'

# Read
result = sheet.values().get(spreadsheetId=sheetId, range=tabName).execute()
values = result.get('values', [])
df = pd.DataFrame(values)
print(df)

# Write
data = df.values.tolist()
data.append(["Updated at", datetime.now().isoformat()])
result = sheet.values().update(spreadsheetId=sheetId, range=tabName, valueInputOption='RAW', body={
    'values': data
}).execute()

This reminds me of a favorite quote of my colleague

Straight forward does not mean easy.

Hope this helps someone save a couple of hours.