# Google Sheets Integration (`solutionssmart/google-sheets-integration`) Actor

Synchronize Apify datasets and Google Sheets with secure read, append, replace, upsert, delete, and filter operations.

- **URL**: https://apify.com/solutionssmart/google-sheets-integration.md
- **Developed by:** [Solutions Smart](https://apify.com/solutionssmart) (community)
- **Categories:** Automation, Developer tools, Integrations
- **Stats:** 1 total users, 0 monthly users, 0.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

Pay per event

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 integration for Apify

**Google Sheets Integration** synchronizes Google Sheets with Apify datasets and Actor workflows. Import spreadsheet rows into an Apify dataset, create worksheet tabs, or write JSON and dataset items back to a sheet with append, replace, upsert, delete, and clear operations.

The Actor is designed for scheduled data pipelines: connect a scraper's dataset to a reporting sheet, keep a content inventory current, or use Google Sheets as a simple operational data store. It supports no-credential reads of public sheets, service-account authentication for unattended automation, and Google API keys for public read-only API access.

#### Main features

- Read Google Sheets rows into the default Apify dataset.
- Create worksheet tabs, then append, replace, upsert, delete, or clear spreadsheet data.
- Upsert records using a unique business key such as `email`, `id`, or `Dateiname`.
- Export direct JSON rows or any Apify dataset.
- Filter reads and deletes using exact-match `ALL` or `ANY` criteria.
- Flatten nested objects and map source fields to report-friendly column headers.
- Batch large dataset exports and save a machine-readable run summary.

### How to use Google Sheets Integration

1. Enable the Google Sheets API in a Google Cloud project.
2. For private sheets or writes, create a service account and download its JSON key.
3. Share each target spreadsheet with the service account `client_email`. Use Viewer access for reads and Editor access for writes.
4. For private sheets or writes, paste the complete JSON key into the secret `serviceAccountKey` field. Do not put the key in source code or a normal input field.
5. Choose an operation, spreadsheet ID or URL, and a sheet name or A1 range. Run the Actor manually, on a schedule, or after another Actor through an Apify integration.

The prefilled example reads Google's public sample sheet without credentials. For your own public read-only spreadsheet, provide a restricted `googleApiKey` when you need API access. API keys cannot write to Google Sheets.

### Google Sheets operations

| Operation | Use it when you need to |
| --- | --- |
| `READ` | Import spreadsheet rows into the default dataset. |
| `CREATE_SHEET` | Create a new worksheet tab, optionally populated with rows or a source dataset. |
| `APPEND` | Add new rows below an existing table. |
| `REPLACE` | Clear the selected range and write a new table. |
| `UPSERT` | Update matching rows and add new rows using `upsertKey`. |
| `DELETE` | Remove rows matching protected filters. |
| `CLEAR` | Empty a selected range without providing new rows. |

`READ` uses the first worksheet when neither `sheetName` nor `range` is provided. For object rows, nested fields are flattened, for example `profile.email`. Use `columnMapping` to rename these fields for the sheet.

### Input examples

#### Import a Google Sheet to an Apify dataset

```json
{
  "operation": "READ",
  "spreadsheetId": "https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit",
  "sheetName": "Leads",
  "firstRowHeaders": true,
  "filters": { "status": "active" },
  "filterMode": "ALL"
}
```

#### Upsert JSON data to Google Sheets

```json
{
  "operation": "UPSERT",
  "spreadsheetId": "SPREADSHEET_ID",
  "sheetName": "Exports",
  "upsertKey": "email",
  "firstRowHeaders": true,
  "columnMapping": { "profile.email": "email" },
  "rows": [
    {
      "name": "Ada",
      "profile": { "email": "ada@example.com" },
      "status": "new"
    }
  ]
}
```

#### Create and populate a new worksheet tab

```json
{
  "operation": "CREATE_SHEET",
  "spreadsheetId": "SPREADSHEET_ID",
  "sheetName": "Actor Test",
  "firstRowHeaders": true,
  "rows": [
    { "id": "write-test-001", "status": "created" }
  ]
}
```

#### Export another Actor's dataset

```json
{
  "operation": "APPEND",
  "spreadsheetId": "SPREADSHEET_ID",
  "sheetName": "Scraper exports",
  "sourceDatasetId": "DATASET_ID_FROM_THE_SOURCE_ACTOR",
  "firstRowHeaders": true
}
```

### Output

For `READ`, each spreadsheet row becomes one item in the default dataset. With `firstRowHeaders` enabled, the first sheet row becomes the object keys. Blank or duplicate headers are made unique.

Every successful operation writes an `OUTPUT` summary record to the default key-value store. It includes the operation, spreadsheet, range, and affected row count. READ summaries also include the number of populated cells read. This makes the Actor straightforward to chain through Apify integrations, the API, or MCP-enabled workflows.

### Limits

READ operations allow up to 1,000,000 populated cells. For larger spreadsheets, select a narrower A1 range or split the import into multiple runs. This limit applies to the data returned by the selected range, not to the total capacity of the Google Sheets file.

### Scheduling and integrations

Use Apify schedules to refresh a reporting sheet at a regular interval. To export a scraper's results automatically, create an Apify integration that runs Google Sheets Integration after the source Actor completes, then supply the source run's dataset ID as `sourceDatasetId`.

### Pricing

Google Sheets Integration uses pay-per-event pricing. It charges once for each successful sheet operation: READ, APPEND, REPLACE, UPSERT, DELETE, or CLEAR. The current price is shown on the Actor's Pricing tab and includes platform usage. Invalid input, authentication failures, and failed Google API requests are not charged.

### FAQ and troubleshooting

#### Why does Google return a permission error?

Share the spreadsheet with the service account's `client_email`. The service account needs Viewer access for `READ` and Editor access for write operations.

#### Why does UPSERT fail?

`UPSERT` requires `firstRowHeaders: true`, a non-empty `upsertKey`, and a value for that key in every input row. The target sheet must have a matching header column.

#### Why does DELETE require filters?

`DELETE` refuses to run without at least one exact-match filter to prevent accidental removal of all sheet rows.

#### Can I use Google OAuth instead of a service account?

This Actor currently uses service accounts for private sheets and API keys for public read-only sheets. Service accounts are better suited to scheduled, non-interactive Apify automations.

#### How do I get support?

Open an issue or contact the Actor publisher from the Actor's Apify Store page. Include the operation, non-sensitive input details, and the run log. Never include service-account JSON keys or API keys in support requests.

# Actor input Schema

## `operation` (type: `string`):

READ imports records. CREATE\_SHEET adds a worksheet tab. APPEND, REPLACE, UPSERT, and DELETE provide CRUD-style data management. CLEAR removes a range.

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

The ID from a Google Sheets URL, or the full spreadsheet URL. A public Google sample sheet is prefilled for testing.

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

Worksheet tab name. Required for CREATE\_SHEET and defaults to the first sheet when reading.

## `range` (type: `string`):

Optional A1 range, such as A1:Z. It may include a sheet name.

## `serviceAccountKey` (type: `string`):

Service-account JSON for private sheets and write operations. Store it as a secret input. Public READ operations can run without credentials.

## `googleApiKey` (type: `string`):

Optional API key for public READ operations. It supports private-sheet API features; published public sheets can be read without credentials. All writes require a service account.

## `firstRowHeaders` (type: `boolean`):

For reads, emits objects keyed by the first row. For writes of object rows, controls whether headers are written.

## `rows` (type: `array`):

Rows as JSON objects or arrays. Used for CREATE\_SHEET, APPEND, REPLACE, and UPSERT unless sourceDatasetId is supplied.

## `sourceDatasetId` (type: `string`):

Optional dataset ID whose items are exported. Takes precedence over Rows.

## `upsertKey` (type: `string`):

Required for UPSERT. Existing rows with the same value are updated; other rows are added.

## `columnMapping` (type: `object`):

Optional JSON object mapping source fields to Google Sheets headers, for example {"profile.email": "Email"}.

## `filters` (type: `object`):

Exact-match filters for READ or DELETE, for example {"status": "active"}. DELETE requires at least one filter.

## `filterMode` (type: `string`):

Match all filter fields or any filter field.

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

Optional maximum number of matched rows returned by READ.

## `maxCells` (type: `integer`):

Maximum populated cells returned by a READ request. Use a narrower A1 range for larger sheets.

## `valueInputOption` (type: `string`):

USER\_ENTERED lets Sheets evaluate formulas and formatting; RAW writes literal values.

## Actor input object example

```json
{
  "operation": "READ",
  "spreadsheetId": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit",
  "sheetName": "Class Data",
  "range": "A1:E",
  "firstRowHeaders": true,
  "rows": [],
  "filterMode": "ALL",
  "maxCells": 1000000,
  "valueInputOption": "USER_ENTERED"
}
```

# Actor output Schema

## `dataset` (type: `string`):

Rows imported by the READ operation. The record shape mirrors the sheet header row.

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

Operation, target spreadsheet and row count for the completed run.

# 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 = {
    "operation": "READ",
    "spreadsheetId": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit",
    "sheetName": "Class Data",
    "range": "A1:E"
};

// Run the Actor and wait for it to finish
const run = await client.actor("solutionssmart/google-sheets-integration").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 = {
    "operation": "READ",
    "spreadsheetId": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit",
    "sheetName": "Class Data",
    "range": "A1:E",
}

# Run the Actor and wait for it to finish
run = client.actor("solutionssmart/google-sheets-integration").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 '{
  "operation": "READ",
  "spreadsheetId": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit",
  "sheetName": "Class Data",
  "range": "A1:E"
}' |
apify call solutionssmart/google-sheets-integration --silent --output-dataset

```

## MCP server setup

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

```

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/W5UjrcLOIAw8eKeNu/builds/RzMdNPhggGAFSKGtl/openapi.json