Setup and Access Google Sheets API With Python
March 27, 2020Google 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 chooseOthers
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 theSPREADSHEET_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.
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
- algo-trading
- algolia
- analytics
- android
- android-ktx
- android-permission
- android-studio
- apps-script
- bash
- binance
- bootstrap
- bootstrapvue
- chartjs
- chrome
- cloud-functions
- coding-interview
- contentresolver
- coroutines
- crashlytics
- crypto
- css
- dagger2
- datastore
- datetime
- docker
- eslint
- firebase
- firebase-auth
- firebase-hosting
- firestore
- firestore-security-rules
- flask
- fontawesome
- fresco
- git
- github
- glide
- godot
- google-app-engine
- google-cloud-storage
- google-colab
- google-drive
- google-maps
- google-places
- google-play
- google-sheets
- gradle
- html
- hugo
- inkscape
- java
- java-time
- javascript
- jetpack-compose
- jetson-nano
- kotlin
- kotlin-serialization
- layout
- lets-encrypt
- lifecycle
- linux
- logging
- lubuntu
- markdown
- mate
- material-design
- matplotlib
- md5
- mongodb
- moshi
- mplfinance
- mysql
- navigation
- nginx
- nodejs
- npm
- nuxtjs
- nvm
- pandas
- payment
- pip
- pwa
- pyenv
- python
- recylerview
- regex
- room
- rxjava
- scoped-storage
- selenium
- social-media
- ssh
- ssl
- static-site-generator
- static-website-hosting
- sublime-text
- ubuntu
- unit-test
- uwsgi
- viewmodel
- viewpager2
- virtualbox
- vue-chartjs
- vue-cli
- vue-router
- vuejs
- vuelidate
- vuepress
- web-development
- web-hosting
- webpack
- windows
- workmanager
- wsl
- yarn