Ad Creative Library Builder

Ad Creative Library Builder

Builds a searchable library of every ad you have run, in a spreadsheet, with AI doing the assembly.

Nearly every creative intelligence project stalls in the same place: nobody can find the ads. The files are across Drive, the platform export has ad IDs but no thumbnails, the naming convention changed twice, and the person who knew where things were has left.

This produces the substrate everything else needs — one row per creative, one lookup per ad ID, and a visual audit that lets a human confirm the whole thing is right. It is deliberately a spreadsheet, because a spreadsheet is the only tool a marketing team will still be using in three years.

1 · Operating contract

• The library is generated whole, never patched. All four tabs are replaced together. Asset IDs are sequential, so a partial update silently desynchronises the tabs and nothing errors.
• Never show stale data. If the source cannot be read, show an error banner. A library displaying yesterday's data as today's is worse than one that is down.
• Never hardcode an ID. Sheet IDs, folder IDs and tab names live in configuration. A hardcoded ID makes the whole thing single-use.
• Never store a secret in the spreadsheet or the script. Anything sensitive goes in the platform's properties store.
• Every ad ID remains traceable. The library reduces the unit of analysis, never the data.
• The audit tab is not optional. A library nobody can verify is a library nobody should trust.

2 · When to run this

Run at the start of any creative intelligence project, and whenever a new brand or account is onboarded.

Run duplicate-ad-creative-finder first — the library is built on deduplicated creative IDs, and building it on ad IDs produces a library that repeats the same creative dozens of times.

3 · Required inputs and preflight

Must have

Improves the output

Trait tags, launch dates, placements, per-ad performance, and caption text.

Preflight

1. Deduplication run? Halt if not. Everything here keys on creative ID.
2. Links resolvable? Sample and confirm. Report any systematic failure pattern before building.
3. Write access confirmed? To the sheet and to the script environment.
4. Configuration collected? Sheet ID, tab names, header row count, column positions. All of it into config, none of it into code.

4 · The procedure

Step 1 — Build the four tabs

Four tabs because they answer four different questions, and a single mega-tab answers none of them well.

Step 2 — Set the header structure

Multi-row headers — a title row, a section-group row, then the actual column names — are readable for humans and hostile to scripts. Record the header row count in configuration and have every reader use it.

Getting this wrong produces an off-by-one that silently shifts every column, and the data looks plausible.

Step 3 — Write the backend

A read-only script that pulls from the sheet on load. Three rules:

• Read live every time. No cached copy, no fallback data. Cached data becomes stale data and stale data gets acted on.
• Fail loudly. If the sheet cannot be read, show a banner saying so. Never fall back to a hardcoded sample — someone will eventually report on it.
• Fixed column positions from config. Name-matching headers looks robust and breaks the first time someone edits a header.

The sheet is the admin panel. There is no separate database and no CMS, which is what makes it maintainable by whoever inherits it.

Step 4 — Build the audit tab properly

One row per ad, showing the assigned creative's thumbnail beside that ad's own original file. Any row where the two images differ is a bad match.

Add a Match column with representative or merged duplicate. To audit fast, filter to merged duplicates only — those are the only rows where a match can be wrong, since representatives are true by definition. On a 1,006-ad library that is roughly 800 rows to check rather than 1,006.

Step 5 — Handle media that does not render

Cloud storage links frequently will not embed in a spreadsheet cell. Convert share links to direct-view endpoints, handle virus-scan interstitials, and where a file cannot render, show the filename and a link rather than a broken image.

A library of broken image icons gets abandoned in a week.

Step 6 — Write the rebuild instructions

The deliverable is not the library, it is the ability to rebuild it. Document: which config values exist and what each controls, the regeneration order, what breaks if tabs are partially updated, and how to add a new brand.

Step 7 — Verify before handing over

• Row counts reconcile across all four tabs
• Every ad ID appears exactly once in ad_mapper
• Every creative in inventory appears in asset_rollup
• Spot-check 20 audit rows visually
• Break the source deliberately and confirm the error banner appears

That last check is the one people skip, and it is the one that matters — an untested failure path is an untested failure path.

5 · Computation rules

• All four tabs regenerated together.
• No hardcoded IDs anywhere. Config only.
• No cached or fallback data.
• Fixed column positions from config, never header-name matching.
• Every ad ID traceable to its creative.
• Audit tab always generated.

6 · Output contract

CREATIVE LIBRARY · [brand] · built 2026-09-02

TABS
  creative_inventory     515 rows   unique creatives incl. carousel cards
  ad_mapper            1,006 rows   one per platform ad
  asset_rollup           202 rows   one per creative, sorted by reuse
  creative_audit       1,006 rows   assigned thumbnail vs original

RECONCILIATION
  ad_mapper rows = source ad rows                    1,006 = 1,006  ✓
  every ad ID appears exactly once                              ✓
  every inventory creative in rollup                            ✓
  audit rows = ad rows                                          ✓

AUDIT
  representative        200 rows   true by definition
  merged duplicate      806 rows   ← check these
  spot-checked           20 rows   2 mismatches found, threshold tightened

MOST REUSED
  IMG-1043   17 ads across Meta and LinkedIn
  VID-1012   11 ads
  CAR-0217    9 ads

CONFIG — nothing hardcoded
  SHEET_ID        in script properties
  TAB names       config
  HEADER_ROWS     3
  column map      config, fixed positions

FAILURE PATH — tested
  source made unreadable → error banner displayed, no data shown  ✓

TO REBUILD
  1. run duplicate-ad-creative-finder → creative inventory + ad map
  2. regenerate all four tabs together, never individually
  3. reconcile row counts
  4. spot-check 20 merged-duplicate audit rows
  5. break the source, confirm the banner, restore

7 · Failure and degraded states

8 · Worked example

Above. Two design decisions determine whether the library is still in use a year later.

No fallback data, ever. The obvious convenience is to cache a copy so the page still renders when the sheet is unreachable. That convenience is how a team ends up reporting on data from three weeks ago without knowing it. An error banner is annoying for ten minutes; silent stale data is wrong for a quarter.

The audit tab filtered to merged duplicates. 1,006 rows is not auditable and nobody will do it. 806 rows is still a lot — but it is the complete set of rows where a match could be wrong, and knowing that makes a spot-check of 20 statistically meaningful rather than arbitrary. In this run it found two mismatches, which changed the deduplication threshold and improved every downstream analysis.

9 · Self-check

1. Were all four tabs generated together?
2. Do row counts reconcile across all four?
3. Is every ad ID traceable to exactly one creative?
4. Are all IDs and tab names in config rather than in code?
5. Does the audit tab show assigned thumbnail beside original, with a match column?
6. Was the failure path tested by breaking the source deliberately?
7. Are rebuild instructions written for someone who was not here?

MIT licensed.