DEV Community: Žan Mažgon

Run a Python script on a schedule for free and write the results to Google Sheets (GitHub Actions + gspread)

Žan Mažgon — Thu, 29 Oct 2026 06:46:53 +0000

You have a Python script that fetches some data. You want it to run every morning and drop the result into a Google Sheet, so you (or your boss) can look at it, chart it or share it. You don't want to rent a server or leave your laptop on.

This tutorial builds exactly that, end to end, with three free pieces:

GitHub Actions runs the script on a cron schedule.
A Google service account lets the script log in without a browser.
gspread writes the row.

The example logs yesterday's weather for a city (from Open-Meteo, a free API that needs no key) into a Weather tab. Swap fetch_yesterday() for your own data source and everything else stays the same.

Total time: about 20 minutes.

Step 1: Create a service account

A service account is a robot Google identity with its own e-mail address. That's what you want for unattended jobs: there's no user login, so there's no token that expires when nobody is around to click "Allow".

In the Google Cloud console, create a project (or pick one).
Under APIs & Services → Library, enable the Google Sheets API.
Under IAM & Admin → Service accounts, create a service account. It doesn't need any project roles.
Open it, go to Keys → Add key → Create new key → JSON. A file downloads. Rename it to service_account.json.

Console menus move around. If a label above doesn't match, search the console for it.

Treat that file like a password. It never goes into git.

Step 2: Share the Sheet with the robot

Create a Google Sheet. Open service_account.json, copy the client_email value (it looks like something@your-project.iam.gserviceaccount.com), and share the Sheet with that address as Editor.

Then copy the Sheet ID from its URL: it's the long string between /d/ and /edit.

Step 3: The script

Create a folder for the project with these two files.

requirements.txt:

gspread>=6,<7
requests>=2.28

job.py:

"""Log yesterday's weather to a Google Sheet, one row per day."""
import json
import os

import gspread
import requests

LAT, LON, TZ = 46.05, 14.51, "Europe/Ljubljana"  # change to your city
TAB = "Weather"
HEADER = ["date", "t_max_c", "t_min_c", "precip_mm"]


def fetch_yesterday() -> list:
    r = requests.get(
        "https://api.open-meteo.com/v1/forecast",
        params={
            "latitude": LAT,
            "longitude": LON,
            "timezone": TZ,
            "daily": "temperature_2m_max,temperature_2m_min,precipitation_sum",
            "past_days": 1,
            "forecast_days": 1,
        },
        timeout=30,
    )
    r.raise_for_status()
    d = r.json()["daily"]  # index 0 = yesterday, index 1 = today
    return [d["time"][0], d["temperature_2m_max"][0],
            d["temperature_2m_min"][0], d["precipitation_sum"][0]]


def get_client() -> gspread.Client:
    raw = os.environ.get("GOOGLE_SA_JSON")
    if raw:  # in GitHub Actions: the whole JSON key lives in a secret
        return gspread.service_account_from_dict(json.loads(raw))
    return gspread.service_account(filename="service_account.json")  # local run


def get_tab(sh: gspread.Spreadsheet) -> gspread.Worksheet:
    try:
        return sh.worksheet(TAB)
    except gspread.WorksheetNotFound:
        ws = sh.add_worksheet(title=TAB, rows=1000, cols=len(HEADER))
        ws.append_row(HEADER)
        return ws


def main() -> None:
    row = fetch_yesterday()
    sh = get_client().open_by_key(os.environ["SHEET_ID"])
    ws = get_tab(sh)

    if row[0] in ws.col_values(1):  # one read request
        print(f"{row[0]} is already in the sheet, nothing to do")
        return

    ws.append_row(row, value_input_option="RAW")  # one write request
    print(f"Logged {row}")


if __name__ == "__main__":
    main()

Three details here are deliberate:

open_by_key, not open("My sheet"). Opening a sheet by title has to search Drive first. Opening it by ID goes straight to the Sheets API, which is one fewer thing that can fail.
The duplicate check. GitHub can occasionally run a scheduled job late, and you'll press "Run workflow" by hand while testing. Checking the date column first makes a second run harmless.
RAW. The date goes in as the literal text 2026-10-28, so the duplicate check compares text with text. (With USER_ENTERED, Sheets turns it into a real date that's displayed in your locale, and "2026-10-28" in col_values(1) stops matching.) Numbers are sent as JSON numbers, so they stay numbers either way.

Try it locally before you automate anything:

python -m venv .venv && source .venv/bin/activate   # Windows: .venv\Scripts\activate
pip install -r requirements.txt
SHEET_ID=your_sheet_id python job.py                 # Windows: set SHEET_ID=... then python job.py

You should see Logged ['2026-10-28', ...] and a new Weather tab. If you get a 403 or SpreadsheetNotFound, the Sheet isn't shared with the client_email (step 2).

Step 4: Put it on GitHub

Create a private repository and add a .gitignore before your first commit:

service_account.json
.venv/

Push job.py and requirements.txt. Then go to Settings → Secrets and variables → Actions → New repository secret and add two secrets:

GOOGLE_SA_JSON: paste the entire contents of service_account.json, unchanged.
SHEET_ID: the Sheet ID.

Paste the whole file. If you copy only the private_key field, or something rewrites its \n line breaks, you get Could not deserialize key data.

Step 5: The schedule

Create .github/workflows/daily.yml:

name: daily-weather-to-sheets

on:
  schedule:
    - cron: "23 6 * * *"            # 06:23 every day...
      timezone: "Europe/Ljubljana"  # ...in this time zone (without this line: UTC)
  workflow_dispatch:                # adds a "Run workflow" button

permissions:
  contents: read

concurrency:
  group: daily-weather              # never two runs at the same time

jobs:
  run:
    runs-on: ubuntu-latest
    timeout-minutes: 10             # a hung job fails instead of burning minutes
    steps:
      - uses: actions/checkout@v7
      - uses: actions/setup-python@v7
        with:
          python-version: "3.13"
          cache: pip
      - run: pip install -r requirements.txt
      - run: python job.py
        env:
          GOOGLE_SA_JSON: ${{ secrets.GOOGLE_SA_JSON }}
          SHEET_ID: ${{ secrets.SHEET_ID }}

Commit it to your default branch (scheduled workflows only run from there). Open the Actions tab, pick the workflow and press Run workflow. When that manual run is green, the schedule takes over.

Cron's five fields are minute, hour, day of month, month and day of week. Some useful ones:

Cron	Runs
`23 6 * * *`	every day at 06:23
`23 6 * * 1-5`	weekdays at 06:23
`7 /6 * *`	every 6 hours, at minute 7
`30 8 1 * *`	the 1st of every month at 08:30

Things worth knowing before you rely on it

Time zones. GitHub added the timezone: key in March 2026. Leave it out and cron times are UTC, which is an hour off your local time for half the year.
Don't schedule on the hour. GitHub warns that scheduled runs can be delayed when load is high, especially at the start of every hour. Pick an odd minute like 23. If a few minutes' delay would hurt, use a real server.
5 minutes is the minimum interval. Anything more frequent belongs on a server.
Public repos go to sleep. In public repositories, GitHub disables scheduled workflows after 60 days without repository activity. Use a private repo (which also keeps your logs private).
Cost. Public repositories run Actions for free. Private ones get a monthly free allowance on the Free plan (check GitHub's billing page for the current amount). A daily job that takes about a minute uses roughly 30 minutes a month.
Failures should be loud. If job.py raises, the run goes red and GitHub can e-mail you. Turn on notifications for failed workflows in your GitHub settings. Don't wrap main() in a try/except that only prints; that turns a broken job into a green one.
Batch as you grow. One row a day is two API requests. If you later write hundreds of rows, send them in one append_rows() or update() call instead of looping, because the Sheets API limit counts requests per minute, not cells.

If this setup still breaks after a few days, I wrote about the five errors behind most failures (403, invalid_grant after 7 days, 429, broken keys in CI, duplicate rows) in an earlier post.

Recap

Service account + Sheets API, key saved as service_account.json
Sheet shared with the client_email as Editor
job.py opens the sheet by ID, checks for duplicates and appends one row
Key and Sheet ID stored as repository secrets, never committed
A workflow with schedule + workflow_dispatch, tested by hand once

That's a free, serverless daily job that runs whether your laptop is on or not.

A free one-page cheat sheet with fixes for the five most common Python → Google Sheets errors: https://whop.com/whop-0bab/fix-the-5-most-common-python-google-sheets-errors-free-1-page-cheat-sheet/.

Disclosure: I also sell a starter kit that takes this further (a 47-page guide, a tested toolkit with backoff and upsert helpers, a connection checker, ready-made workflows and three Sheet templates): https://whop.com/whop-0bab/python-google-sheets-running-on-autopilot-the-starter-kit/. Everything in this tutorial works without it.

Python + Google Sheets on autopilot: why your script dies after 7 days (and 4 more errors)

Žan Mažgon — Wed, 28 Oct 2026 18:08:48 +0000

Your Python script writes to a Google Sheet perfectly on your laptop. Then you put it on a schedule and it breaks a few days later. Here are the five errors behind most of those failures, what they actually mean, and how to fix them.

I'll use gspread in the examples, but the causes are about Google's APIs, so they apply to any client library.

1. `403: The caller does not have permission`

A service account is a separate Google identity. It can only touch files that were shared with it.

Fix: open the Sheet, press Share, and add the client_email value from your service-account JSON key (it looks like name@project.iam.gserviceaccount.com) as Editor. Viewer can read but not write.

A close relative is 403: Google Sheets API has not been used in project ... or it is disabled. You enabled the API in a different Cloud project than the one that created your key. Enable the Sheets API (and the Drive API if you open sheets by title) in the project that owns the key, then wait a minute or two.

2. `invalid_grant: Token has been expired or revoked` after exactly 7 days

If your script uses OAuth user credentials and the OAuth consent screen is in Testing status, Google issues refresh tokens that expire after 7 days. Spreadsheet scopes are not exempt from that rule (only basic name/e-mail/profile scopes are).

Fix: for unattended jobs, use a service account. There is no user token to expire. If you must use OAuth, publish the consent screen (read what that changes first) and make sure failures are loud (see "make failures visible" below).

3. `429: Quota exceeded ... Write requests per minute per user`

The Sheets API limit counts requests, not cells. At the time of writing, Google's usage-limits page lists 300 read and 300 write requests per minute per project, and 60 per minute per user per project. Check the page, because limits change.

Writing a 5,000 row x 12 column table:

Strategy	Requests	Minimum time at 60/min
one request per cell (`update_cell` in a loop)	60,000	about 16 hours
one request per row (`append_row` in a loop)	5,000	about 84 minutes
one batch	1	under a minute

Fix, in this order: (1) batch your writes, (2) then add backoff for the occasional 429/5xx.

import random, time, gspread

gc = gspread.service_account(filename="service_account.json")
ws = gc.open_by_key("YOUR_SHEET_ID").worksheet("Data")

rows = [["date", "usd"], ["2026-10-27", 1.1150]]   # your whole table, as a list of lists
ws.update(values=rows, range_name="A1", value_input_option="USER_ENTERED")  # one request

def call_with_backoff(fn, *args, tries=6, **kwargs):
    for n in range(tries):
        try:
            return fn(*args, **kwargs)
        except gspread.exceptions.APIError as e:
            if e.code not in (429, 500, 502, 503) or n == tries - 1:
                raise                    # not retryable, or out of attempts
            time.sleep(min(2 ** n + random.random(), 64))   # exponential backoff + jitter

Retry only what is worth retrying. A 403 "permission denied" will never fix itself; retrying it just hides the problem.

4. Works locally, fails in CI: `Could not deserialize key data`

The private_key inside the JSON contains \n line breaks. If you paste the key into a CI secret and something converts or strips them, the key can no longer be parsed (you may also see Invalid JWT Signature if the key was deleted).

Fix: store the entire JSON file, unmodified, as one secret, and load it in the job. In GitHub Actions:

# .github/workflows/daily.yml
on:
  schedule: [{ cron: "15 6 * * *" }]   # UTC
  workflow_dispatch:                    # manual "run now" button
jobs:
  run:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: { python-version: "3.12" }
      - run: pip install gspread
      - run: python job.py
        env:
          SA_JSON: ${{ secrets.SA_JSON }}   # whole JSON file content
          SHEET_ID: ${{ secrets.SHEET_ID }}

import json, os, gspread
gc = gspread.service_account_from_dict(json.loads(os.environ["SA_JSON"]))

Two scheduling gotchas: scheduled workflows run from the default branch only, and on public repositories GitHub disables scheduled workflows after 60 days without repository activity. Always keep the manual workflow_dispatch trigger so you can test the job on demand.

5. Duplicate rows after a retry

If a job fails halfway and you re-run it, appended rows are duplicated. Appending is right for logs; reports need an upsert: use a key (for example the date), update the row if the key exists, append if it does not.

def upsert_by_date(ws, date_str, values):
    dates = ws.col_values(1)               # one read
    if date_str in dates:
        r = dates.index(date_str) + 1
        ws.update(values=[[date_str, *values]], range_name=f"A{r}")
    else:
        ws.append_row([date_str, *values], value_input_option="USER_ENTERED")

(For many rows, read the key column once and write all updates in a single batch_update.)

Make failures visible

Write a "last success (UTC)" timestamp into a cell, and colour it red with conditional formatting when it's older than two days.
Log counts, not data: "fetched 4, inserted 1, updated 3".
Make the CI job exit non-zero on failure, so GitHub e-mails you.
Keep a "does my connection work?" script that checks credentials, sharing, clock skew and a scratch-tab write before you debug anything else.

A free cheat sheet, and the long version

I put the five errors above on a one-page cheat sheet: https://whop.com/whop-0bab/fix-the-5-most-common-python-google-sheets-errors-free-1-page-cheat-sheet/.

Disclosure: I'm the author of a paid starter kit that covers all of this in depth (a 47-page guide, a tested Python toolkit, a connection-checking tool, GitHub Actions workflows and three Sheet templates): https://whop.com/whop-0bab/python-google-sheets-running-on-autopilot-the-starter-kit/. Everything in this article works without it.