Free AI Meta Ads Monitor: Google Sheets, Gemini, NotebookLM

Mar 10, 2026 · 8 min read · Updated Oct 17, 2026

💡

TL;DR: Maintaining substantial ad budgets can be exhausting, so I built a system that uses Gemini to generate recommendations. It pulls real-time data from the Meta Marketing API, analyzes metrics, and generates AI insights. You can even chat with the data. Total cost: $0.

Why I Built This

If you run a Shopify store and spend serious money on Meta ads, you know the frustration. Meta’s Ads Manager is cluttered, slow, and makes it difficult to see patterns across dozens of active creatives.

Third-party AI dashboards exist, but they typically cost $200 to $500 per month. For a growing Shopify brand, that is difficult to justify when the same outcome can be achieved with free tools and a weekend of setup.

So I built my own with the Meta Marketing API, Google Apps Script, Google Sheets, Gemini and NotebookLM. It syncs every 30 minutes, flags creative fatigue, highlights new ads the moment they launch, and lets me ask the live data questions in plain English.

The AI Stack (All Free)

Tool	Role
Meta Marketing API	Fetches real-time ad data, metrics, creatives, and targeting
Google Apps Script	Automates syncing, processes data, writes to Sheets
Google Sheets	Live dashboard with color-coded performance indicators
Gemini	Real-time Q&A against sheet data
NotebookLM	Deep weekly AI analysis of exported ad data

No servers. No databases. No subscriptions. Just AI working directly on real data.

How the Real-Time Sync Works

The system runs on two automated schedules.

Two schedules: every 30 minutes from 9 AM to 11 PM for metrics, creative data, targeting and week over week comparisons, and a nightly job for video downloads, captions and transcripts, split because Apps Script stops a run at six minutes

Every 30 Minutes (9 AM to 11 PM)

Fetches:

Metrics
Creative data
Targeting info
Week-over-week comparisons

All results are written to Google Sheets within seconds.

Every Night at Midnight

Downloads video creatives to Google Drive
Fetches captions and transcripts
Stores shareable links directly in the sheet

const CONFIG = {
  ACCESS_TOKEN: PropertiesService.getScriptProperties().getProperty('ACCESS_TOKEN'),
  AD_ACCOUNT_ID: 'act_XXXXXXXXXXXXXXXXX',
  API_VERSION: 'v25.0',
  CAMPAIGN_IDS: [
    'YOUR_CAMPAIGN_ID_1',
    'YOUR_CAMPAIGN_ID_2',
  ],
};

💡

Keep the token in Script Properties and request only ads_read. The script is then read only and cannot touch a campaign or a budget, which is what you want from something running on a timer.

What the AI Sees

Each ad gets one flat row containing every metric needed for analysis.

Group	Columns
Identity	Ad name, campaign, ad set, status, budget
Lifetime metrics	Spend, CTR, outbound CPC, CPM
Purchase funnel	Adds to cart, checkouts, purchases, purchase value, cost per purchase, ROAS, spend per cart, spend per checkout
Week over week	Spend, CTR, purchases, ROAS, cost per purchase, each with a percentage change column
Creative	Headline, body copy, CTA button type
Targeting	Age range, gender, placements, audience name, geo
Video	Thumbnail, Google Drive link, captions or transcript
AI signals	Fatigue status, new ad flag

This flat structure makes it extremely easy for Gemini and NotebookLM to reason across the entire account.

The Color-Coded Dashboard

The sheet uses a visual system so performance can be scanned in seconds.

Dashboard row colours: purple for a new ad in the last 3 days, green for cost per purchase under $20, yellow for $20 to $40, red above $50 or $20 spent with zero purchases, white for not enough data

Additional Cell Signals

Amount spent: blue heat map, darker for higher relative spend.
Cost per purchase: green under $20, yellow $20 to $40, red above $40.
Purchases: dark green for 5 or more, light green for 1 to 4, red for zero.
Fatigue status: Healthy, Moderate or Severe.
Week over week columns: green text for improvement, red for decline.

AI-Powered Creative Fatigue Detection

Every sync compares CTR from the last 7 days with the previous 7 days and flags the drop.

const ctrDrop = lastWeekCTR > 0 ? (lastWeekCTR - thisWeekCTR) / lastWeekCTR : 0;

if (ctrDrop >= 0.50) fatigueStatus = 'Severe Fatigue';
else if (ctrDrop >= 0.30) fatigueStatus = 'Moderate Fatigue';
else fatigueStatus = 'Healthy';

Fatigued ads land on a Fatigue Alerts sheet with the ad name, campaign, both CTRs, the percentage drop, spend, purchases and severity.

💡

A 30 percent CTR drop week over week is Moderate, 50 percent is Severe. Tune the thresholds to your account, but keep two levels so the sheet tells you what to look at first.

Automatic New Ad Detection

The script stores every Ad ID it has seen in Script Properties. Any ID it has not seen before is flagged as a new ad.

function checkAndUpdateNewAds(adIds) {
  const now = Date.now();
  const knownAds = getKnownAdIds();

  for (const id of adIds) {
    if (!knownAds[id]) {
      knownAds[id] = now;
    }
  }

  saveKnownAdIds(knownAds);
  return knownAds;
}

New ads remain highlighted for three days, making it easy to see when Meta begins allocating spend.

The AI Layer

Gemini: Chat With Your Live Data

Gemini sits inside Google Sheets and answers questions against the live rows. The ones I ask most:

Which ads have spent over $50 this week with zero purchases?

What is my blended ROAS across all active campaigns?

Show every ad where CTR dropped more than 40 percent versus last week.

Which campaign has the lowest cost per purchase?

Gemini reads the sheet structure directly, so the flat one row per ad layout above is what makes these answers accurate.

NotebookLM: Deep Weekly Analysis

NotebookLM is for the longer weekly read. Each Monday:

Export the Active Ads sheet as CSV
Upload it to NotebookLM
Add brand context notes
Ask analytical questions

Questions it is good at: which ads have high ROAS but low spend, what the top headlines have in common, which audiences convert under $35 cost per purchase, which ads to scale. It synthesises across hundreds of rows in one pass.

The Prompt Used for NotebookLM

You are a performance marketing analyst for a US-based Shopify brand.
Our ROAS target is 2.5x. Target Cost per Purchase is under $35.

Analyze this Meta Ads data and give me:

1. Top 5 performing ads by ROAS and what they have in common
2. Top 5 wasted spend ads (high spend with low or zero purchases)
3. Patterns in winning headlines and body copy
4. Which audiences convert best and at what CPP
5. Specific actions to take in the next 48 hours

Be direct. Skip the preamble. Lead with numbers.

💡

Put your targets in the prompt. The ROAS and cost per purchase lines are what make the analysis specific to you; without them the model gives generic advice a dashboard already shows.

Setting It Up

Step 1: Create a Meta App

Go to developers.facebook.com
Create a new app with Business type
Add the Marketing API product
Generate a User Token with ads_read permission
Exchange it for a long-lived token

Step 2: Set Up the Google Sheet

Open Google Sheets
Go to Extensions → Apps Script
Paste the script
Open Project Settings → Script Properties
Add:

ACCESS_TOKEN = your long lived token

Step 3: Add Campaign IDs

CAMPAIGN_IDS: [
  'YOUR_CAMPAIGN_ID_1',
  'YOUR_CAMPAIGN_ID_2',
],

Campaign IDs are in the Ads Manager URL when a campaign is selected.

Step 4: First Sync

Run syncFast() once by hand to authorise permissions. After that everything runs on the triggers.

Step 5: Enable Auto Refresh

Start Smart Refresh (30 minutes).

Two triggers are created:

smartRefresh every 30 minutes
nightlyVideoDownload every midnight

Technical Lessons Learned

CTR values are already percentages. Formatting them as percent in Sheets multiplies them by 100. Use the custom format 0.00"%".
Budget values return in cents. Divide by 100.
Error 80004 is a rate limit. It shows up during development; the script should pause and retry.
Always paginate. Campaigns with more than 100 ads come back in pages; follow paging.next until it is null.
Apps Script stops a run at six minutes. Heavy work such as video downloads belongs in the nightly job only.

Real Results

After several weeks: fatigued creatives caught earlier, new ads visible within 30 minutes, the weekly analysis down from two hours to fifteen minutes, wasted spend reallocated faster, and midday budget questions answered without opening Ads Manager.

What Comes Next

Next on the list: a weekly AI email digest, a spend pacing tracker, and AI clustering of creative patterns.

Final Thoughts

💡

You do not need a $300 a month tool for AI ad reporting. The Marketing API, Apps Script, Sheets, Gemini and NotebookLM are all free, and the result is an AI layer that knows your numbers and your targets, refreshed every 30 minutes.

If you run Meta ads for a Shopify brand and want the full Apps Script and the sheet template, join the newsletter and reply to the welcome email with the word monitor; I send it back the same day. If you would rather have it set up on your account with the alerts tuned to your numbers, that is one of the builds I do.

The fatigue signals this monitor flags are explained in Facebook ad fatigue, and the reason a refresh often fails to reset them is in creative similarity. If you would rather ask the account questions in plain English than build a sheet, Google’s MCP server for Ads does the same for the Google side.

Frequently asked questions

What should I check every morning to manage my Shopify ads?

Four numbers, in two minutes: yesterday's spend against budget, cost per purchase by ad against your target, frequency on prospecting ad sets, and anything new that spent money with no result. The monitor in this post writes those into a sheet overnight and colours the rows, so the morning check is reading five coloured lines rather than opening Ads Manager.

Can you monitor Meta Ads performance for free?

Yes. The Meta Marketing API, Google Sheets and a free model tier cover it. The paid AI ad dashboards mostly package the same API data with a chart layer on top.

How do you pull Meta Ads data into Google Sheets?

Create a Meta app to get API access, then have Apps Script call the Marketing API on a timer and write the results into the sheet. The post covers the app setup, the sheet structure and the refresh schedule.

How often should an ads dashboard refresh?

Often enough to catch a problem the same day, not so often that you burn API quota. A sync through business hours with a fuller nightly rebuild is a reasonable shape for most accounts.

Can AI detect creative fatigue automatically?

It can flag the pattern: rising frequency alongside falling click-through and rising cost per result on a specific creative. The judgement about what to do next still belongs to a person, but the surfacing can be automated.

What is NotebookLM useful for with ads data?

Longer-form weekly analysis across a lot of rows at once, where you want a written read rather than a single answer. It suits the retrospective pass better than the quick daily question.

Is it worth paying for an AI ads dashboard?

If you want it running tomorrow with support, yes. If you are willing to spend a weekend on setup, the free stack gets you most of the same output, and you keep control of what the model actually sees.

Part of AI automation and MCP, where every guide on the subject is listed in reading order.

Nikhil Sharma

Nikhil Sharma runs data and operations for a DTC brand on Shopify and consults for Shopify brands on Meta and Google ads, conversion and AI automation. He writes case studies and implementation guides from accounts and systems he has run himself.

Book a call More about me