# Amazon Consolidated Dashboard System --- ## Context Operational data in Amazon FBA environments is fragmented across multiple reporting layers: - Listing reports (identity + catalog structure) - Inventory snapshots (stock + fulfillment state) - Sales aggregation metrics (velocity signals) - Keepa enrichment (images + metadata) Individually useful. Collectively powerful. This system merges them into one operational truth layer. --- ## Core Idea > One listing spine. Everything else attaches. By treating the **Active Listing Report** as the core spine, we iterate through every SKU and attach corresponding data from other reports. This ensures our dashboard only shows items we actively have listings for, reducing noise from legacy or deleted inventory data. --- ## Data Architecture The system expects three primary data exports pasted into specific sheets: 1. **`All_Listing_Report`**: The source of truth for active SKUs, ASINs, Item Names, and Listing Status. 2. **`FBA_Inventory_Snapshot`**: Contains stock levels (available, inbound, reserved) and velocity metrics (7, 30, 60-day sales). 3. **`Keepa`**: A custom export containing ASINs and Keepa image URLs to provide visual context in the dashboard. --- ## System Code ### 1. Google Apps Script — `Code.gs` ```javascript /** * @OnlyCurrentDoc * Amazon Consolidated Dashboard System * Single-source operational intelligence layer */ const CONFIG = { sheets: { listing: 'All_Listing_Report', inventory: 'FBA_Inventory_Snapshot', keepa: 'Keepa', dashboard: 'Dashboard', }, headers: { listingSku: 'seller-sku', asin: 'asin1', itemName: 'item-name', status: 'status', invSku: 'sku', available: 'available', inbound: 'inbound-quantity', daysOfSupply: 'days-of-supply', unfulfillable: 'unfulfillable-quantity', reservedCustomer: 'Reserved Customer Order', reservedTransfer: 'Reserved FC Transfer', reservedProcessing: 'Reserved FC Processing', salesT7: 'sales-shipped-last-7-days', salesT30: 'sales-shipped-last-30-days', salesT60: 'sales-shipped-last-60-days', keepaAsin: 'ASIN', keepaImage: 'Image', }, image: { width: 100, }, }; function onOpen() { SpreadsheetApp.getUi() .createMenu('Dashboard Tools') .addItem('Open Dashboard Controls', 'showSidebar') .addToUi(); } function showSidebar() { const html = HtmlService.createHtmlOutputFromFile('Sidebar') .setTitle('Dashboard Controls') .setWidth(300); SpreadsheetApp.getUi().showSidebar(html); } function createConsolidatedDashboard(contextString) { const ss = SpreadsheetApp.getActiveSpreadsheet(); return _generateDashboard(ss, contextString); } function _generateDashboard(ss, contextString) { const listing = _getSheet(ss, CONFIG.sheets.listing); const inventory = _getSheet(ss, CONFIG.sheets.inventory); const keepa = _getSheet(ss, CONFIG.sheets.keepa); const listingData = _read(listing); const inventoryData = _read(inventory); const keepaData = _read(keepa); const inventoryMap = buildInventoryMap( inventoryData.headers, inventoryData.rows, ); const salesMap = buildSalesMap(inventoryData.headers, inventoryData.rows); const keepaMap = buildKeepaMap(keepaData.headers, keepaData.rows); const li = listingData.headers; const skuI = li.indexOf(CONFIG.headers.listingSku); const asinI = li.indexOf(CONFIG.headers.asin); const nameI = li.indexOf(CONFIG.headers.itemName); const statusI = li.indexOf(CONFIG.headers.status); const output = []; listingData.rows.forEach((r) => { const sku = r[skuI]; if (!sku) return; const asin = r[asinI]; const inv = inventoryMap[sku] || {}; const sales = salesMap[sku] || {}; let image = `=IMAGE("https://via.placeholder.com/100")`; let link = ''; if (asin) { if (keepaMap[asin]) { image = `=IMAGE("${keepaMap[asin]}")`; } link = `https://www.amazon.com/dp/${asin}`; } output.push([ image, r[nameI], asin, sku, link, r[statusI], inv.reservedCustomer || 0, inv.available || 0, inv.reservedTransfer || 0, inv.reservedProcessing || 0, inv.inbound || 0, inv.daysOfSupply || 0, inv.unfulfillable || 0, sales.t7 || 0, sales.t60minus30 || 0, sales.t30 || 0, (sales.t30 || 0) - (sales.t60minus30 || 0), ]); }); const sheet = _getOrCreate(ss, CONFIG.sheets.dashboard); sheet.clear(); const headers = [ 'Image', 'Name', 'ASIN', 'SKU', 'Amazon Link', 'Status', 'Customer Orders', 'Available', 'FC Transfers', 'FC Processing', 'Inbound', 'Days of Supply', 'Unfulfillable', 'Sales 7D', 'Sales 31–60D', 'Sales 30D', 'Delta', ]; sheet.getRange(1, 1, 1, headers.length).setValues([headers]); if (output.length) { sheet.getRange(2, 1, output.length, output[0].length).setValues(output); } return `Dashboard updated: ${output.length} SKUs processed.`; } function buildInventoryMap(headers, rows) { const m = {}; const i = indexMap(headers); rows.forEach((r) => { const sku = r[i['sku']]; if (!sku) return; m[sku] = { available: num(r[i['available']]), inbound: num(r[i['inbound-quantity']]), daysOfSupply: num(r[i['days-of-supply']]), unfulfillable: num(r[i['unfulfillable-quantity']]), reservedCustomer: num(r[i['Reserved Customer Order']]), reservedTransfer: num(r[i['Reserved FC Transfer']]), reservedProcessing: num(r[i['Reserved FC Processing']]), }; }); return m; } function buildSalesMap(headers, rows) { const m = {}; const i = indexMap(headers); rows.forEach((r) => { const sku = r[i['sku']]; if (!sku) return; const t7 = num(r[i['sales-shipped-last-7-days']]); const t30 = num(r[i['sales-shipped-last-30-days']]); const t60 = num(r[i['sales-shipped-last-60-days']]); m[sku] = { t7, t30, t60minus30: Math.max(0, t60 - t30), }; }); return m; } function buildKeepaMap(headers, rows) { const m = {}; const i = indexMap(headers); rows.forEach((r) => { const asin = r[i['ASIN']]; const img = r[i['Image']]; if (asin && img) m[asin] = img; }); return m; } function _getSheet(ss, name) { const s = ss.getSheetByName(name); if (!s) throw new Error(`Missing sheet: ${name}`); return s; } function _getOrCreate(ss, name) { return ss.getSheetByName(name) || ss.insertSheet(name); } function _read(sheet) { const values = sheet.getDataRange().getValues(); return { headers: values[0].map(String), rows: values.slice(1), }; } function indexMap(headers) { const m = {}; headers.forEach((h, i) => (m[h] = i)); return m; } function num(v) { return parseFloat(v) || 0; } ``` ### How the Core Script Works 1. **O(1) Data Lookups**: Instead of using nested loops or slow spreadsheet formulas like `VLOOKUP`, the script builds memory-efficient JavaScript objects (`buildInventoryMap`, `buildSalesMap`, `buildKeepaMap`). These act as dictionaries, allowing instant data retrieval when we iterate through the listing spine. 2. **Velocity Delta**: The script automatically calculates the difference between the last 30 days of sales and the prior 30 days (`sales.t30 - sales.t60minus30`), giving immediate visual feedback on whether a product's velocity is accelerating or decaying. 3. **Image Rendering**: It utilizes the Keepa image URL wrapped in the spreadsheet `=IMAGE()` function to provide a visual cue for each row, falling back to a placeholder if missing. --- ## 2. Sidebar UI — `Sidebar.html` To make the system user-friendly for operators who shouldn't touch the script editor, we implement a simple sidebar UI. ```html

Dashboard Control

``` ### How the UI Works - It runs inside the Google Sheets environment via `HtmlService`. - A button triggers the `createConsolidatedDashboard` function asynchronously. - Success and error handlers provide immediate visual feedback to the user on the dashboard generation status. --- ## Closing State This system compresses Amazon FBA operational complexity into a single execution layer. No extra tabs. No fragmented views. Just one dashboard that behaves like an operational cockpit.