Google Sheets and Python - Tutorial 2020
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.
- 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
pip install gspread
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)
Join My Newsletter! Get Python and ML tips emailed directly to your inbox. Each month you’ll get a summary of all the content I created, including the newest videos, articles, promotions, tips, and more.
Implement popular Machine Learning algorithms from scratch using only built-in Python modules and numpy.
Advanced Python Tutorials. It covers topics like collections, decorators, generators, multithreading, logging, and much more.
Learn all the necessary basics to get started with this deep learning framework.