Setup and Access Google Sheets API With Python

July 1, 2019

Google Cloud Platform Project and Enable API

You need to create a new or use existing Google Cloud Platform project.

Enable Sheets API in API Library.

Setup Credentials

Goto Google Cloud Console -> Credentials -> OAuth consent screen.

Go back to Credentials to create OAuth client ID:

Application type: Web application, Android, Chrome App, iOS, Other; I choose Others as I am running a local script.
Name: For you to identify the usage of this key

You will be shown client ID and secret. Click OK.

Click Download JSON and save the file as credentials.json.

Install Libraries

pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib

Code

Read all rows from existing Spreadsheet

Google Drive -> New -> Spreadsheets. Insert 2 rows of data into the Sheets.
From the URL (e.g. https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0), copy the SPREADSHEET_ID (e.g. 1hlTYOqy10Q-1q32ZEkdeBx_D9y_c283_ZHLvPOcJ6b1)

import pickle
import os.path
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request

# https://developers.google.com/sheets/api/guides/authorizing
SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
# SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

SHEET_NAME = 'Sheet1'
SPREADSHEET_ID = '1hlTYOqy10Q-1q32ZEkdeBx_D9y_c283_ZHLvPOcJ6b1'

TOKEN_FILE = 'token.pickle'
CREDENTIAL_FILE = 'credentials.json'

credentials = None

if os.path.exists(TOKEN_FILE):
    with open(TOKEN_FILE, 'rb') as token:
        credentials = pickle.load(token)

if not credentials or not credentials.valid:
    if credentials and credentials.expired and credentials.refresh_token:
        credentials.refresh(Request())
    else:
        flow = InstalledAppFlow.from_client_secrets_file(
            CREDENTIAL_FILE, SCOPES)
        credentials = flow.run_local_server(port=10800)
    # Save the credentials for the next run
    with open(TOKEN_FILE, 'wb') as token:
        pickle.dump(credentials, token)

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

sheet = service.spreadsheets()

# read row 2
# RANGE_NAME = f"'{SHEET_NAME}'!A2"
# read all rows
range_notation = SHEET_NAME
result = sheet.values().get(spreadsheetId=SPREADSHEET_ID,
                            range=range_notation).execute()
rows = result.get('values', [])

if not rows:
    print('No data found.')
else:
    for row in rows:
        print(row)

NOTE: Refer Google Sheets Range A1 Notation.

References:

By Desmond Lua

A dream boy who enjoys programming and travelling, maker of Travelopy. Follow me on @d_luaz.

Tags:

Simple Guide to Python Threading

Python 3.x: Threading vs Multiprocessing vs Asyncio

Python 3.x Float Rounding Error (Decimal)

Google Sheets App Scripts: Make PubSub Publish Call