Keep NowMetrix metrics in Google Sheets

The spreadsheet acts as a small, customer-controlled data bridge:

NowMetrix REST API → Google Apps Script → Google Sheet → optional Looker Studio report

The data is not imported into Google Analytics. Apps Script requests the selected NowMetrix endpoint with your API key and writes the returned rows into the spreadsheet. Looker Studio can then use that worksheet as a separate data source.

1. In NowMetrix, open Settings → API and create a separate key labeled Google Sheets.
2. Copy the complete key when it is shown. It cannot be displayed again later.
3. Note the tracker ID for the website you want to export.
4. Create a new Google Sheet, for example NowMetrix Export.
5. Set the spreadsheet time zone to the same time zone as the NowMetrix tracker.

The example below exports the last 30 daily rows from /v1/overview. Each row contains the date, tracker, tracker time zone, pageviews, and visits.

In the Google Sheet, select Extensions → Apps Script. Then:

1. Open Project Settings using the gear icon.
2. Under Script Properties, select Add script property.
3. Add the following two properties:

Replace the contents of Code.gs with this script. It checks the configuration, limits spreadsheet access to the current document, handles API errors, and only replaces the worksheet after a successful response.

/**
 * @OnlyCurrentDoc
 */

const NOWMETRIX_BASE_URL = 'https://api.nowmetrix.com';

function fetchNowMetrix(path) {
  const properties = PropertiesService.getScriptProperties();
  const apiKey = properties.getProperty('NOWMETRIX_API_KEY');
  const trackerId = properties.getProperty('NOWMETRIX_TRACKER_ID');

  if (!apiKey || !trackerId) {
    throw new Error('NOWMETRIX_API_KEY or NOWMETRIX_TRACKER_ID is missing.');
  }

  const separator = path.includes('?') ? '&' : '?';
  const url = NOWMETRIX_BASE_URL
    + path
    + separator
    + 'site=' + encodeURIComponent(trackerId);

  const response = UrlFetchApp.fetch(url, {
    method: 'get',
    headers: {
      Authorization: 'Bearer ' + apiKey,
      Accept: 'application/json'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  if (status < 200 || status >= 300) {
    throw new Error('NowMetrix API returned HTTP ' + status + ': ' + body);
  }

  return JSON.parse(body);
}

function toSheetDate(value) {
  const parts = value.split('-').map(Number);
  return new Date(parts[0], parts[1] - 1, parts[2]);
}

function syncNowMetrixDaily() {
  const result = fetchNowMetrix('/v1/overview?days=30');
  const rows = result.daily.map(day => [
    toSheetDate(day.date),
    result.site,
    result.timezone,
    day.pageviews,
    day.visits
  ]);

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName('Daily')
    || spreadsheet.insertSheet('Daily');
  const values = [
    ['date', 'tracker', 'timezone', 'pageviews', 'visits'],
    ...rows
  ];

  sheet.clearContents();
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
  sheet.setFrozenRows(1);
  sheet.getRange(1, 1, 1, values[0].length).setFontWeight('bold');

  if (rows.length > 0) {
    sheet.getRange(2, 1, rows.length, 1).setNumberFormat('yyyy-mm-dd');
    sheet.getRange(2, 4, rows.length, 2).setNumberFormat('#,##0');
  }

  sheet.autoResizeColumns(1, values[0].length);
}

Do not replace NOWMETRIX_API_KEY with the actual key in the source. The script reads it from Script Properties at runtime. Keep @OnlyCurrentDoc at the top: it tells Google to request access to this spreadsheet instead of all spreadsheets in your account.

1. Name the project NowMetrix Google Sheets Export in the top-left corner.
2. Save the Apps Script project using the disk icon or the keyboard shortcut.
3. Select syncNowMetrixDaily in the function menu in the editor toolbar.
4. Select Run next to the function menu.

Google asks for permission the first time the script runs. Select Review permissions and choose the Google account that owns the spreadsheet.

A warning such as Google hasn't verified this app is expected for a private, self-created Apps Script project. The developer email shown by Google should be your own Google account. The project is not a NowMetrix Google app and does not give NowMetrix access to your Google account.

1. Confirm that you created the Apps Script project and that the developer email is yours.
2. Select Advanced on the warning screen.
3. Continue to your named project. Google may label this link as unsafe because the private project is not verified.
4. Review the requested permissions and select Allow or Continue.

If you already approved broader spreadsheet access, remove the project under Google Account → Security → Connections to third-party apps and services, then run the saved script again to request the reduced permissions.

Google documents this authorization flow and the @OnlyCurrentDoc restriction in its Apps Script authorization guide.

After authorization, Apps Script continues the run. Wait for Execution completed in the execution log, return to the spreadsheet, and open the new Daily worksheet. The result should look similar to this:

In Apps Script, open Triggers using the clock icon and add a trigger:

The trigger runs under the Google account that created it. Keep that account active and review Apps Script failure notifications. Avoid unnecessarily frequent requests: the NowMetrix public API currently allows 20 requests per minute per tracker, shared across keys and users.

The example replaces the rolling 30-day table on every successful run. This prevents duplicate dates and keeps the sheet compact.

1. Create or open a report in Looker Studio.
2. Select Add data → Google Sheets.
3. Select the spreadsheet and the Daily worksheet.
4. Use the first row as headers and connect the data source.
5. Confirm that date is a date and that pageviews and visits are numeric metrics.

You can now build time-series charts, scorecards, tables, and calculated fields such as pageviews / visits. Looker Studio reads this as a separate data source; it does not turn NowMetrix values into Google Analytics events.

Google documents the remaining report settings in its Google Sheets connector guide.

• Use a separate NowMetrix API key for the export and grant only the required tracker access.
• Store the key only in Script Properties or another server-side secret store.
• Keep @OnlyCurrentDoc in the script to restrict Google Sheets access to this document.
• Never include the key in cells, formulas, report parameters, URLs, screenshots, or logs.
• Limit edit access to the Apps Script project. Editors must be treated as trusted users.
• Authorize the script only when Google shows your own account as the developer.
• Use a separate customer-owned spreadsheet for each NowMetrix account.
• Review both Google Sheet and Looker Studio sharing settings before publishing a report.
• Revoke the API key immediately if the sheet, script, or Google account may be compromised.