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

Title: "How to Automate Outbound in Google Sheets: Apps Script Part 2"

Date: 2026-08-07

Draft: false

Description: "If you’re looking for a way to automate outbound processing in Google Sheets, this post walks through a practical setup. Once you start managing inventory in Google Sheets, the first painful bottleneck is usually outbound: every time an order comes in, you look up the quantity in the LOCATIONS sheet, subtract it with a calculator, type it back in, and then log the history in the OUTBOUND sheet…"

Keywords: "Google Sheets outbound automation, Google Sheets Apps Script stock deduction, Google Sheets warehouse outbound processing, Apps Script savePicking implementation, Google Sheets OUTBOUND sheet automation, Google Sheets inventory management sidebar"

Theme: "Series – Warehouse Inventory Management, Complete Version"

Category: "Excel & Workflow Automation"

Image: "/images/20260807-q142-881e62-hero.svg"

Keyword: "Automating Warehouse Outbound in Google Sheets — Apps Script Part 2"

How to Automate Outbound in Google Sheets: Apps Script Part 2

Intro – If you’re still subtracting stock by hand on every outbound

Once you start managing inventory in Google Sheets, the first big hurdle is outbound processing. If, every time an order comes in, you look up the quantity in the LOCATIONS sheet, subtract it with a calculator, type it back in, and then record the history on an OUTBOUND sheet, there are just too many points where manual work can introduce errors. When multiple people are touching the same sheet at once, it also becomes very easy to lose track of “which exact stock snapshot this subtraction was based on.”

This post focuses on a Google Sheets outbound automation setup. We’ll build an outbound sidebar with Apps Script and write code that automatically deducts from LOCATIONS stock. If you already followed Part 1 and implemented inbound handling and putaway storage (savePutaway), in this Part 2 we’ll add an outbound sheet (OUTBOUND) and implement a savePicking function. The goal is to complete a flow where entering outbound info in the sidebar immediately creates an OUTBOUND record and adds a negative row to LOCATIONS to deduct stock in one shot.

Base sheet structure for outbound automation

To automate outbound processing in Google Sheets, it helps a lot to keep the sheet structure and data flow as simple as possible. A layout that has worked fairly reliably in practice is to use four core sheets plus one more for outbound:

• INBOUND – Planned and actual inbound records
• LOCATIONS – Current stock by location (cumulative inbounds and outbounds)
• FLOOR_MAP – Warehouse location definitions
• SETTINGS – Shared settings such as dropdown options and code values
• OUTBOUND – Outbound history (added in this post)

We’ll assume you already created the first four sheets in Part 1, plus a “Warehouse” menu, an inbound sidebar, and the putaway save function (savePutaway). In that state, the LOCATIONS sheet shows current inventory by summing quantities for each location–model combination.

For outbound, at minimum you need to leave enough information to trace back what happened later:

• Which order number (or outbound instruction number)
• From which location
• Which model
• How many units were shipped
• When it was processed

So the OUTBOUND sheet usually starts with these five columns:

• Column A: ORDER_NO
• Column B: LOCATION
• Column C: MODEL
• Column D: QTY
• Column E: TIMESTAMP

Once this structure is in place, your Apps Script will write to this sheet and to LOCATIONS at the same time to record outbound history and deduct stock.

OUTBOUND sheet and basic constants

First, it’s convenient to define sheet names as constants in Apps Script, because you’ll reuse them often. If a sheet name changes later, you only need to update it in one place.

1. What this code does

• Defines constants for the main sheet names like INBOUND, LOCATIONS, OUTBOUND, etc.

1. Where to paste it

• Google Sheets → Extensions → Apps Script → at the very top of Code.gs.

1. What to do after pasting

• Just save. No need to run anything at this point.

const SHEET_INBOUND = 'INBOUND';      // → Inbound sheet name
const SHEET_LOCATIONS = 'LOCATIONS';  // → Stock sheet name
const SHEET_OUTBOUND = 'OUTBOUND';    // → Outbound sheet name
const SHEET_SETTINGS = 'SETTINGS';    // → Settings sheet name
const SHEET_FLOOR = 'FLOOR_MAP';      // → Location definition sheet name

How to check it: as long as there are no red error markers in the Apps Script editor and the actual sheet tab names match these constants exactly, you’re good to go.

Next, create a new OUTBOUND tab in your spreadsheet and enter the following headers in cells A1–E1:

• A1: ORDER_NO
• B1: LOCATION
• C1: MODEL
• D1: QTY
• E1: TIMESTAMP

Now you have the minimum structure ready to receive outbound history.

getStockByLocation – Get current stock by location

The core of outbound automation is accurately answering: “Right now, how many units of this model are at this location?” For that, you need a function that reads the LOCATIONS sheet and sums the quantities for a given location–model pair to compute current stock. You’ll reuse this both for outbound validation and for showing current stock in the sidebar.

1. What this code does

• Reads the entire LOCATIONS sheet and sums up the quantities for the given location and model to return current stock.

1. Where to paste it

• Apps Script → Code.gs, somewhere below the constants.

1. What to do after pasting

• Save, then optionally run a test and inspect the logs.

function getStockByLocation(location, model) {       // → Get stock for a location/model
  const ss = SpreadsheetApp.getActive();            // → Current spreadsheet
  const sheet = ss.getSheetByName(SHEET_LOCATIONS); // → LOCATIONS sheet
  const range = sheet.getDataRange();               // → Entire data range
  const values = range.getValues();                 // → Read as 2D array

  let total = 0;                                    // → Total quantity

  for (let i = 1; i < values.length; i++) {         // → Skip header row
    const row = values[i];                          // → Current row
    const rowLocation = row[0];                     // → Column A: LOCATION
    const rowModel = row[1];                        // → Column B: MODEL
    const qty = row[2];                             // → Column C: QTY

    if (rowLocation === location && rowModel === model) { // → Matching row
      total += Number(qty) || 0;                    // → Add to total
    }
  }

  return total;                                     // → Return current stock
}

How to check it: put some test data in LOCATIONS (several rows with the same location and model, with both positive and negative quantities), then in a sheet cell enter a test function in the editor (see below) and see if the sum matches what you expect.

function testGetStock() {                            // → run this from the editor
  const result = getStockByLocation('L-01', 'ABC123'); // → use your own data
  Logger.log('On hand: ' + result);                   // → check the execution log
}

Testing with a custom function in a cell (=getStockByLocation(...)) is possible, but custom functions recalculate on their own schedule and cannot use every service, so values may look stale. Running the test function from the editor is more reliable.

savePicking – Validate stock, then write OUTBOUND and deduct LOCATIONS

Now let’s implement the main outbound function, savePicking. Using the outbound data sent from the sidebar, this function will:

1. Call getStockByLocation to get current stock
2. Validate that the outbound quantity is greater than 0 and not more than the available stock
3. Append a new outbound history row to the OUTBOUND sheet
4. Append a negative quantity row to the LOCATIONS sheet to deduct stock

Because we never overwrite stock directly and instead keep both inbound and outbound as a cumulative log, it becomes much easier to audit or trace errors later.

1. What this code does

• Validates outbound request data and writes to both the OUTBOUND and LOCATIONS sheets.

1. Where to paste it

• Apps Script → Code.gs, right below getStockByLocation.

1. What to do after pasting

• Save; later, your sidebar JavaScript will call this function.

function savePicking(data) {                          // → outbound save function
  const location = String(data.location || '').trim();  // → picking location
  const model    = String(data.model    || '').trim();  // → model code
  const orderNo  = String(data.orderNo  || '').trim();  // → order number
  const qty      = Number(data.qty);                    // → quantity as a number

  if (!location || !model || !orderNo) {             // → block empty values
    throw new Error('Order number, location and model are all required.');
  }
  if (!isFinite(qty) || qty <= 0) {                  // → block non-numbers and zero
    throw new Error('Quantity must be a number greater than 0.');
  }

  const lock = LockService.getScriptLock();          // → prevents concurrent picking
  if (!lock.tryLock(10000)) {                        // → wait up to 10 seconds
    throw new Error('Another user is saving right now. Please try again.');
  }

  try {                                              // → read and write inside the lock
    const ss = SpreadsheetApp.getActive();           // → current spreadsheet
    const currentStock = getStockByLocation(location, model); // → read stock after locking

    if (qty > currentStock) {                        // → not enough stock
      throw new Error('Not enough stock. On hand: ' + currentStock);
    }

    const timestamp = new Date();                    // → current time
    ss.getSheetByName(SHEET_OUTBOUND).appendRow([    // → append the outbound record
      orderNo,                                       // → order number
      location,                                      // → location
      model,                                         // → model
      qty,                                           // → quantity
      timestamp                                      // → time
    ]);

    ss.getSheetByName(SHEET_LOCATIONS).appendRow([   // → append the deduction row
      location,                                      // → location
      model,                                         // → model
      -qty,                                          // → negative quantity
      'PICKING',                                     // → movement type
      timestamp                                      // → time
    ]);

    SpreadsheetApp.flush();                          // → write through immediately

    return {                                         // → result for the sidebar
      success: true,                                 // → status
      remaining: currentStock - qty                  // → stock after picking
    };
  } finally {
    lock.releaseLock();                              // → always release the lock
  }
}

How to check it: put a test inbound row of +100 units for a certain location/model into LOCATIONS, then call savePicking with a quantity of 10. You should see one new row in OUTBOUND, one new row with -10 in LOCATIONS, and getStockByLocation should now return 90.

Playground — run the outbound flow right here

Before building the sheet, you can watch the outbound logic work. Below is a Google Sheets simulator that runs inside this page. The code above runs unchanged against a fake sheet built in your browser — no sign-in, no authorization prompt.

Press ▶ Run and it ships 10 units out of the 100 ABC123 units sitting at location L-01. Watch the log: a history row is appended to OUTBOUND, a -10 row is added to LOCATIONS, and the stock drops to 90. Change qty: 10 to qty: 500 and run it again to see the stock validation actually stop you. ↺ Reset sheets puts everything back.

Edit the code and press Run. This is a simulator running on a fake sheet in your browser, not a real Google Sheet.

The playground code is the article code with the sidebar screen removed. The simulator has no input panel, so demo() supplies the values a picker would type; the lock, validation and write order are exactly the same as on a real sheet.

Sidebar HTML – Input fields, current stock display, and outbound button

In real use, it’s much more intuitive to let on-site staff work from a sidebar form and click a button than to run Apps Script functions directly. If you already built an inbound sidebar in Part 1, you can add another section in the same HTML file just for outbound.

1. What this code does

• Creates an outbound input form (location, model, quantity, order number), shows current stock, and provides a button to save outbound.

1. Where to paste it

• Inside the <body> of your sidebar HTML file (e.g., sidebar.html) in the Apps Script project.

1. What to do after pasting

• Save, then reopen the sidebar from your custom script menu to check the UI.

<div id="picking-section">
  <h3>Outbound</h3>

  <label>LOCATION</label>
  <input type="text" id="pickLocation">

  <label>MODEL</label>
  <input type="text" id="pickModel">

  <button type="button" onclick="onClickCheckStock()">Check Current Stock</button>
  <div id="currentStockDisplay">Current stock: -</div>

  <label>QTY</label>
  <input type="number" id="pickQty" min="1">

  <label>ORDER NO</label>
  <input type="text" id="pickOrderNo">

  <button type="button" onclick="onClickSavePicking()">Save Outbound</button>
</div>

<script>
  function onClickCheckStock() {                                // → "Check Current Stock" button
    const location = document.getElementById('pickLocation').value;
    const model = document.getElementById('pickModel').value;

    google.script.run
      .withSuccessHandler(function(stock) {                     // → On success
        document.getElementById('currentStockDisplay').innerText =
          'Current stock: ' + stock;                            // → Update display
      })
      .withFailureHandler(function(err) {                       // → On failure
        alert('Error checking current stock: ' + err.message);  // → Show message
      })
      .getStockByLocation(location, model);                     // → Call Apps Script
  }

  function onClickSavePicking() {                               // → "Save Outbound" button
    const location = document.getElementById('pickLocation').value;
    const model = document.getElementById('pickModel').value;
    const qty = document.getElementById('pickQty').value;
    const orderNo = document.getElementById('pickOrderNo').value;

    const data = {                                              // → Data to send
      location: location,
      model: model,
      qty: qty,
      orderNo: orderNo
    };

    google.script.run
      .withSuccessHandler(function(result) {                    // → On success
        alert('Outbound has been saved. Remaining stock: ' + result.remaining);
        document.getElementById('currentStockDisplay').innerText =
          'Current stock: ' + result.remaining;                 // → Refresh display
      })
      .withFailureHandler(function(err) {                       // → On failure
        alert('Error during outbound: ' + err.message);         // → Show error
      })
      .savePicking(data);                                       // → Call Apps Script
  }
</script>

How to check it: in your spreadsheet, use the “Warehouse” menu → open the sidebar. Enter a location/model and click [Check Current Stock]; the quantity from LOCATIONS should appear. Then fill in quantity and order number, click [Save Outbound], and verify that rows are added to both OUTBOUND and LOCATIONS.

Practical tips: concurrency, permissions, and testing

Getting the code to “run” is only the start. To actually use outbound automation in a warehouse where multiple people work at once, you’ll want to think through a few real-world issues.

1) Stock accuracy when multiple users pick the same item

When several staff members are picking the same location/model at the same time, they might all read nearly the same currentStock and try to deduct concurrently. In that case, you can end up with more deducted than you physically have, leading to negative stock.

A perfect solution would be to move to a transactional WMS, but within Google Sheets, a realistic mitigation looks like this:

• Keep the current design where savePicking re-checks stock inside the function.
• If there isn’t enough stock, throw an error so the invalid outbound is never written.
• For items with heavy simultaneous activity, adopt an operational rule that “only one person picks from this item at a time,” and manage picking order to reduce overlaps.

If you want more control, you can use Apps Script’s LockService. Acquire a lock at the start of savePicking and release it at the end. That forces inbound requests to be processed one at a time, which reduces the chance of over-deducting. Just be careful not to lock too broadly, or overall performance can suffer. Apply it only in truly high-contention parts of your code.

2) Script permissions and sharing

If multiple people will use the outbound automation, check these items in advance:

• Use a shared, work-controlled account as the script owner and sheet owner, so things remain stable when team members change.
• A responsible person should complete the initial authorization flow when running the script for the first time.
• If external parties (3PLs, outsourced warehouses, etc.) will use it, sort out Google account requirements and access policies ahead of time.

3) Test data and validation steps

Before going live, it’s wise to run at least the following basic tests:

• Create several locations and models, then repeat inbound → outbound workflows multiple times and check that all sums line up.
• Intentionally request more than available stock and confirm the error message shows up as expected.
• Try clicking save with required fields (location, model, quantity, order number) left blank, and then harden your code so bad data cannot get in.

You can tighten things up by adding simple input validation logic to savePicking (for example, throwing an error when location or model is empty, or when quantity is not a valid number) so that common mistakes on the floor don’t silently pollute your data.

Closing – Small automations can dramatically reduce outbound mistakes

In this post, we used Google Sheets and Apps Script to build an outbound sidebar and automatically handle both OUTBOUND history and LOCATIONS stock deduction. The core ideas are:

1. Maintain a separate OUTBOUND sheet for outbound history
2. Use getStockByLocation to compute current stock per location–model
3. Let savePicking handle, in one go: stock validation → OUTBOUND record → negative row in LOCATIONS

With this structure in place, you can automate a large chunk of the manual “calculator and typing” work, while keeping a transparent, sheet-based log of all inbounds and outbounds.

From here, you can extend the system with barcode scanner input, integrations with order lists, or automatic picking list generation. But in most warehouses, simply getting this “basic outbound automation” stable already makes a huge difference. A practical way to move forward is to duplicate this setup into a test file, run a small pilot with your team, and refine it step by step based on real usage.