The Metrics API in Google Sheets

By the HonestTag team · Published September 19, 2026

What it does

A script running inside your own Google Sheet calls two read-only endpoints, /_ht/api/v1/scorecard and /_ht/api/v1/gap, with your own Metrics API key, and writes the numbers into two sheet tabs. Run it once by hand, then attach a daily time-driven trigger so the sheet refreshes itself.

Before you start

Open the app, go to Analytics, and find the Metrics API card. Click Generate key. The key is shown once, right there, and only its fingerprint is stored on our side afterward: if you lose it, generate a new one, which replaces the old one immediately.

Set it up

1. In your Google Sheet, open Extensions > Apps Script.
2. Delete the placeholder code and paste the script below.
3. Run the setup function once. It asks for your Metrics API key and stores it as a script property, a place Apps Script keeps configuration and secrets separate from the spreadsheet's own cells. The key is never written into a cell and never into the script's own text.
4. Google will ask you to authorize the script the first time it runs. This is Google's own authorization step for the services the script uses (here your spreadsheet and fetching an external URL); approve it to let the script reach the HonestTag app host.
5. Run refreshAll once by hand to confirm it works, then add a trigger: in the Apps Script editor, open Triggers, add one for refreshAll, choose Time-driven and a daily interval.

The script

One function per concern: fetchMetricsApi calls one endpoint and turns each non-200 status, and a reply that says reporting is locked to the free Mirror view, into a plain-English error, and one small function per endpoint flattens the response into the rows it writes. It reads only the fields named below; nothing here is invented.

// HonestTag Metrics API -> Google Sheets
// 1. Run setup() once. It asks for your Metrics API key (from the Metrics
//    API card in the app) and stores it as a script property, never in a
//    cell and never in this script.
// 2. Run refreshAll() once and authorize the script when Google asks.
// 3. In the Apps Script editor: Triggers (alarm icon) > Add Trigger >
//    refreshAll > Time-driven > Day timer, so it refreshes once a day.

var HT_APP_HOST = 'https://app.honesttag.com';

function setup() {
  var key = Browser.inputBox('Paste your HonestTag Metrics API key');
  if (key && key !== 'cancel') {
    PropertiesService.getScriptProperties().setProperty('HT_METRICS_API_KEY', key);
  }
}

function refreshAll() {
  refreshScorecard();
  refreshGap();
}

function fetchMetricsApi(endpoint, windowKind) {
  var key = PropertiesService.getScriptProperties().getProperty('HT_METRICS_API_KEY');
  if (!key) throw new Error('Run setup() first to store your Metrics API key.');
  var url = HT_APP_HOST + '/_ht/api/v1/' + endpoint + '?window=' + windowKind;
  var response = UrlFetchApp.fetch(url, {
    headers: { Authorization: 'Bearer ' + key },
    muteHttpExceptions: true,
  });
  var code = response.getResponseCode();
  if (code === 401) throw new Error('HonestTag: this key is wrong or revoked. Generate a new one on the Metrics API card.');
  if (code === 403) throw new Error('HonestTag: this key is valid but your plan does not include the Metrics API.');
  if (code === 429) throw new Error('HonestTag: too many requests. Slow the refresh down.');
  if (code !== 200) throw new Error('HonestTag: unexpected response ' + code + ': ' + response.getContentText());
  var body = JSON.parse(response.getContentText());
  // A store whose paid reporting is locked (unpaid or paused plan) gets a 200
  // with mirror: true and no summary, not an error status.
  if (body.mirror) throw new Error('HonestTag: reporting is locked to the free Mirror view (' + body.posture + '). Check Billing in the app.');
  return body;
}

function refreshScorecard() {
  var data = fetchMetricsApi('scorecard', '30d');
  var rows = [
    ['date', 'window_from', 'window_to', 'orders', 'net_revenue_cents', 'spend_cents', 'mer', 'nmer', 'ncac_cents', 'aov_cents'],
    [data.today, data.window.from, data.window.to, data.summary.current.orders,
     data.summary.current.net_revenue_cents, data.summary.current.spend_cents,
     data.summary.current.mer, data.summary.current.nmer,
     data.summary.current.ncac_cents, data.summary.current.aov_cents],
  ];
  writeRows('Scorecard', rows);
}

function refreshGap() {
  var data = fetchMetricsApi('gap', '30d');
  var rows = [['platform', 'spend_cents', 'claimed_conversions', 'delivered_conversions', 'claimed_roas', 'fcnc_ncac_cents']];
  for (var i = 0; i < data.channels.length; i++) {
    var row = data.channels[i];
    rows.push([row.platform, row.spend_cents, row.claimed_conversions, row.delivered_conversions, row.claimed_roas, row.fcnc_ncac_cents]);
  }
  writeRows('Gap', rows);
}

function writeRows(sheetName, rows) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName) || ss.insertSheet(sheetName);
  sheet.clearContents();
  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}

Using IMPORTDATA instead

Google's own reference for IMPORTDATA gives it one argument: "the url from which to fetch the .csv or .tsv-formatted data." Nothing in that reference lets it send a header, so it cannot carry the key the Metrics API requires. The key must never be placed in the URL as a query parameter either: the Metrics API accepts it only as Authorization: Bearer <key>, so a plain IMPORTDATA formula cannot authenticate to it at all. Use the Apps Script above instead.

Key hygiene

• The key is shown once at generation, in the app, and only its fingerprint is stored on our side afterward.
• Store it as a script property, never in a cell: a cell is visible to anyone who can view the sheet, and a shared or exported spreadsheet carries its cells with it.
• Google's own documentation describes script properties as a property store "that all users can access, but only within this script," so treat the script project itself the way you would treat a password, and treat anyone with editor access to it as able to read the key.
• If a sheet or script project that ever held the key changes hands, or you are not sure who still has editor access to it, generate a new key on the Metrics API card. The old one stops working immediately.

What this does not do

The sheet is not a real-time view. It refreshes on whatever schedule your trigger runs, once a day if you followed the steps above. Apps Script also bounds how often a script can call out: Google's own quota page lists 20,000 URL Fetch calls per day for a consumer Google account, far above what a daily refresh needs, and the Metrics API itself accepts 60 requests per minute from one address. Google states that its quotas are "subject to elimination, reduction, or change at any time, without notice," so treat the daily figure as headroom, not a promise.