Generate a warehouse pick or packing list

When a Northwind order comes in, someone in the warehouse has to walk the shelves and gather every item. A raw order printout makes that walk longer than it needs to be: the SKUs are in the order they were typed, not the order you would walk them, so the picker zigzags up and down the aisles and doubles back.

This automation turns an order into a proper pick list. It looks up where each SKU lives, groups the items by aisle, sorts the aisles, and writes the whole thing into a clean Google Doc with a tick box against every line. The picker walks the aisles once, top to bottom, ticking as they go.

What you’ll need

• An Orders sheet with a header row and three columns: orderNumber, sku and quantity — one row per item on an order.
• A Skus sheet with a header row and three columns: sku, aisle and bin — the warehouse location of every SKU.
• The two spreadsheet IDs dropped into the config block in place of the placeholders.
• Permission for the script to create Google Docs in your Drive (granted on first run).

The script

// The spreadsheet holding the Orders sheet.
const ORDERS_SHEET_ID = '1abcOrdersId';

// The spreadsheet holding the Skus location sheet.
const SKUS_SHEET_ID = '1abcSkusId';

// The location shown when a SKU is missing from the Skus sheet.
const UNKNOWN_LOCATION = { aisle: '?', bin: '?' };

/**
 * Builds a print-ready pick list for one order: a Google Doc with the
 * order's items grouped by aisle and a tick box on every line.
 *
 * @param {string} orderNumber The order to pick.
 * @return {string} The URL of the generated Doc.
 */
function buildPickList(orderNumber) {
  // 1. Read the Orders sheet and keep only this order's rows.
  const orders = readSheet(ORDERS_SHEET_ID)
    .filter((r) => r.orderNumber === orderNumber);
  if (!orders.length) {
    Logger.log(`No rows found for order ${orderNumber}.`);
    return '';
  }

  // 2. Read the Skus sheet into a lookup keyed by SKU.
  const skus = Object.fromEntries(
    readSheet(SKUS_SHEET_ID).map((s) => [s.sku, s])
  );

  // 3. Group the order's items by aisle, attaching each item's location.
  const grouped = new Map();
  for (const o of orders) {
    const loc = skus[o.sku] || UNKNOWN_LOCATION;
    if (!grouped.has(loc.aisle)) grouped.set(loc.aisle, []);
    grouped.get(loc.aisle).push({ ...o, ...loc });
  }

  // 4. Create the Doc and give it a title.
  const doc = DocumentApp.create(`Pick list — ${orderNumber}`);
  const body = doc.getBody();
  body.appendParagraph(`Pick list — Order ${orderNumber}`)
    .setHeading(DocumentApp.ParagraphHeading.TITLE);

  // 5. Write one section per aisle, aisles in walking order.
  for (const [aisle, items] of [...grouped].sort()) {
    body.appendParagraph(`Aisle ${aisle}`)
      .setHeading(DocumentApp.ParagraphHeading.HEADING2);
    for (const it of items) {
      body.appendParagraph(`☐ Bin ${it.bin}  ${it.sku}  × ${it.quantity}`);
    }
  }

  // 6. Save and hand back the Doc URL.
  doc.saveAndClose();
  Logger.log(`Pick list ready: ${doc.getUrl()}`);
  return doc.getUrl();
}

/**
 * Reads a sheet's first tab into an array of objects, one per row, keyed
 * by the header names.
 *
 * @param {string} id The spreadsheet ID to read.
 * @return {Object[]} One object per data row.
 */
function readSheet(id) {
  const [h, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((r) => Object.fromEntries(h.map((k, i) => [k, r[i]])));
}

How it works

1. buildPickList reads the Orders sheet through readSheet and filters it to the rows for the order number you passed in. If nothing matches, it logs a message and stops.
2. It reads the Skus sheet into a lookup object keyed by SKU, so finding any item’s location is a single property access.
3. It walks the order’s items and groups them into a Map by aisle. Each item carries its aisle and bin; a SKU missing from the Skus sheet falls back to the ? location so it still appears on the list.
4. It creates a new Google Doc and sets the order number as the title.
5. It writes one section per aisle. Spreading the Map and calling .sort() puts the aisles in order, so the picker walks them in sequence. Each item is a line with a tick box, the bin, the SKU and the quantity.
6. It saves the Doc and returns its URL, which is also logged.

Example run

Say order NW-5567 has these rows on the Orders sheet:

orderNumber	sku	quantity
NW-5567	TENT-2P	1
NW-5567	MAT-FOAM	2
NW-5567	STOVE-GAS	1

…and the Skus sheet places them like this:

sku	aisle	bin
TENT-2P	C	12
MAT-FOAM	A	04
STOVE-GAS	C	07

buildPickList('NW-5567') produces a Doc reading:

Pick list — Order NW-5567

Aisle A
☐ Bin 04  MAT-FOAM  × 2

Aisle C
☐ Bin 12  TENT-2P  × 1
☐ Bin 07  STOVE-GAS  × 1

The two Aisle C items are gathered together even though they were typed apart on the order, so the picker visits each aisle once.

Run it

This is an on-demand job — you build a pick list when an order is ready to go, not on a schedule:

1. In the Apps Script editor, select buildPickList.
2. Because it takes an argument, add a small wrapper such as pickNW5567() that calls buildPickList('NW-5567'), and run that.
3. Approve the authorisation prompt the first time.
4. Open the URL from the execution log to print the list.

To let warehouse staff trigger it themselves, add a custom menu so it appears in the spreadsheet:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Warehouse')
    .addItem('Build pick list for selected order', 'buildPickListFromCell')
    .addToUi();
}

Have buildPickListFromCell read the active cell for the order number and pass it to buildPickList.

Watch out for

• The order number match is exact. NW-5567 and nw-5567 are treated as different orders — keep the casing consistent on the Orders sheet.
• A SKU missing from the Skus sheet lands in an Aisle ? section with bin ?. That is deliberate, so the item is never silently dropped — but it signals the Skus sheet needs updating.
• Aisles sort as text, so Aisle 10 comes before Aisle 2. Pad aisle codes (02, 10) or use letters if walking order matters.
• Every run creates a brand-new Doc in your Drive. Rebuilding a list for the same order leaves the old one behind — clear out stale pick lists, or have the script move them to a dedicated folder.
• The list reflects the sheet at the moment it runs. If the order changes after the Doc is generated, build a fresh list rather than editing the Doc by hand.