Introduction

Google Sheets is where a lot of real business data lives. Sales reports, inventory tracking, project timelines, survey results — if your team uses Google Workspace, there is a good chance the data you need is sitting in a spreadsheet.

Python can read from, write to, and automate Google Sheets through the Google Sheets API. With the gspread library, most operations take just a few lines of code.

In this tutorial, you will learn how to:

• Set up Google Sheets API authentication
• Read data from a sheet
• Write and update cells
• Add and delete rows
• Work with multiple worksheets
• Automate common workflows

All examples are tested on Python 3.12 with gspread 6.x.

---

Setup

Step 1: Install gspread

pip install gspread

Step 2: Create Google Cloud Credentials

To access Google Sheets from Python, you need a service account — a non-human Google account that can be granted access to specific sheets.

1. Go to the Google Cloud Console
2. Create a new project (or select an existing one)
3. Navigate to APIs & Services → Library
4. Search for Google Sheets API and enable it
5. Also enable the Google Drive API
6. Navigate to APIs & Services → Credentials
7. Click Create Credentials → Service Account
8. Give it a name, click through the steps, and create
9. On the service account page, click Keys → Add Key → Create new key → JSON
10. Download the JSON key file — save it as credentials.json in your project folder

Important: Never commit credentials.json to version control. Add it to .gitignore.

Step 3: Share Your Sheet

1. Open your Google Sheet
2. Click Share
3. Add the service account email address (found in credentials.json under "client_email")
4. Give it Editor access

Step 4: Authenticate in Python

import gspread

gc = gspread.service_account(filename="credentials.json")
print("Authenticated successfully")

Expected output:

Authenticated successfully

---

Opening a Spreadsheet

By URL

spreadsheet = gc.open_by_url(
    "https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/edit"
)

By Name

spreadsheet = gc.open("My Sales Report")

By ID

spreadsheet = gc.open_by_key("YOUR_SHEET_ID")

The sheet ID is the long string in the URL between /d/ and /edit.

---

Working with Worksheets

A spreadsheet can contain multiple worksheets (tabs).

# Get the first worksheet
worksheet = spreadsheet.sheet1

# Get by name
worksheet = spreadsheet.worksheet("Sales Data")

# Get all worksheets
all_sheets = spreadsheet.worksheets()
for sheet in all_sheets:
    print(sheet.title)

# Add a new worksheet
new_sheet = spreadsheet.add_worksheet(title="Report", rows=100, cols=20)

# Delete a worksheet
spreadsheet.del_worksheet(worksheet)

---

Reading Data

Read All Data

worksheet = spreadsheet.sheet1

# Get all values as a list of lists
all_data = worksheet.get_all_values()

for row in all_data:
    print(row)

Expected output (for a sheet with headers and 3 rows):

['Name', 'Department', 'Salary']
['Alice', 'Engineering', '75000']
['Bob', 'Marketing', '82000']
['Carol', 'Engineering', '90000']

Read as List of Dicts

records = worksheet.get_all_records()

for record in records:
    print(record)

Expected output:

{'Name': 'Alice', 'Department': 'Engineering', 'Salary': 75000}
{'Name': 'Bob', 'Department': 'Marketing', 'Salary': 82000}
{'Name': 'Carol', 'Department': 'Engineering', 'Salary': 90000}

get_all_records() uses the first row as headers and returns a list of dictionaries — much more convenient for data processing.

Read a Specific Cell

# By row and column (1-indexed)
value = worksheet.cell(1, 1).value
print(value)

# By A1 notation
value = worksheet.acell("A1").value
print(value)

# A range of cells
values = worksheet.get("A1:C3")
print(values)

Read a Specific Row or Column

# First row (headers)
headers = worksheet.row_values(1)
print(headers)

# First column
names = worksheet.col_values(1)
print(names)

---

Writing Data

Write a Single Cell

# By row and column
worksheet.update_cell(1, 1, "New Value")

# By A1 notation
worksheet.update_acell("B2", "Updated")

Write a Range of Cells

# Write a 2D list to a range
data = [
    ["Alice", "Engineering", 75000],
    ["Bob", "Marketing", 82000],
    ["Carol", "Engineering", 90000],
]

worksheet.update("A2:C4", data)

Append Rows

# Append a single row
worksheet.append_row(["Dave", "Sales", 68000])

# Append multiple rows
new_rows = [
    ["Eve", "Design", 71000],
    ["Frank", "Engineering", 88000],
]

worksheet.append_rows(new_rows)

append_row and append_rows add data after the last row with content, making them ideal for logging or incrementally growing a dataset.

---

Updating Data

Update Specific Cells

# Update a single cell
worksheet.update_acell("C2", 80000)

# Update multiple non-contiguous cells
worksheet.update([
    {"range": "A1", "values": [["Name"]]},
    {"range": "B1", "values": [["Department"]]},
    {"range": "C1", "values": [["Salary"]]},
])

Find and Update

# Find a cell by value
cell = worksheet.find("Alice")
print(f"Found at row {cell.row}, col {cell.col}")

# Update the cell in the same row, different column
worksheet.update_cell(cell.row, 3, 85000)

---

Deleting Data

Clear a Range

worksheet.batch_clear(["A2:C10"])

Delete a Row

worksheet.delete_rows(3)  # delete row 3

# Delete multiple rows (delete from bottom up to avoid index shifting)
worksheet.delete_rows(5, 7)  # delete rows 5 through 7

Clear Entire Sheet

worksheet.clear()

---

Real-World Example: Daily Sales Report

This example reads sales data from one sheet, computes a summary, and writes it to another sheet:

import gspread
from datetime import date

gc = gspread.service_account(filename="credentials.json")
spreadsheet = gc.open("Sales Tracker")

# Read raw sales data
sales_sheet = spreadsheet.worksheet("Raw Data")
records = sales_sheet.get_all_records()

# Compute totals by department
totals: dict[str, float] = {}
for record in records:
    dept = record["Department"]
    amount = float(record["Amount"])
    totals[dept] = totals.get(dept, 0) + amount

# Write summary to a report sheet
report_sheet = spreadsheet.worksheet("Summary")
report_sheet.clear()

# Write headers
report_sheet.append_row(["Department", "Total Sales", "Generated On"])

# Write data rows
for dept, total in sorted(totals.items()):
    report_sheet.append_row([dept, total, str(date.today())])

print(f"Report updated: {len(totals)} departments")

Pair this with the scheduler tutorial to run it automatically every day.

---

Real-World Example: Log Script Results

Use Sheets as a lightweight log for automated scripts:

import gspread
from datetime import datetime

def log_to_sheet(spreadsheet_url: str, status: str, message: str) -> None:
    gc = gspread.service_account(filename="credentials.json")
    spreadsheet = gc.open_by_url(spreadsheet_url)
    log_sheet = spreadsheet.worksheet("Logs")

    log_sheet.append_row([
        str(datetime.now()),
        status,
        message,
    ])

# Usage
log_to_sheet(
    "https://docs.google.com/spreadsheets/d/YOUR_ID/edit",
    "SUCCESS",
    "Processed 142 records"
)

---

Handling Rate Limits

The Google Sheets API has rate limits — by default, 60 requests per minute per user. For scripts that make many requests, add a small delay:

import time
import gspread

worksheet = spreadsheet.sheet1

for i, row in enumerate(data):
    worksheet.append_row(row)

    if i % 10 == 0:
        time.sleep(1)  # pause every 10 rows

For better performance, use batch operations instead of one request per cell:

# Slow — one API call per row
for row in data:
    worksheet.append_row(row)

# Fast — one API call for all rows
worksheet.append_rows(data)

---

Common Issues

APIError: [429] RESOURCE_EXHAUSTED

You have exceeded the rate limit. Add delays between requests or use batch operations.

SpreadsheetNotFound

The service account does not have access to the sheet. Share the sheet with the service account email address from credentials.json.

WorksheetNotFound

The worksheet name does not match exactly (including capitalization and spaces). Use spreadsheet.worksheets() to list available sheets.

Credentials File Not Found

Make sure credentials.json is in the same directory as your script, or provide the full path:

gc = gspread.service_account(filename="/path/to/credentials.json")

---

Quick Reference

Operation	Code
Open by URL	gc.open_by_url(url)
Open by name	gc.open("Sheet Name")
Get worksheet	spreadsheet.worksheet("Tab Name")
Read all as list	worksheet.get_all_values()
Read all as dicts	worksheet.get_all_records()
Read cell	worksheet.acell("A1").value
Write cell	worksheet.update_acell("A1", value)
Write range	worksheet.update("A1:C3", data)
Append row	worksheet.append_row([...])
Append rows	worksheet.append_rows([[...], [...]])
Find cell	worksheet.find("value")
Delete row	worksheet.delete_rows(n)
Clear range	worksheet.batch_clear(["A1:C10"])
Clear sheet	worksheet.clear()

---

Wrap-Up

gspread makes Google Sheets automation accessible with just a few lines of Python. Once the authentication is set up, reading, writing, and managing spreadsheet data is straightforward.

The most powerful use cases combine multiple automation tools: scrape data with requests and BeautifulSoup, clean it with pandas, and push results directly to a Google Sheet that your team can see in real time. Schedule the whole workflow with cron or Task Scheduler and you have a lightweight data pipeline with no infrastructure required. For questions or future tutorial ideas, get in touch via the Contact page.