# Public Google Sheet to JSON - Read Any Shared Sheet, No Login (`appdata-labs/public-google-sheet-reader`) Actor

Paste the link of any publicly shared Google Sheet and get one typed JSON row per sheet row: numbers, booleans, ISO dates, clean column keys. Optional tab and SQL-like query. Read-only and public-only: no login, no OAuth, no API key, and private sheets are never touched.

- **URL**: https://apify.com/appdata-labs/public-google-sheet-reader.md
- **Developed by:** [Kobkoon Janngam](https://apify.com/appdata-labs) (community)
- **Categories:** Agents, MCP servers, Developer tools
- **Security:** Limited permissions
- **Stats:** 1 total users, 0 monthly users, 0.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $3.00 / 1,000 row returneds

This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events.
Since this Actor supports Apify Store discounts, the price gets lower the higher subscription plan you have.

Learn more: https://docs.apify.com/actors/running/actors-in-store.md#pay-per-event

## What's an Apify Actor?

An Actor is a serverless cloud program that runs on the Apify platform. It has two run modes.
In Batch mode, an Actor accepts a well-defined JSON input, performs an action which can take anything from a few seconds to a few hours,
and optionally produces a well-defined JSON output, datasets with results, or files in key-value store.
In Standby mode, an Actor provides a web server which can be used as a website, API, or an MCP server.

Apify vocabulary and the platform model are defined once, in the agent quickstart at https://apify.com/agents.md.

## How to integrate an Actor?

If asked about integration, you help developers integrate Actors into their projects.
You adapt to their stack and deliver integrations that are safe, well-documented, and production-ready.

Do not guess an integration path. Every one of them is in the agent quickstart at https://apify.com/agents.md: the Apify MCP server, Agent Skills with the Apify CLI, the JavaScript and Python clients, the REST API, and the account-free path for an agent with no human to sign in. It also carries the rule on stating cost before the first paid run.

For examples already wired to this Actor's own input schema, see the [API](#api) section below.

Each client library has reference documentation the quickstart does not restate: [JavaScript/TypeScript](https://docs.apify.com/api/client/js/docs.md) (`npm install apify-client`) and [Python](https://docs.apify.com/api/client/python/docs.md) (`pip install apify-client`).

# README

## Public Google Sheet to JSON — read any shared sheet, no login

**Paste the link of a publicly shared Google Sheet and get one typed JSON row per sheet row.** No Google account, no OAuth, no API key. For developers, analysts, no-code builders and AI agents that were handed a sheet link and just need the data.

**Quick start:** press **Start** with no configuration. It reads a public sample sheet and returns 30 rows. Then paste your own link into `sheets`. The sheet must be shared as "Anyone with the link can view".

### Sample output

| student_name | gender | class_level | home_state | major | extracurricular_activity | \_rowNumber |
|---|---|---|---|---|---|---|
| Alexandra | Female | 4. Senior | CA | English | Drama Club | 1 |
| Andrew | Male | 1. Freshman | SD | Math | Lacrosse | 2 |
| Anna | Female | 1. Freshman | NC | English | Basketball | 3 |
| Becky | Female | 2. Sophomore | SD | Art | Baseball | 4 |

These rows come from a real run on 8 November 2026 against the Google Sheets API sample sheet, whose names are fictional sample data.

The columns are yours: every key comes from your sheet's own header, so `Home State` becomes `home_state`. Every row also carries `_sheetId`, `_sheetName`, `_rowNumber` and `_truncatedColumns`. The `SUMMARY` record lists each sheet's columns with their original labels and types.

### What you get

- **Typed values.** Numbers stay numbers, booleans stay booleans, dates become ISO (`2024-01-15`, or `2024-01-15T09:30:00` with a time, in the sheet's own time zone). A number formatted with leading zeros, such as `00123`, is returned as that text, because that is what the cell means.
- **Clean keys.** Header names are sanitised and de-duplicated. A blank header becomes `column_C`. The empty columns out to the edge of the grid are dropped.
- **Rows an AI agent can hold.** A row stays under 4 KB. A longer cell is cut and named in `_truncatedColumns`. A sheet too wide to fit keeps its left columns; the log and `SUMMARY` name the ones left out, and `query` can select the ones you need.

### Input

| Field | Default | Notes |
|---|---|---|
| `sheets` | a public sample sheet | The link from your browser, with or without `#gid=`, or a bare sheet ID. A bad entry is skipped with its reason; the others still run. |
| `sheetName` | empty | The name on the tab. Empty means the tab in the link, or the first tab. |
| `query` | empty | Google's Query Language, with column letters: `select A, E where E = 'Art'`. |
| `maxRows` | `100` | Across the whole run, up to 50,000. |
| `firstRowIsHeader` | `true` | Off: every row is data and keys are `column_A`, `column_B`. |

### Public and read-only, and what that means

This Actor reads **only sheets their owner chose to share publicly**. It sends no login, cookie or token of any kind. A private sheet answers with a redirect to Google's sign-in page; the Actor stops there, does not follow it, charges nothing and tells you: "This sheet is not shared publicly; ask its owner to set 'Anyone with the link can view'".

It **never writes**. It cannot add rows, change cells or create sheets. If you need to read private sheets or write to a sheet, you need a tool that signs in to your Google account through OAuth, which this one deliberately does not do. In exchange, there is nothing to set up, and no permission over your Google account to grant.

**A public sheet can still contain personal data.** This Actor adds no enrichment and looks nothing up, but you are responsible for what you read and how you use it, including under data protection law and the sheet owner's terms. Do not assume that a shared sheet is free to republish.

### Traps worth knowing

- **A wrong tab name does not fail.** Google serves the first tab when a tab name or gid does not exist. The answer does not name its tab, so the Actor compares it with the first tab and warns in the log, and sets `sameDataAsFirstTab` in `SUMMARY`, when they are identical. That is correct when you did name the first tab.
- **Mixed columns lose values.** Google gives a column one type, the majority one, and [treats the minority as null](https://support.google.com/docs/answer/3093343). A mostly numeric column with a few text notes returns null for those notes. This is how the source behaves, and no client can recover them.
- **`_rowNumber` is a position in the answer**, 1 being the first data row. With a `query` it is the position in the query's result, not the row in the sheet.
- **"Publish to the web" links** (`/spreadsheets/d/e/2PACX-...`) are not served by this endpoint, and the Actor says so. Use the sheet's normal link.

### Empty results and errors

A query that matches nothing, or a sheet with no data, **succeeds with an empty dataset** and says so in the log. A bad query fails with Google's reason in plain words. If several sheets are listed, one failure does not stop the others; the run only fails when nothing could be read. `SUMMARY` lists every skipped entry and failed sheet with its reason.

### How it works

The Actor calls Google's documented [Visualization API query endpoint](https://developers.google.com/chart/interactive/docs/querylanguage), `/spreadsheets/d/<ID>/gviz/tq`, and reads its [JSON response format](https://developers.google.com/chart/interactive/docs/dev/implementing_data_source). It checks docs.google.com/robots.txt at the start of each run, keeps the Crawl-delay of one second it asks for, and identifies itself with a truthful User-Agent.

### Pricing

You pay per row returned: **$3 per 1,000 rows**. Blank rows and sheets that could not be read cost nothing. The run respects your spending limit.

### Disclaimer

This is an unofficial tool, not affiliated with or endorsed by Google. Google Sheets is a trademark of Google LLC. The data belongs to whoever owns each sheet, and you are responsible for your use of it.

# Actor input Schema

## `sheets` (type: `array`):

Sheets to read: the full link you copy from the browser, with or without #gid=, or a bare sheet ID. A link with #gid= reads that tab. Each entry is judged alone, so a bad one is skipped with its reason and the others still run. The sheet must be shared as 'Anyone with the link can view'.

## `sheetName` (type: `string`):

Optional. The name on the tab at the bottom of the sheet, exactly as written; it applies to every sheet in the list and beats a #gid= in a link. Leave empty for the tab in the link, or the first tab. Google serves the first tab when a name does not exist, so the run warns if the data is identical to the first tab.

## `query` (type: `string`):

Optional. A Google Visualization API Query Language query, to pick columns or filter rows before they reach you. Use column letters, not header names, and single quotes around text, for example: select A, E where E = 'Art'. Leave empty for every row.

## `maxRows` (type: `integer`):

Return at most this many rows in the whole run, across all sheets. Up to 50000. You pay per row returned, so keep this low until you know the size of the sheet.

## `firstRowIsHeader` (type: `boolean`):

On: the sheet's first row names the columns, so a column called Home State becomes the key home_state, and that row is not returned as data. Off: every row is data and the keys are column_A, column_B and so on.

## Actor input object example

```json
{
  "sheets": [
    "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0"
  ],
  "sheetName": "Class Data",
  "query": "select A, E where E = 'Art'",
  "maxRows": 5000,
  "firstRowIsHeader": true
}
```

# Actor output Schema

## `rows` (type: `string`):

One row per sheet row, keyed by the sheet's own column headers, plus \_sheetId, \_sheetName, \_rowNumber and \_truncatedColumns

## `summary` (type: `string`):

How many sheets and rows were read, each sheet's columns and types, and which sheets or entries could not be used, with the reason.

# API

You can run this Actor programmatically using our API. Below are code examples in JavaScript, Python, and CLI, as well as the OpenAPI specification and MCP server setup.

## JavaScript example

```javascript
import { ApifyClient } from 'apify-client';

// Initialize the ApifyClient with your Apify API token
// Replace the '<YOUR_API_TOKEN>' with your token
const client = new ApifyClient({
    token: '<YOUR_API_TOKEN>',
});

// Prepare Actor input
const input = {
    "sheets": [
        "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit"
    ],
    "sheetName": "",
    "query": "",
    "maxRows": 100,
    "firstRowIsHeader": true
};

// Run the Actor and wait for it to finish
const run = await client.actor("appdata-labs/public-google-sheet-reader").call(input);

// Fetch and print Actor results from the run's dataset (if any)
console.log('Results from dataset');
console.log(`💾 Check your data here: https://console.apify.com/storage/datasets/${run.defaultDatasetId}`);
const { items } = await client.dataset(run.defaultDatasetId).listItems();
items.forEach((item) => {
    console.dir(item);
});

// 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/js/docs

```

## Python example

```python
from apify_client import ApifyClient

# Initialize the ApifyClient with your Apify API token
# Replace '<YOUR_API_TOKEN>' with your token.
client = ApifyClient("<YOUR_API_TOKEN>")

# Prepare the Actor input
run_input = {
    "sheets": ["https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit"],
    "sheetName": "",
    "query": "",
    "maxRows": 100,
    "firstRowIsHeader": True,
}

# Run the Actor and wait for it to finish
run = client.actor("appdata-labs/public-google-sheet-reader").call(run_input=run_input)

# Fetch and print Actor results from the run's dataset (if there are any)
print(f"💾 Check your data here: https://console.apify.com/storage/datasets/{run.default_dataset_id}")
for item in client.dataset(run.default_dataset_id).iterate_items():
    print(item)

# 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/python/docs/quick-start

```

## CLI example

```bash
echo '{
  "sheets": [
    "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit"
  ],
  "sheetName": "",
  "query": "",
  "maxRows": 100,
  "firstRowIsHeader": true
}' |
apify call appdata-labs/public-google-sheet-reader --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,appdata-labs/public-google-sheet-reader"
        }
    }
}
```

The hosted server signs you in with OAuth on first connect, so no API token belongs in this config. Clients without OAuth support can send an `Authorization: Bearer <APIFY_API_TOKEN>` header instead, using a token from API & Integrations in Apify Console (https://console.apify.com/settings/integrations).

## OpenAPI specification

Download the OpenAPI definition: https://api.apify.com/v2/actors/rtKhIs44xfU8ToYq1/builds/ac1t88LRQQOMBoFxu/openapi.json