Free Python Google Sheets API Script – Read & Write Data

Python and Google Sheets, properly connected — not exported CSVs, but a script that reads and writes the live sheet. The setup is the part everyone gets stuck on, so the walkthrough goes through the Google Cloud side click by click.

The connection, step by step

Enable the Google Sheets API and the Google Drive API in the Cloud Console, create a service account, generate a JSON key and download it. Then the step that is easy to miss and breaks everything when skipped: share your Google Sheet with the service account's email address as an editor. A service account is its own identity; without that share it has no access to your file, and the error it returns does not say so clearly.

What the script does

It connects, writes data into the sheet, reads it back, and builds an HTML dashboard from it using Plotly — charts, a top-performers ranking and a key statistics block. Answer yes at the prompt and auto-refresh is enabled on a thirty-second cycle, so the dashboard updates itself while the sheet changes. Missing Python libraries are installed on first run rather than left to you.

Why do this at all

Google Sheets is a database everyone in the office can already read, and Python is where the real processing happens — pandas, scraping, machine learning, scheduled jobs. Joining them means your script's output lands somewhere non-technical colleagues can open, and their edits are readable by your script. The example is a student dashboard; the connection works for anything.

Versions

VS Code and Python are the only tools. Python 3.11 or 3.10 are recommended over the newest release, which is the usual cause of library errors on a fresh install.