Amazon Product Data in Google Sheets with Apps Script

You can pull Amazon product data into Google Sheets with a few lines of Apps Script. A custom function such as =AMAZON_PRODUCT(A2, "ATVPDKIKX0DER") calls the SellerMagnet API through UrlFetchApp and fills the row with the product's title, Buy Box price and bestseller rank. For more than a few dozen rows, a menu-driven refresh that fetches in parallel is faster and spends credits only when you click it.

How do I connect Google Sheets to the SellerMagnet API?

Everything happens inside the spreadsheet; there is nothing to install. You need an API key - a free account includes 500 credits - and a sheet with ASINs in column A and marketplace IDs in column B.

1. Open the script editorIn the spreadsheet, choose Extensions, then Apps Script.
2. Store the API keyIn Project Settings, add a script property named SELLERMAGNET_API_KEY with your key as the value.
3. Paste the codeReplace the contents of Code.gs with the functions below and save.
4. Use itType the custom function into a cell, or reload the sheet to get the SellerMagnet menu.

How do I write a custom function for Amazon data?

A custom function is an Apps Script function you call from a cell like a built-in formula. This one returns a single row - title, price, currency, rank - which spills into the three cells to its right. It checks the ASIN first, because product lookup does not validate the format and bills a malformed ASIN like any other request. Marketplace IDs for all 23 storefronts are in the marketplace ID list.

Code.gs - a custom function: =AMAZON_PRODUCT(A2, B2)

const API = "https://sellermagnet-api.com/api/amazon-product-lookup";

/**
 * Live Amazon product data from the SellerMagnet API.
 * @param {string} asin 10-character ASIN
 * @param {string} marketplaceId e.g. "ATVPDKIKX0DER" for amazon.com
 * @return Title, Buy Box price, currency and bestseller rank.
 * @customfunction
 */
function AMAZON_PRODUCT(asin, marketplaceId) {
  asin = String(asin || "").trim().toUpperCase();
  if (!/^[A-Z0-9]{10}$/.test(asin)) return [["Invalid ASIN", "", "", ""]]; // would be billed
  const key = PropertiesService.getScriptProperties().getProperty("SELLERMAGNET_API_KEY");
  const res = UrlFetchApp.fetch(lookupUrl(asin, marketplaceId, key), { muteHttpExceptions: true });
  return [toRow(res)];
}

function lookupUrl(asin, marketplaceId, key) {
  return API + "?asin=" + encodeURIComponent(asin) +
    "&marketplaceId=" + encodeURIComponent(marketplaceId) +
    "&api_key=" + encodeURIComponent(key);
}

function toRow(res) {
  let body = {};
  try { body = JSON.parse(res.getContentText()); } catch (e) {} // some errors are HTML
  if (res.getResponseCode() !== 200 || !body.success) {
    return ["Error " + res.getResponseCode() + ": " + (body.message || ""), "", "", ""];
  }
  const p = body.data.productInfo;
  const box = p.buyBoxInfo || {};
  const rank = (p.bestsellerRanks || {}).main_category || {};
  return [p.title || "", box.price ?? "", box.currencyCode || "", rank.rank ?? ""];
}

The four fields the function reads (trimmed lookup response)

{
  "success": true,
  "data": {
    "productInfo": {
      "title": "PlayStation®5 console (slim)",
      "buyBoxInfo": { "price": 444.99, "currencyCode": "USD" },
      "bestsellerRanks": { "main_category": { "name": "Video Games", "rank": 31 } }
    }
  }
}

When does a custom function spend credits?

Every time it runs, and it runs once per cell whenever that cell's arguments change. Copying the formula down 500 rows is 500 requests. Volatile functions such as NOW() or RAND() are not allowed as arguments to a custom function, so pass only the ASIN and marketplace cells. Each call must also finish within 30 seconds, which is ample for one product but the wrong tool for a whole catalogue.

How do I refresh hundreds of rows at once?

Use a menu item instead. UrlFetchApp.fetchAll sends a batch of requests in parallel, and a script run from a menu may take up to 6 minutes. The version below sends 10 requests at a time - inside the per-key concurrency cap - skips blank or malformed ASINs, and writes results after each batch, so a timeout keeps what was already paid for. For lists in the thousands, the bulk ASIN lookup guide covers deduplication and resuming.

Code.gs - a SellerMagnet menu that refreshes the whole sheet

const SHEET = "Products"; // A: ASIN, B: marketplace ID, C:F filled in
const BATCH = 10;          // requests in flight at a time

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("SellerMagnet")
    .addItem("Refresh products", "refreshProducts")
    .addToUi();
}

function refreshProducts() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET);
  const last = sheet.getLastRow();
  if (last < 2) return;
  const rows = sheet.getRange(2, 1, last - 1, 2).getValues();
  const key = PropertiesService.getScriptProperties().getProperty("SELLERMAGNET_API_KEY");
  const out = rows.map(() => ["", "", "", ""]);

  const todo = [];
  rows.forEach(([asin, marketplaceId], i) => {
    const clean = String(asin || "").trim().toUpperCase();
    if (/^[A-Z0-9]{10}$/.test(clean)) todo.push({ i, url: lookupUrl(clean, marketplaceId, key) });
    else if (clean) out[i] = ["Invalid ASIN", "", "", ""];
  });

  for (let start = 0; start < todo.length; start += BATCH) {
    const batch = todo.slice(start, start + BATCH);
    const responses = UrlFetchApp.fetchAll(batch.map((t) => ({ url: t.url, muteHttpExceptions: true })));
    responses.forEach((res, j) => { out[batch[j].i] = toRow(res); });
    sheet.getRange(2, 3, out.length, 4).setValues(out); // keep paid results if the run times out
  }
  sheet.getRange(2, 3, out.length, 4).setValues(out); // also covers sheets with only invalid rows
}

Refreshing on a schedule

A time-driven trigger runs the same function without anyone opening the sheet. Run the one-line setup below once from the editor. Scheduled runs count against Google's trigger quota of 90 minutes a day on consumer accounts and 6 hours on Workspace, and every run spends one credit per ASIN - pick the interval the way the Buy Box monitoring guide describes.

Run once: refresh every 6 hours

function scheduleRefresh() {
  // Running this twice must not create a second trigger (and double the spend).
  ScriptApp.getProjectTriggers()
    .filter((t) => t.getHandlerFunction() === "refreshProducts")
    .forEach((t) => ScriptApp.deleteTrigger(t));
  ScriptApp.newTrigger("refreshProducts").timeBased().everyHours(6).create();
}

To keep a price history instead of overwriting it, append one timestamped row per product to a second tab at the end of each scheduled run. appendRow adds the row below the last filled one, so the tab becomes a log you can chart.

Append today's prices to a History tab (call at the end of refreshProducts)

function logHistory(rows, out) {
  const history = SpreadsheetApp.getActive().getSheetByName("History"); // create the tab first
  const now = new Date();
  rows.forEach(([asin, marketplaceId], i) => {
    const [title, price, currency, rank] = out[i];
    if (price !== "") history.appendRow([now, asin, marketplaceId, price, currency, rank]);
  });
}

Why not use IMPORTXML instead?

IMPORTXML scrapes a product page with an XPath expression, so it depends on Amazon's HTML: when the markup changes or the request is refused, the formula returns an error or the wrong value, and it cannot read data such as the bestseller rank reliably across marketplaces. An API returns the same named JSON fields every time, which is why the functions above read title, price and rank by name. The Amazon web scraping page covers the trade-off.

Which approach should I use?

Bottom line: use the custom function for a handful of rows, the menu refresh for a catalogue, and a trigger when nobody should have to click. If you would rather not maintain a script at all, the DataPipeline scheduler delivers scheduled Amazon data to S3, a webhook or email, and pricing shows what each refresh costs.