Google Sheets API: Use a Spreadsheet as Data

Quick answer: The Google Sheets API (version 4) lets a website, script or app read and write data in a Google Sheet over HTTPS. Create a project in the Google Cloud Console, enable the Sheets API, create a service account, share the sheet with that account’s email and call the API with a client library in Python, Node.js, PHP or another language. It works well for small datasets that non-technical staff need to edit, such as price lists, opening hours, stock levels or form logs. It is not a replacement for a database when you need heavy traffic, complex queries or strict data integrity.

Why use a spreadsheet as a data source

Plenty of businesses already run important information in spreadsheets. Staff know how to edit them, they are easy to share and they need no special software. The problem is getting that data to the places it is needed: the website, a dashboard, another system.

The Google Sheets API bridges that gap. Typical uses:

A price list or menu edited in Sheets and displayed on the website
Store locations or opening hours maintained by an operations team
Website form submissions written to a sheet for a team without a CRM
A simple stock or availability list read by an ordering page
Data collected from several systems into one sheet for reporting
Configuration values (banner text, feature switches) that marketing can change

If APIs are new to you, our plain guide to what is an API explains the basics.

Ways to get data out of Google Sheets

Before writing code, consider the simplest option that works.

Publish to the web as CSV. File, then Publish to the web, gives you a public CSV URL. Easy, but the data is public and caching can delay updates. Only suitable for non-sensitive data.
Connector tools. Zapier, Integromat and similar tools can watch a sheet for new rows and send data elsewhere without code. See our overview of Zapier.
Google Apps Script. Scripts attached to the sheet can send data out, respond to edits or expose a simple web endpoint.
The Sheets API. Full programmatic read and write access with proper authentication. Best for integrations that need control and privacy.

Setting up the Sheets API

1. Create a Google Cloud project

Go to the Google Cloud Console, create a project and enable the Google Sheets API from the API library.

2. Choose how to authenticate

Service account: best for server-to-server integrations, such as your website reading a sheet. The service account has its own email address. Share the sheet with that address, just as you would with a colleague.
OAuth client: for apps that act on behalf of a signed-in user and access their own sheets. Our explainer on OAuth covers how that consent flow works.
API key: can read sheets that are public. Not suitable for private data or writing.

For most business integrations, a service account is the right choice.

3. Create and store credentials safely

Create a key for the service account and download the JSON file. Store it outside your web root and out of source control. Treat it like a password: anyone with the file can access every sheet shared with that account.

4. Share the sheet

Share the specific spreadsheet with the service account email. Give view access if the integration only reads, edit access if it writes.

Key concepts

Spreadsheet ID: the long string in the sheet’s URL between /d/ and /edit.
Range: A1 notation such as Prices!A2:D100 or a whole sheet name like Prices.
Values: data comes back as a list of rows, each row a list of cell values.
Value input option: when writing, RAW stores exactly what you send; USER_ENTERED treats it as if a person typed it, so formulas and dates are parsed.

The main methods you will use:

spreadsheets.values.get to read a range
spreadsheets.values.batchGet to read several ranges at once
spreadsheets.values.update to overwrite a range
spreadsheets.values.append to add rows to the end of a table
spreadsheets.batchUpdate for formatting, adding sheets and structural changes

A simple example in Python

This reads a price list from a sheet using the official Google API client library and a service account.

from google.oauth2 import service_account
from googleapiclient.discovery import build

SCOPES = ["https://www.googleapis.com/auth/spreadsheets.readonly"]
creds = service_account.Credentials.from_service_account_file(
    "/secure/path/service-account.json", scopes=SCOPES)

service = build("sheets", "v4", credentials=creds)
result = service.spreadsheets().values().get(
    spreadsheetId="YOUR_SPREADSHEET_ID",
    range="Prices!A2:C").execute()

for name, price, available in result.get("values", []):
    print(name, price, available)

Writing a row is similar with values().append() and a body containing the row values. Libraries such as gspread for Python and the googleapis package for Node.js make common tasks shorter.

Design tips for sheets used as data

A sheet that people edit by hand needs some structure to work reliably as a data source.

Use a header row and keep column names stable. Code usually depends on column order or names.
One table per tab. Do not mix notes, totals and data on the same sheet the API reads.
Use data validation for columns like status or category so people pick from a list instead of typing variations.
Protect header rows and formula columns from accidental edits.
Keep formats consistent: dates, prices and yes/no values in one format.
Add an “active” or “publish” column so editors can prepare rows before they go live.

Performance, caching and limits

The Sheets API has usage quotas per project and per user over short time windows. A website that calls the API on every page view can hit those limits quickly and will be slower than reading from its own database.

Better patterns:

Cache the data on your server for a few minutes and refresh in the background.
Sync on a schedule: a job reads the sheet every few minutes and stores the result in your database or a JSON file.
Use batch requests to read several ranges at once.
Handle errors and retries with backoff when the API returns rate limit or temporary errors.

Our guide to API integration covers retry and error-handling habits that apply here too.

Security considerations

Share sheets only with the service accounts that need them.
Use read-only scopes when you only read.
Do not store sensitive personal data in sheets unless your policies allow it and access is tightly controlled.
Validate and sanitise data read from the sheet before displaying it on your site. A stray script tag in a cell should not end up running on your pages.
Rotate service account keys periodically and remove unused ones.

When to outgrow Google Sheets

Sheets is a great starting point. Move to a proper database or application when:

Data grows into tens of thousands of rows and performance suffers
Several systems write at the same time and conflicts appear
You need relationships between tables, complex queries or transactions
You need detailed access control, audit trails or compliance features
The sheet has become business-critical and a bad edit could cause real damage

Often the next step is a small custom admin interface backed by a database, which keeps the ease of editing without the risks.

FAQ

Is the Google Sheets API free?

Use of the API is generally free within its usage quotas. Check current quota details in the Google Cloud Console for your project.

Can I use the API from WordPress?

Yes. A small plugin or theme function can read a sheet with a PHP client library and cache the result. Some plugins also display sheet data with a shortcode.

How quickly do changes appear?

API reads return current data. If you cache, changes appear when the cache refreshes.

Can several people edit while the API reads?

Yes. The API reads the current state. For writes from code, avoid overwriting ranges people are editing.

Put your spreadsheets to work

The Google Sheets API turns a familiar tool into a simple data source for your site and systems. Keep the sheet structured, cache reads and know when to graduate to a database. If you want help connecting spreadsheets to the rest of your business, see how we help you automate the busywork.

Google Sheets API: Turning a Spreadsheet Into a Simple Data Source