Export Facebook Ads to Google Sheets and Copy What Works
Pull competitor ads from the Meta Ad Library into one sortable Google Sheet with =FACEBOOKADS(), then sort by start date to find the ads they keep running. 20 scraping credits per ad, no screenshots.
Phil Nguyen · 6 min read

=FACEBOOKADS() searches the Meta Ad Library by Facebook page name or keyword and writes the ads into your Google Sheet, one row per ad, with the ad copy, headline, call to action, start date and status. You get one sortable list of what your competitors are running, so you can find the ads they keep running and brief your next test in minutes.
Picture an agency media buyer who checks five competitors in the Meta Ad Library every Monday and screenshots or copies each ad into a doc. At about 2 minutes per ad, 50 ads is 100 minutes, more than an hour and a half before any analysis starts. One formula replaces the copying, and 50 ads cost 1,000 scraping credits.
How do you pull Facebook ads into Google Sheets?
Type the formula in any cell:
=FACEBOOKADS(pageNames, searchTerms, country, activeStatus, maxResults)
- pageNames: the page name from the page URL, the part after facebook.com/ (for example
examplebrand). Use one value or a range such asA2:A6. - searchTerms: keywords to search across the whole Ad Library. Give at least one of pageNames or searchTerms.
- country: a two-letter country code,
USby default. It filters keyword searches only. A page name lookup returns that page's ads from every country it runs in. - activeStatus:
"active","inactive", or blank for both. - maxResults: the most ads to return, 20 by default. It is a total across every page name and keyword in the call.
Two examples:
=FACEBOOKADS("examplebrand", , , "active")
=FACEBOOKADS(, "protein powder", "GB", "active", 30)
The cell shows ⏳ Scraping... straight away while the search runs in the background, then the ads arrive in the sheet.
What columns do you get?
The ads land in a new tab called Facebook Ads Results. The first column, Source, holds the cell reference of the formula that fetched each row. Then come:
Page Name, Ad Body, CTA, Link URL, Platform, Started, Ended, Status, Page Likes, Ad Archive ID, Ad Library URL, Ad Headline, CTA Type.
A few details worth knowing:
- Ad Body is cut to 500 characters.
- Ended stays blank while the ad is still running.
- Status reads Active or Inactive.
- Ad Library URL links straight to the ad in the Meta Ad Library.
Here is the shape of the output, with made-up values for illustration:
| Source | Page Name | Ad Body | CTA | Started | Status |
|---|---|---|---|---|---|
| B2 | Example Brand | Your morning routine, minus the 20 minutes... | Shop now | 2026-06-02 | Active |
| B2 | Example Brand | New: the travel kit that fits in a pocket... | Learn more | 2026-10-28 | Active |
With Keep Formula on (the default), the formula cell is kept as text, '=FACEBOOKADS(...), so it does not run again on its own. With Keep Formula off, the cell shows a status such as "✅ 50 results → Facebook Ads Results".
What can you do with the results?
- Find the ads that keep running. Filter Status to Active and sort Started from oldest to newest. An ad that has run for months is one the advertiser has kept running, which makes it a better model for your next test than a screenshot picked at random. The sort takes seconds against the 100 minutes of copying in the example above.
- See which offers and landing pages they push. Filter or count by CTA Type and Link URL, for example
=COUNTIF(E:E, E2)next to each Link URL. Testing an offer a competitor already backs with spend is a smaller bet than testing one you guessed, which is money not wasted on an untested angle. - Tag every hook with AI. In an empty column of the results tab, add
=AITEXT("In under 8 words, name the hook this ad uses", C2)where column C is Ad Body, and fill it down. With default settings the answer appears in the cell to the right of each AITEXT formula, so leave that column empty. A tagged list of 50 hooks is a creative brief you did not have to write. - Catch new campaigns every week. Re-run the search each Monday and filter Started to the last seven days. A competitor's new push shows up the week it starts instead of weeks later.
How much does it cost?
FACEBOOKADS costs 20 scraping credits per ad. It uses scraping credits only, no AI tokens, unless you add AITEXT columns yourself.
Using the example above: 50 ads is 50 x 20 = 1,000 scraping credits. The Solo plan includes 10,000 scraping credits a month, so a weekly sweep of 50 ads uses 4,000 to 5,000 of them in a month.
If your balance cannot cover the full maxResults, SheetMagic lowers maxResults to the number of ads your balance does cover and runs that. If it cannot cover even one ad, the cell shows an out of credits message with the amount needed and the amount you have. For the bigger picture on credits, see AI tokens vs scraping credits.
How do you scale to many competitors?
You have two options:
- One formula, a range of pages. Put page names in A2:A6 and use
=FACEBOOKADS(A2:A6, , , "active", 50). maxResults is split across the pages, so 50 across five pages asks for about 10 ads each. - One formula per competitor. Put each page name in column A and
=FACEBOOKADS(A2, , , "active", 20)in column B, then drag it down. Formulas dragged down one column share one results tab, and the Source column tells you which row each ad came from.
What are the common pitfalls?
- Expecting the ads in the formula cell. Results always go to the Facebook Ads Results tab, never below the formula. If that name is taken, a numbered tab such as "Facebook Ads Results (2)" is used.
- Setting country for page names. Country only filters keyword searches.
- A blank input cell. A formula pointed at an empty cell returns "#ERROR: empty input" and does not run.
- A blank Ad Headline. Headlines built from template placeholders come through blank. Read Ad Body instead.
- Nesting FACEBOOKADS inside AITEXT. The formula returns the Scraping placeholder first, so AITEXT would read the placeholder. Point AITEXT at the result cells instead.
- Re-running a frozen formula. With Keep Formula on, delete the leading apostrophe to run it again. Each run is charged again.
For more scrapers that feed a sheet, see the web scraping guide. To turn research like this into a prospect list, see sales lead generation.
Text and scraping formulas run on the free allowance. Install from the Google Workspace Marketplace, no card, no expiry.
Install free for Google SheetsAbout the author
Phil NguyenPhil builds SheetMagic, the Google Sheets add-on that brings AI and web scraping into the spreadsheet, used by 6,000+ people. He writes these guides from the formulas he runs on his own sheets.
