Created
June 18, 2021 13:06
-
-
Save gsidhu/db7aa3acaf2c87677457c238d6321d94 to your computer and use it in GitHub Desktop.
Access Google Sheets using a Service Account API Key
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
""" | |
## ATTENTION | |
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 code snippet useful 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 32 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 | |
""" | |
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']] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment