Google Sheets custom metrics for Meta Ads rules

AdOps custom metrics let a rule compare Meta’s numbers against yours. A custom metric names a Google spreadsheet ID, a sheet name, a lookup column and a value column, and AdOps resolves it at evaluation time by finding the row whose lookup column matches the campaign and reading its value column. The result behaves like any other metric.

Why put a number in a spreadsheet at all?#

Because some of the numbers that should govern a campaign never appear in Ads Manager. Margin after cost of goods. Stock on hand. A target ROAS that differs by SKU because the gross margin differs by SKU. A media buyer who maintains those figures already maintains them in a sheet.

The alternative is hardcoding a threshold into every rule and re-editing every rule when the number changes. A custom metric moves that number out of the rule and into the sheet, where the person who owns it can change it without touching automation.

What does a custom metric look like?#

Creating one takes a name, an optional description, and four binding fields.

Field	Example	What it is
Google Spreadsheet ID	the id in the sheet URL	Which spreadsheet to open
Sheet Name	Sheet1	Which tab inside it
Column ID	Date	The lookup column matched against the campaign
Value Column	Revenue	The column whose number is returned

The builder places the hint that the spreadsheet ID is found in the spreadsheet URL, and the data source type select currently offers Google Sheets. When you save, AdOps opens the sheet and confirms that both named columns exist. If either is missing you get a red Google Sheets Validation Failed modal listing every problem and a how-to-fix checklist, rather than a rule that silently evaluates against nothing at 3 a.m.

The worked example: a target ROAS per SKU#

Keep a sheet your buyer owns. One row per campaign, keyed by campaign ID in the lookup column, with the target ROAS for the SKU that campaign sells in the value column.

Then write one rule for the whole account. Set a condition on Purchase ROAS over Last 7 days, switch its compare mode to metric, and point the compared side at the custom metric. The threshold is no longer a number you typed. It is whatever the buyer put in the sheet this morning, per campaign.

Pair it with a Pause action and a name tag, and one rule now enforces per-SKU economics across an account where every SKU has a different break-even. When the merchandising team drops a price, they edit a cell, and the automation follows.

The same metric can drive a budget change directly. A budget action’s value type accepts custom_metric, so the amount written to Meta can come from a column that already holds the number your finance team agreed to.

How does AdOps read the sheet without hammering Google?#

Three layers, in order.

1. CSV export. AdOps hits Google’s direct CSV export endpoint with a 30-second timeout and parses it with a quote-aware parser. That parser tracks quoting state, handles commas inside quoted cells and escaped doubled quotes, trims header names, skips blank lines, and drops any row whose column count does not match the header. Real back-office sheets contain currency, addresses and product names with commas in them, and a naive comma split shifts every value one column left.
2. Google Sheets API. Permission and not-found errors are surfaced immediately rather than retried; other failures fall through to the slower API download.
3. MongoDB mirror. Every successful fetch is written to a google_sheets collection. If Google is unreachable entirely, the engine serves the last known rows from that mirror.

On top of that sits an in-memory cache keyed by spreadsheet and sheet name. A hit within 2 minutes of the last access is served from memory, so hundreds of campaigns in one batch share a single spreadsheet read. Batch fetches run 3 sheets in parallel per chunk.

Be aware of what that cache window actually means. The 2 minutes is measured from the last access, not from the last fetch. Under continuous access, which is exactly the large-batch case, the same snapshot can be served for longer than 2 minutes. Edit the sheet before a run, not in the middle of one, and treat the value as the number your team last agreed rather than a live feed.