← All posts · 2026-08-07 · Sophia Excel & Automation

How to Auto-Aggregate Inventory in Google Sheets | Apps Script Part 3

Introduction — Refreshing inventory status at once, without formulas

When you look up how to auto-aggregate inventory in Google Sheets, most examples use SUMIFS or QUERY formulas. Your inbound and outbound data builds up nicely, but as soon as you want to see current stock by Location and Model, formulas get complex and the sheet slows down. In this post, we’ll use a Google Sheets Apps Script–based inventory method to completely recalculate and refresh the STOCK sheet with a single script run.

This is Part 3 following Build a Warehouse Inventory Management System with Google Sheets | Apps Script Automation and How to Automate Outbound in Google Sheets: Apps Script Part 2. In the previous posts, we built a structure where the inbound put-away log sheet (LOCATIONS) and outbound log sheet (OUTBOUND) are filled automatically. Now we’ll use that data to complete the part where, without any formulas, a single Apps Script run automatically aggregates current stock by Location and Model into the STOCK sheet.

STOCK sheet structure — Keep it simple with only the essentials

In practice, the key question for an inventory status sheet is: “Does it highlight only the important information at a glance?” If you cram in too many columns at once, it may look rich, but becomes hard to maintain. The STOCK sheet we’re building in Google Sheets will start with just Location, Model, inbound qty, outbound qty, and current stock, and we’ll add columns later only when needed.

A basic example of STOCK sheet columns:

1. Location
2. Model
3. InQty (inbound total)
4. OutQty (outbound total)
5. Stock (current stock = InQty - OutQty)

We’ll assume the inbound data is already stored in the LOCATIONS sheet, and outbound data in the OUTBOUND sheet. Each of them needs at least the following columns:

• LOCATIONS sheet
• Location column
• Model column
• Qty column (inbound quantity)

• OUTBOUND sheet
• Location column
• Model column
• Qty column (outbound quantity)

A frequent question in the field is: “Should I view inventory only by model, or by model and location together?” In real warehouse operations, you’ll almost always need the location as well. Even for the same model, which rack and which zone it’s in matters. In this post, we’ll aggregate by the (Location, Model) combination and show that in the STOCK sheet. If you want overall totals only by model, it’s safer to build a pivot table or a separate summary sheet on top of this.

Another important principle is: “The STOCK sheet is always fully overwritten by the script.” If someone slips in formulas or edits values directly in the middle, things can break at the next aggregation. So we’ll design it such that every time we run the script, it clears the STOCK sheet and writes everything again from scratch. Once you design your Google Spreadsheet inventory status script this way, even if some bad input gets in, you can restore consistency just by recalculating once.

Implementing inventory aggregation with Apps Script — Understand the flow first

Before diving into code, let’s outline the flow of this “Google Sheets inbound–outbound current stock automation” that we’re implementing. In a Google Sheets Apps Script–based inventory system, the script does five main things:

1. Read all inbound data from the LOCATIONS sheet and build a Map of inbound totals by (Location, Model).
2. Read all outbound data from the OUTBOUND sheet and build outbound totals with the same keys.
3. Merge the two Maps and calculate InQty, OutQty, and Stock (current stock) per (Location, Model).
4. Clear the STOCK sheet, write headers again, then fully overwrite it with the calculated data.
5. Use Script Lock (LockService) so it’s safe even if multiple users run it at the same time.

Applying this method to real work gives you benefits like:

• No need to lay down complicated formulas like SUMIFS or QUERY in the sheet.
• Even if you move the Location/Model columns around, you only need to edit the constants (CONFIG) at the top of the code.
• You can keep the STOCK sheet strictly read-only, and handle all inputs/edits only on LOCATIONS and OUTBOUND, making the data flow very clear.

There are trade-offs, too. If you’re not comfortable editing code, this can feel like a higher barrier to entry, and whenever you change the structure you also have to adjust the Apps Script. From actual usage in the field, once you’re at hundreds or thousands of inbound/outbound lines per day, an Apps Script “SUMIFS-style” inventory calculation tends to be more stable in maintenance and speed than pure formulas. In this post, we’ll write the code so that all sheet names and column indices live together in a config object at the top, minimizing the scope of changes later when you tweak the structure.

Implementing inventory aggregation with Apps Script

Step 1 — Set up config constants and basic helper functions

In this step, we’ll collect sheet names and column indices into a CONFIG constant, and create basic helper functions to read data.

1) What it does: Centrally manage inbound/outbound sheet names and column indices, and safely fetch sheets and data ranges.

2) Where to paste: Extensions → Apps Script → Code.gs, at the very top of the file.

3) What to do after pasting: Save (⌘S or Ctrl+S) and check that there are no errors.

// → Change only this section to match your own sheet
const CONFIG = {                                                        // → Configuration collection
  STOCK_SHEET_NAME: 'STOCK',                                           // → Inventory sheet name
  LOCATIONS_SHEET_NAME: 'LOCATIONS',                                   // → Inbound sheet name
  OUTBOUND_SHEET_NAME: 'OUTBOUND',                                     // → Outbound sheet name

  // Column numbers in the LOCATIONS sheet (starting from 1)
  LOCATIONS_COL_LOCATION: 1,                                           // → Location column number
  LOCATIONS_COL_MODEL: 2,                                              // → Model column number
  LOCATIONS_COL_QTY: 3,                                                // → Inbound quantity column number

  // Column numbers in the OUTBOUND sheet (starting from 1)
  OUTBOUND_COL_LOCATION: 1,                                            // → Location column number
  OUTBOUND_COL_MODEL: 2,                                               // → Model column number
  OUTBOUND_COL_QTY: 3,                                                 // → Outbound quantity column number

  HEADER_ROW: 1                                                        // → Header row number
};

// → Get a sheet object by name (create if it doesn’t exist)
function getOrCreateSheet_(name) {                                     // → Sheet retrieval function
  const ss = SpreadsheetApp.getActiveSpreadsheet();                    // → Current spreadsheet
  let sheet = ss.getSheetByName(name);                                 // → Find sheet by name
  if (!sheet) {                                                        // → If it doesn’t exist
    sheet = ss.insertSheet(name);                                      // → Create a new sheet
  }
  return sheet;                                                        // → Return the sheet
}

// → Get the data range of a sheet as a 2D array
function getDataRangeValues_(sheet, headerRow) {                       // → Data read function
  const lastRow = sheet.getLastRow();                                  // → Last row number
  const lastCol = sheet.getLastColumn();                               // → Last column number
  if (lastRow <= headerRow || lastCol === 0) {                         // → When there is no data
    return [];                                                         // → Return an empty array
  }
  return sheet                                                         // → From the sheet
    .getRange(headerRow + 1, 1, lastRow - headerRow, lastCol)          // → Range below the header
    .getValues();                                                      // → Get values as an array
}

How to confirm it’s working: If you click Save and there are no red error marks, Step 1 is complete.

Step 2 — Aggregate inbound/outbound by (Location, Model)

In this step, we’ll read the LOCATIONS and OUTBOUND sheets and build Maps of inbound and outbound totals by (Location, Model).

1) What it does: Pre-calculates InQty and OutQty totals per Location/Model combination.

2) Where to paste: Directly below the Step 1 code.

3) What to do after pasting: Save, then run the testBuildStockMaps() function and check the logs.

// → Helper to create a (Location, Model) key
function makeKey_(location, model) {                                   // → Key creation function
  return location + '||' + model;                                      // → String with separator
}

// → Aggregate LOCATIONS and OUTBOUND into Map-shaped totals
function buildStockMaps_() {                                           // → Aggregation function
  const locSheet = getOrCreateSheet_(CONFIG.LOCATIONS_SHEET_NAME);     // → Inbound sheet
  const outSheet = getOrCreateSheet_(CONFIG.OUTBOUND_SHEET_NAME);      // → Outbound sheet

  const locValues = getDataRangeValues_(locSheet, CONFIG.HEADER_ROW);  // → Inbound data
  const outValues = getDataRangeValues_(outSheet, CONFIG.HEADER_ROW);  // → Outbound data

  const inMap = {};                                                    // → Inbound total Map
  const outMap = {};                                                   // → Outbound total Map

  // → Aggregate inbound data
  locValues.forEach(row => {                                           // → For each row
    const location = String(row[CONFIG.LOCATIONS_COL_LOCATION - 1]);   // → Location value
    const model = String(row[CONFIG.LOCATIONS_COL_MODEL - 1]);         // → Model value
    const qty = Number(row[CONFIG.LOCATIONS_COL_QTY - 1]) || 0;        // → Quantity as number

    if (!location || !model) {                                         // → If required values are missing
      return;                                                          // → Skip this row
    }
    const key = makeKey_(location, model);                             // → Create key
    inMap[key] = (inMap[key] || 0) + qty;                              // → Accumulate total
  });

  // → Aggregate outbound data
  outValues.forEach(row => {                                           // → For each row
    const location = String(row[CONFIG.OUTBOUND_COL_LOCATION - 1]);    // → Location value
    const model = String(row[CONFIG.OUTBOUND_COL_MODEL - 1]);          // → Model value
    const qty = Number(row[CONFIG.OUTBOUND_COL_QTY - 1]) || 0;         // → Quantity as number

    if (!location || !model) {                                         // → If required values are missing
      return;                                                          // → Skip this row
    }
    const key = makeKey_(location, model);                             // → Create key
    outMap[key] = (outMap[key] || 0) + qty;                            // → Accumulate total
  });

  return { inMap, outMap };                                            // → Return both Maps
}

// → Test helper to inspect aggregation results in the console
function testBuildStockMaps() {                                        // → Test function
  const { inMap, outMap } = buildStockMaps_();                         // → Run aggregation
  Logger.log('IN:' + JSON.stringify(inMap));                           // → Log inbound totals
  Logger.log('OUT:' + JSON.stringify(outMap));                         // → Log outbound totals
}

How to confirm it’s working: In the Apps Script editor, choose testBuildStockMaps from the function list, click Run, then go to Executions → Execution log. If you see IN/OUT logs with (Location||Model) keys and summed quantities, it’s working correctly.

Step 3 — Write current stock to the STOCK sheet and hook up a menu

In Step 3, we’ll use the aggregated Maps to calculate current stock and overwrite the STOCK sheet in full. We’ll also use LockService to prevent conflicts from concurrent runs, and add a “Refresh Stock” button to the menu.

#### Step 3‑1 — Calculate stock and fully refresh the STOCK sheet

1) What it does: Uses InQty and OutQty to calculate Stock (current stock), then clears and repopulates the STOCK sheet.

2) Where to paste: Directly below the Step 2 code.

3) What to do after pasting: Save and run the buildStock() function once to generate the STOCK sheet.

// → Write inventory by (Location, Model) into the STOCK sheet
function buildStock() {                                                // → Main inventory aggregation
  const lock = LockService.getScriptLock();                            // → Lock object

  if (!lock.tryLock(30000)) {                                          // → Wait up to 30 seconds
    throw new Error('Another user is currently aggregating inventory. Please try again shortly.'); // → Lock acquisition failed
  }

  try {                                                                // → Begin error handling
    const { inMap, outMap } = buildStockMaps_();                       // → Get aggregated totals

    const stockSheet = getOrCreateSheet_(CONFIG.STOCK_SHEET_NAME);     // → Inventory sheet
    stockSheet.clearContents();                                        // → Clear all contents

    // → Write header row
    const header = ['Location', 'Model', 'InQty', 'OutQty', 'Stock'];  // → Header array
    stockSheet.getRange(1, 1, 1, header.length).setValues([header]);   // → Write to row 1

    const rows = [];                                                   // → Body data array

    // → Merge key lists from inbound and outbound
    const keys = new Set();                                            // → Key collection
    Object.keys(inMap).forEach(k => keys.add(k));                      // → Add inbound keys
    Object.keys(outMap).forEach(k => keys.add(k));                     // → Add outbound keys

    // → Calculate stock for each key
    keys.forEach(key => {                                              // → Loop through each key
      const [location, model] = key.split('||');                       // → Restore values
      const inQty = inMap[key] || 0;                                   // → Inbound total
      const outQty = outMap[key] || 0;                                 // → Outbound total
      const stock = inQty - outQty;                                    // → Current stock

      rows.push([location, model, inQty, outQty, stock]);              // → Add one row
    });

    // → Only write when there is data
    if (rows.length > 0) {                                             // → Check that rows exist
      stockSheet.getRange(2, 1, rows.length, header.length)            // → Range from row 2
        .setValues(rows);                                              // → Write values
    }

    // → Optional: make it easier to view by sorting
    stockSheet.sort(1);                                                // → Sort by column 1 (Location)
  } finally {                                                          // → Always runs
    lock.releaseLock();                                                // → Release the lock
  }
}

How to confirm it’s working: In Apps Script, select the buildStock function and run it. Then go back to your spreadsheet and check that a STOCK sheet has been created, with Location/Model rows and InQty, OutQty, and Stock filled in. Edit a few quantities in LOCATIONS and OUTBOUND, run it again, and confirm that the STOCK values change accordingly.

#### Step 3‑2 — Add a “Refresh Stock” button to the menu

1) What it does: When the spreadsheet opens, it adds a “Inventory Management” menu with a “Refresh Stock” item that runs buildStock() with a click.

2) Where to paste: At the very bottom of the same Code.gs file.

3) What to do after pasting: Save, then reload the spreadsheet.

// → Add menu when the sheet is opened
function onOpen() {                                                    // → Runs when sheet opens
  const ui = SpreadsheetApp.getUi();                                   // → UI object
  ui.createMenu('Inventory Management')                                // → Menu name
    .addItem('Refresh Stock', 'buildStock')                            // → Add menu item
    .addToUi();                                                        // → Attach to UI
}

How to confirm it’s working: After you reload the sheet, you should see an “Inventory Management” menu at the top. When you click “Refresh Stock” and the STOCK sheet is repopulated with up‑to‑date data, it’s working correctly.

Comparing formula-based vs script-based approaches — When scripts shine

Using Apps Script isn’t the only way to build a STOCK sheet in Google Sheets. For simple setups, SUMIFS or QUERY can be enough. For example, if you only need current stock by model, you can use something like:

• Inbound total example:

=SUMIFS(LOCATIONS!C:C, LOCATIONS!B:B, A2)

• Outbound total example:

=SUMIFS(OUTBOUND!C:C, OUTBOUND!B:B, A2)

• Current stock:

=C2-D2

Or you can group LOCATIONS and OUTBOUND by model with QUERY, then combine those two aggregated tables with VLOOKUP. If your data volume is small and you don’t need to consider locations, this can be perfectly fine.

However, once you try to run a real operation with formula-only “Google Sheets inventory auto aggregation,” you’ll often run into issues like:

• When data rows reach into the thousands, automatic recalculation gets noticeably slow.
• While adding/copying formulas, reference ranges get misaligned in a few spots, causing incorrect calculations on certain rows.
• As soon as you include location, you have multiple criteria, several SUMIFS, and helper columns tangled together, and the structure quickly becomes hard to reason about.

The Apps Script–driven Google Sheets STOCK sheet method we built here pushes all calculations into code and keeps the sheet itself as a simple view of results. If stock numbers ever look suspicious, you just click “Refresh Stock,” and it recalculates from the LOCATIONS and OUTBOUND raw data. This greatly reduces errors from broken formulas or manual edits. In small warehouses and offices I’ve tested with, it ran reliably and without noticeable speed issues even at several hundred transactions per day.

Practical tips from real usage — Structure changes and error checks

Here are some tips from running this structure in real warehouses and offices. If you’re introducing Google Sheets Apps Script for inventory management for the first time, these can save you time.

First, keep the STOCK sheet strictly “view-only.” If anyone starts hand-editing quantities directly in STOCK, the next automatic aggregation will overwrite them and cause confusion. In practice, it helps to put a bold note in cell A1 saying, “This sheet is generated automatically. Do not edit manually.” It’s also worth protecting the STOCK sheet via the Protect range/sheet feature.

Second, whenever you change the column structure of LOCATIONS or OUTBOUND, you must revisit the CONFIG constants. For example, if you insert a helper column before the inbound quantity column, what used to be column 3 becomes column 4. If you don’t update the code, the inventory script will interpret some other column as quantity and compute wrong stock numbers. In production, we literally added “Check Apps Script CONFIG column indices” as a checklist item for any structure change.

Third, using a lock so that multiple people can click “Refresh Stock” at once without issues is essential. Initially, we only had a bare buildStock() function, and occasionally ended up with a STOCK sheet left blank. Investigation showed two users had run it almost exactly at the same time and collided during the clear/write sequence. After adding LockService.getScriptLock(), wrapping things with try/finally, and throwing an error on lock failure (as in the current code), the problem disappeared.

Fourth, whenever you suspect something’s wrong, get in the habit of checking testBuildStockMaps() first to see the raw aggregation. Comparing the IN/OUT Maps in the execution logs with the actual inbound/outbound data makes it easy to tell whether the problem is in the source data or in the STOCK-writing part. In practice, when stock looked off, we followed this routine: ① Check raw data → ② Run testBuildStockMaps() and inspect logs → ③ Re-run buildStock() to rebuild STOCK.

Common errors and how to fix them

When you first roll out this Google Spreadsheet inventory status script, you’ll likely see the same issues come up. Here are the most common and how to resolve them.

1. Script authorization warnings

The first time you run an Apps Script, you may see a “This app isn’t verified” warning. In that case:

• Click “Advanced” in the warning dialog, then
• Click “Go to (project name)”.

For internal automation scripts, granting permissions once with the author’s account is usually enough; it won’t prompt you again afterward.

1. Weird stock numbers due to mismatched column indices

If you add columns to LOCATIONS or OUTBOUND or change the order without updating the CONFIG constants, Stock values will diverge from reality. To fix this, check in this order:

• In each sheet, verify the actual positions of the Location, Model, and Qty columns (starting from 1).
• Compare these with LOCATIONS_COL_LOCATION, LOCATIONS_COL_MODEL, LOCATIONS_COL_QTY, and the OUTBOUND_COL_* values in the CONFIG object.
• After fixing, run testBuildStockMaps() once to confirm the totals, then run buildStock() to regenerate the STOCK sheet.

1. STOCK sheet appears empty because there’s no data

In test environments, if there’s little or no data in LOCATIONS and OUTBOUND, the STOCK sheet may show only headers or appear empty. In that case, enter two or three sample inbound/outbound rows and run buildStock() again. Especially when you’re first introducing this setup, it’s safer to validate the structure with dummy data before relying on real numbers.

Once you’re aware of these points, most issues that arise while building your Google Sheets STOCK sheet can be diagnosed and fixed directly on site.

Conclusion

We’ve walked through, step by step, how to use inbound and outbound data to implement a Google Sheets inventory auto-aggregation method with Apps Script. The core idea is to let LOCATIONS and OUTBOUND accumulate records, while the STOCK sheet is always fully recalculated and overwritten by the script. Structuring things this way dramatically reduces errors from tangled formulas and manual edits.

If you’ve read this far, the next step is simple: open the Apps Script editor on your spreadsheet, adjust the CONFIG constants to match your sheet structure, and run buildStock() once. Once you’ve confirmed it works, you can keep your stock always up to date by just clicking “Refresh Stock” in the menu to recalculate current stock by Location and Model. In the next post in this series, we’ll look at how to extend this STOCK data into minimum stock alerts and a simple dashboard.