Build a Warehouse UI in Google Sheets Apps Script
If you’re looking for a way to build a warehouse management UI in Google Sheets, the core idea is simple: use four sheets (INBOUND·LOCATIONS·FLOOR_MAP·SETTINGS) plus one Apps Script sidebar to handle inbound check, putaway location assignment, and floor map visualization all in one flow. You can do this with just Google Sheets and Apps Script, essentially at zero cost, without a separate WMS.
On the floor, many teams still handle “check inbound by container → decide where to store → later search where it is” with Excel plus verbal communication. This post summarizes a minimum reproducible flow and code skeleton based on something I’ve actually used.
1. Build the basic structure with 4 sheets
First, create the following four sheets in your Google Sheet.
1) INBOUND – inbound plan, inspection, and putaway result
- A: ETA_DATE (YYYY-MM-DD)
- B: CONTAINER (e.g., CTR-00001)
- C: MODEL (e.g., MODEL-A)
- D: QTY
- E: PUTAWAY (e.g.,
B22-4) - F: STATUS (PLANNED / RECEIVED / PUTAWAY_DONE, etc.)
→ Upload each day’s inbound containers here and show only “today’s ETA” in the sidebar.
2) LOCATIONS – actual inventory table
- A: LOCATION (e.g., B22-4)
- B: CONTAINER
- C: MODEL
- D: QTY
- E: TIMESTAMP
→ When inbound is confirmed, append one row to this sheet. This becomes the source of truth for stock by location.
3) FLOOR_MAP – rack/slot visualization
- Example: use range B18~G24 as the warehouse rack view
- Columns (B~G): rack columns
- Rows (18~24): rack levels
- In each cell, display slots 1–6 separated by line breaks
- Apps Script will draw colors and text into this range.
4) SETTINGS – parameter collection
- Later move constants such as FLOOR_MAP_RANGE, MAX_SLOTS, color levels, etc. here for easier maintenance.
2. Apps Script: menu and sidebar skeleton code
In the spreadsheet, go to Extensions → Apps Script and put the following basic skeleton into Code.gs.
```javascript
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('창고')
.addItem('입고 UI 열기', 'showInboundSidebar')
.addToUi();
}
function showInboundSidebar() {
const html = HtmlService.createHtmlOutputFromFile('Sidebar')
.setTitle('입고 / Putaway');
SpreadsheetApp.getUi().showSidebar(html);
}
function getTodayInbound() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('INBOUND');
const values = sh.getDataRange().getValues();
const today = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), 'yyyy-MM-dd');
const header = values[0];
const etaIdx = header.indexOf('ETA_DATE');
const result = values.filter((row, i) =>
i > 0 && row[etaIdx] && row[etaIdx].toString().slice(0,10) === today
);
return result; // Used as dropdown data in the Sidebar
}
function savePutaway(data) {
// data: {container, model, qty, location}
const ss = SpreadsheetApp.getActive();
const inbound = ss.getSheetByName('INBOUND');
const locations = ss.getSheetByName('LOCATIONS');
// 1) Write to LOCATIONS
locations.appendRow([
data.location,
data.container,
data.model,
data.qty,
new Date()
]);
// 2) Update INBOUND (simple example: find first row by container)
const range = inbound.getDataRange();
const values = range.getValues();
const header = values[0];
const contIdx = header.indexOf('CONTAINER');
const putIdx = header.indexOf('PUTAWAY');
const statIdx = header.indexOf('STATUS');
for (let i = 1; i < values.length; i++) {
if (values[i][contIdx] === data.container) {
values[i][putIdx] = data.location;
values[i][statIdx] = 'PUTAWAY_DONE';
break;
}
}
range.setValues(values);
refreshFloorMap();
}
function refreshFloorMap() {
const ss = SpreadsheetApp.getActive();
const locSh = ss.getSheetByName('LOCATIONS');
const mapSh = ss.getSheetByName('FLOOR_MAP');
const setSh = ss.getSheetByName('SETTINGS');
// Example: SETTINGS!A1 = FLOOR_MAP_RANGE, A2 = MAX_SLOTS
const floorRangeA1 = setSh.getRange('A1').getValue() || 'B18:G24';
const maxSlots = setSh.getRange('A2').getValue() || 6;
const mapRange = mapSh.getRange(floorRangeA1);
const colors = [];
const texts = [];
// Initialize defaults
for (let r = 0; r < mapRange.getNumRows(); r++) {
colors[r] = [];
texts[r] = [];
for (let c = 0; c < mapRange.getNumColumns(); c++) {
colors[r][c] = '#ffffff';
texts[r][c] = '';
}
}
// Read LOCATIONS and build slot count/container text per cell
// (Add logic to map LOCATION → cell coordinates to fit your operation)
// ...
mapRange.setBackgrounds(colors);
mapRange.setValues(texts);
mapRange.setNumberFormat('@'); // Force as text
}
```
These functions cover the basic flow of “menu → open sidebar → fetch today’s inbound → save putaway → refresh floor map.”
3. Sidebar.html: build inbound UI with google.script.run
Add a Sidebar.html file to build the sidebar UI. The key is to call Apps Script functions with google.script.run.functionName() when buttons are clicked.
```html
<!DOCTYPE html>
<html>
<body>
<h3>입고 / Putaway</h3>
<label>Select container</label>
<select id="containerSelect" onchange="onContainerChange"></select>
<div id="info"></div>
<h4>Select location</h4>
<div id="loc-ui">
<!-- Create selection buttons for B~G / 18~24 / 1~6 via JS -->
</div>
<button onclick="save()">Save</button>
<script>
let inboundRows = [];
let selected = { container: '', model: '', qty: 0, location: '' };
function loadInbound() {
google.script.run
.withSuccessHandler(function(rows){
inboundRows = rows;
const sel = document.getElementById('containerSelect');
rows.forEach(function(r){
const opt = document.createElement('option');
opt.value = r[1]; // CONTAINER
opt.text = r[1];
sel.appendChild(opt);
});
})
.getTodayInbound();
}
function onContainerChange() {
const cont = document.getElementById('containerSelect').value;
const row = inboundRows.find(r => r[1] === cont);
if (!row)