Three Ways to Monitor Prepaid API Balances (Excel, Python + Cron, Power Automate)

If you run a platform that depends on prepaid APIs, SMS gateways, identity verification, and airtime, you already know the failure mode. The wallet runs dry at 2am, verifications start failing, and nobody notices until a customer complains.

My manager asked for something simple: check the balances three times a day and write them down. That request turned into three working solutions of increasing automation and a pile of undocumented gotchas that cost me an afternoon.

This post covers all three, with the full code, so you can pick whichever fits your setup.

A note on placeholders

Anything in <ANGLE BRACKETS> is a value you replace with your own. I've used placeholders instead of the real names from my workplace:

Placeholder	Replace with
<FOLDER>	The folder holding your files
<WORKBOOK>	Your Excel file name
<ACCOUNT-1>, <ACCOUNT-2>	Your own sub-account names
<TIMEZONE>	Your time zone, e.g. Africa/Lagos
<UTC-OFFSET>	Your offset in hours, e.g. 1 for UTC+1
<KEY>, <SECRET-KEY>, <APP-ID>, <USERNAME>	Your API credentials

The three services I use as examples Termii, Dojah and Africa's Talking are public APIs common in African fintech. Swap in whichever providers you use. The pattern is identical for any service with a balance endpoint.

The three options

	Effort to set up	Who reads the balance	Runs unattended
1. Plain spreadsheet	5 minutes	You	No
2. Script + cron	1 hour	The script	Yes
3. Office Script + Power Automate	30 minutes	You	Half — rows appear, you type balances

Option 2 is the real answer. Option 3 is what you use if you can't run a server or if the balance genuinely can't be read by API. Option 1 is where everyone starts, and it's worth getting the columns right even if you automate later, because the automation writes into the same shape.

Option 1: Get the spreadsheet right first

Most people build this wrong, so it's worth spending a minute on.

One row per service per check

Not one column per service. It's more typing, but it's the only shape you can later sort, filter, chart, or pivot. Wide layouts die the moment you add a fourth service.

Date	Time	Service	Balance	Status	Note	Days left
2026-08-18	8:00am	Termii	75732.33	OK		6.3
2026-08-18	8:00am	Dojah	1550140.00	OK		22.4
2026-08-18	8:00am	Africa's Talking – <ACCOUNT-1>	93430.72	OK		11.1
2026-08-18	8:00am	Africa's Talking – <ACCOUNT-2>	100000.00	OK

Four rules that matter

One value per cell. I first saw this sheet with three balances crammed into a single cell as text. Excel then sees text, not money, and can't compare or total anything. Split them.

Separate rows for separate accounts. If one provider has two accounts, they are two services. Give them distinct names and stick to those names everywhere.

Type plain numbers. 75732.33, not ₦75,732.33. Apply the currency symbol through Home → Number Format → Currency. A typed symbol turns the cell into text.

Type times as text. 8:00am as text, not as a real Excel time. Excel silently converts real times into fractions of a day, and then your automation can't match its rows to yours. Format the column as Text before you start.

Don't use a fixed money threshold

This is the mistake worth avoiding. If one service holds ₦1.5M and another holds ₦75k, no single "low balance" figure works for both.

Use days of runway instead: how long the balance lasts at the current spending rate.

days left = current balance ÷ average daily spend

• Low — 3 days or less
• Critical — 1 day or less

One extra rule: a Friday evening balance has to survive until Monday morning. If topping up needs finance approval, treat 4 days as your Friday floor.

Never put keys in the sheet

Obvious, but it happens. No API keys, no passwords, no login details in cells. The sheet gets shared; the keys shouldn't travel with it.

Option 2: A script on a server (the one I'd recommend)

All three providers expose the balance over HTTP, so a small script can read it and log it without anyone remembering to.

What it does per run

1. Calls each provider's balance endpoint
2. Works out the spend rate and days of runway
3. Appends one row per service to a CSV
4. Sends a chat alert only when something is low, critical, or failed

Silence means everything is fine. That's the whole point an alert that fires every time is an alert people stop reading.

The endpoints

Termii

GET https://api.ng.termii.com/api/get-balance?api_key=<KEY>

Returns balance and currency as JSON. Note that SMS, voice and OTP all draw on the same wallet at different rates, so the naira figure is the only reliable number; unit counts are estimates.

Africa's Talking

GET https://api.africastalking.com/version1/user?username=<USERNAME>
Header: apiKey: <KEY>

Returns UserData.balance as a string like "NGN 1785.5000". You have to strip the currency prefix before treating it as a number.

Dojah

GET https://api.dojah.io/api/v1/balance
Headers: Authorization: <SECRET-KEY>
         AppId: <APP-ID>

Check this path against Dojah's current docs — it has moved before, and it may differ by account type. The script below handles this by searching the response for a balance field rather than assuming a fixed shape.

The script

Standard library only, so there's nothing to install beyond Python 3.

#!/usr/bin/env python3
"""
balance_check.py - read prepaid API balances, log them, alert when low.

Usage:
    ./balance_check.py            # fetch, log, alert if needed
    ./balance_check.py --dry-run  # fetch and print, write nothing
    ./balance_check.py --test     # check config and connectivity only
"""

import csv
import json
import os
import re
import sys
import urllib.error
import urllib.parse
import urllib.request
from datetime import datetime, timedelta, timezone
from pathlib import Path

BASE_DIR = Path(__file__).resolve().parent
ENV_FILE = BASE_DIR / ".env"
LOG_FILE = BASE_DIR / "balances.csv"

LOCAL_TZ = timezone(timedelta(hours=1))   # <UTC-OFFSET>
LOOKBACK_DAYS = 7
LOW_DAYS = 3.0
CRITICAL_DAYS = 1.0
HTTP_TIMEOUT = 25

CSV_HEADER = ["Date", "Time", "Service", "Balance", "Days left", "Status", "Note"]


def load_env(path):
    """Read a simple KEY=value file. Real environment variables win."""
    cfg = {}
    if path.exists():
        for line in path.read_text(encoding="utf-8").splitlines():
            line = line.strip()
            if not line or line.startswith("#") or "=" not in line:
                continue
            key, _, value = line.partition("=")
            cfg[key.strip()] = value.strip().strip('"').strip("'")
    cfg.update({k: v for k, v in os.environ.items()
                if k in cfg or k.startswith(("TERMII_", "DOJAH_", "AT_", "ALERT_"))})
    return cfg


def to_number(value):
    """Turn 'NGN 1,785.5000' or 42150 into a float."""
    if isinstance(value, (int, float)):
        return float(value)
    match = re.search(r"-?\d[\d,]*\.?\d*", str(value))
    if not match:
        raise ValueError(f"no number found in {value!r}")
    return float(match.group(0).replace(",", ""))


def find_balance(payload):
    """Walk a JSON response looking for a balance field.

    Keeps the script working when a provider changes its response shape.
    """
    preferred = ("wallet_balance", "balance", "available_balance", "amount")
    stack = [payload]
    while stack:
        node = stack.pop(0)
        if isinstance(node, dict):
            for key in preferred:
                if key in node:
                    try:
                        return to_number(node[key])
                    except ValueError:
                        pass
            stack.extend(node.values())
        elif isinstance(node, list):
            stack.extend(node)
    raise ValueError("could not find a balance field in the response")


def http_get_json(url, headers=None):
    request = urllib.request.Request(url, headers=headers or {}, method="GET")
    with urllib.request.urlopen(request, timeout=HTTP_TIMEOUT) as response:
        return json.loads(response.read().decode("utf-8", errors="replace"))


def require(cfg, *keys):
    missing = [k for k in keys if not cfg.get(k)]
    if missing:
        raise RuntimeError("missing in .env: " + ", ".join(missing))
    return [cfg[k] for k in keys]


# --- one fetcher per provider ------------------------------------------------

def fetch_termii(cfg):
    (api_key,) = require(cfg, "TERMII_API_KEY")
    base = cfg.get("TERMII_BASE_URL", "https://api.ng.termii.com")
    url = f"{base}/api/get-balance?" + urllib.parse.urlencode({"api_key": api_key})
    return find_balance(http_get_json(url, {"Accept": "application/json"}))


def fetch_dojah(cfg):
    secret_key, app_id = require(cfg, "DOJAH_SECRET_KEY", "DOJAH_APP_ID")
    url = cfg.get("DOJAH_BALANCE_URL", "https://api.dojah.io/api/v1/balance")
    headers = {"Authorization": secret_key, "AppId": app_id,
               "Accept": "application/json"}
    return find_balance(http_get_json(url, headers))


def fetch_africastalking(cfg):
    username, api_key = require(cfg, "AT_USERNAME", "AT_API_KEY")
    base = cfg.get("AT_BASE_URL", "https://api.africastalking.com")
    url = f"{base}/version1/user?" + urllib.parse.urlencode({"username": username})
    return find_balance(http_get_json(url, {"apiKey": api_key,
                                            "Accept": "application/json"}))


SERVICES = [
    ("Termii", fetch_termii),
    ("Dojah", fetch_dojah),
    ("Africa's Talking", fetch_africastalking),
]


# --- spend rate --------------------------------------------------------------

def read_history(service):
    """Return [(datetime, balance)] for one service, oldest first."""
    if not LOG_FILE.exists():
        return []
    rows = []
    with LOG_FILE.open(newline="", encoding="utf-8") as handle:
        for row in csv.DictReader(handle):
            if row.get("Service") != service or not row.get("Balance"):
                continue
            try:
                stamp = datetime.strptime(
                    f"{row['Date']} {row['Time']}", "%Y-%m-%d %H:%M"
                ).replace(tzinfo=LOCAL_TZ)
                rows.append((stamp, float(row["Balance"])))
            except (ValueError, KeyError):
                continue
    rows.sort(key=lambda item: item[0])
    return rows


def days_remaining(service, current_balance, now):
    """Days of balance left, judged on the last week.

    Only counts drops between consecutive readings, so a mid-week top-up
    does not make the spend rate look artificially small.
    """
    cutoff = now - timedelta(days=LOOKBACK_DAYS)
    points = [p for p in read_history(service) if p[0] >= cutoff]
    points.append((now, current_balance))
    if len(points) < 2:
        return None

    total_spent = 0.0
    for (_, earlier), (_, later) in zip(points, points[1:]):
        if later < earlier:
            total_spent += earlier - later

    span_days = (points[-1][0] - points[0][0]).total_seconds() / 86400
    if span_days < 0.2 or total_spent <= 0:
        return None

    return round(current_balance / (total_spent / span_days), 1)


def status_for(days_left):
    if days_left is None:
        return "OK"                 # not enough history to judge yet
    if days_left <= CRITICAL_DAYS:
        return "Critical"
    if days_left <= LOW_DAYS:
        return "Low"
    return "OK"


# --- output ------------------------------------------------------------------

def append_rows(rows):
    new_file = not LOG_FILE.exists()
    with LOG_FILE.open("a", newline="", encoding="utf-8") as handle:
        writer = csv.writer(handle)
        if new_file:
            writer.writerow(CSV_HEADER)
        writer.writerows(rows)


def send_alert(webhook_url, text):
    payload = json.dumps({"text": text}).encode("utf-8")
    request = urllib.request.Request(
        webhook_url, data=payload,
        headers={"Content-Type": "application/json"}, method="POST")
    with urllib.request.urlopen(request, timeout=HTTP_TIMEOUT) as response:
        return response.status


def describe_error(exc):
    if isinstance(exc, urllib.error.HTTPError):
        return f"HTTP {exc.code} from API"
    if isinstance(exc, urllib.error.URLError):
        return f"cannot reach API ({exc.reason})"
    return f"{type(exc).__name__}: {exc}"


def main(argv):
    dry_run = "--dry-run" in argv
    test_only = "--test" in argv
    cfg = load_env(ENV_FILE)

    now = datetime.now(LOCAL_TZ)
    date_text = now.strftime("%Y-%m-%d")
    time_text = now.strftime("%H:%M")

    rows, alerts, summary = [], [], []

    for service, fetch in SERVICES:
        try:
            balance = fetch(cfg)
        except Exception as exc:                      # noqa: BLE001
            note = describe_error(exc)
            rows.append([date_text, time_text, service, "", "", "CHECK FAILED", note])
            alerts.append(f"- {service}: CHECK FAILED - {note}")
            summary.append(f"{service}: failed ({note})")
            continue

        left = days_remaining(service, balance, now)
        state = status_for(left)
        rows.append([date_text, time_text, service, f"{balance:.2f}",
                     "" if left is None else f"{left}", state, ""])

        readable = f"{balance:,.2f}" + (f" ({left} days left)" if left else "")
        summary.append(f"{service}: {readable} - {state}")
        if state in ("Low", "Critical"):
            alerts.append(f"- {service}: {readable} - {state.upper()}")

    print(f"{date_text} {time_text}")
    for line in summary:
        print("  " + line)

    if test_only:
        return 0
    if dry_run:
        print("\n[dry run] nothing written, nobody alerted")
        return 0

    append_rows(rows)
    print(f"\nWrote {len(rows)} rows to {LOG_FILE}")

    webhook = cfg.get("ALERT_WEBHOOK_URL")
    if alerts and webhook:
        message = (f"**Balance check {date_text} {time_text} - attention needed**\n"
                   + "\n".join(alerts))
        try:
            send_alert(webhook, message)
            print("Alert sent")
        except Exception as exc:                      # noqa: BLE001
            print(f"Alert failed: {describe_error(exc)}", file=sys.stderr)
    elif alerts:
        print("Alerts raised but ALERT_WEBHOOK_URL is not set", file=sys.stderr)

    return 2 if alerts else 0


if __name__ == "__main__":
    sys.exit(main(sys.argv[1:]))

The config file

.env, in the same folder:

# Termii
TERMII_API_KEY=

# Dojah
DOJAH_SECRET_KEY=
DOJAH_APP_ID=
DOJAH_BALANCE_URL=https://api.dojah.io/api/v1/balance

# Africa's Talking
AT_USERNAME=
AT_API_KEY=

# Chat webhook - leave blank to disable alerts
ALERT_WEBHOOK_URL=

Setting it up

mkdir -p /opt/<FOLDER>/balance-monitor
cd /opt/<FOLDER>/balance-monitor
# copy balance_check.py and .env here
chmod 600 .env
chmod +x balance_check.py
./balance_check.py --test

--test reads the balances and prints them without writing anything. Fix whatever it complains about before going further.

The cron entry

CRON_TZ=<TIMEZONE>
0 8,16,20 * * * cd /opt/<FOLDER>/balance-monitor && ./balance_check.py >> monitor.log 2>&1

The CRON_TZ line is the important part. Most cloud servers run on UTC. If your local time is UTC+1 and you skip it, every check fires an hour late. This is the single most common way this kind of job goes quietly wrong.

Getting the CSV into Excel

balances.csv opens directly. Better, link it so it refreshes itself: Data → From Text/CSV, point at the file, and the sheet updates without anyone emailing anything.

Option 3: Office Script + Power Automate

Use this when you can't run a server or when a balance genuinely has to be read by a human from a dashboard.

What this option can and cannot do

Let's kill the obvious idea first, because I wasted time on it: > Office Scripts cannot reliably fetch external APIs. A script can call an > outside URL when you click Run in the browser, but that same call fails > when Power Automate runs it on a schedule. Microsoft blocks external > requests in the scheduled context.

So Office Scripts can't be your fetcher. What it can do — well — is everything that happens inside the workbook: creating the day's rows, calculating, colouring, and summarising.

That gives you a half-automated setup: the rows appear by themselves each morning with Date, Time and Service filled in, and you only type the balance.

There is also a paid route

Power Automate's HTTP action can call the APIs directly; no server needed.
It's a premium action, so check your licence first, add it and look for the diamond badge. The Excel Online (Business) → Run script action used below is standard and free.

The script

Two sheets: Sheet1 for the log, Settings for per-service thresholds. The script creates Settings for you on first run.

/**
 * Creates the day's monitoring rows, fills Status by formula, works out
 * days of runway, and colours everything.
 *
 * You only type the Balance.
 *
 * Safe to run twice: if today's rows exist it skips creating them.
 *
 * daysAhead = 0 -> today's rows   |   daysAhead = 1 -> tomorrow's rows
 */

const SERVICES: string[] = [
  "Termii",
  "Dojah",
  "Africa's Talking – <ACCOUNT-1>",
  "Africa's Talking – <ACCOUNT-2>",
];

const SLOTS: { label: string; hour: number }[] = [
  { label: "8:00am", hour: 8 },
  { label: "4:00pm", hour: 16 },
  { label: "8:00pm", hour: 20 },
];

const DEFAULT_LIMITS: { service: string; low: number; critical: number }[] = [
  { service: "Termii", low: 30000, critical: 10000 },
  { service: "Dojah", low: 300000, critical: 100000 },
  { service: "Africa's Talking – <ACCOUNT-1>", low: 30000, critical: 10000 },
  { service: "Africa's Talking – <ACCOUNT-2>", low: 30000, critical: 10000 },
];

const DATA_SHEET = "Sheet1";
const SETTINGS_SHEET = "Settings";
const LOOKBACK_DAYS = 7;
const UTC_OFFSET_HOURS = 1;   // <UTC-OFFSET>

const HEADERS = ["Date", "Time", "Service", "Balance", "Status", "Note", "Days left"];
const COL = { date: 0, time: 1, service: 2, balance: 3, status: 4, note: 5, days: 6 };

const DATE_FORMAT = "ddd, d mmm yyyy";
const MONEY_FORMAT = "#,##0.00";
const DAYS_FORMAT = "0.0";

const COLOUR_FILL_ME = "#FFF9E6";
const COLOUR_OK = "#E6F4EA";
const COLOUR_OK_TEXT = "#1E6C33";
const COLOUR_LOW = "#FFF3CD";
const COLOUR_LOW_TEXT = "#8A5A00";
const COLOUR_CRIT = "#F8D7DA";
const COLOUR_CRIT_TEXT = "#A31621";
const COLOUR_HEADER = "#003B66";
const COLOUR_DAY_LINE = "#BFBFBF";

const MS_PER_DAY = 24 * 60 * 60 * 1000;


function main(workbook: ExcelScript.Workbook, daysAhead: number = 0): void {
  const sheet = workbook.getWorksheet(DATA_SHEET) ?? workbook.getWorksheets()[0];
  if (!sheet) {
    console.log("No worksheet found.");
    return;
  }

  ensureSettingsSheet(workbook);
  ensureHeaders(sheet);

  const today = localDate(daysAhead);
  const added = addDayIfMissing(sheet, today);

  refreshStatusFormulas(sheet);
  refreshDaysLeft(sheet);
  applyLook(sheet);

  console.log(added > 0
    ? `Added ${added} rows for ${today.text}.`
    : `${today.text} already present. Refreshed only.`);
}


// --- headings and settings ---------------------------------------------------

function ensureHeaders(sheet: ExcelScript.Worksheet): void {
  const table = sheet.getTables()[0];

  if (table) {
    const existing = table.getHeaderRowRange().getValues()[0]
      .map(value => String(value ?? "").trim())
      .filter(value => value !== "");
    for (let i = existing.length; i < HEADERS.length; i++) {
      table.addColumn(-1, undefined, HEADERS[i]);
    }
    return;
  }

  const used = sheet.getUsedRange();
  const empty = !used || (used.getRowCount() === 1 && used.getColumnCount() === 1
    && String(used.getValues()[0][0]).trim() === "");

  if (empty) {
    sheet.getRangeByIndexes(0, 0, 1, HEADERS.length).setValues([HEADERS]);
  } else {
    const row = sheet.getRangeByIndexes(0, 0, 1, HEADERS.length);
    const current = row.getValues()[0];
    const patched: (string | number)[] = [];
    let changed = false;
    for (let i = 0; i < HEADERS.length; i++) {
      const value = String(current[i] ?? "").trim();
      if (value === "") { patched.push(HEADERS[i]); changed = true; }
      else patched.push(value);
    }
    if (changed) row.setValues([patched]);
  }

  const header = sheet.getRangeByIndexes(0, 0, 1, HEADERS.length);
  header.getFormat().getFont().setBold(true);
  header.getFormat().getFont().setColor("#FFFFFF");
  header.getFormat().getFill().setColor(COLOUR_HEADER);
  sheet.getFreezePanes().freezeRows(1);
}

function ensureSettingsSheet(workbook: ExcelScript.Workbook): void {
  if (workbook.getWorksheet(SETTINGS_SHEET)) return;

  const settings = workbook.addWorksheet(SETTINGS_SHEET);

  const rows: (string | number)[][] = [["Service", "Low below", "Critical below"]];
  for (const limit of DEFAULT_LIMITS) {
    rows.push([limit.service, limit.low, limit.critical]);
  }
  settings.getRangeByIndexes(0, 0, rows.length, 3).setValues(rows);

  const header = settings.getRangeByIndexes(0, 0, 1, 3);
  header.getFormat().getFont().setBold(true);
  header.getFormat().getFont().setColor("#FFFFFF");
  header.getFormat().getFill().setColor(COLOUR_HEADER);

  const moneyCodes: string[][] = [];
  for (let i = 1; i < rows.length; i++) moneyCodes.push([MONEY_FORMAT, MONEY_FORMAT]);
  settings.getRangeByIndexes(1, 1, rows.length - 1, 2).setNumberFormat(moneyCodes);

  settings.getRange("A:C").getFormat().autofitColumns();
}


// --- the day's rows ----------------------------------------------------------

function addDayIfMissing(
  sheet: ExcelScript.Worksheet,
  today: { serial: number; text: string }
): number {
  for (const row of readRows(sheet)) {
    if (row.serial === today.serial) return 0;
  }

  const values: (string | number)[][] = [];
  for (const slot of SLOTS) {
    for (const service of SERVICES) {
      values.push([today.serial, slot.label, service, "", "", "", ""]);
    }
  }

  const table = sheet.getTables()[0];
  let firstRow: number;
  if (table) {
    table.addRows(-1, values);
    const body = table.getRangeBetweenHeaderAndTotal();
    firstRow = body.getRowIndex() + body.getRowCount() - values.length;
  } else {
    const used = sheet.getUsedRange();
    firstRow = used ? used.getRowIndex() + used.getRowCount() : 1;
    sheet.getRangeByIndexes(firstRow, 0, values.length, HEADERS.length)
      .setValues(values);
  }

  if (firstRow > 1) {
    sheet.getRangeByIndexes(firstRow, 0, 1, HEADERS.length)
      .getFormat()
      .getRangeBorder(ExcelScript.BorderIndex.edgeTop)
      .setColor(COLOUR_DAY_LINE);
  }

  return values.length;
}


// --- Status by formula -------------------------------------------------------

/**
 * Status is a formula, not a written value, so it appears the instant a
 * balance is typed. No need to re-run the script.
 */
function refreshStatusFormulas(sheet: ExcelScript.Worksheet): void {
  const bounds = dataBounds(sheet);
  if (!bounds) return;

  const formulas: string[][] = [];
  for (let i = 0; i < bounds.count; i++) {
    const r = bounds.firstRow + i + 1;
    formulas.push([
      `=IF($D${r}="","",` +
      `IFNA(IF($D${r}<=XLOOKUP($C${r},${SETTINGS_SHEET}!$A$2:$A$100,${SETTINGS_SHEET}!$C$2:$C$100),"Critical",` +
      `IF($D${r}<=XLOOKUP($C${r},${SETTINGS_SHEET}!$A$2:$A$100,${SETTINGS_SHEET}!$B$2:$B$100),"Low","OK")),` +
      `"No limit set"))`
    ]);
  }

  sheet.getRangeByIndexes(bounds.firstRow, COL.status, bounds.count, 1)
    .setFormulas(formulas);
}


// --- days of runway ----------------------------------------------------------

function refreshDaysLeft(sheet: ExcelScript.Worksheet): void {
  const bounds = dataBounds(sheet);
  if (!bounds) return;

  const rows = readRows(sheet);
  const output: (string | number)[][] = [];

  for (const row of rows) {
    if (row.balance === null || row.stamp === null) { output.push([""]); continue; }

    const history = rows
      .filter(other =>
        other.service === row.service &&
        other.balance !== null &&
        other.stamp !== null &&
        other.stamp <= (row.stamp as number) &&
        other.stamp >= (row.stamp as number) - LOOKBACK_DAYS * MS_PER_DAY)
      .sort((a, b) => (a.stamp as number) - (b.stamp as number));

    if (history.length < 2) { output.push([""]); continue; }

    let spent = 0;
    for (let i = 1; i < history.length; i++) {
      const before = history[i - 1].balance as number;
      const after = history[i].balance as number;
      if (after < before) spent += before - after;
    }

    const spanDays =
      ((history[history.length - 1].stamp as number) -
       (history[0].stamp as number)) / MS_PER_DAY;

    if (spanDays < 0.2 || spent <= 0) { output.push([""]); continue; }

    output.push([Math.round((row.balance / (spent / spanDays)) * 10) / 10]);
  }

  sheet.getRangeByIndexes(bounds.firstRow, COL.days, output.length, 1)
    .setValues(output);
}


// --- colours -----------------------------------------------------------------

function applyLook(sheet: ExcelScript.Worksheet): void {
  const bounds = dataBounds(sheet);
  if (!bounds) return;

  setColumnFormat(sheet, bounds, COL.date, DATE_FORMAT);
  setColumnFormat(sheet, bounds, COL.balance, MONEY_FORMAT);
  setColumnFormat(sheet, bounds, COL.days, DAYS_FORMAT);

  const balances = sheet.getRangeByIndexes(bounds.firstRow, COL.balance, bounds.count, 1);
  balances.clearAllConditionalFormats();
  const blank = balances
    .addConditionalFormat(ExcelScript.ConditionalFormatType.presetCriteria)
    .getPreset();
  blank.getFormat().getFill().setColor(COLOUR_FILL_ME);
  blank.setRule({ criterion: ExcelScript.ConditionalFormatPresetCriterion.blanks });

  const status = sheet.getRangeByIndexes(bounds.firstRow, COL.status, bounds.count, 1);
  status.clearAllConditionalFormats();
  addWordRule(status, "Critical", COLOUR_CRIT, COLOUR_CRIT_TEXT);
  addWordRule(status, "Low", COLOUR_LOW, COLOUR_LOW_TEXT);
  addWordRule(status, "OK", COLOUR_OK, COLOUR_OK_TEXT);

  const all = sheet.getRangeByIndexes(0, 0, bounds.firstRow + bounds.count, HEADERS.length);
  all.getFormat().setWrapText(false);
  all.getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.top);
  all.getFormat().autofitColumns();
}

function addWordRule(
  range: ExcelScript.Range, word: string, fill: string, text: string
): void {
  const rule = range
    .addConditionalFormat(ExcelScript.ConditionalFormatType.containsText)
    .getTextComparison();
  rule.getFormat().getFill().setColor(fill);
  rule.getFormat().getFont().setColor(text);
  rule.getFormat().getFont().setBold(true);
  rule.setRule({
    text: word,
    operator: ExcelScript.ConditionalTextOperator.contains,
  });
}

/** Number formats want a grid of codes, one per cell, not a single string. */
function setColumnFormat(
  sheet: ExcelScript.Worksheet,
  bounds: { firstRow: number; count: number },
  column: number,
  code: string
): void {
  const codes: string[][] = [];
  for (let i = 0; i < bounds.count; i++) codes.push([code]);
  sheet.getRangeByIndexes(bounds.firstRow, column, bounds.count, 1)
    .setNumberFormat(codes);
}


// --- helpers -----------------------------------------------------------------

interface LoggedRow {
  serial: number | null;
  stamp: number | null;
  service: string;
  balance: number | null;
}

function dataBounds(
  sheet: ExcelScript.Worksheet
): { firstRow: number; count: number } | null {
  const table = sheet.getTables()[0];
  if (table) {
    const body = table.getRangeBetweenHeaderAndTotal();
    const count = body.getRowCount();
    return count > 0 ? { firstRow: body.getRowIndex(), count } : null;
  }
  const used = sheet.getUsedRange();
  if (!used || used.getRowCount() < 2) return null;
  return { firstRow: 1, count: used.getRowCount() - 1 };
}

function readRows(sheet: ExcelScript.Worksheet): LoggedRow[] {
  const bounds = dataBounds(sheet);
  if (!bounds) return [];

  const values = sheet
    .getRangeByIndexes(bounds.firstRow, 0, bounds.count, HEADERS.length)
    .getValues();

  return values.map(row => {
    const serial = readSerial(row[COL.date]);
    const hour = slotHour(String(row[COL.time] ?? ""));
    const cell = row[COL.balance];
    const balance = typeof cell === "number" ? cell : parseMoney(String(cell ?? ""));

    return {
      serial,
      stamp: serial === null ? null : (serial * MS_PER_DAY) + (hour * 3600000),
      service: String(row[COL.service] ?? "").trim(),
      balance,
    };
  });
}

function readSerial(cell: string | number | boolean): number | null {
  if (typeof cell === "number") return Math.floor(cell);
  const text = String(cell ?? "").trim();
  if (text === "") return null;
  const parsed = new Date(text);
  if (isNaN(parsed.getTime())) return null;
  return toExcelSerial(parsed.getFullYear(), parsed.getMonth() + 1, parsed.getDate());
}

function parseMoney(text: string): number | null {
  const cleaned = text.replace(/[^0-9.\-]/g, "");
  if (cleaned === "" || cleaned === "-" || cleaned === ".") return null;
  const value = Number(cleaned);
  return isNaN(value) ? null : value;
}

function slotHour(label: string): number {
  const wanted = label.trim().toLowerCase().replace(/\s+/g, "");
  for (const slot of SLOTS) {
    if (slot.label.toLowerCase().replace(/\s+/g, "") === wanted) return slot.hour;
  }
  return 12;
}

/** Excel counts days from 30 December 1899. */
function toExcelSerial(year: number, month: number, day: number): number {
  return Math.round(
    (Date.UTC(year, month - 1, day) - Date.UTC(1899, 11, 30)) / MS_PER_DAY);
}

/** Today in local time. Scheduled runs happen in UTC, so the offset is added. */
function localDate(daysAhead: number): { serial: number; text: string } {
  const now = new Date();
  const local = new Date(
    now.getTime() + UTC_OFFSET_HOURS * 3600000 + daysAhead * MS_PER_DAY);
  const year = local.getUTCFullYear();
  const month = local.getUTCMonth() + 1;
  const day = local.getUTCDate();
  const pad = (n: number) => (n < 10 ? `0${n}` : `${n}`);
  return {
    serial: toExcelSerial(year, month, day),
    text: `${year}-${pad(month)}-${pad(day)}`,
  };
}

Installing the script

1. Open <WORKBOOK>.xlsx in Excel on the web
2. Automate → New Script
3. Paste the whole thing in
4. Name it, then Save
5. Click Run once and confirm it works

Do steps 4 and 5 in that order and let Excel handle the saving this matters, see the gotchas below.

Scheduling it At make.powerautomate.com:

1. Create → Scheduled cloud flow
2. Recurrence: every 1 day, time zone your own, at hour 7
3. Add action: Excel Online (Business) → Run script
4. Pick Location, Document Library, File, and the Script from the dropdown
5. daysAhead → 0
6. Test → Manually, then Save

Running at 07:00 means the rows are waiting before the 08:00 check. If you'd rather build tomorrow's rows the night before, schedule 20:30 and set daysAhead to 1.

The gotchas, and what each error means

This is the part I wish someone had written down.

Only arrow functions may be used in array method callbacks

The Office Scripts linter refuses function references in callbacks even built-in ones.

// rejected
const names = range.getValues()[0].map(String);

// accepted
const names = range.getValues()[0].map(value => String(value));

(intermediate value).setFormat is not a function

Conditional formats don't accept a format object. You walk into them instead.

// wrong
cf.getCellValue().setFormat({ fill: { color: "#FFF9E6" } });

// right
cf.getCellValue().getFormat().getFill().setColor("#FFF9E6");

Same for fonts: .getFormat().getFont().setColor(...).

Number formats need a grid, not a string

setNumberFormat expects one code per cell, sized to the range:

// unreliable
range.setNumberFormat("#,##0.00");

// correct - a 2D array matching the range
const codes: string[][] = [];
for (let i = 0; i < rowCount; i++) codes.push(["#,##0.00"]);
range.setNumberFormat(codes);

Detecting blank cells

Use the built-in criterion, not a ="" formula rule — the formula version misjudges cells holding empty strings.

const cf = range
  .addConditionalFormat(ExcelScript.ConditionalFormatType.presetCriteria)
  .getPreset();
cf.getFormat().getFill().setColor("#FFF9E6");
cf.setRule({ criterion: ExcelScript.ConditionalFormatPresetCriterion.blanks });

Power Automate's Script dropdown says "No items"

This one cost me the most time. Power Automate only reads scripts from one folder:

OneDrive → My files → Documents → Office Scripts

I had saved my .osts file into a working folder alongside the workbook. The dropdown stayed empty with no explanation.

The fix is to let Excel save it for you — Automate → New Script → Save puts it in the right place automatically. If you already have a stray .osts elsewhere, move it into Documents/Office Scripts and delete the copy, so you aren't maintaining two versions.

Other reasons the dropdown stays empty:

• The script belongs to a different account than the flow's connection
• The script was never saved, only left open in the editor
• The dropdown is cached — delete the Run script action, save the flow, and add it back

Time zones will get you

Three separate places, all defaulting to UTC:

Where	What to set
cron	CRON_TZ=<TIMEZONE> above the schedule line
Power Automate Recurrence	The Time zone field
Office Script	Add your offset to new Date()

An hour's drift sounds harmless until your "8am" check lands at 9am and the overnight drain is invisible.

Sharing the .osts file does nothing

I shared the script file, thinking the flow needed access. It doesn't; the flow runs as the connected account. Sharing a script file only exposes your code.
Undo it.

Which one should you use?

Run a server? Option 2. The script is a hundred-odd lines and has no dependencies and removes the human entirely. A check that only happens when someone remembers isn't early warning.

No server, or a balance only visible on a dashboard? Option 3. The rows build themselves, and you fill in a number three times a day.

Premium Power Automate licence? Option 3 plus HTTP actions gets you fully automated with no server at all.

Whichever you pick, get the sheet shape right first, and drive the alerts off days of runway rather than a fixed amount of money. A number that means "comfortable" for one provider means "we're down tomorrow" for another.

Two last things

Don't keep the log in personal cloud storage if a manager depends on it. Put it in a shared team location. Files in a personal drive vanish when accounts get disabled.

Keep credentials out of the folder holding files you actively share. One mis-click on a folder-level share is all it takes.