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

Description and use

Automating data transfer between Apify and Google Sheets: reading from public sheets, writing via Sheets API, and using a ready-made Apify actor.

Problem

Scraping jobs often need to output rows to a spreadsheet and sometimes read spreadsheet inputs back as JSON for further processing.

Outcome

The article demonstrates how to automate data transfer between Apify and Google Sheets, highlighting pitfalls such as header management, nested data flattening, deduplication, formula injection, and API limits, and offers a ready-made actor to handle these concerns.

Workflow

• 1. Use CSV export URL for public sheets to read data without an API key.
• 2. Set up a Google Cloud project, enable Sheets API, create a service account, share the sheet with the account, and use spreadsheets.values.append or values.update to write data.
• 3. Deploy or use the hosted Apify actor "Google Sheets Import & Export" to handle headers, nested data, deduplication, and safe replace logic automatically.

Evidence

If a sheet is shared as "Anyone with the link can view", you don't need an API key to read it.

Source

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

Source

I packaged this as a hosted tool, Google Sheets Import & Export on Apify, with the source on GitHub.

Source