Python Engineer

Free Python and Machine Learning Tutorials

Become A Patron and get exclusive content! Get access to ML From Scratch notebooks, join a private Slack channel, get priority response, and more! I really appreciate the support!

Google Sheets and Python - Tutorial 2020

16 May 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.

Setup

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)