Exporting statistics to an external table
Everything Qubix records is yours, and sometimes it has to live somewhere else as well: a shared spreadsheet the team keeps open all day, a finance report, a dashboard somebody else already built.
A script closes this. It reads the slice you describe, sends it to an address that accepts rows, and remembers which hours have gone out in full — so a re-run after a failure fills the gap instead of starting over. An hour that was cut short is sent again from the beginning, so the receiver has to tolerate seeing the same hour twice.
This is about sending your numbers out. To read them inside Qubix, the sections and cards already show them — start from My dashboards.
Step 1. Prepare the receiver
The script sends rows over HTTP, so on the other side you need an address that accepts them. Two common shapes:
- A spreadsheet. Google Sheets does not accept rows directly — you publish a small Apps Script web app next to the sheet, and it writes what arrives into the rows. The address of that web app is what the script sends to.
- Anything else that speaks HTTP — a warehouse endpoint, an internal service, a low-code receiver.
Whatever you pick, the address has to be allowed for outbound requests: an administrator adds its host in System → the JavaScript tab. Until then the script stops on the first send and says so in the console:
Send failed, stopping: host "script.google.com" not in allowlist
Step 2. Look at the slice before you export it
Open any script and run the query on its own first — it costs nothing and shows exactly what will land in the table. The event names are in the Event reference.
function main() {
const rows = sql`
SELECT toStartOfHour(event_time) AS hour,
geo AS country,
campaign_id,
event,
count() AS n
FROM qubix_events
WHERE event_time > now() - INTERVAL 6 HOUR
AND event IN ('campaign_visit', 'reg', 'dep')
GROUP BY hour, country, campaign_id, event
ORDER BY hour, country
LIMIT 20`
for (const r of rows) console.log(r.hour, r.country, r.campaign_id, r.event, r.n)
}
Change the grouping until the output is the table you want. Which tables and columns a query can reach is in Tables for sql queries.
Step 3. The script
Set the schedule to 5 * * * * — five minutes past every hour. The script exports whole hours, so running it a little after the hour turns means the hour it picks up is already complete.
// ════════════════════════════════════════════════════════════════════════════
// Sending a slice of your statistics to an external table.
//
// Every hour the script works out which hours have not been exported yet,
// reads them in one query, and posts them to your receiver in batches.
// Only hours that went out in full are remembered, so nothing is lost. An hour
// cut short by a failure is sent again from the start on the next run — make
// the receiver tolerate seeing the same hour twice.
//
// A script is exactly one `function main()` with everything inside it — that
// is the shape the editor accepts.
// ════════════════════════════════════════════════════════════════════════════
function main() {
// ─── Everything you change for your own setup lives here ──────────────────
const options = {
// What to export
slice: {
// The window to cover. The script exports whole hours only, so a run
// that fires mid-hour never sends a half-filled one — see `run` below.
hoursBack: 24,
// Which events to count. Event names: /reference/events
countEvents: ['campaign_visit', 'reg', 'dep'],
},
// Where the rows go
send_to: {
// The address that accepts the rows. The administrator must allow this
// host in System -> JavaScript; otherwise the request is refused with a
// clear message in the console.
url: 'https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec',
// Sent as headers; put an API key here if the receiver wants one.
headers: { 'Content-Type': 'application/json' },
// How many rows go in one request. A spreadsheet endpoint chokes on
// thousands of rows in a single body; several smaller requests do not.
// An hour bigger than this is split across requests, so a failure inside
// it makes the whole hour repeat on the next run.
rowsPerRequest: 200,
},
run: {
// Hours that went out in full are remembered, so a re-run after a failure
// sends only what is missing. An hour interrupted midway is not remembered
// and is therefore repeated whole.
stateKey: 'exported_hours',
// How many hours to keep in that memory before forgetting them.
rememberHours: 168,
// How long to wait for the receiver before giving up on a request.
requestTimeoutMs: 15000,
// Must NOT exceed user_scripts.sql_max_rows in System -> JavaScript. A query
// is cut at that many rows, and the cut is only mentioned in the run console
// — the script gets a short list with no flag, so this number is the only
// thing that can notice it. Set it ABOVE the setting and the check dies
// silently: the engine cuts, the flag never fires, and the missing rows are
// marked as exported. Below the setting it merely fires early, which costs
// nothing.
maxRowsPerRun: 10000,
},
}
// ─── 1. Which hours still need sending ────────────────────────────────────
// Work in whole hours: the current, unfinished hour is deliberately left for
// the next run. Telemetry is written with a delay, so an hour that has only
// just started is always short — exporting it would send numbers that keep
// growing after they arrive in the table.
const nowHour = Math.floor(Date.now() / 3600000)
const wanted = []
for (let h = nowHour - options.slice.hoursBack; h < nowHour; h++) wanted.push(h)
const alreadySent = ctx.state.get(options.run.stateKey) || []
const sentSet = {}
for (const h of alreadySent) sentSet[h] = true
const todo = wanted.filter(function (h) { return !sentSet[h] })
if (!todo.length) {
console.log('Every hour in the window has already been exported.')
return
}
console.log('Hours to export:', todo.length, '(of', wanted.length, 'in the window)')
// ─── 2. Read the slice ────────────────────────────────────────────────────
// One query for the whole range, not one per hour: a run has a budget of
// twenty queries, and an hour-by-hour loop would spend it on the window
// alone. The hour is part of the grouping, so the rows come back split.
const fromSec = todo[0] * 3600
const toSec = (todo[todo.length - 1] + 1) * 3600
// The columns are written out here rather than assembled from a setting on
// purpose: values passed through `${…}` travel as safe parameters, so they
// can carry a date or a list — but never a column name. Change the slice by
// editing this query, which is also the only place that shows what is sent.
const rows = sql`
SELECT toStartOfHour(event_time) AS hour,
geo AS country,
campaign_id,
event,
count() AS n
FROM qubix_events
WHERE event_time >= toDateTime(${fromSec})
AND event_time < toDateTime(${toSec})
AND event IN (${options.slice.countEvents})
GROUP BY hour, country, campaign_id, event
ORDER BY hour, country`
const truncated = rows.length >= options.run.maxRowsPerRun
console.log('Rows read:', rows.length, truncated ? '(ceiling hit)' : '')
if (!rows.length) {
console.log('Nothing recorded in those hours — nothing to send.')
return
}
// ─── 3. Send them, in batches ─────────────────────────────────────────────
let batch = []
let everythingWentOut = true
// The hour of the last row that actually left AND ended on an hour boundary.
// A batch closes on an hour change or when it is full, so a full batch can end
// mid-hour — that hour is not marked here, otherwise its remaining rows would
// never be sent. When the query hit its row ceiling the boundary hour is short
// too, and it is excluded below.
let lastRowSent = null
for (let i = 0; i < rows.length; i++) {
batch.push(rows[i])
const isLast = i === rows.length - 1
const hourEnds = isLast || rows[i + 1].hour !== rows[i].hour
if (batch.length < options.send_to.rowsPerRequest && !hourEnds) continue
let res
try {
res = ctx.fetch(options.send_to.url, {
method: 'POST',
headers: options.send_to.headers,
body: JSON.stringify({ rows: batch }),
timeout_ms: options.run.requestTimeoutMs,
})
} catch (e) {
// The address is not on the allowlist, or the receiver is unreachable.
// Stop here: the hours in this batch stay unremembered, so the next run
// picks them up instead of losing them.
console.log('Send failed, stopping:', e.message)
everythingWentOut = false
break
}
if (res.status >= 400) {
console.log('Receiver refused with', res.status, '- stopping so nothing is lost')
everythingWentOut = false
break
}
console.log('Sent', batch.length, 'rows, receiver answered', res.status)
if (hourEnds) lastRowSent = Math.floor(new Date(batch[batch.length - 1].hour + 'Z').getTime() / 3600000)
batch = []
}
// ─── 4. Remember what went through ────────────────────────────────────────
// Only hours whose rows actually left are written down, so a failure halfway
// leaves the rest for the next run instead of silently skipping them.
const sentHours = (everythingWentOut && !truncated)
? todo
: todo.filter(function (h) {
if (lastRowSent === null) return false
return truncated ? h < lastRowSent : h <= lastRowSent
})
if (!everythingWentOut || truncated) {
console.log('Hours confirmed sent:', sentHours.length, 'of', todo.length,
'- the rest stay for the next run')
}
const keep = alreadySent.concat(sentHours)
.filter(function (h) { return h > nowHour - options.run.rememberHours })
ctx.state.set(options.run.stateKey, keep)
console.log('Remembered hours:', keep.length)
}
What the script is built around
Whole hours, never a partial one. Telemetry arrives with a delay, so the hour that has only just started is always short. Exporting it would put numbers into your table that keep growing after they land there. The current hour is left for the next run — which is why the schedule fires a few minutes past the hour.
A window, not a moving marker. The script asks "which of the last 24 hours have not gone out yet", instead of keeping a position that steps forward. A marker that steps forward walks past an event that arrived late, and it never comes back for it.
One query for the whole range. A run has a budget of twenty queries. Reading hour by hour would spend the budget on the window alone and leave nothing for anything else.
A failure loses nothing. An hour is written down only when a request ended exactly on its boundary — that is, when all of its rows have left. If the receiver is unreachable or answers with a refusal, the script stops, remembers only the hours that completed, and the next run picks the rest up. An hour that was cut in the middle is repeated in full, so make the receiver tolerate the same hour arriving twice — a spreadsheet script that replaces rows by hour, or an endpoint that ignores a repeat.
The row ceiling is noticed, not ignored. A single query is cut at user_scripts.sql_max_rows rows, and the engine mentions the cut only in the run console — the script receives a short list with nothing to mark it. So the script compares what came back against maxRowsPerRun itself, which is why that number has to match the setting: on a hit it says (ceiling hit), treats the boundary hour as unfinished, and leaves the rest of the window for the next run. If you see that line often, lower hoursBack or narrow the grouping.
What you will see in the console
A normal run:
Hours to export: 24 (of 24 in the window)
Rows read: 136
Sent 136 rows, receiver answered 200
Remembered hours: 24
The next run, an hour later, has one hour to do rather than twenty-four. And a run that could not reach the receiver:
Hours to export: 24 (of 24 in the window)
Rows read: 136
Send failed, stopping: host "script.google.com" not in allowlist
Hours confirmed sent: 0 of 24 - the rest stay for the next run
Remembered hours: 0
Nothing was remembered, so the next run starts over from the same place.
The ▶ Run button under the editor runs the code on live data right away — see Testing the script. Point url at your receiver and watch the console: the rows either arrive, or the console names the reason.