Barcode lookup in Google Sheets

/**
 * Barcode Report for Google Sheets
 * https://barcodereport.com/barcode-lookup-google-sheets
 *
 * Look up product names, brands and categories by UPC, EAN, GTIN or ISBN
 * straight from a spreadsheet.
 *
 *   =BARCODELOOKUP(A2)                          product title for the code in A2
 *   =BARCODELOOKUP(A2, "brand")                 one field
 *   =BARCODELOOKUP(A2:A500, "title,brand,category")
 *                                               several fields for a whole column
 *
 * Setup (once per spreadsheet):
 *   1. Extensions > Apps Script. Paste this whole file, replacing what is there, and save.
 *   2. Reload the spreadsheet. A "Barcode Report" menu appears.
 *   3. Barcode Report > Set API key, and paste your key. Google asks you to allow
 *      the script to connect to barcodereport.com the first time.
 *   Free key (100 lookups a month): https://barcodereport.com/register
 *
 * Each code looked up uses one lookup from your allowance, including codes we
 * don't have. Results are cached for 6 hours so a recalculation doesn't charge
 * you again; to keep results for good, copy the cells and Paste special >
 * Values only. Use one formula over a whole column rather than one per row:
 * it goes out as batches of 100 codes, which is faster and stays well inside
 * the API's per-minute rate limit.
 *
 * The API key is stored in this spreadsheet's document properties, so anyone
 * who can edit the spreadsheet's script can read it. Don't put a paid key in a
 * sheet you share for editing.
 *
 * MIT License. Copyright (c) 2026 Barcode Report.
 */

var BR_API = 'https://barcodereport.com/api/v1/';
var BR_KEY_PROPERTY = 'BARCODEREPORT_API_KEY';
var BR_CACHE_SECONDS = 21600; // 6 hours, the Cache service maximum
var BR_BATCH = 100;           // POST /api/v1/lookup accepts up to 100 codes
var BR_MAX_UNCACHED = 1000;   // 10 batches: inside the 30 s limit and the batch rate limit
var BR_NOT_FOUND = 'NOT FOUND';
var BR_FIELDS = ['barcode', 'title', 'brand', 'category', 'description', 'image_url', 'min_price',
  'price_currency', 'country_of_registration', 'model_number', 'color', 'size', 'weight',
  'product_dimension', 'quantity', 'authors', 'publisher', 'publish_date', 'number_of_pages',
  'upc_a', 'ean_13', 'gtin_14', 'amazon_asin', 'attribution'];

/**
 * Looks up products by barcode (UPC, EAN, GTIN or ISBN) with Barcode Report.
 *
 * @param {A2:A100} codes A barcode or a range of barcodes.
 * @param {"title,brand"} fields Optional. Comma-separated fields to return: title (default),
 *     brand, category, description, image_url, min_price, price_currency,
 *     country_of_registration, authors, publisher, publish_date, number_of_pages, upc_a,
 *     ean_13, gtin_14, amazon_asin, quantity, size, color, weight, model_number. "all" gives
 *     title, brand, category, description and image_url.
 * @return Product data, one row per code and one column per field.
 * @customfunction
 */
function BARCODELOOKUP(codes, fields) {
  var cols = brParseFields_(fields);
  var grid = Array.isArray(codes) ? codes : [[codes]];
  var flat = [];
  grid.forEach(function (row) {
    (Array.isArray(row) ? row : [row]).forEach(function (v) { flat.push(brNormalise_(v)); });
  });
  var wanted = flat.filter(function (c, i) { return c && flat.indexOf(c) === i; });
  var products = brLookup_(wanted);

  var out = flat.map(function (code) {
    if (!code) return cols.map(function () { return ''; });
    var p = products[code];
    if (!p) return cols.map(function (f, i) { return i === 0 ? BR_NOT_FOUND : ''; });
    return cols.map(function (f) { return p[f] === null || p[f] === undefined ? '' : p[f]; });
  });
  // A single cell with a single field returns a plain value, not a 1x1 array.
  return out.length === 1 && cols.length === 1 ? out[0][0] : out;
}

/**
 * Searches products by name, brand or category and returns barcode, title and brand.
 *
 * @param {"nutella 400g"} query What to search for.
 * @param {10} limit Optional. How many results, 1 to 50 (default 10). Uses one lookup.
 * @return Matching products: barcode, title, brand, one row each.
 * @customfunction
 */
function BARCODESEARCH(query, limit) {
  query = String(query || '').trim();
  if (!query) return '';
  var n = Math.max(1, Math.min(50, parseInt(limit, 10) || 10));
  var cache = CacheService.getDocumentCache();
  var cacheKey = 'brs:' + n + ':' + query.toLowerCase().slice(0, 200);
  var hit = cache && cache.get(cacheKey);
  var body = hit ? JSON.parse(hit) : brRequest_('GET', 'search?q=' + encodeURIComponent(query) + '&per_page=' + n);
  if (!hit && cache) cache.put(cacheKey, JSON.stringify(body), BR_CACHE_SECONDS);
  if (!body.results || !body.results.length) return BR_NOT_FOUND;
  return body.results.map(function (r) { return [r.barcode, r.title || '', r.brand || '']; });
}

/** Adds the Barcode Report menu. Runs when the spreadsheet opens. */
function onOpen() {
  SpreadsheetApp.getUi().createMenu('Barcode Report')
    .addItem('Set API key', 'brSetApiKey')
    .addItem('Check remaining lookups', 'brShowUsage')
    .addItem('Clear cached results', 'brClearCache')
    .addToUi();
}

function brSetApiKey() {
  var ui = SpreadsheetApp.getUi();
  var res = ui.prompt('Barcode Report API key',
    'Paste the key from https://barcodereport.com/dashboard. No account yet? A free key with 100 lookups a ' +
    'month is at https://barcodereport.com/register', ui.ButtonSet.OK_CANCEL);
  if (res.getSelectedButton() !== ui.Button.OK) return;
  var key = res.getResponseText().trim();
  if (!key) return;
  PropertiesService.getDocumentProperties().setProperty(BR_KEY_PROPERTY, key);
  try {
    var acct = brRequest_('GET', 'account');
    ui.alert('Saved. ' + brUsageText_(acct));
  } catch (e) {
    ui.alert('Saved, but the test call failed: ' + e.message);
  }
}

function brShowUsage() {
  SpreadsheetApp.getUi().alert(brUsageText_(brRequest_('GET', 'account')));
}

function brClearCache() {
  // The Cache service has no "clear all"; bumping the namespace orphans old entries.
  PropertiesService.getDocumentProperties().setProperty('BARCODEREPORT_CACHE_NS', String(Date.now()));
  SpreadsheetApp.getUi().alert('Cached results cleared. Edit a formula to look the codes up again.');
}

function brUsageText_(acct) {
  var line = (acct.plan_name || 'Your plan') + ': ' + acct.api_usage + ' of ' + acct.plan_limit + ' lookups used';
  if (acct.usage_reset_at) line += ', resets ' + String(acct.usage_reset_at).slice(0, 10);
  return line + '. More lookups: https://barcodereport.com/pricing';
}

/** Codes -> {code: product}. Missing codes are absent. Uses the cache, then batches of 100. */
function brLookup_(codes) {
  var found = {};
  if (!codes.length) return found;
  var cache = CacheService.getDocumentCache();
  var ns = PropertiesService.getDocumentProperties().getProperty('BARCODEREPORT_CACHE_NS') || '0';
  var keyFor = function (c) { return 'br:' + ns + ':' + c; };
  var cached = cache ? cache.getAll(codes.map(keyFor)) : {};
  var todo = [];
  codes.forEach(function (c) {
    var v = cached[keyFor(c)];
    if (v === undefined || v === null) todo.push(c);
    else if (v !== '0') found[c] = JSON.parse(v);
  });

  if (todo.length > BR_MAX_UNCACHED) {
    throw new Error('Barcode Report: ' + todo.length + ' new codes in one formula. Split the list into ranges of ' +
      'up to ' + BR_MAX_UNCACHED + ' rows (a formula has 30 seconds to finish).');
  }
  for (var i = 0; i < todo.length; i += BR_BATCH) {
    var chunk = todo.slice(i, i + BR_BATCH);
    var body = brRequest_('POST', 'lookup', { barcodes: chunk });
    var toCache = {};
    (body.results || []).forEach(function (r) {
      if (r.found && r.product) {
        found[r.requested_barcode] = r.product;
        toCache[keyFor(r.requested_barcode)] = JSON.stringify(r.product);
      } else {
        toCache[keyFor(r.requested_barcode)] = '0';
      }
    });
    if (cache) cache.putAll(toCache, BR_CACHE_SECONDS);
  }
  return found;
}

function brRequest_(method, path, payload) {
  var key = PropertiesService.getDocumentProperties().getProperty(BR_KEY_PROPERTY);
  if (!key) {
    throw new Error('Add your Barcode Report API key: Barcode Report menu > Set API key. ' +
      'Free key: https://barcodereport.com/register');
  }
  var options = {
    method: method.toLowerCase(),
    headers: { 'X-API-Key': key },
    muteHttpExceptions: true,
  };
  if (payload) {
    options.contentType = 'application/json';
    options.payload = JSON.stringify(payload);
  }
  var res = UrlFetchApp.fetch(BR_API + path, options);
  var status = res.getResponseCode();
  var body = {};
  try { body = JSON.parse(res.getContentText() || '{}'); } catch (e) { body = {}; }
  if (status >= 200 && status < 300) return body;
  var code = body.code || ('http_' + status);
  var messages = {
    missing_key: 'Add your API key: Barcode Report menu > Set API key.',
    invalid_key: 'That API key is not recognised. Copy it again from https://barcodereport.com/dashboard.',
    quota_exceeded: 'Your monthly lookups are used up. More at https://barcodereport.com/pricing',
    rate_limited: 'Too many requests a minute. Use one formula over the whole column instead of one per row.',
    bad_request: body.error || 'The request was not accepted.',
  };
  throw new Error('Barcode Report: ' + (messages[code] || body.error || ('HTTP ' + status)) + ' (' + code + ')');
}

/** Field list -> column names. */
function brParseFields_(fields) {
  var s = String(fields === undefined || fields === null || fields === '' ? 'title' : fields).toLowerCase().trim();
  if (s === 'all') return ['title', 'brand', 'category', 'description', 'image_url'];
  var cols = s.split(/[\s,;]+/).filter(String);
  cols.forEach(function (f) {
    if (BR_FIELDS.indexOf(f) < 0) throw new Error('Unknown field "' + f + '". Try: ' + BR_FIELDS.join(', '));
  });
  return cols.length ? cols : ['title'];
}

/**
 * Cell value -> code string. Sheets drops leading zeros when a code is stored
 * as a number, so a UPC-A typed as 049000050103 arrives as 49000050103. Put the
 * zero back for 11-digit numbers (UPC-A) and 9-digit numbers (ISBN-10).
 * Formatting the column as Plain text avoids the problem entirely.
 */
function brNormalise_(v) {
  if (v === null || v === undefined || v === '') return '';
  if (typeof v === 'number') {
    if (!isFinite(v) || v < 0) return '';
    var digits = String(Math.round(v));
    if (digits.length === 11) digits = '0' + digits;
    else if (digits.length === 9) digits = '0' + digits;
    return digits;
  }
  return String(v).trim().replace(/[\s-]/g, '').slice(0, 32);
}