# A revenue sheet

Source: https://developers.weerom.com/docs/use-cases/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.

| What | You need |
| --- | --- |
| Key | **Read key** A read key, kept in the script's properties. |
| Endpoints | `GET /shop` for the time zone, `GET /staff` for names, `GET /payments`. |
| Google | A spreadsheet and its Apps Script editor, under **Extensions**, **Apps Script**. |

The script uses Apps Script's [`UrlFetchApp.fetch`](https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app) to call WeerOm and a [time-driven trigger](https://developers.google.com/apps-script/guides/triggers/installable#time-driven_triggers) 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

```js title="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](https://developers.weerom.com/docs/webhooks) 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](https://developers.weerom.com/docs/use-cases/accountable), 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.
