Track Core Web Vitals in a Google Sheet, without BigQuery

If you want Core Web Vitals history for a handful of URLs, the usual advice is BigQuery and a Looker Studio dashboard. That is a lot of machinery for "show me LCP and INP for these ten pages, once a day, in a sheet I can pivot." You can get the same field data straight into Google Sheets with a bit of Apps Script and the Chrome UX Report (CrUX) API. Here is the whole recipe.

You need two things: a CrUX API key and a Google Sheet.

The request

CrUX exposes real-user data through a single endpoint, records:queryRecord. You POST a URL and a form factor and get back a histogram plus the 75th percentile for each metric.

const ENDPOINT =
  "https://chromeuxreport.googleapis.com/v1/records:queryRecord?key=" + API_KEY;

const response = UrlFetchApp.fetch(ENDPOINT, {
  method: "post",
  contentType: "application/json",
  muteHttpExceptions: true,
  payload: JSON.stringify({ url: "https://example.com", formFactor: "PHONE" }),
});

Two things worth knowing up front, because they save an afternoon:

Form factors are PHONE, DESKTOP, TABLET. To get data aggregated across all of them, you omit the field entirely. There is no "all" value. So build the body conditionally:

const body = { url };
if (formFactor !== "ALL") {   // your own token for "aggregated"
  body.formFactor = formFactor;
}

The current metrics are LCP, INP, CLS, FCP, plus TTFB and RTT. INP replaced FID as the responsiveness metric, so query interaction_to_next_paint, not first_input_delay. If you send no metrics array at all, the API returns everything it has, which is the simplest thing to do.

Fetch, one at a time

Do not reach for UrlFetchApp.fetchAll. Apps Script rate-limits it, and you lose per-request control. Fetch sequentially with a small pause, which is plenty fast for tens of URLs and keeps you well under quotas:

for (const req of requests) {
  const res = UrlFetchApp.fetch(ENDPOINT, req);
  if (res.getResponseCode() === 200) {
    results.push(JSON.parse(res.getContentText()));
  }
  Utilities.sleep(400); // gentle spacing between calls
}

Check the status code before you parse. CrUX returns a normal 404 for URLs it has no data on (low traffic), and you want to skip those cleanly rather than throw.

Shape a row

Each metric comes back as a three-bucket histogram (good / needs improvement / poor) plus a p75. Flatten that into columns, and default missing values so a partial response never breaks the row:

function extractMetric(metric) {
  if (!metric) return ["-", "-", "-", "-"];
  const h = metric.histogram || [];
  return [
    h[0]?.density ?? "-",
    h[1]?.density ?? "-",
    h[2]?.density ?? "-",
    metric.percentiles?.p75 ?? "-",
  ];
}

const m = response.record.metrics;
const row = [
  new Date(),
  response.record.key.formFactor || "AGGREGATED",
  response.record.key.url,
  ...extractMetric(m.largest_contentful_paint),
  ...extractMetric(m.interaction_to_next_paint),
  ...extractMetric(m.cumulative_layout_shift),
  ...extractMetric(m.first_contentful_paint),
];

Write, and schedule

Append rows below whatever is already in the tab, and write a header row the first time the sheet is empty:

const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName("cruxData");
if (sheet.getLastRow() === 0) sheet.appendRow(HEADERS);
sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length)
  .setValues(rows);

Then set a time-driven trigger (Triggers, in the Apps Script editor) to run it daily. That is the whole thing: a spreadsheet that grows a new dated row of Core Web Vitals for each URL every day, ready to chart or pivot. Store the API key in Script Properties rather than in the code, and you are production-ready.

A finished version

I keep a fuller version of this open source, with a per-request audit tab and a couple of niceties, and it is published as an Apps Script library, so you can skip the build above and add it to your own sheet in about a minute. In your Apps Script project open Libraries, paste the Script ID below, look it up, pick a version, and set the identifier to Crux:

1EvW_5vsMBlFpt2fP0xyTmXnxV6PCbcGAd9S8UCIQMNmCCpQY15eRrH7v

Then call the one exposed function from your own entry, keeping the key in Script Properties rather than in code:

async function run() {
  return Crux.extract({
    urls: ["https://example.com"],
    spreadsheetId: "your-spreadsheet-id",
    apiKey: PropertiesService.getScriptProperties().getProperty("CRUX_API_KEY"),
  });
}

The source, including the copy-paste build if you would rather drop the code in directly, is at github.com/jadedm/crux_apps_script. If you would rather understand how it is packaged so it works both pasted in and added as a library, that is a separate post: One source, two artifacts.