Build a Warehouse Inventory System in Google Sheets with Apps Script
If you are looking at a Google Sheets warehouse inventory system, the real question before buying a WMS is how far the spreadsheet you already have can take you. The short answer: four sheets and a single Apps Script sidebar are enough to handle everything from checking inbound containers to recording putaway locations, all from one screen.
This is Part 1 of the series. Here we build the screen that picks a container that arrived today, takes a putaway location, and saves it. The code is published in full, with the exact place to paste it and how to confirm it works.
1. What you get when it's finished
A Warehouse menu appears at the top of the sheet. Opening the sidebar from it shows an input panel on the right, listing only the containers scheduled to arrive today. Pick one and the model and quantity fill in automatically; type the putaway location, press save, and two things happen at once. A stock row is appended to the LOCATIONS sheet, and the matching row in INBOUND is marked as done.
The point that matters on the floor is having exactly one place to type. When people edit the sheet directly, someone eventually overwrites the wrong column or the wrong row. A sidebar means people can only fill the fields you gave them.
2. Four sheets for the skeleton
Create a new Google spreadsheet with four sheets named exactly as below. The names must match the code, capitals included.
INBOUND — scheduled arrivals. Row 1 is the header: CONTAINER, MODEL, QTY, ETA_DATE, PUTAWAY, STATUS. Load the day's expected containers here and the sidebar filters to today automatically.
LOCATIONS — actual stock records. Header: LOCATION, CONTAINER, MODEL, QTY, TIMESTAMP. One row is appended each time a putaway is confirmed, and this sheet becomes the source of truth for stock by location.
FLOOR_MAP — rack layout. Created now, unused in this part. We fill it with color in Part 3.
SETTINGS — configuration. A place to move constants later. Leave it empty for now.
3. Full Code.gs — menu and data handling
In the spreadsheet, open Extensions → Apps Script, clear the default Code.gs, and paste the whole block below. Only the three constants at the top need to match your sheet.
const SHEET_INBOUND = 'INBOUND'; // → name of the inbound sheet (match yours)
const SHEET_LOCATIONS = 'LOCATIONS'; // → name of the stock sheet (match yours)
const MENU_NAME = 'Warehouse'; // → menu label shown on the sheet
function onOpen() { // → runs automatically when the sheet opens
SpreadsheetApp.getUi()
.createMenu(MENU_NAME) // → create the Warehouse menu
.addItem('Open Inbound UI', 'showInboundSidebar') // → add a clickable item
.addToUi(); // → attach the menu to the sheet
}
function showInboundSidebar() { // → opens the input panel on the right
const html = HtmlService.createHtmlOutputFromFile('Sidebar') // → load the Sidebar file
.setTitle('Inbound / Putaway'); // → sidebar title
SpreadsheetApp.getUi().showSidebar(html); // → show it on the right
}
function getTodayInbound() { // → returns only containers due today
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName(SHEET_INBOUND);
if (!sh) { // → tells you if the sheet name is wrong
throw new Error('Sheet ' + SHEET_INBOUND + ' not found.');
}
const values = sh.getDataRange().getValues(); // → read the whole sheet at once
const header = values[0];
const iCont = header.indexOf('CONTAINER'); // → find the column positions
const iModel = header.indexOf('MODEL');
const iQty = header.indexOf('QTY');
const iEta = header.indexOf('ETA_DATE');
const iStat = header.indexOf('STATUS');
const tz = ss.getSpreadsheetTimeZone();
const today = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd'); // → today as text
const rows = [];
for (let i = 1; i < values.length; i++) { // → skip the header row
const r = values[i];
if (!r[iCont]) continue; // → ignore blank rows
if (String(r[iStat]).trim() === 'PUTAWAY_DONE') continue; // → skip finished ones
const eta = r[iEta] instanceof Date
? Utilities.formatDate(r[iEta], tz, 'yyyy-MM-dd')
: String(r[iEta]).slice(0, 10); // → works if the date is text too
if (eta !== today) continue; // → keep today's arrivals only
rows.push({
container: String(r[iCont]),
model: String(r[iModel]),
qty: r[iQty]
});
}
return rows; // → feeds the sidebar dropdown
}
function savePutaway(data) { // → stores the putaway location
const ss = SpreadsheetApp.getActive();
const inbound = ss.getSheetByName(SHEET_INBOUND);
const locations = ss.getSheetByName(SHEET_LOCATIONS);
if (!data.container || !data.location) { // → prevents saving empty values
throw new Error('Container and location are both required.');
}
locations.appendRow([ // → append one stock row
data.location,
data.container,
data.model,
data.qty,
new Date() // → timestamp
]);
const range = inbound.getDataRange();
const values = range.getValues();
const header = values[0];
const iCont = header.indexOf('CONTAINER');
const iPut = header.indexOf('PUTAWAY');
const iStat = header.indexOf('STATUS');
for (let i = 1; i < values.length; i++) { // → find and update the matching row
if (String(values[i][iCont]) === String(data.container)) {
values[i][iPut] = data.location; // → record the location
values[i][iStat] = 'PUTAWAY_DONE'; // → mark it done
break; // → stop at the first match
}
}
range.setValues(values); // → write the changes back
return data.container + ' → ' + data.location + ' saved'; // → message for the screen
}4. Full Sidebar.html — the input screen
In the same Apps Script editor, click Add file (＋) → HTML and name it Sidebar (the .html extension is added for you). Clear the default content and paste this.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
body { font-family: Arial, sans-serif; font-size: 13px; padding: 10px; }
label { display: block; margin-top: 10px; font-weight: bold; }
select, input { width: 100%; padding: 6px; box-sizing: border-box; }
button { width: 100%; margin-top: 14px; padding: 9px; font-weight: bold;
background: #1e3a5f; color: #fff; border: 0; border-radius: 5px; }
#msg { margin-top: 12px; font-size: 12px; }
</style>
</head>
<body>
<label>Containers due today</label>
<select id="containerSelect" onchange="onContainerChange()"></select>
<label>Model</label>
<input id="model" readonly>
<label>Quantity</label>
<input id="qty" readonly>
<label>Putaway location (e.g. B22-4)</label>
<input id="location" placeholder="rack-level">
<button onclick="save()">Save</button>
<div id="msg"></div>
<script>
let inboundRows = []; // → today's inbound list from the server
function loadInbound() { // → runs when the panel opens
google.script.run
.withSuccessHandler(function (rows) { // → fill the dropdown on success
inboundRows = rows;
const sel = document.getElementById('containerSelect');
sel.innerHTML = '<option value="">Select...</option>';
rows.forEach(function (r) {
const opt = document.createElement('option');
opt.value = r.container;
opt.text = r.container;
sel.appendChild(opt);
});
if (rows.length === 0) { // → nothing due today
document.getElementById('msg').innerText = 'No arrivals scheduled today.';
}
})
.withFailureHandler(function (e) { // → show the reason on screen
document.getElementById('msg').innerText = 'Load failed: ' + e.message;
})
.getTodayInbound();
}
function onContainerChange() { // → auto-fill model and quantity
const cont = document.getElementById('containerSelect').value;
const row = inboundRows.find(function (r) { return r.container === cont; });
document.getElementById('model').value = row ? row.model : '';
document.getElementById('qty').value = row ? row.qty : '';
}
function save() { // → when Save is pressed
const payload = {
container: document.getElementById('containerSelect').value,
model: document.getElementById('model').value,
qty: document.getElementById('qty').value,
location: document.getElementById('location').value.trim()
};
if (!payload.container || !payload.location) { // → check empty fields first
document.getElementById('msg').innerText = 'Container and location are required.';
return;
}
document.getElementById('msg').innerText = 'Saving...';
google.script.run
.withSuccessHandler(function (result) { // → refresh the list after saving
document.getElementById('msg').innerText = result;
document.getElementById('location').value = '';
loadInbound();
})
.withFailureHandler(function (e) {
document.getElementById('msg').innerText = 'Save failed: ' + e.message;
})
.savePutaway(payload);
}
loadInbound(); // → run as soon as the sidebar opens
</script>
</body>
</html>5. Installing and verifying
Save the editor (⌘S or Ctrl+S). Then select onOpen in the function list and press Run once. The first run asks for authorization: choose your account, and when the "app isn't verified" screen appears, click Advanced → Go to (project name) and allow it. You are running your own script against your own sheet, so this is expected.
Go back to the spreadsheet tab and refresh. A Warehouse menu appears at the top; Warehouse → Open Inbound UI shows the panel on the right.
To verify, add one test row to INBOUND with ETA_DATE set to today — that part matters. Reopen the sidebar: the container should appear in the dropdown, and after saving a location, LOCATIONS should gain a row while the INBOUND row flips to PUTAWAY_DONE.
6. Two errors you will probably hit
"Sheet INBOUND not found" means the tab name does not match the constant in the code. A trailing space in the tab name is the usual culprit.
An empty dropdown is almost always a date problem: either ETA_DATE is not today, or the date is stored as text in a format that does not parse. Select the column and set Format → Number → Date, then try again.
7. Wrapping up
That is the skeleton of the warehouse inventory system. The key idea is that people never touch the sheet directly — they only use the screen you gave them, and input mistakes drop noticeably as a result.
Leave this code in place; the next part adds outbound processing, followed by timesheet automation, automated reports, and rack-layout visualization, one part at a time. Start by pasting today's code into your own sheet and testing it with a single row.