Daily ERPNext business health snapshot in Google Sheets

Every weekday at 6am in my business timezone, take a snapshot of my ERPNext business health and append it as a single dated row to a KPI tracker tab in a Google Sheet. Use a cron trigger, Monday through Friday. This is a pure snapshot job: no notifications, no commentary, no ranking or interpretation of the numbers.

The row always has these eight columns, in exactly this order: Date, Open Sales Orders, Open SO Value, Overdue Invoices, Overdue Outstanding, POs Awaiting Receipt, PO Value Awaiting Receipt, Items Below Reorder Level.

Open sales orders: use ERPNext Get Document Count (frappe.client.get_count) on the Sales Order DocType filtered to docstatus = 1 with status not in Closed, Completed, or Cancelled. Then use List Documents on Sales Order with those same filters, requesting base_grand_total, and sum it for the value column. Always use the company-currency base amounts so a multi-currency order does not distort the trend line.

Overdue sales invoices: filter Sales Invoice to docstatus = 1, outstanding_amount > 0, and due_date earlier than today. Count them with Get Document Count and sum outstanding_amount, not grand_total, via List Documents using the identical filters so the count and the value always agree.

Purchase orders submitted but not yet received: filter Purchase Order to docstatus = 1, per_received < 100, and status not in Closed, Completed, or Cancelled. Count with Get Document Count and sum base_grand_total with List Documents on the same filters.

Items below reorder level: read the item reorder rules with List Documents against the Item Reorder child table of Item (pass the parent DocType when the API requires it), pulling item_code, warehouse, and warehouse_reorder_level for every item that has a reorder level configured. For each one, call Get Stock Balance (erpnext.stock.utils.get_stock_balance) for that item and warehouse, and count how many have a balance strictly below their reorder level. Count each item and warehouse pair once. If reorder rules cannot be read as a child table on this instance, fall back to List Documents on Item and use the reorder level recorded there. Cap the check at the first 500 reorder rules so the run stays fast.

Important pagination detail: ERPNext list calls return only 20 rows by default. When summing values, page through with limit_start and limit_page_length, or pass limit_page_length = 0, so the totals cover every matching document rather than just the first page. A silently truncated sum is the main way this workflow can go wrong.

Write the row with Google Sheets Append Values into the KPI tracker tab, using valueInputOption USER_ENTERED so the date lands as a real date and the figures land as real numbers that charts and pivot tables can read directly. Format the date as YYYY-MM-DD. Write 0 rather than leaving a blank when a figure legitimately comes back empty, so the series has no holes. If the tab is empty on the first run, write the header row before the first data row.

Before appending, read the existing date column with Get Values and skip the append if today's date is already present, so a re-run never double-writes a day. If any ERPNext call fails, do not append a partial row: fail the run instead, so each day is either fully recorded or not recorded at all.

The column set is fixed. Once the sheet has history, columns must never be reordered or renamed, because everything built on top of the sheet reads by position. If I ask for more figures later, add them as new columns on the right and leave the existing ones exactly where they are.