Turn a Google Sheet Into an iPhone and Apple Watch Widget

#n8n #googlesheets #iphone #applewatch

A spreadsheet is where most of us already track the small numbers of life: the home budget, the side-project revenue, the litres of fuel, the kilometres run. The annoying part is that the number only exists when you open the sheet. You can't glance at it.

This post closes that gap. With two pieces you get any Google Sheet on your iPhone home screen and Apple Watch face:

A small n8n workflow that reads your sheet, keeps only the columns you care about, computes the aggregations you ask for (total, average, per-owner breakdown…), and exposes it as a JSON webhook.
The API Widgets iOS app, which reads that webhook and renders it as a widget.

We'll build a shared household budget widget as the worked example - a donut of who's saving how much, next to a table of where the money went - but nothing in the workflow is budget-specific. Swap the sheet and the config and you have a sales dashboard, a fitness tracker, or a fuel log.

How it works

Download the workflow and import it into your n8n instance.

It's only five nodes:

Webhook - exposes the endpoint GET /webhook/sheets.
Configuration - a Code node that returns which sheet to read and what to compute.
Get row(s) in sheet - pulls every row from the Google Sheet.
Spreadsheet to JSON - keeps the configured columns and runs the aggregations.
Respond to Webhook - returns the result as JSON.

There is no caching and no database. Every call to the webhook reads the sheet live and returns the current numbers. That keeps the workflow trivial to set up - nothing to provision, no stale data to reason about. If you ever put this in front of heavy traffic, that's the point where you'd add a cache.

Step 1: the Google Sheet

Make a sheet with a header row and one row per record. For the shared-budget example, the columns are category, budget, spent, left, and owner:

In n8n, create a Google Sheets credential (OAuth2) and give it access to this sheet. The Get row(s) in sheet node uses that credential; the sheet itself is identified by its document ID - the long string in the sheet URL between /d/ and /edit. That ID isn't typed into the node directly; it comes from the Configuration node (next step), so pointing the workflow at a different sheet is a one-line change.

Step 2: where you configure it

This is the part that replaces the database in fancier setups: the Configuration node. It's a plain Code node that returns three things - the sheet to read, the columns to keep, and the aggregations to compute. Open it and edit the values:

return {
  source: "YOUR_GOOGLE_SHEET_ID",
  columns: [
    { "position": 0, "header": "category", "type": "text" },
    { "position": 1, "header": "budget",   "type": "number" },
    { "position": 2, "header": "spent",    "type": "number" },
    { "position": 3, "header": "left",     "type": "number" },
    { "position": 4, "header": "owner",    "type": "text" }
  ],
  aggregations: [
    { "name": "savings",          "group_by": "owner", "field": "left",  "agg": "sum" },
    { "name": "responsibilities", "group_by": "owner", "field": "owner", "agg": "count" }
  ]
};

source - paste your Google Sheet document ID here.
columns - the headers to keep, in order. header must match the column name in the sheet row for row.
aggregations - the summaries to compute. Each one supports sum, avg, min, max, and count. Add group_by to get a value per distinct group instead of one overall number, and name is the key it shows up under in the response.

The example asks for two numbers per person: savings (the sum of each owner's left column) and responsibilities (how many budget categories each owner is on the hook for).

Step 3: the transform

The Spreadsheet to JSON Code node reads the configuration, picks the requested columns out of every row, and runs the aggregations:

const columns = $('Configuration').first().json.columns;
const aggregations = $('Configuration').first().json.aggregations;

const items = $input.all().map((item) => {
  const row = item.json;
  const out = {};
  for (const col of columns) {
    out[col.header] = row[col.header];
  }
  return out;
});

const aggregators = {
  sum: (vals) => vals.reduce((a, b) => a + Number(b || 0), 0),
  avg: (vals) => (vals.length ? vals.reduce((a, b) => a + Number(b || 0), 0) / vals.length : 0),
  min: (vals) => (vals.length ? Math.min(...vals.map(Number)) : null),
  max: (vals) => (vals.length ? Math.max(...vals.map(Number)) : null),
  count: (vals) => vals.length,
};

const aggResult = {};
for (const agg of aggregations) {
  const fn = aggregators[agg.agg];
  const outKey = agg.name || agg.field;

  if (agg.group_by) {
    const grouped = {};
    for (const item of items) {
      const key = String(item[agg.group_by]);
      (grouped[key] ||= []).push(item[agg.field]);
    }
    const groupOut = {};
    for (const [key, vals] of Object.entries(grouped)) {
      groupOut[key] = fn(vals);
    }
    aggResult[outKey] = groupOut;
  } else {
    aggResult[outKey] = fn(items.map((i) => i[agg.field]));
  }
}

return [{ json: { items, aggregations: aggResult } }];

For the sheet above, the response looks like this:

{
  "items": [
    { "category": "Groceries",     "budget": "600", "spent": "412.5",  "left": "187.5", "owner": "Ann" },
    { "category": "Restaurants",   "budget": "200", "spent": "250",    "left": "-50",   "owner": "Ann" },
    { "category": "Transport",     "budget": "150", "spent": "92.1",   "left": "57.9",  "owner": "Robert" },
    { "category": "Utilities",     "budget": "300", "spent": "287.4",  "left": "12.6",  "owner": "Robert" },
    { "category": "Entertainment", "budget": "150", "spent": "64",     "left": "86",    "owner": "Ann" },
    { "category": "Shopping",      "budget": "200", "spent": "143.8",  "left": "56.2",  "owner": "Ann" },
    { "category": "Health",        "budget": "100", "spent": "45",     "left": "55",    "owner": "Ann" },
    { "category": "Subscriptions", "budget": "80",  "spent": "79.99",  "left": "0.01",  "owner": "Robert" },
    { "category": "Other",         "budget": "300", "spent": "156.7",  "left": "143.3", "owner": "Ann" }
  ],
  "aggregations": {
    "savings": { "Ann": 478, "Robert": 70.51 },
    "responsibilities": { "Ann": 6, "Robert": 3 }
  }
}

items is the raw table; aggregations is the computed summary. The widget can show either - the per-owner savings as a chart, or the full items table as a list. We'll use both.

Step 4: wire it up to API Widgets

Install API Widgets on your iPhone.

In the Source tab, paste the webhook URL:

https://n8n.example.com/webhook/sheets

Then switch to the Design tab. For this budget we use the Split widget type - two panes side by side, bound to two parts of the JSON:

Left column → Pie, with the Data Source expression ${api['aggregations']['savings']} - the donut of savings per owner.
Right column → Table, with the Source expression ${api['items']} - the list of categories and what was spent.

The expression language reaches straight into the JSON the workflow returns, so anything you put under items or aggregations is one expression away from being on screen.

Final result

Tap the preview (the eye icon) and you can see the finished widget right in the app before placing it - the savings donut for Ann and Robert next to the spending table:

Add it to your home screen as usual, and the same layout works on the Apple Watch face (the watch widget requires the pro version of API Widgets).

The sheet stays the single source of truth - edit a row, and the next time the widget refreshes, your wrist catches up. Swap the sheet ID and the Configuration node, and the same workflow serves any number you keep in a spreadsheet.

Originally published at digit11.com.