DEV Community

takahiro hashito
takahiro hashito

Posted on

There is no API for \"how much have I spent this month\" — export the billing rows instead

#googlecloud #database #devops

Background

I run more than twenty small sites on Google Cloud by myself. Each one is cheap; the total is the number I actually care about.

For a while I checked the billing console by hand, which means I stopped checking it. So I decided to put "spend so far this month" on the operations dashboard I already look at every day.

That is where I got stuck: there is no API that returns your current month-to-date spend. The Cloud Billing API exposes the price catalog — the unit price of each SKU — not what you have actually been charged. You can multiply usage by unit price and call it an estimate, but an estimate is not an invoice. This post is about the way around that.

How it works

The trick is to stop looking for an aggregate endpoint and export the raw line items instead. Google Cloud can export Cloud Billing data to BigQuery on a daily basis. Once enabled, every billed line item lands in a table, one row at a time.

Cloud Billing
    | (enable billing export)
    v
BigQuery table  gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX
    |  one row = one service, one charge, one time window
    |
    +- SQL (A): this month grouped by service  -> "spend this month" tile
    +- SQL (B): last 30 days grouped by day    -> the trend chart
Enter fullscreen mode Exit fullscreen mode

I query it with the bq CLI that ships with the Google Cloud SDK and parse the JSON in Node. No BigQuery client library, no extra dependency.

One thing to know up front: the export only contains data from the day you enable it onward. It does not backfill. If you think you might want this later, turn it on now.

Implementation

Current month, grouped by service:

SELECT service.description svc, ROUND(SUM(cost),0) cost
FROM `PROJECT.billing_export.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX`
WHERE invoice.month = FORMAT_DATE("%Y%m", CURRENT_DATE())
GROUP BY svc
HAVING cost > 0
ORDER BY cost DESC
Enter fullscreen mode Exit fullscreen mode

The one design decision here is invoice.month. It is a string like "202608" that billing itself assigns to each row. If you instead build the month boundary from dates — DATE(usage_start_time) >= '2026-08-01' — the first and last day drift depending on which timezone you resolve in. The billing side already knows where its month starts, so let it decide.

Last 30 days, grouped by day:

SELECT FORMAT_DATE("%Y%m%d", DATE(usage_start_time)) d, ROUND(SUM(cost),0) c
FROM `PROJECT.billing_export.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX`
WHERE DATE(usage_start_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND DATE(usage_start_time) < CURRENT_DATE()
GROUP BY d ORDER BY d
Enter fullscreen mode Exit fullscreen mode

Today is deliberately excluded. Its line items have not all arrived yet, so including it guarantees a chart where the last bar is always suspiciously short.

Calling it from Node:

const out = execFileSync(
  "bq",
  [
    "query",
    "--project_id=PROJECT",
    "--use_legacy_sql=false",
    "--format=json",
    q,
  ],
  { encoding: "utf8", stdio: ["ignore", "pipe", "ignore"] },
);
const rows = JSON.parse(out);
Enter fullscreen mode Exit fullscreen mode

--format=json gives you output that JSON.parse accepts directly — an array of {"svc": "...", "cost": "..."} objects, with numbers arriving as strings.

Gotchas

Going through a shell produced a zero-byte file every day

The first version built a command string and handed it to execSync. Soon the repository root started collecting files:

?? 0
?? DATE_SUB(CURRENT_DATE()
Enter fullscreen mode Exit fullscreen mode

Both zero bytes. The culprits were the > and the ( inside the SQL.

execSync hands the command to a shell. On POSIX, single quotes protect the contents — but cmd.exe on Windows does not treat single quotes as quoting at all. So HAVING cost>0 was read as a redirect and created a file named 0. The other name came out of the same mangling around the parenthesis.

The fix is not better escaping. It is removing the shell:

// Pass an argument array instead of going through a shell. cmd.exe does not treat
// single quotes as quoting, so via a shell the `>` and `(` inside the SQL are
// executed as redirects and drop zero-byte files named `0` and
// `DATE_SUB(CURRENT_DATE()` into the repo root every run (observed).
// stderr is dropped via stdio (`2>/dev/null` is POSIX-only and does nothing on Windows).
Enter fullscreen mode Exit fullscreen mode

execFileSync passes the argument array straight to the process, so no quoting rules apply. For the same reason, appending 2>/dev/null is not a portable way to silence a command — use the stdio option.

If an arbitrary string goes to an external command, do not send it through a shell. That is usually framed as an injection concern; here it showed up as plain portability, and the answer was the same.

What this query still gets wrong

For honesty, the current limitations:

  1. Credits are not added back. Each row carries a credits array where free-tier and discount adjustments live. SUM(cost) alone means the number is pre-credit, so it reads higher than the real invoice. Matching the invoice exactly requires summing UNNEST(credits) as well.
  2. No partition pruning. Billing export tables are partitioned by ingestion time, and neither invoice.month nor DATE(usage_start_time) is that key — so every run scans the whole table. Harmless at my volume, but that is where _PARTITIONTIME and --maximum_bytes_billed belong once it grows.
  3. Failures silently become null. stderr is discarded, so if bq fails there is no record of why.

The third one is partly handled where daily rows are merged:

// Days missing from both maps get sessions:0 / cost:null
// (so "cost not fetched" stays distinct from "cost was zero").
Enter fullscreen mode Exit fullscreen mode

Sessions can legitimately be zero for a day, so they default to 0. Cost cannot distinguish "free day" from "fetch failed" unless you keep them apart, so it defaults to null. The right default depends on the metric, not on the storage.

The result

The dashboard lives alongside the other tools I have built: https://hashitosystem.com

Wrap-up

Here is what the month-to-date total actually looks like day to day:

2026-07-16   259 JPY
2026-07-22   416 JPY
2026-07-26   558 JPY
2026-08-05    48 JPY   <- new month, back to zero
2026-08-10   130 JPY
Enter fullscreen mode Exit fullscreen mode

Watching it reset and climb makes the slope visible, and the slope is what tells you something changed. The absolute number never worried me; not knowing whether it was accelerating did.

The transferable lesson is about where to look. When no API returns the aggregate you want, you are not stuck choosing between giving up and estimating — check whether the raw rows can be exported. Swapping the search from "aggregation API" to "export feature" turns a guess into one SQL query over real data.

This article is about my own side project. It was written with AI assistance.

Top comments (0)