How to access the Google Sheets API with a Service Account

Note: This works as of October 2023. If you’re reading this in the future, please check the Google Sheets API documentation for any changes.

Why I make this?

The sample Python Quickstart code on Google Developer’s site only shows how to access a Google Sheet using OAuth credentials. But that feels like an overkill when you simply want to read data from a sheet.

This code does just that.

What is this for?

This code snippet serves the following use-case:

1. You want to simply READ data from a Google Sheets document
2. You don’t want to set up OAuth and instead want to use the Service Account API that lets you plug the API key in for making requests

With this code you can use a Google Sheets file as a read-only database from your front-end application. There would be three parts to your workflow:

1. The front-end that makes requests to… (e.g. Vue or React)
2. Your API which returns a response by reading from… (e.g. Node or FastAPI)
3. A Google Sheets file

Why is this helpful?

This way your application has READ-ONLY access to your Sheets. You can manually update your Sheets by logging into your Google account from the browser and the changes will be reflected immediately on your app.

Pre-requisites

1. A Google account
2. Follow the Pre-requisites at https://developers.google.com/sheets/api/quickstart/python to:
   • install the Python modules with pip;
   • create a project on GCP;
   • enable the Sheets API;
   • create a user with restricted permissions;
   • create a service account;
   • download the API key (as JSON) for that service account
3. Place the downloaded JSON in the same folder as this .py file OR update the path in line 6 below
4. Create a Google Sheet with some sample content in it. Share the file with View only access to the email of the service account you just created. It looks like: SERVICE_ACCOUNT_NAME@GCP_PROJECT_NAME.iam.gserviceaccount.com

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

from googleapiclient.discovery import build
from google.oauth2.service_account import Credentials # Note: the Python Quickstart code imports from oauth2.credentials. DON'T DO THAT. 

SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']

credentials = Credentials.from_service_account_file('client_secret.json', scopes=SCOPES) # Note: the Python Quickstart code uses the `from_authorized_user_file` function. DON'T DO THAT.

service = build('sheets', 'v4', credentials=credentials)

# The ID of the spreadsheet to retrieve data from.
# Open your spreadsheet in the browser and copy the part from the URL
# E.g. https://docs.google.com/spreadsheets/d/<SPREADSHEET_ID>/edit
SPREADSHEET_ID = 'ID FROM YOUR SPREADSHEET' 

# The A1 notation of the values to retrieve.
# See details under #Cell here: https://developers.google.com/sheets/api/guides/concepts
CELL_RANGE = 'Sheet1!A1:B4'

# Call the Sheets API
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=SPREADSHEET_ID,
                            range=CELL_RANGE).execute()
values = result.get('values', [])

# Do something with the returned values
print(values)

# Sample response -
# [['A1', 'B1'],
# ['A2', 'B2'],
# ['A3', 'B3'],
# ['A4', 'B4']]

Tada! You can now read data from your Google Sheets file using a Service Account API key.