Maven Lane Documentation

Sync Acumatica Inventory To Google Sheets

4 min read

Last Updated: Jan 22, 2026

Open workflow in n8n

Executive Summary

This flow synchronizes the current state of inventory, broken down by SKU, to track what is on hand, available, and in transit. It synchronizes Total On Hand, Total Available, and Total In Transit. It also synchronizes the detailed Total On Hand and Total Available per warehouse.

INFO

Acumatica → n8n → Google Sheets
Trigger: Every 1 day at 00:00

Data Source

Source: Acumatica

Overview

The data is sourced from a Generic Inquiry built natively in Acumatica and exposed via the OData API. The specifications of this Generic Inquiry are as follows:

Data Sources

Where data is sourced from

  • PX.Objects.IN.INSite as Site
  • PX.Objects.IN.INSiteStatus as SiteStatus
  • PX.Objects.IN.InventoryItem as InventoryItem

Results Output

What is output from the Inquiry

  • SiteID (e.g., RESEARCH, 1167)
  • QtyOnHand
  • QtyAvail as QtyAvailable
  • InventoryCD as SKU

Filters

Conditions a record must meet to be included

  • InventoryCD (SKU) starts with ML Purpose: filter out any inactive SKUs
  • InventoryCD (SKU) does not contain -PK0 Purpose: filter out any multi-packs
  • Site is not RESEARCH Purpose: this is not a physical location
  • Site is not ML-MAIN Purpose: this is not an active location
  • Item is a Stock Item Purpose: all our products that actually exist are Stock Items
  • Site is Active Purpose: we don’t want to pull from inactive/archived locations

Relations

How different data tables are joined

  1. Join SiteStatus to InventoryItem
    • Parent: InventoryItem
    • Child: SiteStatus
    • Join type: Left Join
    • Join condition: InventoryItem.InventoryID == SiteStatus.InventoryID
  2. Join Site to SiteStatus
    • Parent: SiteStatus
    • Child: Site
    • Join type: Inner Join
    • Join condition: SiteStatus.SiteID == Site.SiteID

Data Manipulation (n8n Workflow)

Overview

This workflow extracts inventory data from Acumatica, transforms it from a vertical database structure to a horizontal spreadsheet format, and loads it into Google Sheets. It also enforces a daily logic check to generate historical snapshots exactly once per day, at midnight.

Trigger: Every 1 day at 00:00

Logic Flow

The workflow runs on a Daily Schedule to refresh the “Current Inventory” sheet and manage historical archives.

  1. Extraction & Validation: The flow requests the full Generic Inquiry via OData and searches the Historical Inventory Snapshots Folder for a file matching the current date pattern ([<current date>] - ...).
  2. Conditional Routing:
    • If Snapshot Exists: The workflow proceeds directly to update the live sheet.
    • If Snapshot is Missing: The workflow executes the Archival Path:
      1. Create a new spreadsheet named [<current date>] - Start of Day Stock Levels Snapshot.
      2. Move the new sheet to the target Drive folder.
      3. Format the sheet to receive data.
  3. Transformation: The raw data is processed through a JavaScript Code Node to restructure the dataset.
  4. Loading:
    1. The Current Inventory Levels sheet is Cleared.
    2. The Current Inventory Levels sheet is Appended with fresh data.
    3. If the Archival Path was triggered, the dataset is also Appended to the new Snapshot file.

Transformation Rules

The Code Node performs a Pivot Operation to convert rows per SKU/Warehouse into a single row per SKU with dynamic columns.

  • Data Preparation
    • Trimming: Removes fixed-width database whitespace from InventoryID and Warehouse.
    • Type Casting: Parses string-based numbers (e.g., "0.0000") into floating-point integers to prevent concatenation errors.
  • “In-Transit” Logic
    • If a warehouse is IN-TRANSIT, the quantity is summed only into Total In Transit.
    • These items are excluded from Total On Hand and Total Available to strictly represent physically present stock.
    • IN-TRANSIT does not generate a warehouse column.
  • Dynamic Pivoting
    • For all other warehouses, dynamic column headers are generated (e.g., DFW2-3053 On Hand, DFW2-3053 Available).
    • Quantities are summed into global totals (Total On Hand) and specific warehouse columns.
  • Zero-Filling
    • The node compiles a master list of all warehouse columns found in the dataset.
    • It iterates through every SKU row and explicitly sets 0 for any warehouse column that has no data, ensuring a consistent table structure.

Data Destination

Destination: Google Sheets

Overview

The destination of this flow is two-fold. The Current Inventory Levels Spreadsheet is cleared and updated on every run. Additionally, a historical snapshot is created as a permanent record. The naming convention is [<current date>] - Start of Day Stock Levels Snapshot. Once created, this file is populated and stored in the Historical Inventory Snapshots Folder.

Graph View

Backlinks