Google Sheets Export: Apify Dataset to Sheets

What this Actor does

It writes rows to a tab in a Google Sheets spreadsheet. The rows can come from an Apify dataset, a JSON URL or a JSON array you paste in. You choose how they're written:

• append adds rows below the existing data.
• replace overwrites the tab.
• upsert updates rows whose key already exists and appends the rest.
• dedupe-append appends only rows whose key isn't in the tab yet.

It signs in with a Google service account that you create, so there is no "Sign in with Google" screen and no third-party app needs access to your Google account. Setup takes about 5 minutes and is described below.

The input form opens with Dry run turned on and a two-row sample. Run it as is to see the exact rows and columns that would be written. Dry runs write nothing and cost nothing.

How it handles the usual problems

• New fields between runs. If today's data has a field that yesterday's didn't, it becomes a new column on the right. Existing columns never move, and rows are matched to columns by the header text in row 1, not by position.
• Large exports. The total size is checked before anything is written. A spreadsheet can hold at most 10,000,000 cells across all tabs. If the export would go over, the run stops with the numbers and nothing is written. Rows are written in batches of up to 5,000 rows or about 1 MB per request.
• Google rate limits. Requests are spaced at least 1.1 seconds apart, under Google's default of 60 requests per minute per account. Responses 408, 429 and 5xx, and network errors, are retried up to 8 times with exponential backoff (honouring Retry-After).
• Safe retries. Every write goes to explicit row numbers and resizes are absolute, so a retried request can't add a row twice.
• Access errors. Spreadsheet access is checked before reading your data. Errors say what to change, for example: "The sheet isn't shared with bot@project.iam.gserviceaccount.com. Share it as Editor and run again."
• Empty input. In replace mode, an empty dataset leaves the sheet as it was instead of clearing it. A scraper run that returned nothing won't wipe your sheet.
• Missing tab. It is created, sized to fit the data.

Set up Google access (about 5 minutes, once)

You need a Google account. Creating the project and using the Sheets API doesn't need a billing account.

1. Create a project. Open https://console.cloud.google.com/projectcreate, enter any name (for example sheets-export) and click Create.
2. Enable the Sheets API. Open https://console.cloud.google.com/apis/library/sheets.googleapis.com, check that your new project is selected at the top, and click Enable.
3. Create a service account. Open https://console.cloud.google.com/iam-admin/serviceaccounts, click Create service account, enter a name (for example apify-sheets) and click Done. You don't need to give it any roles.
4. Create a JSON key. Click the new service account, open the Keys tab, choose Add key > Create new key, select JSON and click Create. A .json file downloads.
   • If Google says key creation is disabled by an organization policy (common on company Workspace accounts), ask your Workspace admin, or use a personal Google account for the project.
5. Paste the key. Open the .json file in a text editor, copy all of it, and paste it into Google service account key in this Actor's input. Apify stores secret input fields encrypted. The Actor never logs the key or saves it anywhere.
6. Share the spreadsheet. In the file you'll see "client_email": "apify-sheets@your-project.iam.gserviceaccount.com". In Google Sheets, click Share, paste that email, choose Editor and click Send.

To stop access later, remove the email from the sheet's sharing settings or delete the key in Google Cloud.

First run

1. Paste the key, then the spreadsheet URL (straight from the address bar).
2. Optionally set Tab name. If it's empty, the first tab is used.
3. Keep Dry run on and run. With a key, the preview is checked against the real tab: it reads the existing header, keys and last row, so the row numbers in the output are the ones that would be written. Nothing is written.
4. Turn Dry run off and run again to write.

Input

Field	What it does
serviceAccountKey	Full JSON key of the service account (secret). Needed unless dryRun is on.
spreadsheetId	Spreadsheet URL or ID. IDs of any length are accepted.
sheetName	Tab to write to. Created if missing. Empty = first tab.
mode	append (default), replace, upsert, dedupe-append.
keyColumns	For upsert and dedupe-append: header names or field paths that identify a row. Several = combined key.
dryRun	Output the rows that would be written; write and charge nothing.
datasetId	Apify dataset ID or URL.
dataUrl	Public URL returning a JSON array, JSON Lines, or an object with an items, data or results array.
rawData	JSON array of objects, or of arrays whose first array is the header.
maxRows, offset	Limit and skip input rows.
columns	Write only these fields, in this order (dot paths such as price.value).
excludeColumns	Fields to leave out when columns is empty.
columnRenames	Header text per field, e.g. {"price.value": "Price"}.
addNewColumns	Add fields missing from the header as new columns (default on). Off: leave them out.
flatten	Nested objects become dot-path columns (default on). Off: written as JSON text.
arrayHandling	join (default, one cell joined by arraySeparator), json, or expand (one column per item).
arraySeparator	Default , .
valueInputOption	RAW (default) keeps text as text, so a value like =IMPORTXML(...) is not run as a formula. USER_ENTERED parses values as if typed.
previewLimit	Rows output by a dry run (default 200). The counts cover all rows.

If more than one data source is set, datasetId is used first, then dataUrl, then rawData.

Example: keep a product sheet up to date

{
    "serviceAccountKey": "<pasted JSON key>",
    "spreadsheetId": "https://docs.google.com/spreadsheets/d/1AbC.../edit",
    "sheetName": "Products",
    "mode": "upsert",
    "keyColumns": ["url"],
    "datasetId": "<dataset ID>",
    "columns": ["url", "title", "price.value", "inStock"],
    "columnRenames": { "price.value": "Price" }
}

Each run updates the rows whose url is already in the tab and appends the new ones. In upsert, only the columns this export writes are changed; other columns, such as your own notes, are left alone.

How the modes treat the sheet

• Row 1 is always the header row.
• append writes below the last row that has any value, even if there are empty rows in between.
• replace writes the header and rows from row 1, then clears any old values below and to the right. If a replace run fails halfway, the tab holds a mix of old and new rows. Running it again fixes that.
• upsert matches keys as text, after trimming spaces, so 101 in the sheet matches "101" in the data. If the same key appears several times in the input, the last one is written. If it appears several times in the sheet, only the first of those rows is updated.
• dedupe-append keeps the first row of each key from the input and skips keys already in the tab.
• Rows whose key is empty are skipped in both key modes and counted in the summary.

Run it automatically after another Actor

Save your settings as a task first, so the key is stored once, encrypted, and never has to go in a webhook.

1. In this Actor, fill in the key, spreadsheet, tab and mode. Turn Dry run off. Click Save as a new task.

2. Open the Actor or task that produces the data, go to its Integrations tab, and choose Connect Actor or task. Pick the task you saved and the event Run succeeded.

3. In the integration's input, set:

   { "datasetId": "{{resource.defaultDatasetId}}" }
   

   Use the datasetId field even though integrations also attach a payload field. Apify only lets this Actor read datasets passed in the datasetId field.

To do the same through the API, create a webhook on the source Actor for ACTOR.RUN.SUCCEEDED. Point it at https://api.apify.com/v2/actor-tasks/<your task ID>/runs and use this payload template:

{ "datasetId": {{resource.defaultDatasetId}} }

The fields you send are merged into the task's saved input.

Don't run two exports into the same tab at the same time. Each run decides its row numbers when it starts.

Output

• Dataset. A dry run outputs one item per row, up to previewLimit: { "dryRun": true, "action": "append" | "update", "sheetRow": 5, "values": { header: value } }. Every run ends with one summary item ("action": "summary") with the counts, the spreadsheet link and any warnings. A real run doesn't copy the written rows into the dataset.
• Key-value store, record SUMMARY. Full details: rows read, appended, updated and skipped (by reason), the final column list, columns added, grid size, total cells in the spreadsheet, API requests and retries.

Pricing

You pay a small fee for each run that writes rows, plus a fee per row actually written (appended or updated). Rows are counted per batch, after Google confirms the write. Skipped rows, dry runs and runs that write nothing are not charged. See the Pricing tab for current prices.

If you set a maximum cost per run, the Actor writes only as many rows as that allows. In replace mode it won't start at all, rather than leave a half-written sheet.

Limits

• 10,000,000 cells per spreadsheet, counted across all tabs including empty grid cells. To make room, delete unused rows and columns.
• 50,000 characters per cell. Longer values are cut to fit and counted in truncatedCells.
• 18,278 columns per tab. With arrayHandling: "expand", long arrays can create many columns.
• Only Google Sheets files work. An .xlsx stored in Drive has to be converted first (File > Save as Google Sheets).
• In append, upsert and dedupe-append, columns this Actor doesn't write are left alone. Replace clears all old values in the tab (formatting stays). Cells it writes are overwritten.

Moving from lukaskrivka/google-sheets

This Actor accepts that Actor's main input field names, so an existing integration needs few changes. It is a separate project and not affiliated with it.

Their field	Here
spreadsheetId, datasetId, rawData, offset	Same meaning
limit	Same as maxRows
mode append / replace	Same. modify, read and load backup are not supported.
range	Only the tab name is used (Data!A1:Z writes to tab Data)
deduplicateByField	Same as keyColumns. With mode append it runs as dedupe-append.
columnsOrder	Listed fields first, the rest in the order first seen

Sign-in is different: instead of authorizing a Google account in the browser, you paste a service account key once (see above).

Errors and fixes

Message starts with	Fix
The sheet isn't shared with ...	Share the spreadsheet with that email as Editor.
... can read this spreadsheet but not edit it	Change the email's access from Viewer to Editor.
The Google Sheets API is not enabled	Enable it for the project named in the message (step 2).
Google rejected the service account key	The key was deleted or disabled. Create a new JSON key.
This export needs ... rows x ... columns	The 10M-cell limit. Nothing was written; reduce the data or free up cells.
That is a "Publish to the web" link	Use the normal URL from the address bar.

Found a problem? Open an issue on the Issues tab with the run ID. Please don't include your key.