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

Google Sheets warehouse rack layout visualization | Apps Script auto-color + locking

Intro: When you want to see inventory at a glance with colors, not numbers

When you manage warehouse inventory in Google Sheets, you can usually see the quantity by location, but it’s often not obvious which racks are full or which areas are almost empty. This post focuses on Google Sheets warehouse rack layout visualization: we’ll draw the actual rack shapes on the FLOOR_MAP sheet and set things up so that cell background colors change automatically based on inventory levels. By the end of this post, you’ll have a FLOOR_MAP sheet whose cell colors update automatically.

I’ll assume you’ve already followed the earlier posts, Building a warehouse inventory management system in Google Sheets | Apps Script automation and Automatic inventory aggregation in Google Sheets | Apps Script Part 3, where we built a structure that automatically aggregates current stock into the STOCK sheet based on inbound and outbound movements. This post adds the FLOOR_MAP rack layout + color visualization layer on top of that. If your STOCK sheet already has current quantities per location, you can apply the code below as-is and end up with a FLOOR_MAP sheet whose FLOOR_MAP area colors are automatically refreshed.

In a real environment, multiple users may be processing outbound orders or running aggregations at the same time, so it’s safer to wrap the refreshFloorMap() code with LockService so it doesn’t collide when saved/executed concurrently. The complete code below already includes this locking logic.

Designing FLOOR_MAP: draw rack shapes in the sheet first

The first thing you need to do when building a warehouse rack layout in Google Sheets is to clearly define the FLOOR_MAP sheet structure. If you map rack columns and levels to sheet columns and rows in a way that resembles the actual racks, it becomes much easier to write Google Sheets Apps Script warehouse management code that applies colors later.

In practice, it’s usually designed like this: create a sheet named FLOOR_MAP, and leave a few rows at the top blank for titles, legends, filter buttons, and so on. For example, you might designate columns B through G and rows 18 through 24 as the rack-layout area, mapping the B18:G24 range to your actual rack positions. Each cell represents a single warehouse location (e.g., A01-01-01). Once this is set up, the code can refer to the entire rack block with a single line like FLOOR_RANGE_A1 = 'B18:G24'.

A key point here is to decide in advance how your location code rules will map to sheet coordinates. For example, if rack columns increase from left to right and levels increase from top to bottom, floor staff can look at FLOOR_MAP and easily find the corresponding physical rack. In a real warehouse, if you flip the direction after the initial design, it will remain confusing forever, so it’s best to place the physical drawing and the sheet side by side and carefully match them once.

It also helps to create a small color legend near the top-left or right side of FLOOR_MAP. For example, you might label “Full (red = COLOR_FULL) / Moderate (yellow = COLOR_OK) / Empty (gray = COLOR_EMPTY)” and show actual colored cells in the sheet. This makes the Google Sheets inventory status visualization semantics obvious to everyone. The legend isn’t directly linked to the code, but it serves as a reference whenever you discuss color policies with the field team.

Designing SETTINGS: manage thresholds and colors in a sheet

You could hard-code colors as numbers directly in Apps Script, but in real operations you’ll frequently want to change rack capacity or the definition of “full.” That’s why I always recommend collecting configuration values in a separate sheet and having the code read them. This lets you adjust thresholds during operations without modifying the code.

Create a new sheet named SETTINGS and prepare a table like this. In this post, all thresholds are expressed as integer percentages from 0 to 100.

• Column A: key name
• Column B: value

Example structure:

• MAX_PER_LOCATION / 100 → maximum quantity per location (example value)
• WARN_THRESHOLD / 50 → warning threshold (%)
• FULL_THRESHOLD / 90 → full threshold (%)
• COLOR_EMPTY / #eeeeee → gray when the quantity is 0 or very low
• COLOR_OK / #fff2cc → yellow when above warning but below full
• COLOR_FULL / #f4cccc → red when at or above full

In the code, we calculate usage ratio as qty / MAX_PER_LOCATION, then compare this to WARN_THRESHOLD / 100 and FULL_THRESHOLD / 100 to decide the color. For example, if MAX_PER_LOCATION = 100, WARN_THRESHOLD = 50, and FULL_THRESHOLD = 90, then 0 units will be gray, 1–49 will be below the yellow threshold (pre-warning), 50–89 will be yellow, and 90 or more will be red (full).

With this structure, you can keep the Google Sheets setBackgrounds color automation logic unchanged while freely tweaking the thresholds and colors in the SETTINGS sheet. If the field requests “we’d like to see the yellow zone a bit earlier,” you can, for example, change WARN_THRESHOLD to 40. If you mistakenly set MAX_PER_LOCATION to 0, the code will treat the ratio as 0% and handle everything as close to COLOR_EMPTY while also surfacing a configuration error message, prompting you to verify the settings again.

FLOOR_MAP refresh overview: read STOCK and apply colors

Now let’s build the core refreshFloorMap() function. It reads per-location quantities from the STOCK sheet, writes location codes and quantities into FLOOR_MAP cells, and performs Google Sheets setBackgrounds color automation. The general flow is:

1. Read maximum quantity, warning threshold, full threshold, and color values from the SETTINGS sheet.
2. Read inventory status from the STOCK sheet and build an object like location code → quantity.
3. For each location code written in the FLOOR_MAP rack range (B18:G24), look up its quantity in STOCK.
4. Compute usage ratio as qty / MAX_PER_LOCATION, compare against thresholds, and choose one of the colors: empty, moderate, or full.
5. Write cell text in the form location code + line break + quantity, and call setValues and setBackgrounds to apply everything in one shot.

If you connect this with the earlier post, Google Sheets outbound automation | Apps Script Part 2, you could also call refreshFloorMap() automatically right after each outbound operation. But at first, it’s safer to operate via a manual refresh menu button in the toolbar. In this post, I’ll break the full code into functional pieces, so even if you’re not familiar with coding, you can follow step by step and complete it.

When multiple users process inbound/outbound operations at the same time, refreshFloorMap() might be triggered concurrently. Since those runs could overwrite the same range, you might see unexpected color results or temporary errors. To avoid this, the code below adds locking via LockService to prevent concurrent execution.

FLOOR_MAP / SETTINGS code structure: constants and settings reader

Step 1 — Collect sheet names and keys into constants

This code defines the FLOOR_MAP, STOCK, and SETTINGS sheet names, plus SETTINGS keys, all in one place.

Where to paste: at the very top of the Code.gs file in the Apps Script editor.

What to do after pasting: just save (Ctrl+S or ⌘S).

// --- 시트 이름, 범위, SETTINGS 키 상수 정의 ---
// 여기만 본인 환경에 맞게 바꾸세요
const SHEET_FLOOR_MAP = 'FLOOR_MAP';       // → rack layout sheet name
const SHEET_STOCK     = 'STOCK';           // → inventory summary sheet name
const SHEET_SETTINGS  = 'SETTINGS';        // → settings sheet name

const FLOOR_RANGE_A1  = 'B18:G24';         // → rack range (columns = racks, rows = levels)

const KEY_MAX_PER_LOC    = 'MAX_PER_LOCATION';  // → max quantity key
const KEY_WARN_THRESHOLD = 'WARN_THRESHOLD';    // → warning ratio key (0–100)
const KEY_FULL_THRESHOLD = 'FULL_THRESHOLD';    // → full ratio key (0–100)

const KEY_COLOR_EMPTY = 'COLOR_EMPTY';     // → empty color key
const KEY_COLOR_OK    = 'COLOR_OK';        // → moderate color key
const KEY_COLOR_FULL  = 'COLOR_FULL';      // → full color key

How to confirm it works: if there are no red error markers in the editor, you’re fine.

Step 2 — Function to read thresholds and colors from SETTINGS

This code reads the key–value table in the SETTINGS sheet and converts it into a JavaScript object.

Where to paste: directly under the constants you just defined.

What to do after pasting: save, then click Run next to the getSettingsMap function and approve the authorization request.

/**
 * Read SETTINGS sheet (keys in col A, values in col B) and return as a Map-like object
 */
function getSettingsMap() {                             // → read SETTINGS into an object
  const ss = SpreadsheetApp.getActive();                // → get current spreadsheet
  const sh = ss.getSheetByName(SHEET_SETTINGS);         // → get SETTINGS sheet
  if (!sh) {                                            // → if sheet is missing
    throw new Error('Could not find SETTINGS sheet');   // → throw error
  }

  const lastRow = sh.getLastRow();                      // → last row number
  if (lastRow < 2) {                                    // → if no data rows
    throw new Error('SETTINGS sheet has no data');      // → throw error
  }

  const range = sh.getRange(2, 1, lastRow - 1, 2);      // → range A2:B[last]
  const values = range.getValues();                     // → read all key–value rows

  const map = {};                                       // → result object
  values.forEach(function(row) {                        // → process each row
    const key = String(row[0]).trim();                  // → key as string
    const value = row[1];                               // → raw value
    if (key) {                                          // → if key is not empty
      map[key] = value;                                 // → store in object
    }
  });

  return map;                                           // → return all settings
}

How to confirm it works: after running getSettingsMap, check the execution log; if you see something like {MAX_PER_LOCATION=100.0, WARN_THRESHOLD=50.0…}, it’s working.

STOCK / FLOOR_MAP integration: build stock map and apply colors

Step 3 — Function to fetch per-location quantities from STOCK

This code reads the STOCK sheet and builds data in the form location code → quantity. In this example, column A holds the location code and column B holds the current quantity. If your structure differs, adjust the column indexes.

Where to paste: under the getSettingsMap function.

What to do after pasting: save, then run buildStockMap to check authorization and data structure.

/**
 * Read STOCK sheet and return per-location quantities as a Map-like object
 * Example assumption: col A = location code, col B = current quantity
 */
function buildStockMap() {                              // → build an object from STOCK
  const ss = SpreadsheetApp.getActive();                // → get current spreadsheet
  const sh = ss.getSheetByName(SHEET_STOCK);            // → get STOCK sheet
  if (!sh) {                                            // → if sheet is missing
    throw new Error('Could not find STOCK sheet');      // → throw error
  }

  const lastRow = sh.getLastRow();                      // → last row number
  if (lastRow < 2) {                                    // → if no data rows
    return {};                                          // → return empty object
  }

  const range = sh.getRange(2, 1, lastRow - 1, 2);      // → range A2:B[last] (location, qty)
  const values = range.getValues();                     // → read values

  const stockMap = {};                                  // → result object
  values.forEach(function(row) {                        // → process each row
    const loc = String(row[0]).trim();                  // → location code
    const qty = Number(row[1]) || 0;                    // → quantity as number
    if (loc) {                                          // → if location exists
      stockMap[loc] = qty;                              // → store in object
    }
  });

  return stockMap;                                      // → return complete stock map
}

How to confirm it works: if the execution log shows something like {A01-01-01=10.0, A01-01-02=0.0…}, it’s working correctly.

Step 4 — Refresh FLOOR_MAP: auto-update cell values and colors (with LockService)

This is the main refreshFloorMap() function. For each cell in FLOOR_RANGE_A1 (B18:G24), it reads the location code, looks up the quantity in STOCK, and updates both the cell text and background color. WARN_THRESHOLD and FULL_THRESHOLD are assumed to be percentages in the range 0–100, and the code divides them by 100 for ratio comparison.

Additionally, LockService is used so that even if two users trigger refresh at the same time, only one execution will modify FLOOR_MAP at once. If the script fails to obtain a lock, it retries for 3 seconds; if it still can’t, it throws an exception with a “please try again shortly” message.

Where to paste: under the buildStockMap function.

What to do after pasting: save, then run refreshFloorMap once and approve the authorization request.

/**
 * Refresh the rack layout area (B18:G24) of the FLOOR_MAP sheet,
 * updating location text and quantities and applying background colors.
 * Uses LockService to prevent concurrent execution conflicts.
 */
function refreshFloorMap() {                                      // → visualize FLOOR_MAP
  // --- Prevent concurrent execution with LockService ---
  const lock = LockService.getDocumentLock();                     // → document-level lock
  let hasLock = false;
  try {
    // Try to obtain the lock for up to 3 seconds
    hasLock = lock.tryLock(3000);
    if (!hasLock) {
      throw new Error('Another user is updating the rack layout. Please try again in a moment.');
    }

    const ss = SpreadsheetApp.getActive();                        // → get current spreadsheet
    const shMap = ss.getSheetByName(SHEET_FLOOR_MAP);             // → FLOOR_MAP sheet
    if (!shMap) {                                                 // → if sheet is missing
      throw new Error('Could not find FLOOR_MAP sheet');          // → throw error
    }

    const settings = getSettingsMap();                            // → read settings
    const maxPerLoc = Number(settings[KEY_MAX_PER_LOC]) || 0;     // → max quantity per location
    const warnRatio  = Number(settings[KEY_WARN_THRESHOLD]) || 0; // → warning ratio (%)
    const fullRatio  = Number(settings[KEY_FULL_THRESHOLD]) || 0; // → full ratio (%)

    const colorEmpty = String(settings[KEY_COLOR_EMPTY] || '#eeeeee'); // → empty color
    const colorOk    = String(settings[KEY_COLOR_OK]    || '#fff2cc');  // → moderate color
    const colorFull  = String(settings[KEY_COLOR_FULL]  || '#f4cccc');  // → full color

    if (!maxPerLoc || !warnRatio || !fullRatio) {                 // → if any key value is missing
      throw new Error('Please check MAX_PER_LOCATION, WARN_THRESHOLD, and FULL_THRESHOLD in the SETTINGS sheet'); // → throw error
    }

    const stockMap = buildStockMap();                             // → get stock map

    const range = shMap.getRange(FLOOR_RANGE_A1);                 // → get rack range
    const values = range.getValues();                             // → current cell values
    const newValues = [];                                         // → new values array
    const bgColors  = [];                                         // → background colors array

    for (let r = 0; r < values.length; r++) {                     // → loop rows
      const rowValues = values[r];                                // → row values
      const newRowValues = [];                                    // → new row values
      const rowColors = [];                                       // → new row colors

      for (let c = 0; c < rowValues.length; c++) {                // → loop columns
        const cellVal = String(rowValues[c]).trim();              // → cell text
        if (!cellVal) {                                           // → if no location
          newRowValues.push('');                                  // → keep empty
          rowColors.push(colorEmpty);                             // → empty color
          continue;                                               // → next cell
        }

        const loc = cellVal;                                      // → location code
        const qty = Number(stockMap[loc]) || 0;                   // → quantity

        const text = loc + '\n' + qty;                            // → code + quantity
        newRowValues.push(text);                                  // → set cell value

        let ratio = 0;                                            // → usage ratio
        if (maxPerLoc > 0) {                                      // → if we have a base
          ratio = qty / maxPerLoc;                                // → compute ratio
        }

        let color = colorEmpty;                                   // → default color
        if (qty === 0) {                                          // → if quantity is 0
          color = colorEmpty;                                     // → empty color
        } else if (ratio >= fullRatio / 100) {                    // → at or above full
          color = colorFull;                                      // → full color
        } else if (ratio >= warnRatio / 100) {                    // → at or above warning
          color = colorOk;                                        // → moderate color
        } else {                                                  // → below warning
          color = colorOk;                                        // → early moderate zone
        }

        rowColors.push(color);                                    // → add color
      }

      newValues.push(newRowValues);                               // → add row values
      bgColors.push(rowColors);                                   // → add row colors
    }

    range.setValues(newValues);                                   // → apply values in bulk
    range.setBackgrounds(bgColors);                               // → apply colors in bulk

  } finally {
    // Always release the lock if it was acquired
    if (hasLock) {
      lock.releaseLock();
    }
  }
}

How to confirm it works: enter actual location codes into FLOOR_MAP cells B18:G24, run refreshFloorMap, and check whether each cell changes to the “code (top line) + quantity (bottom line)” format and colors split into gray, yellow, and red according to quantity. Locations not present in STOCK will show 0 and be gray. If two people click the menu at the same time and one sees the message “Another user is updating the rack layout…,” then LockService is also working properly.

Menu integration and practical tips

Step 5 — Refresh via a menu button

To let warehouse staff refresh FLOOR_MAP without touching the Apps Script editor, add a custom menu to the Google Sheets toolbar.

Where to paste: at the very bottom of the same file (Code.gs).

What to do after pasting: save, then close and reopen the sheet to see the new menu.

/**
 * Add a "Warehouse tools" menu when the sheet opens
 */
function onOpen() {                                              // → run when sheet opens
  const ui = SpreadsheetApp.getUi();                             // → UI object
  ui.createMenu('Warehouse tools')                               // → new menu name
    .addItem('Refresh rack layout', 'refreshFloorMap')           // → menu item
    .addToUi();                                                  // → add menu to UI
}

How to confirm it works: after reopening the sheet, you should see a Warehouse tools menu at the top, and clicking Refresh rack layout should refresh colors on FLOOR_MAP.

Practical tips for running FLOOR_MAP reliably

Here are a few field-tested lessons from applying Google Sheets warehouse rack layout visualization in real operations:

1. Copy location codes directly from STOCK

When entering location codes into FLOOR_MAP, it’s best to copy them directly from the STOCK location list. Manually typing them often leads to differences in spaces or hyphen positions, which prevents matching with STOCK and shows 0 quantities. Even when building just a few areas at first, always copy from STOCK; this habit significantly reduces mistakes as you expand.

1. Start with simple thresholds

Instead of fine-tuning WARN_THRESHOLD and FULL_THRESHOLD from the start, it’s easier to use simple defaults like 50% and 90%. If MAX_PER_LOCATION is 100, then 49 and below feels “relatively empty,” 50 and above turns yellow to mean “more than half full,” and 90 and above turns red to mean “nearly full.” Once the team sees this visually and gives feedback, you can adjust to 60% and 80%, for example.

1. Validate on a small area first

It’s safer to validate with a minimal example for STOCK and FLOOR_MAP. For instance, enter only about 5 locations in the STOCK sheet and fill a 2×3 block in FLOOR_MAP, then run refreshFloorMap(). This helps you catch common problems early, such as MAX_PER_LOCATION being 0 or mis-typed SETTINGS keys.

1. Schedule with concurrency in mind

Although the code uses LockService to reduce conflicts, it’s still better not to overlap outbound/inbound automation triggers and FLOOR_MAP refresh triggers too tightly. If you use a time-based trigger for refreshFloorMap(), 5–10 minute intervals are more stable than every minute. Combine that with manual refresh via the menu when the field needs an up-to-date view.

1. Expand the range gradually

As your layout grows beyond B18:G24, rather than overcomplicating the script right away, it’s usually better to design a new version that deliberately expands FLOOR_RANGE_A1 or adds ranges step by step. Updating too many ranges at once in a single file can increase run time and collide with other Apps Scripts. Start by visualizing only core aisles or a subset of racks; once that runs reliably, expand in stages.

Conclusion: today’s goal is to build a pilot FLOOR_MAP area

In this post, we built a Google Sheets warehouse rack layout visualization: drawing rack shapes on the FLOOR_MAP sheet, managing thresholds and colors in a SETTINGS sheet, and reading STOCK data to apply text and background colors via setValues and setBackgrounds. By standardizing thresholds as 0–100 percentages and centralizing empty/moderate/full color codes in SETTINGS, you can later change thresholds or colors directly in the sheet.

We also included LockService in refreshFloorMap() to mitigate concurrent save/execution conflicts, so the system can run more stably even with multiple users on the same spreadsheet.

There’s one concrete action you can take right now.

If you already have locations and quantities in the STOCK sheet, create a small 2×3 test area (e.g., B18:G20) on FLOOR_MAP, paste in the full code from this post, fill the SETTINGS sheet with the example values, and then click Warehouse tools → Refresh rack layout once. Once you confirm that colors behave as expected in that small pilot area, scaling the same pattern to your full warehouse FLOOR_MAP becomes much easier.