Pipe any Apify dataset into Google Sheets (and read a sheet back as JSON)

Most scraping jobs end in the same place: someone wants the rows in a spreadsheet. A recruiter wants new job posts, a marketer wants a list of competitor URLs, a founder wants leads they can sort and comment on. And often the reverse is needed too: a sheet of inputs (domains, keywords, product IDs) has to become JSON for the next script.

This post covers three ways to do it, and the details that trip people up along the way.

1. Reading a public sheet: the CSV export

If a sheet is shared as "Anyone with the link can view", you don't need an API key to read it. Every tab has a CSV export URL:

import csv, io, requests  # pip install requests

SHEET_ID = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"  # Google's public sample sheet
url = f"https://docs.google.com/spreadsheets/d/{SHEET_ID}/export?format=csv&gid=0"

r = requests.get(url, timeout=30)
r.raise_for_status()
rows = list(csv.DictReader(io.StringIO(r.text)))
print(len(rows), rows[0])

Output:

30 {'Student Name': 'Alexandra', 'Gender': 'Female', 'Class Level': '4. Senior', 'Home State': 'CA', 'Major': 'English', 'Extracurricular Activity': 'Drama Club'}

gid is the tab ID from the URL (#gid=...). This is fine for quick reads, but it only works for public sheets, gives you formatted text only, and can't write anything.

2. Writing: the Sheets API with a service account

To write, you need the Google Sheets API. The usual setup for scripts and servers is a service account:

1. Create a Google Cloud project, enable the Sheets API, create a service account and download its JSON key.
2. Share the target sheet with the service account's email (...@....iam.gserviceaccount.com) as Editor, just like you'd share with a person. Untick "Notify people".
3. Call spreadsheets.values.append (add rows) or values.update (overwrite) with your rows.

The parts that take longer than expected:

• Headers. Your rows are objects, but the API takes a 2D array. You need to build a header row, keep column order stable between runs, and add new columns when a later run has more fields.
• Nested data. Scraped rows have arrays and objects (tags: ["a", "b"], address.city). Cells hold strings, so you need to flatten them: join them, store them as JSON, or expand them into numbered columns.
• Duplicates. Appending the same scrape twice doubles your sheet. Deduplicating means reading the existing column first.
• Formula injection. With valueInputOption=USER_ENTERED, a scraped value like =IMPORTXML(...) or =HYPERLINK(...) becomes a live formula in your sheet. Use RAW for untrusted data.
• Limits. A sheet holds 10 million cells and a cell holds 50,000 characters, and the API returns 429 if you write too fast, so you need batching and retries.
• Replace safely. "Clear the tab, then write" loses your data if the write fails halfway. Write first, then clear the leftover rows.

3. The hosted option

Disclosure: I packaged this as a hosted tool, Google Sheets Import & Export on Apify, with the source on GitHub. It handles the list above: header merging, dot-path columns (address.city), array handling (join, json or expand), deduplicateBy, RAW by default, and write-then-clear replace. It costs $0.01 per 1,000 rows plus $0.00005 per run.

You share your sheet with the tool's service account (the email is in the input form) or paste your own service account key.

Scrape, then append to a sheet without duplicates

This scrapes 50 URLs from a sitemap, then appends them to a tab, skipping any URL already in the sheet:

import os
from apify_client import ApifyClient  # pip install apify-client

client = ApifyClient(os.environ["APIFY_TOKEN"])
SHEET = "https://docs.google.com/spreadsheets/d/<your-sheet-id>/edit"  # shared with the tool as Editor

scrape = client.actor("swillz/sitemap-url-extractor").call(
    run_input={"startUrls": [{"url": "https://docs.apify.com"}], "maxUrls": 50},
    max_total_charge_usd=0.05,
)

export = client.actor("swillz/google-sheets-import-export").call(
    run_input={
        "operation": "write",
        "spreadsheet": SHEET,
        "sheetName": "docs-urls",
        "datasetId": scrape.default_dataset_id,
        "columns": ["url", "lastmod", "sitemapUrl"],
        "writeMode": "append",
        "deduplicateBy": ["url"],
    },
    max_total_charge_usd=0.05,
)
print(export.status_message)

(On 1.x versions of the client, use run["defaultDatasetId"] and run["statusMessage"].)

The first run prints Wrote 50 rows to docs-urls (append) and creates the tab if it doesn't exist. Running the same export again prints:

0 rows written: All 50 rows already exist in the sheet (deduplicateBy: url).

Skipped duplicates aren't charged. The run's OUTPUT record has the counts (rowsWritten, duplicatesSkipped, sheetCreated, lastRow), so a script can check what happened without opening the sheet.

You can also write rows directly with rawData instead of a dataset:

{
  "operation": "write",
  "spreadsheet": "https://docs.google.com/spreadsheets/d/<your-sheet-id>/edit",
  "sheetName": "companies",
  "writeMode": "replace",
  "rawData": [
    {"company": "Stripe", "domain": "stripe.com", "tags": ["payments", "api"]},
    {"company": "Apify", "domain": "apify.com", "tags": ["scraping"]}
  ]
}

With the default arrayHandling: "join", the tags cell becomes payments, api.

Read a sheet back as JSON

read = client.actor("swillz/google-sheets-import-export").call(
    run_input={
        "operation": "read",
        "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit",
        "range": "A1:F3",
    },
    max_total_charge_usd=0.05,
)
for row in client.dataset(read.default_dataset_id).iterate_items():
    print(row)

Output:

{'Student Name': 'Alexandra', 'Gender': 'Female', 'Class Level': '4. Senior', 'Home State': 'CA', 'Major': 'English', 'Extracurricular Activity': 'Drama Club'}
{'Student Name': 'Andrew', 'Gender': 'Male', 'Class Level': '1. Freshman', 'Home State': 'SD', 'Major': 'Math', 'Extracurricular Activity': 'Lacrosse'}

Row 1 becomes the field names (headerRow: true). Set valueRender: "UNFORMATTED_VALUE" to get raw numbers instead of $1.50-style text.

Without code: run it after another scraper

In the Apify Console, open any scraper's Integrations tab, add an Actor integration that runs the Sheets tool when the run succeeds, and set datasetId to {{resource.defaultDatasetId}}. Every scheduled scrape then lands in your sheet, deduplicated.

Let an AI agent "save it to my sheet" (MCP)

Load a scraper and the Sheets tool in the same Apify MCP session:

{
  "mcpServers": {
    "apify": { "url": "https://mcp.apify.com?tools=swillz/sitemap-url-extractor,swillz/google-sheets-import-export" }
  }
}

Then:

"List the URLs in docs.apify.com's sitemap and append them to my sheet https://docs.google.com/spreadsheets/d/.../edit, tab 'docs', without duplicates."

The agent runs the first tool, passes its dataset ID to the second with deduplicateBy: ["url"], and reports the counts. If the sheet isn't shared yet, the error message includes the service-account email, so the agent can tell you exactly who to share it with.

Wrap-up

• For public, read-only sheets, the CSV export URL is enough.
• To write, use a service account and share the sheet with it like a person.
• Flatten nested fields, keep a stable header, dedupe on append, and use RAW for scraped text.
• When replacing a tab, write first and clear afterwards.