# 📗 Google Sheets Import & Export — Read Public Sheets, No OAuth (`srd836/google-sheets-import-export`) Actor

Read any link-shared Google Sheet with ZERO setup — no API key, no OAuth, no Google Cloud project. Add a service account key to write: append, replace or upsert with deduplication, straight from any Apify dataset. Built for scheduled syncs that must not break. $1 per 1,000 rows.

- **URL**: https://apify.com/srd836/google-sheets-import-export.md
- **Developed by:** [David González](https://apify.com/srd836) (community)
- **Categories:** Integrations, Automation, Developer tools
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $1.00 / 1,000 row writtens

This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events.

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

## What's an Apify Actor?

Actors are web data automations that power AI and operations. They run on the Apify platform to scrape websites, process data, connect APIs, and automate workflows.
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.
Actors are written with capital "A".

## 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.
The best way to integrate Actors is as follows.

- **AI agents and MCP clients** — the [Apify MCP server](https://docs.apify.com/integrations/mcp.md) at `https://mcp.apify.com` (remote, streamable HTTP, OAuth on first use).
- **Agentic workflows and local Actor development** — [Agent Skills](https://apify.com/.well-known/agent-skills/index.json) with the [Apify CLI](https://docs.apify.com/cli/docs.md): `npm install -g apify-cli`, then `apify login`.
- **JavaScript/TypeScript projects** — the official [JS/TS client](https://docs.apify.com/api/client/js/docs.md): `npm install apify-client`.
- **Python projects** — the official [Python client](https://docs.apify.com/api/client/python/docs.md): `pip install apify-client`.
- **Any other language** — the [REST API](https://docs.apify.com/api/v2.md).

For usage examples, see the [API](#api) section below.

For more details, see Apify documentation as [Markdown index](https://docs.apify.com/llms.txt) and [Markdown full-text](https://docs.apify.com/llms-full.txt).

# README

### Google Sheets to Apify and Back — Read Public Sheets With Zero Setup, No OAuth

**The reliable Apify to Google Sheets integration.** This Actor moves data both ways between Apify and Google Sheets: read a sheet into a dataset, or push any Actor's results into a spreadsheet — appending, replacing, or upserting with deduplication.

**Reading a sheet needs no credentials at all.** If the spreadsheet is shared as *"Anyone with the link"*, paste the URL and run — no Google Cloud project, no API key, no consent screen. For private sheets and for every write, it authenticates with a **Google Cloud service account** instead of OAuth, so there is no access token that silently expires and kills your scheduled runs. You pay per row actually delivered, and failed runs charge no row events at all.

> **Unofficial** — this Actor is not affiliated with or endorsed by Google. Google Sheets™ is a trademark of Google LLC.

### Why use this Google Sheets integration?

- ⚡ **Zero setup for public sheets.** Leave the key field empty and it reads any link-shared spreadsheet immediately — no Google Cloud project, no API key, no consent screen. Ideal for public datasets, shared price lists, community trackers, and one-off imports.
- 🔐 **No OAuth, ever.** OAuth tokens expire, refresh flows break, and your 3 a.m. scheduled sync starts failing until a human clicks "re-authorize". A service account key does not expire on its own: paste it once, share the sheet with its email, forget it.
- 🛡️ **Built for scheduled runs that must not fail.** Bad inputs fail fast *before* touching Google, connection is verified before anything is charged, and large jobs are batched to stay well under Google's rate limits.
- 💬 **Errors that tell you what to do.** Every failure prints a machine-readable code plus numbered fix steps — a 403 quotes the *exact email address* you need to share the spreadsheet with. No cryptic stack traces.
- 🧪 **Dry run mode.** Validate credentials, access, and tab, and see exactly what *would* happen — without writing a single cell and without paying per-row events.

### Use cases

- **Apify scraper users** — you already run a scraper on Apify and your team lives in spreadsheets. Point `datasetId` at any run's dataset and its results land in Google Sheets, on a schedule if you want. Works with every Actor in the Store.
- **Automation builders** — keep a master sheet in sync with `upsert` + a deduplication key: matching rows get updated in place, new rows get appended, and re-running never creates duplicates. Idempotent by design.
- **AI agents** — give an agent a stable, structured read/write interface to spreadsheets, with machine-readable error codes it can act on and a dry-run mode for safe probing.

### Sample output

**Read mode** — one dataset item per sheet row (row 1 is the header):

```json
{
    "rowNumber": 2,
    "email": "ada@example.com",
    "name": "Ada Lovelace",
    "score": 42
}
```

**Write modes** — one summary item per run:

```json
{
    "mode": "upsert",
    "sheetName": "Customers",
    "dryRun": false,
    "rowsWritten": 25,
    "rowsUpdated": 18,
    "rowsAppended": 7,
    "headerCreated": false,
    "newColumns": "last_seen",
    "finishedAt": "2026-08-07T15:20:11.000Z"
}
```

You can download the dataset in JSON, CSV, Excel, or HTML.

### Input

To read a publicly shared sheet, one field is enough:

```json
{
    "mode": "read",
    "spreadsheetId": "https://docs.google.com/spreadsheets/d/1BxiMVs0.../edit"
}
```

To write (or to read a private sheet), add the key:

```json
{
    "mode": "upsert",
    "spreadsheetId": "https://docs.google.com/spreadsheets/d/1BxiMVs0.../edit",
    "serviceAccountJson": "…full content of your JSON key file…",
    "deduplicationKey": "email",
    "datasetId": "nWFncsBooi3djDanM"
}
```

| Field | Type | Required | Description |
|---|---|---|---|
| `spreadsheetId` | string | yes | Spreadsheet ID **or** the full sheet URL copied from the browser. |
| `serviceAccountJson` | string (secret) | writes only | Full content of the service account JSON key file. Stored encrypted by Apify. Leave **empty** to read a link-shared sheet. |
| `sheetName` | string | no | Tab name (case-sensitive). Defaults to the first tab. |
| `mode` | select | no | `read` (default), `append`, `replace`, or `upsert`. |
| `deduplicationKey` | string | upsert only | Column that uniquely identifies a row, e.g. `email`. |
| `data` | array | write modes | Inline rows: array of JSON objects, one per row. |
| `datasetId` | string | write modes | Apify dataset whose items should be written (alternative to `data` — provide one, not both). |
| `dryRun` | boolean | no | Validate credentials + access and report what **would** happen, without writing a cell. |

#### Setup

**Public sheet (read):** none. Open the spreadsheet → **Share** → *General access* → **Anyone with the link** → Viewer. Paste the URL, leave the key field empty, run.

**Private sheet, or any write — one-time setup (~3 minutes)**

1. In [Google Cloud Console](https://console.cloud.google.com), enable the **Google Sheets API** (APIs & Services → Library).
2. Under **IAM & Admin → Service accounts**, create a service account and download a JSON key (Keys → Add key → JSON).
3. **Share your spreadsheet** with the service account's email (the `client_email` value inside the JSON) — Editor for writing, Viewer for reading. Paste the JSON into the Actor's key field. Done.

#### Modes and data handling

- **read** — every data row becomes a flat JSON object in the run's dataset.
- **append** — adds rows after the last used row, reusing the existing header order; new columns are added at the end automatically.
- **replace** — clears the tab, then writes a fresh header and all rows.
- **upsert** — matches incoming rows by `deduplicationKey`: matches are updated in place (cells you don't send are kept), the rest are appended.

Keys become column headers; nested objects flatten to dot-notation columns (`user.city`); arrays are stored as JSON strings; values are written raw, so a string like `=SUM(A1)` stays text — nothing is ever executed as a formula.

### Pricing

| Event | Price | When it's charged |
|---|---|---|
| Task start | $0.005 | Once, only after the connection to your spreadsheet succeeds. |
| Row written | $0.001 / row | Only rows actually written (updated or appended). Header rows are free. |
| Row read | $0.001 / row | Only rows actually delivered to the dataset. |

**1,000 rows ≈ $1.** Failed runs charge no row events, and a dry run charges only the task start ($0.005). No subscription, no minimum.

### FAQ

#### How do I export an Apify dataset to Google Sheets automatically?

Set `mode` to `append` or `upsert`, put the dataset ID from your scraper run into `datasetId`, and schedule this Actor to run after the scraper. Every run pushes the fresh results into your sheet.

#### Why does my Google Sheets integration keep failing with a 403 error?

The spreadsheet isn't shared with the service account. This Actor's `SPREADSHEET_ACCESS_DENIED` error quotes the exact email address to share it with — share once, and it works from then on.

#### Can I read a Google Sheet without any credentials?

Yes, as long as the sheet is shared as *"Anyone with the link"*. Leave `serviceAccountJson` empty and the Actor downloads the tab straight from Google's public CSV endpoint. Nothing to configure, nothing to expire. Writing always needs the service account key — public link sharing never grants write access to anonymous callers.

#### Do Google service account keys expire like OAuth tokens?

No. OAuth access tokens expire and rely on refresh flows that can break; a service account JSON key keeps working until you revoke it. That's why this Actor uses service accounts only.

#### How do I sync data to Google Sheets without creating duplicates?

Use `upsert` mode with a `deduplicationKey` (e.g. `email`). Existing rows are updated in place, new rows are appended, and re-running the same sync never duplicates anything.

#### Is it safe to paste a service account key into Apify?

The field is marked secret, so Apify stores it encrypted and hides it in logs. The Actor never writes the key anywhere, and Apify's log library censors credentials.

#### Can I test the connection without touching my spreadsheet?

Yes — enable `dryRun`. It validates the key, the spreadsheet access, and the tab, then reports exactly what would happen without writing a single cell.

### Limitations (honest ones)

- The first row of the tab is always treated as the header; there is no support for headerless sheets or headers on another row.
- Column matching is case-sensitive and exact — `Email` and `email` are different columns.
- Google's limits apply: 10 million cells per spreadsheet, 50,000 characters per cell, and ~300 API requests/min per project (large jobs are batched to stay well under this).
- Write sources are capped at 100,000 rows per run.
- `replace` clears values but keeps cell formatting; it does not delete extra tabs.
- **Credential-free reading** works only on link-shared sheets, is read-only, caps at 25 MB per tab, and returns every cell as text (Google's CSV export carries no type information). Use the service account key if you need numbers typed as numbers.
- No OAuth login option — service account only. That is the point.

### 🤖 For AI Agents & MCP

This Actor is public on the Apify platform, so AI agents can invoke it through the [Apify MCP server](https://mcp.apify.com/) like any other tool: structured JSON input, structured JSON output, and machine-readable error codes (`CREDENTIALS_REJECTED`, `SPREADSHEET_ACCESS_DENIED`, `SHEET_TAB_NOT_FOUND`…) that an agent can parse and act on — including re-trying with corrected input. The `dryRun` flag lets an agent verify access safely before committing writes.

### More actors by srd836

- [YouTube Transcript Scraper](https://apify.com/srd836/youtube-transcript-pro) — extract transcripts and subtitles from YouTube videos.
- [Shopify Store Spy — AliExpress Price Match](https://apify.com/srd836/dropship-price-spy) — analyze Shopify stores and match products to AliExpress prices.
- [Naukri Jobs Scraper](https://apify.com/srd836/naukri-jobs-scraper) — extract job listings from Naukri.com.

### Support

Found a bug or need a feature? Open the **Issues** tab of this Actor and include the error code from the log (e.g. `CREDENTIALS_REJECTED`). Every issue is read and answered, usually within a few days.

***

*This Actor helps you export Apify dataset to Google Sheets automatically, set up an Apify to Google Sheets integration without OAuth, import Google Sheets data into Apify, sync scraper results to a spreadsheet on a schedule, upsert rows into Google Sheets without duplicates, and connect AI agents to Google Sheets using a service account instead of expiring OAuth tokens.*

# Actor input Schema

## `serviceAccountJson` (type: `string`):

Leave empty for public sheets. Otherwise paste the FULL content of your Google Cloud service account JSON key file. To get one: (1) in Google Cloud Console enable the Google Sheets API, (2) create a service account under IAM & Admin → Service accounts and download a JSON key, (3) share your spreadsheet with the service account's email (the `client_email` value inside the JSON). The key is stored encrypted by Apify. Required for `append`, `replace` and `upsert`.

## `spreadsheetId` (type: `string`):

The spreadsheet ID, or simply the full URL of your Google Sheet copied from the browser.

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

Name of the tab to read from or write to, exactly as shown at the bottom of Google Sheets (case-sensitive). Leave empty to use the first tab.

## `mode` (type: `string`):

`read` downloads all rows into the run's dataset — works with no credentials at all on publicly shared sheets. `append` adds rows after the last used row. `replace` wipes the tab and writes rows fresh. `upsert` updates rows whose deduplication key matches and appends the rest. The three write modes require a service account key.

## `deduplicationKey` (type: `string`):

Column/field name that uniquely identifies each row, e.g. `email` or `id`. Required for `upsert` mode; ignored otherwise.

## `data` (type: `array`):

Array of JSON objects, one per row. Keys become column headers. Nested objects are flattened to dot-notation columns (`user.name`); arrays are stored as JSON strings.

## `datasetId` (type: `string`):

ID of an Apify dataset whose items should be written to the sheet — for example the default dataset of a scraper run (find it in the run's Storage tab).

## `dryRun` (type: `boolean`):

Validates the credentials, the spreadsheet access, and the tab, then reports exactly what WOULD happen — without writing a single cell and without charging per-row events.

## Actor input object example

```json
{
  "spreadsheetId": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit",
  "sheetName": "Sheet1",
  "mode": "read",
  "deduplicationKey": "email",
  "data": [
    {
      "email": "ada@example.com",
      "name": "Ada Lovelace",
      "score": 42
    },
    {
      "email": "alan@example.com",
      "name": "Alan Turing",
      "score": 41
    }
  ],
  "datasetId": "nWFncsBooi3djDanM",
  "dryRun": false
}
```

# Actor output Schema

## `results` (type: `string`):

No description

# 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 = {
    "spreadsheetId": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit",
    "mode": "read"
};

// Run the Actor and wait for it to finish
const run = await client.actor("srd836/google-sheets-import-export").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 = {
    "spreadsheetId": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit",
    "mode": "read",
}

# Run the Actor and wait for it to finish
run = client.actor("srd836/google-sheets-import-export").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 '{
  "spreadsheetId": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit",
  "mode": "read"
}' |
apify call srd836/google-sheets-import-export --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,srd836/google-sheets-import-export"
        }
    }
}

```

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/c5tqRoq8jjG0VHt03/builds/6rgr5CdiUlhlsbGWw/openapi.json