How to Look Up Barcodes in Google Sheets and Excel

To do a barcode lookup in Google Sheets, add a short Apps Script custom function that calls a barcode API. Then =BARCODE_LOOKUP(A2, "title") fills the cell with the product name. If you have more than a few dozen rows, use a menu script that sends 100 codes per request and writes plain values instead. Excel can do the same job with Power Query or Office Scripts. And if you'd rather not write any code, upload a CSV.

All the code below uses the BarcodeReport API. A free key gives you 100 lookups a month with no card, enough to build and test this on a sheet of your own codes.

Before anything else: keep the leading zeros

A lot of "not found" results in spreadsheets aren't missing products at all. They're damaged barcodes. Type 036000291452 into a cell formatted as a number and the sheet stores 36000291452. That's 11 digits, which isn't a valid UPC.

Excel stores numbers with 15 digits of precision. Barcodes are at most 14 digits, so you won't lose digits off the end, but you'll still lose zeros off the front and get 3.6E+10 on screen. The scripts below pad an 11-digit number back to 12 digits, which rescues the most common case: a UPC-A that starts with 0. They can't rescue an ISBN-10 like 0306406152, so format the column as text anyway.

Step 1: Store your API key in Script Properties

Why not just put the key in a cell? Because everyone who can view the sheet can read the cell, and so can anyone who gets a downloaded copy. Script Properties live in the script project, not the grid. One caveat: anyone with edit access to the spreadsheet can open the script project, so think of your editors as people who can see the key.

Step 2: The =BARCODE_LOOKUP custom function

Paste this into Code.gs, replacing what's there, and save.

const API_BASE = 'https://barcodereport.com/api';
const CACHE_SECONDS = 21600;          // 6 hours, the CacheService maximum
const NOT_FOUND = '__not_found__';

/**
 * Looks up a UPC, EAN, GTIN or ISBN and returns one field.
 * @param {string|Array} barcode A barcode, or a single-column range of them.
 * @param {string} field "title" (default), "brand", "category", "image_url"...
 * @return The field value(s).
 * @customfunction
 */
function BARCODE_LOOKUP(barcode, field) {
  field = field || 'title';
  if (Array.isArray(barcode)) return lookupRange_(barcode, field);
  const code = normalizeBarcode_(barcode);
  if (!code) return '';
  const product = getProduct_(code);
  return pick_(product, field);
}

function pick_(product, field) {
  if (!product) return 'Not found';
  return product[field] == null ? '' : product[field];
}

function apiKey_() {
  const key = PropertiesService.getScriptProperties().getProperty('BARCODEREPORT_API_KEY');
  if (!key) throw new Error('Add BARCODEREPORT_API_KEY under Project Settings > Script Properties');
  return key;
}

function normalizeBarcode_(value) {
  if (value === '' || value == null) return '';
  let code = String(value).replace(/[\s-]/g, '');
  // A UPC-A starting with 0 comes back as an 11-digit number.
  if (typeof value === 'number' && code.length === 11) code = '0' + code;
  return code;
}

function getProduct_(code) {
  const cache = CacheService.getScriptCache();
  const hit = cache.get('bc:' + code);
  if (hit === NOT_FOUND) return null;
  if (hit) return JSON.parse(hit);

  const res = UrlFetchApp.fetch(
    API_BASE + '/search_barcode?barcode=' + encodeURIComponent(code),
    { headers: { 'X-API-Key': apiKey_() }, muteHttpExceptions: true }
  );
  const status = res.getResponseCode();
  if (status === 404) {                       // not found still costs a lookup,
    cache.put('bc:' + code, NOT_FOUND, CACHE_SECONDS);   // so remember it
    return null;
  }
  if (status !== 200) throw new Error('BarcodeReport HTTP ' + status + ': ' + res.getContentText());
  const text = res.getContentText();
  try { cache.put('bc:' + code, text, CACHE_SECONDS); } catch (e) {}  // 100 KB value limit
  return JSON.parse(text);
}

function lookupRange_(rows, field) {
  const codes = rows.map(r => normalizeBarcode_(r[0]));
  const unique = [...new Set(codes.filter(Boolean))];
  const products = {};
  const cache = CacheService.getScriptCache();
  const cached = cache.getAll(unique.map(c => 'bc:' + c));
  const missing = unique.filter(c => {
    const hit = cached['bc:' + c];
    if (hit === undefined) return true;
    products[c] = hit === NOT_FOUND ? null : JSON.parse(hit);
    return false;
  });
  for (let i = 0; i < missing.length; i += 100) {
    Object.assign(products, postLookup_(missing.slice(i, i + 100)));
  }
  return codes.map(c => [c ? pick_(products[c], field) : '']);
}

Now try it in a cell:

Field names match the API reference: title, brand, category, description, image_url, min_price, price_currency, model_number, size, weight and more. Every field except barcode and title can be empty. Titles have the best coverage, while brand, category and image show up on a smaller share of records. So a blank brand cell usually means "not in the data", not a bug.

Errors such as a bad key, an exhausted quota, or rate limiting show up as #ERROR! with the API's message when you hover. They aren't cached, so the cell retries the next time it recalculates.

Why the cache matters here

Google's docs say the Cache service is "not particularly useful in custom functions". That's true when a function only does math. Here, every call can cost a paid lookup. With the shared cache, title and brand for the same row cost one lookup, not two. A barcode repeated in 30 rows costs one. A recalculation within six hours usually costs nothing. The function also caches not-found results, because a 404 still counts against your quota. Keep the limits in mind: entries expire after at most 6 hours, each value tops out at 100 KB, and the cache holds about 1,000 items before it starts evicting.

Ranges batch automatically

When you pass A2:A200, the function collects the distinct uncached codes and sends them 100 at a time to POST /api/v1/lookup. That uses the postLookup_ helper in Step 3, so paste both blocks. Custom functions must finish within 30 seconds, so keep range formulas to a few hundred rows. For anything bigger, use the menu script.

Step 3: Fill a whole column with one click

Custom functions recalculate, so their results are never really "saved". If your key changes or your quota runs out, those cells can turn into errors the next time they recalculate. For a few thousand rows, you want a script that runs once, writes plain values, and can pick up where it left off. Add this below the code from Step 2:

const FIELDS = ['title', 'brand', 'category', 'image_url'];   // written to B:E
const PAUSE_MS = 2000;                 // Free plan: batch calls are capped at 30/min
const TIME_BUDGET_MS = 5 * 60 * 1000;  // stop before Apps Script's 6-minute limit

function onOpen() {
  SpreadsheetApp.getUi().createMenu('BarcodeReport')
    .addItem('Fill product data (column A)', 'fillProductData').addToUi();
}

function fillProductData() {
  const started = Date.now();
  const sheet = SpreadsheetApp.getActiveSheet();
  const numRows = sheet.getLastRow() - 1;
  if (numRows < 1) return;
  const codes = sheet.getRange(2, 1, numRows, 1).getValues().map(r => normalizeBarcode_(r[0]));
  const out = sheet.getRange(2, 2, numRows, FIELDS.length);
  const values = out.getValues();

  // Distinct codes whose column B is still empty, so re-runs resume.
  const pending = [...new Set(codes.filter((c, i) => c && values[i][0] === ''))];
  if (!pending.length) return;

  const account = JSON.parse(UrlFetchApp.fetch(API_BASE + '/account',
    { headers: { 'X-API-Key': apiKey_() } }).getContentText());
  if (account.usage_remaining < pending.length) {
    SpreadsheetApp.getUi().alert(pending.length + ' lookups needed, ' +
      account.usage_remaining + ' left this period.');
    return;
  }

  for (let i = 0; i < pending.length; i += 100) {
    if (Date.now() - started > TIME_BUDGET_MS) {
      SpreadsheetApp.getActive().toast('Paused near the time limit. Run it again to continue.');
      return;
    }
    const chunk = pending.slice(i, i + 100);
    const inChunk = new Set(chunk);
    const found = postLookup_(chunk);
    codes.forEach((c, row) => {
      if (!inChunk.has(c)) return;
      const p = found[c];
      values[row] = FIELDS.map((f, j) => p ? (p[f] == null ? '' : p[f]) : (j === 0 ? 'Not found' : ''));
    });
    out.setValues(values);             // save progress after every batch
    Utilities.sleep(PAUSE_MS);
  }
  SpreadsheetApp.getActive().toast('Done: ' + pending.length + ' barcodes looked up.');
}

function postLookup_(chunk) {
  for (let attempt = 0; attempt < 3; attempt++) {
    const res = UrlFetchApp.fetch(API_BASE + '/v1/lookup', {
      method: 'post',
      contentType: 'application/json',
      headers: { 'X-API-Key': apiKey_() },
      payload: JSON.stringify({ barcodes: chunk }),
      muteHttpExceptions: true,
    });
    const status = res.getResponseCode();
    if (status === 200) {
      const map = {};
      const toCache = {};
      JSON.parse(res.getContentText()).results.forEach(r => {
        map[r.requested_barcode] = r.found ? r.product : null;
        toCache['bc:' + r.requested_barcode] = r.found ? JSON.stringify(r.product) : NOT_FOUND;
      });
      try { CacheService.getScriptCache().putAll(toCache, CACHE_SECONDS); } catch (e) {}
      return map;
    }
    if (status === 429) {              // rate limited: honor Retry-After
      const h = res.getHeaders();
      Utilities.sleep((Number(h['Retry-After'] || h['retry-after']) || 10) * 1000);
      continue;
    }
    // 401 bad key, 402 quota exceeded, anything else: stop and say why
    throw new Error('BarcodeReport HTTP ' + status + ': ' + res.getContentText());
  }
  throw new Error('Still rate limited after 3 attempts. Try again in a minute.');
}

Reload the spreadsheet and a BarcodeReport menu appears. The first run asks you to authorize the script. After that, it reads column A, skips rows that already have a value in column B, checks your remaining quota through the free GET /api/account call, and writes title, brand, category and image URL into B:E. Rows with no match get "Not found", so they aren't charged again on the next run.

On a paid plan, adjust PAUSE_MS. The batch endpoint is limited to one fifth of your plan's per-minute rate, with a floor of 30 per minute, so set it to 1000 on Standard (60 batch calls a minute), 500 on Premium and 250 on Professional.

Quota math before you press the button

• One lookup per distinct barcode. Duplicates in your sheet are free: the range formula and the menu script both de-duplicate, and so does the batch endpoint itself.
• Not found still counts. That's why both scripts remember misses.
• Batches are all-or-nothing. If a 100-code request would push you over quota, the whole request fails with a 402 and nothing is charged. That's why the menu script checks usage_remaining first.
• Google has quotas too. Apps Script allows 20,000 URL fetches a day on consumer accounts and 100,000 on Workspace (quotas page). At 100 codes per call, you'd run out of BarcodeReport lookups long before Google's limit.

For reference, the Free plan includes 100 lookups a month (one full batch), Standard ($24.95) 10,000, Premium ($74.95) 50,000 and Professional ($99.99) 100,000.

Excel option 1: Power Query

Power Query can send the same batch request. Put your barcodes in an Excel table named Barcodes with a column named Barcode, formatted as Text. Then go to Data > Get Data > From Other Sources > Blank Query, open the Advanced Editor, and paste:

let
    ApiKey   = "YOUR_API_KEY",
    Source   = Excel.CurrentWorkbook(){[Name = "Barcodes"]}[Content],
    Clean    = (v) => let t = Text.Remove(Text.From(v), {" ", "-"}) in
                   (if v is number and Text.Length(t) = 11 then "0" & t else t),
    Codes    = List.Distinct(List.Select(List.Transform(Source[Barcode], Clean),
                   each _ <> null and _ <> "")),
    Fetch    = (batch as list) as list =>
                   Json.Document(Web.Contents("https://barcodereport.com/api/v1/lookup", [
                       Headers = [#"X-API-Key" = ApiKey, #"Content-Type" = "application/json"],
                       Content = Json.FromValue([barcodes = batch])
                   ]))[results],
    Results  = List.Combine(List.Transform(List.Split(Codes, 100), Fetch)),
    AsTable  = Table.FromRecords(Results, {"requested_barcode", "found", "product"}, MissingField.UseNull),
    Expanded = Table.ExpandRecordColumn(AsTable, "product", {"title", "brand", "category", "image_url"})
in
    Expanded

A few things will trip you up the first time:

• Choose Anonymous when Excel asks for credentials. Setting Content turns the request into a POST, and Microsoft's docs say POST requests can only be made anonymously. The key travels in the header instead.
• The key is saved in the workbook. Anyone you send the file to can open the query and read it. Clear it before you share.
• The editor re-runs queries while you click through steps. Build against a five-row test table, then point it at the real one.
• Join the results back with =XLOOKUP([@Barcode], Results[requested_barcode], Results[title], ""). Both sides need to be text.

Excel option 2: Office Scripts

If you have a Microsoft 365 work or school account, the Automate tab runs Office Scripts. These are TypeScript, and they can call fetch. The logic is the same as the Apps Script menu function: read column A with getTexts() so leading zeros survive, de-duplicate, POST 100 codes at a time to /api/v1/lookup, and write the results with setValues(). Microsoft's external calls guide covers the specifics: main has to be async, every JSON response needs a typed interface (the any type isn't allowed), and there's no built-in place to store secrets. Scripts are saved in your OneDrive rather than in the workbook, so a key inside a script doesn't travel with the file unless you share the script along with it.

There are two things to know. fetch doesn't work when a script runs through Power Automate, so run the script from the Automate tab itself. And Microsoft notes that calls fail if an API's CORS policy blocks them; the BarcodeReport API sends permissive CORS headers (Access-Control-Allow-Origin: *, with X-API-Key and Authorization allowed), so browser-based scripts can call it directly. If you need a refresh that runs without opening the file, Power Query is the more dependable choice. Excel's WEBSERVICE() function isn't a good substitute: it can't send headers, and it doesn't work on Mac.

When to skip the spreadsheet and upload a CSV

Bulk lookup takes a CSV or TXT file of up to 100,000 barcodes. A free quick check tells you how many match before you pay anything, and the results come back as a CSV within 24–48 hours at $0.10 per UPC. It's the easiest route for a single big job. If you'll refresh the data every month, an API plan plus the script above will cost much less per code.

Frequently asked questions

Next steps

• Get a free API key: 100 lookups a month, enough to test everything above on your own barcodes.
• Ready-made Google Sheets function: =BARCODELOOKUP() without writing the script yourself.
• Read the API documentation: every field the formula can return, plus error codes and rate-limit headers.
• Upload a CSV to bulk lookup: the free quick check shows your match count before you pay.
• Barcode not found? Here's why: what to do with the rows that come back empty.
• Barcode lookup in JavaScript and Node.js: the same API calls, outside the spreadsheet.