Automating Google Sheets API

Approach

The common approach is:

• create a Google Cloud project
• enable Google Sheets API
• create a Service Account
• share the spreadsheet with the service account email

Popular libraries

• gspread
• google-api-python-client

Minimal example (gspread)

import gspread
 
# Requires a service account JSON key file.
# client = gspread.service_account(filename="service_account.json")
# sheet = client.open("My Sheet").sheet1
# sheet.update_acell("A1", "Hello from Python")

Notes

• Never commit service account JSON keys to git.
• Prefer environment variables or secret managers.

How service-account access works

A Google Sheet belongs to a person. A script is not a person, so it needs its own identity. A service account is that identity: a robot user with an email address such as reports@my-project.iam.gserviceaccount.com. You create it in the Google Cloud console, download a JSON key file, and then share the spreadsheet with the service account's email address, exactly as you would share it with a colleague. Without that last step the script gets a "not found" or permission error, even though the sheet exists.

For scripts that run without a human, this is simpler than OAuth, which opens a browser and asks a user to click Allow.

Reading and writing with gspread

gspread is a small wrapper around the Sheets API. This example shows the calls you will use most. It needs the key file and a sheet that has been shared, so read it rather than running it in a sandbox.

import gspread
 
client = gspread.service_account(filename="service_account.json")
spreadsheet = client.open("Sales 2026")
sheet = spreadsheet.sheet1
 
# Read
records = sheet.get_all_records()      # list of dicts, using row 1 as headers
print(records[:2])
print(sheet.acell("B2").value)         # one cell
 
# Write
sheet.update_acell("D1", "Checked by script")
sheet.append_row(["Pen", 10, 2.5])     # adds a row after the last one with data

get_all_records() returns one dictionary per row, the same shape as csv.DictReader, so cleaning code from the CSV lesson works on it. append_row finds the end of the data for you, which avoids overwriting rows.

Rate limits and batching

The API limits how many requests you can make per minute. A loop that calls update_acell for every cell will hit the limit and fail with a 429 error. Send many cells in one request instead, for example with sheet.update(range_name="A2:C4", values=[[...], [...], [...]]), and pause between large jobs. The argument order of update changed between gspread versions, so use keyword arguments and check the version you installed.

Keeping the key safe

The JSON key file is a password. Anyone who has it can do whatever the service account can do.

• Add service_account.json to .gitignore before the first commit.
• Give the account access only to the sheets it needs, and view-only if it only reads.
• On a server, read the path from an environment variable or a secret manager. Do not paste the key into the code.
• If a key leaks, delete it in the console and create a new one.

Common mistakes

• Forgetting to share the sheet with the service account email.
• Enabling only the Sheets API when you also open files by name, which needs the Drive API.
• Using the header names with trailing spaces. get_all_records keys come from row 1 exactly as typed.
• Writing cell by cell and hitting rate limits.

Check your understanding