Google Sheets as JSON from JavaScript

Which Google Sheets URL to use

Every row below was tested against Google's public "Class Data" sample sheet from a page on https://example.com.

Use gviz JSON when you want filtered rows, the CSV export for a whole tab, the Sheets API when you want a documented quota, and opensheet when you want ready-made objects with no parsing. None of them needs a proxy. Calendar data is different: a Google Calendar ICS feed sends no CORS headers and has to go through one.

Read rows as JSON with the gviz endpoint

https://docs.google.com/spreadsheets/d/ID/gviz/tq?tqx=out:json returned 200 with an Access-Control-Allow-Origin header that echoes your page origin. The body is not plain JSON: it starts with /*O_o*/ and wraps the data in google.visualization.Query.setResponse({...}), so slice from the first { to the last } before JSON.parse. Columns sit in table.cols, each with an id like A and a label. Rows sit in table.rows, each a c array of cells with v for the value, sometimes f for the formatted text, and null for empty cells.

// Runs in a module script or the DevTools console. No proxy: Google sends CORS headers here.
const sheetId = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms';

const url = new URL(`https://docs.google.com/spreadsheets/d/${sheetId}/gviz/tq`);
url.searchParams.set('tqx', 'out:json');
url.searchParams.set('headers', '1');
url.searchParams.set('sheet', 'Class Data');
url.searchParams.set('tq', "select A, D, E where D = 'CA'");

const text = await (await fetch(url)).text();
const result = JSON.parse(text.slice(text.indexOf('{'), text.lastIndexOf('}') + 1));
if (result.status === 'error') throw new Error(result.errors[0].detailed_message);

const labels = result.table.cols.map((column) => column.label || column.id);
const rows = result.table.rows.map((row) =>
  Object.fromEntries(labels.map((label, i) => [label, row.c[i]?.v ?? null])),
);

console.table(rows);

• Always add headers=1. Without it Google guesses how many rows are headers. On the all-text sample sheet a tq query made it guess zero, so the JSON came back with empty labels and the CSV without its header row.
• Dates use 0-based months. A date cell arrives as the string Date(2020,8,1) with a formatted f of Sep-2020: 8 means September.
• A wrong tab name does not error. A misspelled sheet= or an unknown gid= returned the first tab, 30 rows, with status: "ok".
• Query errors come back as HTTP 200. select ZZ returned status: "error" and Invalid query: NO_COLUMN: ZZ in errors[0].detailed_message, so check status, not response.ok.

Filter and sort with the query language

The tq parameter takes a SQL-like query, so Google filters the sheet before it reaches the browser. select A, E where D = 'CA' returned 4 rows, 77 bytes as CSV. The language also has order by, limit and offset, as in Google's own example SELECT A, H, O, Q, R, U LIMIT 5 OFFSET 8. Add range=A1:C4 to limit the source cells, and pick a tab with sheet=Class Data or gid=0. Encode the query with URLSearchParams or encodeURIComponent, as the snippet does.

Download a whole tab as CSV

https://docs.google.com/spreadsheets/d/ID/export?format=csv&gid=0 answers with a 307 redirect to a googleusercontent.com host. Both hops send CORS headers, so fetch() follows it and gets plain CSV: 1,408 bytes for the sample tab. The gviz CSV of the same tab (tqx=out:csv) was 3,238 bytes, because it quotes every cell and added 2 empty columns.

File > Share > Publish to web gives another option, a URL like https://docs.google.com/spreadsheets/d/e/2PACX-.../pub?output=csv. A published sheet answered 307, then 200 text/csv with Access-Control-Allow-Origin: *. The snippet parses the export with Papa Parse 5.7.0, loaded from jsDelivr as an ES module.

const { default: Papa } = await import('https://cdn.jsdelivr.net/npm/papaparse@5.7.0/+esm');

const sheetId = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms';
const response = await fetch(`https://docs.google.com/spreadsheets/d/${sheetId}/export?format=csv&gid=0`);
if (!response.ok) throw new Error(`Sheet export failed: ${response.status}`);

const { data, errors } = Papa.parse(await response.text(), { header: true, skipEmptyLines: true });
if (errors.length) console.warn(errors);

console.table(data.slice(0, 5));

Sheets API v4 and opensheet

The official API, https://sheets.googleapis.com/v4/spreadsheets/ID/values/RANGE?key=KEY, also sends CORS headers: a call with an invalid key came back as a readable 400 JSON error, API key not valid. It returns values as arrays of strings, not objects. Google's limits, updated September 2026: 300 read requests per minute per project and 60 per minute per user, a 429 when you go over, no daily cap within those, a 2 MB recommended payload and a 180 second processing limit. Standard use is free; Google plans to charge for usage over the quota later in 2026. A browser key is visible to anyone who opens your page source, so restrict it to your site and to the Sheets API in the Google Cloud console.

opensheet is a free third-party service: https://opensheet.elk.sh/ID/TAB returns rows as a JSON array of objects keyed by the header row, with Access-Control-Allow-Origin: *. Pick the tab by name or 1-based number and add ?raw=true for unformatted values. Its README says responses are cached for 30 seconds, so edits can take that long to show, and reports 1.5 billion hits a month. It is someone else's server, so it is a dependency you do not control.

Write rows back with Apps Script

API keys only read public data, so writing from the browser goes through a Google Apps Script web app at https://script.google.com/macros/s/ID/exec. We sampled 12 deployed web apps from GitHub. Every one answered the CORS preflight (OPTIONS) with 200 and no Access-Control-Allow-Origin, so a POST with Content-Type: application/json failed in Chrome with "Response to preflight request doesn't pass access control check" (why a CORS preflight fails). GET requests to web apps that return JSON were fine: they redirect to script.googleusercontent.com, which sends Access-Control-Allow-Origin: *.

The fix is to send the JSON string as text/plain. That makes it a CORS simple request, which the browser sends without a preflight, and the script reads it with JSON.parse(event.postData.contents).

// Apps Script (Extensions > Apps Script in the sheet), deployed as a web app with access set to Anyone
function doPost(event) {
  const entry = JSON.parse(event.postData.contents);
  SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].appendRow([new Date(), entry.name, entry.score]);
  return ContentService.createTextOutput(JSON.stringify({ ok: true })).setMimeType(
    ContentService.MimeType.JSON,
  );
}

// Browser: a text/plain body keeps this a simple request, so there is no preflight to fail
const webAppUrl = 'https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec';

const response = await fetch(webAppUrl, {
  method: 'POST',
  headers: { 'Content-Type': 'text/plain;charset=utf-8' },
  body: JSON.stringify({ name: 'Ada', score: 42 }),
});

console.log(await response.json());

Deploy with Who has access set to Anyone. 6 of the 12 sampled web apps returned a 403 HTML page because theirs was not.

When the request fails

• Failed to fetch, with the console pointing at accounts.google.com: the sheet is not shared, or it is a /pub URL for a sheet that was never published, and Google redirected to its sign-in page. Of 30 random sheet IDs from GitHub, 2 did this, 3 returned 410 (deleted) and 25 returned CSV. Access to fetch blocked by CORS policy explains that console message.
• Preflight error on an Apps Script URL: the request sent Content-Type: application/json; switch to text/plain.
• 403 HTML from an Apps Script URL: the deployment's access is not set to Anyone.
• 200 with status "error": the tq query is invalid or names a column that does not exist.
• Rows from the wrong tab: the sheet= name is misspelled or the gid= does not exist.
• 404 HTML page: the spreadsheet ID is wrong.
• 429 from the Sheets API: over 300 reads per minute per project, or 60 per user.

Good questions.