Skip to Content
Developers

A revenue sheet

Revenue per day, per staff member and per payment method in Google Sheets, refreshed every hour by Apps Script.

For an owner who runs the numbers in a spreadsheet: who earned what this month, how much came in as cash, how a Saturday compares to a Tuesday.

A script inside the spreadsheet reads the last 31 days of payments every hour and writes one row per day, staff member and payment method. A pivot table or a chart on top of that row data answers the questions. Nothing runs outside Google.

WhatYou need
KeyRead key A read key, kept in the script's properties.
EndpointsGET /shop for the time zone, GET /staff for names, GET /payments.
GoogleA spreadsheet and its Apps Script editor, under Extensions, Apps Script.

The script uses Apps Script's UrlFetchApp.fetch to call WeerOm and a time-driven trigger to run every hour. We read both pages on 2026-11-21.

Steps

  1. In the spreadsheet, open Extensions, Apps Script, and paste the script below.
  2. Under Project settings, add a script property WEEROM_KEY with the read key. The key stays out of the code and out of the sheet.
  3. Run updateRevenue once and allow the script to reach an outside service.
  4. Under Triggers, add a time-driven trigger for updateRevenue, every hour.
  5. Insert a pivot table on the WeerOm sheet: rows by day, staff or method, values as the sum of amount.

The script

Code.gs
const BASE = "https://api.weerom.com/v1";
const DAYS = 31;

function weerom(path) {
  const key = PropertiesService.getScriptProperties().getProperty("WEEROM_KEY");
  const response = UrlFetchApp.fetch(BASE + path, {
    headers: { Authorization: `Bearer ${key}` },
    muteHttpExceptions: true,
  });
  const json = JSON.parse(response.getContentText());
  if (response.getResponseCode() !== 200) {
    throw new Error(`${json.error.code}: ${json.error.message} (${json.error.request_id})`);
  }
  return json;
}

function updateRevenue() {
  const shop = weerom("/shop");
  const staffName = {};
  for (const person of weerom("/staff").data) staffName[person.id] = person.name;

  const since = new Date(Date.now() - DAYS * 24 * 60 * 60 * 1000).toISOString();
  const totals = {};
  let cursor = null;

  do {
    let path = `/payments?limit=100&order=asc&paid_after=${encodeURIComponent(since)}`;
    if (cursor) path += `&cursor=${encodeURIComponent(cursor)}`;
    const page = weerom(path);

    for (const payment of page.data) {
      const day = Utilities.formatDate(new Date(payment.paid_at), shop.time_zone, "yyyy-MM-dd");
      const staff = staffName[payment.staff_id] || payment.staff_id;
      const method = payment.method || "not noted";
      const key = [day, staff, method].join("|");
      const row = totals[key] || { amount: 0, count: 0, day, method, net: 0, staff, tips: 0, vat: 0 };
      row.count += 1;
      row.amount += payment.amount_cents;
      row.net += payment.net_cents;
      row.vat += payment.vat_cents;
      row.tips += payment.tip_cents;
      totals[key] = row;
    }
    cursor = page.has_more ? page.next_cursor : null;
  } while (cursor);

  const rows = [["day", "staff", "method", "payments", "amount", "net", "vat", "tips"]];
  for (const row of Object.values(totals).sort((a, b) => (a.day + a.staff).localeCompare(b.day + b.staff))) {
    rows.push([row.day, row.staff, row.method, row.count, row.amount / 100, row.net / 100, row.vat / 100, row.tips / 100]);
  }

  const book = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = book.getSheetByName("WeerOm") || book.insertSheet("WeerOm");
  sheet.clearContents();
  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}

Amounts land in the sheet as euros, so a sum or a chart works on them as they are. amount includes VAT. tips is apart from amount.

How often

Every hour. A busy shop has a few hundred payments in 31 days: 2 requests for the shop and the staff, and 3 to 6 pages of payments. That is under 10 requests an hour. A sheet that shows a total does not need each payment within seconds, so it polls. For a live total, a payment.completed webhook can add each payment as it comes, but the sheet then needs a web app address the shop can add, and a payment staff remove sends nothing.

In Excel

Run the Accountable script, or this loop in Node with fetch in place of UrlFetchApp, on a schedule, and open the CSV it writes. This page does not cover Excel's own ways of calling a web address.

Limits

  • Paid cuts only. A booking without a noted payment is not in GET /payments. For booked revenue still to come, read GET /bookings?status=confirmed and sum price_cents.
  • No expenses. The API does not expose the shop's expenses, so profit stays in the admin, under Insights, Profit.
  • Today's VAT rate. net_cents and vat_cents use the shop's VAT rate as it is set today, for old payments too.
  • A rolling window. The sheet holds the last 31 days and rewrites them each hour. To keep a year, raise DAYS, or copy each finished month to its own sheet.

On this page