How to work with the Google Sheets API and Python
Learn how to use Google Sheets API in Python. We are using the gspread module for this.
#more
Learn how to use Google Sheets API in Python. We are using the gspread module for this. It's super simple to setup a project, and then access and modify our spreadsheet with a Python script. Google Sheets can be pretty powerful and used as a backend to store some data for your web applications.
You can find and test the code on GitHub.
Check out the gspread documentation here.
Setup¶
- Google Developer Console: https://console.developers.google.com
- New Project -> Activate Drive and Sheets API
-
Create credentials
- -> service account -> name + role=editor
- ->create key and download json
-
Share client_email from json in your worksheet
Use the gspread module¶
Installation¶
pip install gspread
Usage¶
import gspread #
gc = gspread.service_account(filename='credentials.json')
sh = gc.open_by_key("xxxx") # or by sheet name: gc.open("TestList")
worksheet = sh.sheet1
### retrieve data ###
res = worksheet.get_all_records() # list of dictionaries
res = worksheet.get_all_values() # list of lists
print(res)
print(len(res))
values_list = worksheet.row_values(1)
print(values_list)
values_list = worksheet.col_values(1)
print(values_list)
print(worksheet.row_count, worksheet.col_count)
print(worksheet.get('A1'))
#print(worksheet.get('A1:C1'))
# INSERT UPDATE
user = ["Susan", "28", "Sydney"]
#worksheet.insert_row(user, 3)
#worksheet.insert_row(user, 2) #same with column
#worksheet.append_row(user)
#worksheet.update_cell(1,2, value)
# DELETE
#worksheet.delete_rows(1)
#worksheet.delete_columns(1)
Create client manually¶
Use the following if you have the credentials already loaded and in JSON format:
import json
from google.oauth2.service_account import (
Credentials as ServiceAccountCredentials,
)
DEFAULT_SCOPES = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive',
]
with open('credentials.json', 'r') as f:
credentials = json.load(f)
creds = ServiceAccountCredentials.from_service_account_info(credentials, scopes=DEFAULT_SCOPES)
gc = gspread.Client(auth=creds)
FREE VS Code / PyCharm Extensions I Use
✅ Write cleaner code with Sourcery, instant refactoring suggestions: Link*
Python Problem-Solving Bootcamp
🚀 Solve 42 programming puzzles over the course of 21 days: Link*