Step by step process to integrate Apify with Google Sheets for automated reporting

If you want to pull web data into a living, breathing Google Sheet—without copy-pasting or late-night CSV downloads—this guide is for you. We'll walk through how to hook up Apify, a web scraping platform, directly to Google Sheets for automated, always-up-to-date reporting.

No magic. No marketing fluff. Just practical steps that work, and a few honest warnings about where things can break.

Why bother with this integration?

If you're scraping data from websites, Apify is one of the better tools out there—especially if you're not interested in maintaining your own scripts. But Apify dumps its results into a "dataset" on their platform, not into a dashboard or spreadsheet you can share with your team or boss.

Google Sheets, on the other hand, is where most people actually want to see their reports. But Google Sheets can't natively talk to Apify.

So, we need to bridge the gap: set up an automated system that takes fresh Apify data and puts it into a Google Sheet, without you lifting a finger after setup.

What you'll need

Before you start, make sure you have:

  • An Apify account (free tier is fine to start)
  • A Google account with access to Google Sheets
  • Basic comfort with copying API keys and pasting code. (You don't need to be a dev, but you can't be afraid of a little copy/paste.)
  • 30–45 minutes for the first run-through. After that, it's hands-off.

Step 1: Set up your Apify Actor or Task

First, you need some data to automate. In Apify, they call scripts "Actors"—these are the jobs that scrape or process data.

  1. Log in to Apify.
  2. Choose or create an Actor: You can use one of their ready-made Actors (like a website scraper) or build your own. If you're not sure, start with a public Actor from their store.
  3. Run the Actor manually at least once. This creates a dataset with your scraped results. You'll need the dataset ID later.

Pro tip: Click the "Storage" tab after your Actor runs. Copy the Dataset ID; it looks like a string of letters and numbers.

Step 2: Get your Apify API token

You'll need this to let Google Sheets talk to your Apify account.

  1. In Apify, click your avatar (top right), then "Account."
  2. Find your "API tokens" section.
  3. Copy your API token. Keep it handy, but don’t share it—this is basically your password for automation.

Step 3: Set up your Google Sheet

You can use any Google Sheet, but it's best to start with a new one until you know things are working.

  1. Create a new Google Sheet.
  2. Name your sheet and tab something obvious, like "Apify Data."

Step 4: Add Google Apps Script to fetch Apify data

Google Apps Script is the glue here. It lets your sheet fetch data from external APIs—like Apify’s.

  1. In your Google Sheet, click Extensions > Apps Script.

  2. Delete any code in the editor. Paste this in:

    javascript /* * Fetches Apify dataset as JSON and writes it to the sheet. / function importApifyData() { var apiToken = 'YOUR_APIFY_API_TOKEN'; // <-- Replace this var datasetId = 'YOUR_DATASET_ID'; // <-- Replace this var url = 'https://api.apify.com/v2/datasets/' + datasetId + '/items?format=json';

    var options = { 'headers': { 'Authorization': 'Bearer ' + apiToken }, 'muteHttpExceptions': true };

    var response = UrlFetchApp.fetch(url, options); var data = JSON.parse(response.getContentText());

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); sheet.clearContents();

    if (data.length === 0) { sheet.getRange(1, 1).setValue('No data found.'); return; }

    // Write headers var headers = Object.keys(data[0]); sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

    // Write data rows var rows = data.map(function(item) { return headers.map(function(header) { return item[header]; }); }); sheet.getRange(2, 1, rows.length, headers.length).setValues(rows); }

  3. Replace 'YOUR_APIFY_API_TOKEN' and 'YOUR_DATASET_ID' with your actual values.

  4. Click the disk icon to save. Name the project something like "Apify Import."

Step 5: Run the import function manually (first time)

  1. In the Apps Script editor, select importApifyData from the dropdown.
  2. Click the play ▶️ button to run it.
  3. The first time, Google will ask for permissions. Click through (read what you’re accepting if you care about security).
  4. Flip back to your sheet. If all went well, your data appears, headers and all.

If you see errors:
- Double-check your API token and dataset ID. - Make sure your dataset actually has results. - If you get a permissions error, you probably clicked "Deny" somewhere. Try again.

Step 6: Automate the import with a trigger

No one wants to click buttons every day. Let’s set up a trigger so your sheet updates itself.

  1. In Apps Script, click the clock icon ("Triggers") on the left.
  2. Click + Add Trigger in the lower right.
  3. Set these options:
    • Choose which function: importApifyData
    • Select event source: Time-driven
    • Select type of time-based trigger: pick what works for you (e.g., Hourly, Daily)
  4. Save.

Honest take:
Google Apps Script time triggers are sometimes flaky. If you're expecting 100% reliability, you might be disappointed. Sometimes triggers just... don’t run. For most simple reporting needs, though, it’s good enough.

Step 7: (Optional) Clean up and format your data

Your scraped data might be ugly, inconsistent, or full of blanks. Google Sheets formulas can help, but don't expect miracles.

  • Use built-in filters to hide junk.
  • Use conditional formatting to highlight issues.
  • For more advanced cleanup, consider cleaning data on the Apify side first.

Gotchas, limitations, and what to ignore

  • Big datasets choke Sheets: If you’re pulling in thousands of rows, Google Sheets will slow to a crawl. Try to limit your dataset or summarize before importing.
  • API changes: If Apify changes their API, your script may break. Not common, but not impossible.
  • Authentication headaches: If you change your Apify token, remember to update the script. Old tokens stop working.
  • Google quotas: Apps Script has daily quotas. If you hammer it with too many requests, Google might throttle you or shut you down for the day.
  • Ignore template add-ons: There are Google Sheets add-ons that say they can do this without code, but most are unreliable or expensive. You're better off with the script.

Pro tips

  • Test with small data first. Don't try to pull in 10,000 rows on your first go.
  • Version your script. If you tweak the function, make a backup copy. Debugging is easier if you can roll back.
  • Share with care. If you share your sheet, others can see the script (but not the API token unless you hardcode it). Still, treat API tokens like passwords.
  • Monitor for errors. If your automation stops, check the "Executions" tab in Apps Script for error logs.

Wrapping up

That’s it. You now have a working, no-BS pipeline from Apify to Google Sheets. It’s not fancy, but it’s reliable enough for most reporting needs—and you’ll learn a lot more from tweaking and iterating than from chasing fancy integrations.

Keep it simple. If you hit a wall, step back and figure out the smallest setup that meets your needs. And if something breaks, chances are it’s a copy/paste error or a credentials mixup—so don’t panic, just retrace your steps.

Happy automating.