How do I get my figures into Google Sheets?

A formula in Google Sheets cannot fetch your figures on its own: the read API expects the key in the request header, and no formula sets one. A short script in Apps Script does it instead, the scripting environment that comes with every Google spreadsheet. Every morning it fetches the daily rows of your account and writes them to a sheet called “Metrunio”. Pivot tables and charts built on that sheet then show the new figures.

How to set it up

1. 01In the portal, create a read key under “Exports & AI connections” in “API access”, ideally one just for this spreadsheet. It is shown in full only once.
2. 02Open the spreadsheet and choose “Extensions > Apps Script”. Replace the contents of Code.gs with the template below and save.
3. 03Open “Project Settings” on the left and add the property METRUNIO_READ_KEY under “Script Properties”, with your key as its value.
4. 04Select the function installDailyTrigger at the top and click “Run”. The first time, Google asks for your consent: the script wants to edit spreadsheets, fetch an external address and run daily, even when you are not signed in. If Google says the app has not been verified: it is your own script, and “Advanced” lets you continue.
5. 05The sheet “Metrunio” is then in place, and the trigger refills it every morning between 6 and 7 am.

The template

// Metrunio: daily rows per channel and campaign in the sheet "Metrunio".
// Enter your read key in the project settings under "Script Properties"
// as METRUNIO_READ_KEY, never here in the code.
const API_URL = 'https://api.metrunio.com/v1/data/daily';
const SHEET_NAME = 'Metrunio';
const COLUMNS = [
  ['day', 'Day'],
  ['channelName', 'Channel'],
  ['campaignName', 'Campaign'],
  ['attributedConversions', 'Attributed conversions'],
  ['attributedRevenue', 'Attributed revenue'],
  ['postViewConversions', 'Post-view conversions'],
  ['unattributedConversions', 'Unattributed conversions'],
  ['unattributedRevenue', 'Unattributed revenue'],
  ['clicks', 'Clicks'],
  ['impressions', 'Impressions'],
  ['viewableImpressions', 'Viewable impressions'],
  ['cost', 'Cost'],
];

// Fetches the figures and rewrites the sheet. The trigger calls this every morning.
function updateMetrunio() {
  const key = PropertiesService.getScriptProperties().getProperty('METRUNIO_READ_KEY');
  if (!key) throw new Error('The script property METRUNIO_READ_KEY is missing.');

  const response = UrlFetchApp.fetch(API_URL, {
    headers: { Authorization: 'Bearer ' + key, 'Accept-Language': 'en' },
    muteHttpExceptions: true,
  });
  const body = response.getContentText();
  if (response.getResponseCode() !== 200) {
    throw new Error('Metrunio ' + response.getResponseCode() + ': ' + errorMessage(body));
  }

  const values = [COLUMNS.map((column) => column[1])];
  for (const row of JSON.parse(body).data) {
    values.push(COLUMNS.map((column) => cell(row[column[0]])));
  }
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName(SHEET_NAME) || spreadsheet.insertSheet(SHEET_NAME);
  sheet.clearContents();
  sheet.getRange(1, 1, values.length, COLUMNS.length).setValues(values);
}

// Empty cost stays empty: no cost recorded, which is not the same as zero.
// A name starting with =, +, - or @ stays text and does not become a formula.
function cell(value) {
  if (value === null || value === undefined) return '';
  if (typeof value === 'string' && /^[=+@-]/.test(value)) return "'" + value;
  return value;
}

// The API's message, in the language from Accept-Language.
function errorMessage(body) {
  try {
    const error = JSON.parse(body).error;
    return [error.message].concat(error.resolution || []).join(' ');
  } catch (parseError) {
    return body.slice(0, 300);
  }
}

// Run once: creates the daily run between 6 and 7 am and fills the sheet right away.
function installDailyTrigger() {
  for (const trigger of ScriptApp.getProjectTriggers()) {
    if (trigger.getHandlerFunction() === 'updateMetrunio') ScriptApp.deleteTrigger(trigger);
  }
  ScriptApp.newTrigger('updateMetrunio').timeBased().everyDays(1).atHour(6).create();
  updateMetrunio();
}

What the sheet contains

One row per day, channel and campaign for the last 30 days up to and including today, counted in your account’s time zone. Today is complete in the sheet only after the next morning’s run. Attributed conversions and attributed revenue follow your account’s default model, like the reports; clicks and impressions are measured.

If a cost cell stays empty, no cost was recorded for that row. That is not the same as zero. Unattributed conversions sit in the row without channel and campaign, and the sums per channel are the same as under “Channels & attribution”.

Adjusting it

• Columns: the COLUMNS list sets the order and the headings. Remove what you do not need. The API also delivers the IDs of channel and campaign (channelId, campaignId) and the attributed acquisition value (attributedSetValue). That is the value you gave a conversion yourself, not revenue.
• Period: append, for example, ?from=2026-01-01 to API_URL. Without an end, the period runs up to today. A query returns at most 50,000 rows; if it needs more, the API refuses instead of dropping rows, and a shorter period helps.
• Model: with ?model=linear the API uses a fixed model instead of your account’s default model. You join two settings with &, for example ?from=2026-01-01&model=linear.

If a run fails, for example because the key was revoked or has expired, Google sends you an email with Metrunio’s message. The sheet then keeps the state of the last successful run.

Why not IMPORTDATA?

IMPORTDATA fetches an address but sends no header. The key would therefore have to be in the address, and so in a formula that anyone you show the spreadsheet to can read. The read API only accepts the key in the request header anyway.