Shipped · in daily use

IT Stock Tracker

A consumables stock ledger that bridges the corporate divide. By connecting locked-down Microsoft 365 services (Active Directory user sync and Outlook email reminders via Power Automate) with a highly flexible n8n automation engine running externally, the system establishes a secure, reliable data bridge that keeps inventory accurate, audit-logged, and completely automated.

Google SheetsApps Scriptn8nPower AutomateInternal tool

Request a live demo →← Back to work

internal tool · it stock tracker

The challenge

The stock lived in a system nobody could update fast enough.

In a large company, IT department consumables (cables, spare parts, and small hardware) fell outside the main warehouse system. Updating anything there meant going through a long request chain, days for a change that should take minutes. IT tickets move fast; the stock records never kept up.

The solution

The M365 & n8n Bridge: Connecting Corporate Infrastructure Securely.

In an enterprise environment, Active Directory and corporate Outlook are heavily restricted, preventing direct access from external tools. Rather than attempting a complex, high-friction direct API integration, I built a reliable hybrid bridge. n8n acts as the central execution engine (processing and validating transactions, and managing the database ledger), while Power Automate handles the Microsoft-native actions (pulling employees from Active Directory and triggering nudge emails via Outlook). Communicating strictly via structured webhook contracts, this cooperation creates a reliable, zero-maintenance data pipeline that respects corporate IT boundaries.

The impact

Audits stopped being a guessing game, and paperwork stopped getting forgotten.

Because every change is logged automatically instead of relying on someone remembering to update a cell, physical audits now reconcile against a real transaction trail instead of a static number. A mismatch gets flagged as a variance row, not silently overwritten, so follow-up on a stock issue happens the same day instead of waiting for the next manual recount. And because a small poller now watches every item handed out, the internal paperwork it requires no longer depends on someone remembering to file it.

How it runs

One menu, one gateway, one ledger.

Open a dialog

A user picks an action from the sheet's custom menu (stock in, stock out, new item, audit) never types into a cell.

Submit

The dialog posts a JSON payload to n8n, tagged with which action it is and which location it applies to.

Validate

n8n re-checks everything the dialog already checked, the secret, the SKU, the quantity, because a payload could always be sent straight to the webhook, bypassing the dialog entirely.

Journal

The transaction is appended to the ledger. Stock is never edited directly, it's recomputed as the sum of everything AKTIF for that item at that location.

Log, notify, remind

The change lands in the change log; if it was a stock-out, a separate poller later confirms the required paperwork followed, and reminds the recipient automatically if it hasn't.

What it does

The moving parts.

One data gateway, deliberately split three waysEvery write still goes through n8n and nowhere else, but the core ledger, the employee-directory sync, and the paperwork-reminder poller each live in their own independent workflow instead of one growing monolith. A bug in one can never take the others down with it.
Stock is never typed, only computedThere's no cell anyone edits directly. Stock is always the live sum of every AKTIF transaction for that item at that location, so the number on screen and the ledger behind it can never drift apart.
Per-location stock, not one poolThe same item can sit at zero in one warehouse and dozens in another. A location only gets a stock row the first time something actually moves through it there. The sheet never implies availability that was never true.
Audits don't overwrite, and unexplained gaps get a paper trailA physical count doesn't get pasted over the system number; instead, it creates a PENYESUAIAN (adjustment) transaction sized to the exact variance, tagged back to the audit. Anyone can later attach an explanation to that variance, which appends to the same row instead of replacing it, the "why" behind a shrinkage stays linked to the number, not lost in a side conversation.
Over-withdrawal is flagged, not blockedIf someone takes out more than the system thinks is on the shelf, it's still accepted and logged, just flagged as an anomaly for the next audit to resolve. Blocking it would just teach people to stop using the tool the moment reality disagrees with the sheet.
Directory-Fed Recipient Picker (M365 Integration)Who received an item must be a verified employee to prevent inventory leakage. A scheduled Power Automate job queries the corporate Active Directory and pushes the active user directory to n8n, which keeps the sheet's searchable recipient list synchronized. The ledger stores verified, official email addresses rather than free-typed text.
Automated Outlook Nudges (n8n + Outlook)When a stock-out occurs, the system expects corresponding internal paperwork. A scheduled poller in n8n monitors pending entries and requests Power Automate to automatically send a personalized reminder email to the recipient via corporate Outlook. The bridge automates the chase, logging the reminder history.
Every edit and deletion is loggedEditing a master item, or clearing out a location's remaining stock, is never invisible: a separate change log records who changed what and when, and clearing stock writes a compensating adjustment first, so nothing just vanishes from the numbers.

Technical stack

How it's built.

Google Sheets

The interface people already knew, and the protected data store, locked owner-only so direct edits aren't possible, only submissions through the menu.

Apps Script

UI only. It opens the dialogs and calls the webhook; it never calls setValue or appendRow itself. Every write is n8n's job, not the sheet's.

n8n

The orchestrator and validator. It acts as the central gatekeeper, validating Apps Script payloads, recomputing the ledger, and coordinating data sync requests to and from Power Automate.

Power Automate

The Microsoft-native bridge. It interfaces directly with corporate Active Directory to sync users and uses Microsoft Outlook to dispatch automated paperwork reminders, communicating with n8n strictly through webhooks.

How it fits together

How the pieces connect.

The sheet is only the face, where every write passes through n8n before it lands, and two Microsoft-side jobs feed it fresh people-data and nudge stragglers. No sensitive details, just the wiring.

Google Sheetsprotected · owner-only

the team works through a custom menu, where every action opens a dialog, never a raw cell edit

Apps ScriptUI only · dialogs, no writes

the dialog posts to n8n, tagged by action and location; the matching workflow re-validates everything before it touches the sheet

n8n3 independent workflows

n8n writes as the sheet owner, appending to the ledger and recomputing stock from scratch

Transaksi ledgerappend-only, per location

Stock Sekarangrecomputed, never typed

on its own schedule, Power Automate pulls the company directory in and pushes paperwork-reminder emails out, talking to n8n only through its webhook contract

Power AutomateAD sync · reminder emails

Company directoryActive Directory

Who this fits for

Who it's for.

◆

Ops teams already on spreadsheetsTeams that don't want new software to learn, but do need a real audit trail behind the sheet they already use.

▲

IT / facilities departmentsAny internal team tracking consumables or small assets that fall outside the company's main inventory system.

●

Anyone who's been burned by manual auditsIf stock counts and system numbers keep drifting apart, journaled transactions fix the root cause instead of the symptom.