1. Tutorials
  2. Google Sheets API

Setup and Access Google Sheets API With Python

March 27, 2020

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:

    keys = dict((v,k) for k,v in enumerate(rows[0]))

    for row in rows[1:]:
        # print(row[keys['url']])
        print(row)

NOTE: Refer Google Sheets Range A1 Notation.

References:

❤️ Is this article helpful?
Buy me a coffee☕ or support my work to keep this space 🖖 and ad-free.
If you can't, do send some 💖 to @d_luaz or help to share this article.
👶 Apps I built
Travelopy - discover travel places in Malaysia, Singapore, Taiwan, Japan.
Pixtory App (Alpha) - easily organize photos on your phone into a blog.
暖心芽 (WIP) 🌞❤️🌱 - reminder of hope, warmth, thoughts and feelings (or just quotes).
LuaPass - offline password manager
By Desmond Lua
A dream boy who enjoys making apps, travelling and gardening. Follow me on @d_luaz.
This work is licensed under a
Creative Commons Attribution-NonCommercial 4.0 International License.