Google Sheets Competitor Ad Dashboard: Build It With Apps Script

A Google Sheets competitor ad dashboard is the unglamorous answer to a question every analyst eventually asks: how do I get competitor ads in front of my whole team without buying another BI seat? You already live in Sheets. Your team already reads Sheets. The only missing piece is a script that pulls ad data on a schedule and writes it into rows.

TL;DR: Around 80 lines of Apps Script turn a blank spreadsheet into a living Google Sheets competitor ad dashboard. UrlFetchApp calls the AdLibrary API with Bearer auth, a time-driven trigger refreshes it every morning at 6am, dedup runs on ad_key, and a sparkline-plus-pivot layer sits on top. Conditional formatting flags any ad first seen in the last 48 hours. Three competitors tracked daily costs about 90 credits a month. The sheet holds up well to roughly 50,000 rows, then you graduate.

This guide walks the whole build: the fetch script, the auth, the trigger, the dashboard layer, the sharing setup, and the honest list of places where Sheets stops being the right tool.

Why a Google Sheets competitor ad dashboard beats a BI tool (for now)

Before you spec out Looker Studio or a Metabase instance, run the math on what a competitor ad monitoring dashboard actually needs to do. It answers four questions on a Monday morning. What did competitors launch last week? What formats are they pushing? Which creatives have run long enough to matter? And has anything changed since the last check?

A Google Sheets competitor ad dashboard answers all four, and it gets you things a BI tool makes you work for:

• Zero onboarding. Everyone already knows how to filter a column and read a pivot table.
• Editable analysis. An analyst can add a notes column, tag a creative angle, or build a one-off COUNTIFS without filing a ticket.
• Free distribution. Share the link. Done. No per-seat licensing conversation with finance.
• A real API runtime attached. Apps Script is a full JavaScript environment with scheduled execution baked in, and it ships with every Google account.

The case against the spreadsheet shows up at scale, covered honestly in the breakdown section. For a roster of three to fifteen competitors, the Sheet wins on speed-to-shipped. Most teams that buy a dashboard tool first rebuild the spreadsheet version anyway, because the BI build stalls in week three. The spreadsheet ships this afternoon.

This is the same logic that drives teams toward n8n recipes or Zapier workflows for ad ops. Use the infrastructure you already have until the data outgrows it.

The architecture: one Sheet, one script, one API

The whole system has three parts.

The Sheet holds two tabs. A raw ads tab where the script appends one row per creative, and a dashboard tab with pivots, sparklines, and a "new this week" view built on top. Raw data and presentation stay separated, which is the one spreadsheet discipline that prevents future pain.

The Apps Script does the work: fetch ads from the API, skip the ones already in the sheet, append the rest, log credit balance. It runs on a time-driven trigger, so nobody has to remember anything.

The API is the data source, and the choice here decides what your Google Sheets competitor ad dashboard can actually show. That decision deserves its own section, because it is the step most spreadsheet builds get wrong.

If you want a deeper view of how this fits a broader research practice, the competitor ad research strategy framework covers the analysis side, and the ongoing monitoring playbook covers diff-detection patterns this dashboard feeds into.

Step 0: pick your data source

Meta's Ad Library API is the original here, and credit where due: it is free, official, and the right tool if you need political and social-issue ad transparency data. It is also a poor fit for a commercial competitor dashboard. Coverage of regular commercial ads is limited to the EU and UK, access requires identity verification and an app review that can take days, and tokens expire every 60 days unless you build a refresh flow. All of that before the first row lands in your sheet.

Google's Ads Transparency Center has the same shape of problem from the other side. It shows you Google ads in a browser, but it is a web interface, not an API your script can call.

Meta's free API is fine for one platform. The moment you want TikTok, YouTube, or LinkedIn ads in the same sheet, you need something else. That something is the AdLibrary API, a paid REST API that returns commercial ads across Facebook, Instagram, TikTok, YouTube, Google, LinkedIn, Twitter, Pinterest, and more, from a single endpoint. Each result carries what Meta's free API never gives you for commercial ads: engagement counts, impression signals, an estimated spend figure, runtime, and a 0–1000 heat score. One adl_ key, no app review, no token babysitting.

It costs money (it sits on the Business plan), so the honest framing is this. If political ads on Meta are genuinely all you need, use Meta's free API and skip the next paragraph. If your dashboard needs commercial ads, multi-platform coverage, or performance signals to sort by, the paid API is the version of this build that your team will still use in month six. The full endpoint catalog lives in the adlibrary API documentation guide if you want to read ahead.

The rest of this walkthrough uses the AdLibrary API. Pricing is credit-based: one credit per search call, and a failed search refunds its credit automatically.

Set up the spreadsheet

Create a new spreadsheet and name the first tab ads. Put these headers in row 1:

pulled_at | brand | ad_key | advertiser_name | platform | ad_text | format | impressions | likes | shares | first_seen | last_seen

Twelve columns, one row per creative. A few deliberate choices:

• ad_key is your primary key. It is the stable identifier the API returns for every creative (meta_…, linkedin_…, and so on). All dedup logic hangs on it.
• brand is the search term you used, not the advertiser name the API returns. Keeping both lets you spot when a keyword search pulls in adjacent advertisers.
• format stores the numeric ads_type (1 image, 2 video, 3 carousel, 4 collection). Map it to labels in the dashboard tab, not in the raw data.
• Timestamps stay as dates. The script converts the API's Unix timestamps to Date objects so Sheets can filter and chart them natively.

Add a second tab named dashboard and leave it empty for now. Then open Extensions → Apps Script to create the bound script project.

One more setup step before code. In the Apps Script editor, go to Project Settings → Script Properties and add a property named ADLIBRARY_API_KEY with your key as the value. Keys are created in your AdLibrary dashboard on the Business plan, start with adl_, and are shown once at creation. Script Properties keep the key out of the code, which matters because anyone with edit access to the Sheet can read the script. Never paste an API key into a cell or a code literal.

The Apps Script: UrlFetchApp with Bearer auth

Here is the core script. It loops a competitor list, calls the search endpoint for each brand, and collects new ads into rows.

const API_URL = 'https://adlibrary.com/api/search';
const SHEET_NAME = 'ads';
const COMPETITORS = ['gymshark', 'alo yoga', 'vuori'];

function pullCompetitorAds() {
  const key = PropertiesService.getScriptProperties()
    .getProperty('ADLIBRARY_API_KEY');
  if (!key) throw new Error('Set ADLIBRARY_API_KEY in Script Properties.');

  const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
  const known = getKnownAdKeys(sheet);
  const rows = [];

  COMPETITORS.forEach(function (brand, i) {
    if (i > 0) Utilities.sleep(7000); // respect the 10 requests/min limit

    const response = UrlFetchApp.fetch(API_URL, {
      method: 'post',
      contentType: 'application/json',
      headers: { Authorization: 'Bearer ' + key },
      payload: JSON.stringify({
        keyword: brand,
        appType: '3',
        daysBack: 30,
        sortField: '-last_seen',
        pageSize: 60
      }),
      muteHttpExceptions: true
    });

    if (response.getResponseCode() !== 200) {
      console.error(brand + ' failed: ' + response.getContentText());
      return;
    }

    const data = JSON.parse(response.getContentText());

    data.results.forEach(function (ad) {
      if (known.has(ad.ad_key)) return; // already in the sheet
      rows.push([
        new Date(),
        brand,
        ad.ad_key,
        ad.advertiser_name,
        ad.platform,
        (ad.message || '').slice(0, 500),
        ad.ads_type,
        ad.impression,
        ad.like_count,
        ad.share_count,
        new Date(ad.first_seen * 1000),
        new Date(ad.last_seen * 1000)
      ]);
    });

    console.log(brand + ': ' + data.total + ' matches, ' +
      data._credits.remaining + ' credits left');
  });

  if (rows.length) {
    sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length)
      .setValues(rows);
  }
}

function getKnownAdKeys(sheet) {
  const last = sheet.getLastRow();
  if (last < 2) return new Set();
  return new Set(sheet.getRange(2, 3, last - 1, 1).getValues().flat());
}

A few mechanics worth understanding rather than copying blind.

Auth is one header. UrlFetchApp takes a headers object, and Authorization: Bearer adl_… is the entire authentication story. Compare that with the OAuth dance Meta's Marketing API requires and you understand why this build takes an afternoon instead of a sprint.

muteHttpExceptions: true is load-bearing. Without it, a 402 or 429 response throws and kills the whole run mid-loop. With it, you read the status code yourself and decide. A 402 means insufficient credits and the response body tells you the balance. A 429 means you hit the rate limit and should back off. Both deserve a log line, not a crash.

The Utilities.sleep(7000) line respects the rate limit. The API allows 10 search requests per minute per key. Seven seconds between calls keeps a long competitor list safely under it without any retry logic.

One search costs one credit. The script logs _credits.remaining after every call, so your execution log doubles as a balance monitor. Three brands daily is about 90 credits a month. A 30-brand agency roster, pulled daily, is around 900, which is why this workflow maps to the Business plan's 1,000+ monthly credits.

Keyword search casts a wide net on purpose. A brand keyword can match mentions by other advertisers, which is sometimes noise and sometimes exactly the ad intelligence you wanted. When you need a strict single-brand feed for Meta, pass metaPageId with the brand's page ID instead of a keyword, and the search short-circuits to that page's ads only.

Writing ads to rows without duplicates

The dedup approach above is deliberately boring. Read column C into a Set, skip any ad_key already present, append the rest in one setValues call. Boring is correct here for three reasons.

First, batch writes are fast and atomic in the way that matters. One setValues call per run means a failed execution never leaves a half-written row block in the middle of your data. Appending row by row with appendRow inside the loop is roughly 60 times slower and can interleave badly if two executions overlap.

Second, ad_key is stable across calls, which makes it the only dedup key worth using. Ad text changes between variants. Advertiser names get reformatted. The key does not.

Third, append-only history is the feature, not a storage bug. Because every run only adds creatives it has never seen, pulled_at becomes a discovery timestamp. Filter the sheet to pulled_at = today and you have the "what launched overnight" view that makes a dashboard worth opening. This is the same diff-detection pattern that the Make.com automation recipes implement with scenario state, except your state store is the sheet itself.

Two data-honesty rules carry over from the API into the sheet. Impression figures come from platform transparency data and arrive as bucketed ranges rather than exact counts, so treat the impressions column as a band for sorting, never as a precise number for ad spend math. And spend, where you use it, is always an estimate. If you want to sanity-check what a competitor's footprint implies in budget terms, run the bands through the ad spend estimator rather than presenting raw numbers to a client as fact.

Schedule it: time-driven triggers and refresh cadence

A dashboard nobody refreshes is a screenshot. Apps Script's installable triggers fix that with five lines:

function installTrigger() {
  ScriptApp.newTrigger('pullCompetitorAds')
    .timeBased()
    .everyDays(1)
    .atHour(6)
    .create();
}

Run installTrigger once from the editor, grant the authorization prompt, and the pull now happens every morning between 6 and 7am in your script's timezone, whether or not anyone opens the sheet. Manage it later from the clock icon in the editor sidebar.

Cadence is a real decision, not a default. Daily at 6am is right for most teams because creative launches are a daily-granularity event and the morning run means the dashboard is fresh before standup. Hourly is almost always wasted credits, since the daysBack: 30 window means re-scanning the same ads 24 times to catch a launch a few hours earlier. Weekly is too slow if you act on competitor launches, fine if the dashboard feeds a monthly creative testing retro.

The credit math makes cadence concrete. Daily pulls for three brands: ~90 credits a month. The same three brands hourly: ~2,160. Same information, 24 times the cost.

One quota note: Apps Script consumer accounts get 20,000 UrlFetchApp calls a day and a 6-minute runtime ceiling per execution, per Google's published quotas. A competitor list would need to be hundreds of brands long before either limit matters, and the API's own 10/min limit will slow you down first.

The dashboard layer: sparklines, pivots, and formatting that flags new ads

Raw rows answer questions for the analyst who wrote the script. The dashboard tab answers them for everyone else.

Pivot: format mix per brand. Insert a pivot table on the ads range with brand as rows, format as columns, and COUNTA of ad_key as values. This single table shows you who is going heavy on video, who is still running static images, and whose carousel share jumped this month. Format mix shifts are often the earliest visible sign of a strategy change.

Sparklines: launch velocity per brand. Build a small helper block where each row counts one brand's new ads per week:

=COUNTIFS(ads!$B:$B, $A2, ads!$K:$K, ">="&C$1, ads!$K:$K, "<"&C$1+7)

with week-start dates across row 1, then render each brand's row with =SPARKLINE(C2:N2). Twelve tiny charts replace a meeting. A flat line that suddenly spikes is a brand entering a testing sprint, which the creative strategist workflow treats as the trigger to go look at what they are testing.

Conditional formatting: flag new ads. On the ads tab, select the data range and add a custom-formula rule:

=$K2 > NOW() - 2

Anything first seen in the last 48 hours gets a background color. This is the highest-value five minutes of the whole build, because it converts the sheet from an archive into an alert surface. Whoever opens it sees immediately whether something new happened, with zero reading.

A longevity view. Add a computed runtime column, =L2-K2 in days, and sort descending. Long-running creatives are the closest thing competitor data has to a performance signal, since advertisers kill what does not convert. An ad that survived 90 days of someone else's ad budget deserves a place in your next creative brief. Pair longevity with the engagement columns and you have a poor man's winners report. For the rigorous version, with portfolio scoring and winner-versus-loser deltas, the API's winners endpoint does it properly, and the Claude Code workflow guide shows it running inside an agent loop.

Sharing the sheet with your team

Distribution is where the Sheet earns its keep over a BI tool, but a few defaults need changing.

Share the spreadsheet with view or comment access for most of the team and reserve edit access for whoever owns the script. This is a security boundary, not office politics. Editors can open the bound Apps Script project, and an editor could modify the script to exfiltrate the key from Script Properties. Viewers cannot.

Protect the ads tab (right-click the tab, then "Protect sheet") so nobody sorts the raw data in place and orphans the dashboard formulas. Human interaction belongs on the dashboard tab or on filter views, which let each person filter without affecting what others see.

For the team that will not open even a shared link, push the diff to them. A short addition to the script collects each run's new rows into a digest and mails it:

if (rows.length) {
  MailApp.sendEmail({
    to: '[email protected]',
    subject: rows.length + ' new competitor ads — ' +
      new Date().toDateString(),
    body: rows.map(function (r) {
      return r[1] + ' | ' + r[4] + ' | ' + String(r[5]).slice(0, 80);
    }).join('\n')
  });
}

Now the spreadsheet is also an alerting system. Agencies running this pattern per client tend to fold the digest into their reporting rhythm, and the client reporting playbook covers how to turn that raw feed into something a client reads as proof of vigilance rather than noise.

Where Sheets breaks down

The honest section. A Google Sheets competitor ad dashboard has a ceiling, and you should know where it is before you hit it.

Row volume. Google caps a spreadsheet at 10 million cells. At twelve columns that is a theoretical 800,000 rows, but the practical limit arrives far earlier. Past roughly 50,000 rows, recalculation lags, pivots stutter, and full-column COUNTIFS formulas start costing seconds per edit. A daily pull across ten competitors produces a few hundred rows a week, so you have months of runway, not years. Archive rows older than 180 days to a separate file, or treat the slowdown as your graduation notice.

No media previews. This hurts more than row limits. The sheet stores preview_img_url and the ad text, but ads are a visual medium and a creative team evaluating a hook needs to see the creative. =IMAGE(url) can render thumbnails inline, but at hundreds of rows it slows the sheet badly and external hosts often block the fetch. Video previews are simply impossible in a cell. The working pattern: keep the URL clickable for spot checks, and let deep creative review happen outside the sheet, either in the AdLibrary app or through AI enrichment calls that turn a video into a structured text teardown your sheet can hold.

Execution limits. The 6-minute Apps Script runtime ceiling combined with the API's 10 requests per minute means one execution comfortably covers about 50 search calls. Beyond that you are batching across trigger runs, which is infrastructure work in a spreadsheet macro language.

No relational layer. One brand maps to many pages, many platforms, and creatives that are really one concept in eight variants. Sheets flattens all of that into rows, so ad fatigue analysis and concept-level dedup get painful fast in formula land.

When you outgrow the sheet, the script logic ports cleanly. The same search calls feed a proper database, an n8n workflow, an MCP server your AI agent queries directly, or agent-driven pipelines that do the analysis as well as the collection. And if you came to this article hoping to skip APIs entirely and scrape, read the scraping tools comparison first. Scraping Meta breaks on every markup change and violates their terms, which is a fragile foundation for a dashboard your team depends on.

What does not port is the zero-cost distribution. Enjoy it while the row count is low.

Frequently Asked Questions

Can Google Sheets pull competitor ads automatically?

Yes. Google Apps Script runs JavaScript inside a spreadsheet, and its UrlFetchApp service can call any REST API. Combined with a time-driven trigger, the sheet refreshes itself on a schedule with no manual steps. You need an ad data API to call, since platforms like Meta limit free commercial ad data, and the script writes each returned ad as a row.

How do I authenticate an API call in Apps Script?

Pass the token in a headers object: UrlFetchApp.fetch(url, { headers: { Authorization: 'Bearer YOUR_KEY' } }). Store the key in Script Properties (Project Settings → Script Properties) instead of the code, because anyone with edit access to the spreadsheet can read the bound script. The AdLibrary API uses a single adl_ Bearer key with no OAuth flow or token expiry.

How often should a Google Sheets competitor ad dashboard refresh?

Daily is the sweet spot. New creative launches are a daily-granularity event, so a 6am trigger has the dashboard fresh before your team starts work. Hourly pulls multiply API cost roughly 24x while re-scanning mostly unchanged data. Weekly is acceptable only when the dashboard feeds a monthly retro rather than active competitive response.

How many rows can Google Sheets handle for ad tracking?

The hard limit is 10 million cells per spreadsheet, about 800,000 rows at twelve columns. Performance degrades much sooner. Expect noticeable lag past roughly 50,000 rows once pivots and COUNTIFS formulas sit on the data. Archive rows older than 180 days to a second file, or migrate to a database when daily use feels slow.

Does Meta's free Ad Library API work with Google Sheets?

Technically yes, since Apps Script can call it. Practically it is a poor dashboard source for commercial research: full ad coverage is restricted to political and social-issue ads outside the EU and UK, access requires identity verification plus app review, and tokens expire every 60 days. It works well for transparency research on Meta. Multi-platform commercial dashboards need a commercial API.

Your Google Sheets competitor ad dashboard, shipped

The whole build is one spreadsheet, roughly 80 lines of script, a trigger, and three formulas. An afternoon of work, most of it copy-paste. What you get is a living view of competitor creative strategy that your whole team can read without learning a tool, refreshed every morning before anyone asks.

The spreadsheet is the cheap part. The data source is the decision. Meta's free Ad Library API earns its place for transparency research, and it will frustrate you for commercial multi-platform tracking within the first week. The AdLibrary API exists for exactly this gap: one key, every major platform, and the engagement, runtime, spend-estimate, and heat signals that let a sort order do the analysis for you.

API access ships with the Business plan at €329/month with 1,000+ credits, which covers a daily pull across a 30-brand roster with room left for enrichment teardowns on the winners. Start with three competitors and the script above. If the 6am email ever stops feeling useful, delete the trigger. Nothing about this build locks you in, which is exactly why it is the right first dashboard.