Barcode Lookup Sheet: Copy-Paste Apps Script Pattern

What this script does

Prerequisites

Walkthrough

Edge cases

How to test

Hardening for production

Variations

Full code: barcodeLookupSheet()

Format barcode columns as text, fill Catalog, then run barcodeLookupSheet or install onEditBarcode.

/**
 * Look up a barcode scanned into a cell and write product fields beside it.
 */
const CATALOG = "Catalog"; // A barcode, B name, C price, D bin
const SCAN = "ScanEntry";  // A barcode input, B name, C price, D bin

function barcodeLookupSheet() {
  const ss = SpreadsheetApp.getActive();
  const cat = ss.getSheetByName(CATALOG).getDataRange().getValues();
  const map = {};
  for (let i = 1; i < cat.length; i++) {
    map[String(cat[i][0])] = { name: cat[i][1], price: cat[i][2], bin: cat[i][3] };
  }

  const sheet = ss.getSheetByName(SCAN);
  const values = sheet.getDataRange().getValues();
  for (let i = 1; i < values.length; i++) {
    const code = String(values[i][0] || "").trim();
    if (!code) continue;
    const hit = map[code];
    if (hit) {
      values[i][1] = hit.name;
      values[i][2] = hit.price;
      values[i][3] = hit.bin;
    } else {
      values[i][1] = "NOT_FOUND";
      values[i][2] = "";
      values[i][3] = "";
    }
  }
  sheet.getRange(1, 1, values.length, 4).setValues(values.map(function (r) { return r.slice(0, 4); }));
}

function onEditBarcode(e) {
  if (!e || !e.range) return;
  if (e.range.getSheet().getName() !== SCAN) return;
  if (e.range.getColumn() !== 1) return;
  barcodeLookupSheet();
}

1. Line 12: Indexes catalog by barcode string.
2. Line 26: Marks unknown scans.
3. Line 34: Installable trigger entry point.
4. Line 37: Ignores edits outside the barcode column.
5. Line 5: Sheet name guard for the trigger.