Skip to content

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.


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*

* These are affiliate link. By clicking on it you will not have any additional costs. Instead, you will support my project. Thank you! 🙏