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.
| 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. |
| A 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
- In the spreadsheet, open Extensions, Apps Script, and paste the script below.
- Under Project settings, add a script property
WEEROM_KEYwith the read key. The key stays out of the code and out of the sheet. - Run
updateRevenueonce and allow the script to reach an outside service. - Under Triggers, add a time-driven trigger for
updateRevenue, every hour. - Insert a pivot table on the
WeerOmsheet: rows byday,stafformethod, values as the sum ofamount.
The script
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, readGET /bookings?status=confirmedand sumprice_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_centsandvat_centsuse 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.