GKit
All posts
Blog

Read and Write Google Sheets from Python

Use the requests library to read, filter, and append rows to any Google Sheet — no OAuth setup, no gspread, no service account JSON.

4 min read
#python#google-sheets#api#requests

Why not gspread or the official Google Sheets API?

The official Google Sheets API requires a service account JSON file, OAuth2 credentials, and a library like gspread or google-api-python-client. The setup takes 20–30 minutes before you write a single line of application code. For scripts, internal tools, or prototypes, that overhead is hard to justify.

SheetsAPI gives you a plain HTTP endpoint. You authenticate with a single bearer token and make GET or POST requests with requests. That's it.

Prerequisites

pip install requests

You'll need:

  • A Google Sheet set to Anyone with the link can view (for public access), or a private sheet with an API key
  • Your user key from the GKit dashboard
  • Your sheet name — the tab name at the bottom of your spreadsheet (e.g. Sheet1)

The first row of your sheet must be a header row. Column names become the field names in the API response.

Fetching all rows

import requests
 
USER_KEY = "YOUR_USER_KEY"
SHEET_NAME = "YOUR_SHEET_NAME"
 
url = f"https://sheetsapi.gkit.mreshank.com/api/spreadsheets/{USER_KEY}/{SHEET_NAME}"
 
response = requests.get(url)
response.raise_for_status()
 
payload = response.json()
rows = payload["data"]
 
for row in rows:
    print(row)

The response shape is always:

{
  "data": [
    { "name": "Alice", "email": "alice@example.com", "score": "92" },
    { "name": "Bob",   "email": "bob@example.com",   "score": "87" }
  ],
  "meta": {
    "total": 2,
    "limit": 100,
    "offset": 0
  }
}

Note that all values come back as strings — Google Sheets doesn't have a strict type system, so numeric columns like score above are strings in the response. Cast them as needed.

Filtering rows

Use search=field:value to filter by a column value. This is a case-insensitive substring match by default.

# Find all rows where the "status" column contains "active"
response = requests.get(url, params={"search": "status:active"})
rows = response.json()["data"]

For an exact match (no substring), add search_exact=1:

response = requests.get(url, params={
    "search": "email:alice@example.com",
    "search_exact": "1"
})

You can combine search with limit and offset for pagination:

def fetch_page(user_key, sheet_name, page=0, per_page=50):
    url = f"https://sheetsapi.gkit.mreshank.com/api/spreadsheets/{user_key}/{sheet_name}"
    response = requests.get(url, params={
        "limit": per_page,
        "offset": page * per_page,
    })
    response.raise_for_status()
    return response.json()
 
payload = fetch_page("YOUR_USER_KEY", "YOUR_SHEET_NAME", page=0)
print(f"Fetched {len(payload['data'])} of {payload['meta']['total']} rows")

Sorting rows

Use sort=fieldName for ascending, sort=-fieldName for descending:

# Most recent entries first (assumes a "date" column)
response = requests.get(url, params={"sort": "-date", "limit": 10})
recent = response.json()["data"]

Selecting specific fields

If your sheet has many columns but you only need a few, use fields to reduce payload size:

response = requests.get(url, params={"fields": "name,email"})

Only the name and email keys will appear in each row object.

Appending a row

POST to the same endpoint with a JSON body. Each key maps to a column header in your sheet:

new_row = {
    "name": "Carol",
    "email": "carol@example.com",
    "score": "95"
}
 
response = requests.post(url, json=new_row)
response.raise_for_status()
print(response.json())

The row is appended after the last non-empty row in the sheet. Column order doesn't matter — values are mapped by header name, not position.

Using an API key for private sheets

If your sheet is not publicly shared, you need to pass your secret API key in the Authorization header:

API_KEY = "sk_your_key_here"
 
headers = {"Authorization": f"Bearer {API_KEY}"}
 
response = requests.get(url, headers=headers)
rows = response.json()["data"]

Keep the key out of source code. Load it from an environment variable:

import os
import requests
 
API_KEY = os.environ["SHEETSAPI_KEY"]
USER_KEY = os.environ["SHEETSAPI_USER_KEY"]
SHEET_NAME = "responses"
 
url = f"https://sheetsapi.gkit.mreshank.com/api/spreadsheets/{USER_KEY}/{SHEET_NAME}"
headers = {"Authorization": f"Bearer {API_KEY}"}
 
response = requests.get(url, headers=headers)
response.raise_for_status()
data = response.json()["data"]

A reusable client class

Once you're using SheetsAPI in more than one place, a thin wrapper saves repetition:

import os
import requests
 
class SheetsClient:
    BASE = "https://sheetsapi.gkit.mreshank.com/api/spreadsheets"
 
    def __init__(self, user_key, api_key=None):
        self.user_key = user_key
        self.headers = {}
        if api_key:
            self.headers["Authorization"] = f"Bearer {api_key}"
 
    def _url(self, sheet_name):
        return f"{self.BASE}/{self.user_key}/{sheet_name}"
 
    def get(self, sheet_name, **params):
        r = requests.get(self._url(sheet_name), headers=self.headers, params=params)
        r.raise_for_status()
        return r.json()
 
    def append(self, sheet_name, row: dict):
        r = requests.post(self._url(sheet_name), headers=self.headers, json=row)
        r.raise_for_status()
        return r.json()
 
 
# Usage
client = SheetsClient(
    user_key=os.environ["SHEETSAPI_USER_KEY"],
    api_key=os.environ["SHEETSAPI_KEY"],
)
 
payload = client.get("orders", sort="-date", limit=20)
for order in payload["data"]:
    print(order["id"], order["status"])
 
client.append("orders", {"id": "ORD-1042", "status": "pending", "date": "2026-06-25"})

Error handling

from requests.exceptions import HTTPError
 
try:
    response = requests.get(url, headers=headers)
    response.raise_for_status()
    data = response.json()["data"]
except HTTPError as e:
    if e.response.status_code == 401:
        print("Invalid or missing API key")
    elif e.response.status_code == 404:
        print("Sheet not found — check user key and sheet name")
    else:
        print(f"API error: {e.response.status_code}")

What this doesn't do

SheetsAPI is a read/append API. It does not support updating or deleting individual rows, formatting cells, or working with formulas. If you need those operations, the official Sheets API or gspread is the right tool. For the common case — read data, filter it, write new rows — this approach eliminates most of the setup cost.

Share