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

Meta Ads Manager is slow and hard to read across dozens of creatives, and AI ad dashboards run $200 to $500 a month. I built the same thing with Google Sheets, the Meta Marketing API, Gemini and NotebookLM for nothing. Here is the whole build.

Author: Nikhil Sharma (https://nikhil.pro/page/about/)
Published: 2026-03-10
Updated: 2026-10-17
Canonical: https://nikhil.pro/build-ai-meta-ads-monitor-google-sheets-gemini/
License: CC BY 4.0, attribution required.

> 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.

[Image: 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

```javascript
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 <code>ads_read</code>. 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.

[Image: 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.

```javascript
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**.

```javascript
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:

1. Export the Active Ads sheet as CSV
2. Upload it to NotebookLM
3. Add brand context notes
4. 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

```plaintext
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

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

### Step 2: Set Up the Google Sheet

1. Open Google Sheets
2. Go to Extensions → Apps Script
3. Paste the script
4. Open Project Settings → Script Properties
5. Add:

```plaintext
ACCESS_TOKEN = your long lived token
```

### Step 3: Add Campaign IDs

```javascript
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](https://nikhil.pro/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](https://nikhil.pro/page/ai-automation-for-ecommerce).

The fatigue signals this monitor flags are explained in [Facebook ad fatigue](https://nikhil.pro/ways-to-fix-ad-fatigue), and the reason a refresh often fails to reset them is in [creative similarity](https://nikhil.pro/meta-ads-creative-similarity-explained). If you would rather ask the account questions in plain English than build a sheet, [Google's MCP server for Ads](https://nikhil.pro/connect-google-ads-to-claude) 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.


## Work with the author

- Book a call with Nikhil Sharma: https://nikhil.pro/meeting
- All services: https://nikhil.pro/page/about/