How to read a public Google Sheet as JSON without OAuth or an API key (2026)

Short answer: for a sheet shared as "Anyone with the link can view", call https://docs.google.com/spreadsheets/d/SHEET_ID/gviz/tq?tqx=out:json&headers=1. It answers without a login or a key. The response is JSON wrapped in a JavaScript call, so strip the wrapper first. Below is working code, four traps we measured on 8 November 2026 (Bangkok time; the image shows the same run in UTC), and a ready-made Actor if you would rather not handle them yourself.

The rows in this post come from Google's own Sheets API sample sheet. Its names are fictional.

Do it yourself in 10 lines

const id = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms';
const url = `https://docs.google.com/spreadsheets/d/${id}/gviz/tq?tqx=out:json&headers=1`;

const text = await (await fetch(url)).text();
// The body is /*O_o*/ google.visualization.Query.setResponse({...});
const { table } = JSON.parse(text.slice(text.indexOf('{'), text.lastIndexOf('}') + 1));

const cols = table.cols.map((c) => c.label).filter(Boolean);
const rows = table.rows.map((r) => Object.fromEntries(cols.map((name, i) => [name, r.c[i]?.v ?? null])));
console.log(rows.length, rows[0]);

This uses the query endpoint of Google's Visualization API. The full Sheets API can also read private sheets and write, but it needs a Google Cloud project and credentials. Use that one if you need either.

Four traps we measured (8 November 2026)

Trap	What we sent	What came back
Header is treated as data	No headers parameter	All column labels blank, 31 rows, and "Student Name" as row 1
The fix	&headers=1	Labels filled in, 30 rows
Empty columns are included	Either call	22 columns for a sheet with 6 real ones
A wrong tab name does not fail	&sheet=NoSuchTab	HTTP 200 and the first tab's data, with the same sig value

The last one is the dangerous one. Your code keeps running on the wrong tab and nothing reports an error, because the response never names its tab. One way to catch it is to fetch the first tab as well and compare sig.

Dates also arrive as strings such as Date(2024,0,15), and the month counts from zero, so convert them before storing.

Filter in the URL with Query Language

Add &tq= with a URL-encoded query. Columns are referred to by letter, not by header name.

Query	Rows	Result
select A, D, E where D = 'CA'	4	Only students from California
select E, count(A) group by E	4	Art 7, English 8, Math 8, Physics 7

These are counts from real runs on the sample sheet. Google evaluates the query, so you only download the rows you asked for.

Or call it as one API

Public Google Sheet to JSON is an Apify Actor that wraps this endpoint. We built it, so weigh that accordingly. It handles the traps above: it cleans the headers, drops the empty columns, converts dates to ISO, warns when a tab name looks wrong, and keeps each row under 4 KB so an AI agent can hold it. It costs $0.003 per row returned.

curl -X POST "https://api.apify.com/v2/acts/appdata-labs~public-google-sheet-reader/run-sync-get-dataset-items" \
  -H "Authorization: Bearer YOUR_APIFY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sheets": ["https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0"]}'

That is the exact call behind the image above. It uses Apify's run-sync-get-dataset-items endpoint, and agents on the Apify MCP server can call it as a tool.

Measured on Apify, 8 November 2026	Result
Full sheet	30 rows in 3.9 s
With a where query	4 rows in 1.6 s
A good link beside the junk entry not a sheet	30 rows; the junk entry is skipped
Largest row	267 bytes

If 10 lines of code are enough for your sheet, use them.

FAQ

Does this work on a private sheet?
No, and it should not. The sheet's owner has to choose "Anyone with the link can view". Neither the code above nor the Actor sends any login. The Actor stops if Google redirects it to sign-in; we tested that handling with a simulated response, not a real private sheet. If you get no data, ask the owner to share the sheet.

Can it write to the sheet?
No. This endpoint only reads. To write, use the Sheets API with OAuth.

What about a "Publish to web" link?
Those links look like /d/e/2PACX-... and did not answer on this endpoint when we tried one. Use the normal share link.

Can I rely on this endpoint in production?
Google documents the query language and the data source protocol, but the sheet and headers parameters did not appear on the pages we read. We verified them by testing, so test them again before you depend on them.

Is it safe to read any public sheet?
Technically yes, but a public sheet can still contain people's personal data. What you do with it is your responsibility.