DEV Community

kaiyilu
kaiyilu

Posted on

Batch-Downloading Excel Image URLs & Calculating Amazon 85% Product Fill in Pure Browser JS

#shopify #javascript #webdev #ecommerce

When preparing a 200-SKU supplier catalog for Shopify, Amazon, and Etsy, three tedious engineering headaches show up almost every single time:

  1. Vendor Excel sheets full of raw image URLs: Clicking 80 CDN links one by one scrambles your SKU-101-main.jpg naming convention, while generic "tab-downloader" browser extensions throw away your spreadsheet's SKU and category columns.
  2. Shopify Dawn mobile hero banner clipping: A 2000 × 800 desktop hero banner looks spacious on a monitor, yet loses 70% of its horizontal width when rendered inside a 3:4 mobile container.
  3. Amazon main image RGB(255, 255, 255) suppression: Even when studio lighting looks white to the naked eye (#FAFAFA), failing pure #FFFFFF edge checks—or leaving the product smaller than Amazon's 85% longest-axis fill rule—triggers silent search suppression.

Here is the zero-dependency browser math we open-sourced in kaiyilu/ecommerce-image-specs to run all three workflows 100% locally in a browser tab without uploading unpublished catalog photos to a backend server.

1. Mapping Excel / CSV URL Columns into Collision-Safe SKU ZIPs

Suppose a supplier sends an .xlsx or .csv file with three columns: image_url, sku_filename, and category_folder.

First, watch out for the classic spreadsheet trap: clickable =HYPERLINK() cells. If a cell displays "View Photo" instead of a literal https://... string, standard CSV exports drop the underlying URL. A 4-line Excel VBA UDF extracts the raw address cleanly before saving:

Function GetURL(rng As Range) As String
    If rng.Hyperlinks.Count > 0 Then GetURL = rng.Hyperlinks(1).Address
End Function
Enter fullscreen mode Exit fullscreen mode

Once parsed in the browser, filenames coming from messy ERP sheets often contain OS-reserved characters (:, *, /, ?) or duplicate SKU rows when multiple color variants share a base code. Sanitizing segments and appending deterministic -2, -3 suffixes prevents silent overwrites inside fflate ZIP streams:

export function buildSafeZipPaths(rows) {
  const used = new Set();
  const cleanSeg = (val, fallback) => {
    const cleaned = String(val ?? "")
      .replace(/[\u0000-\u001f<>:"|?*]/g, "-")
      .replace(/[\\/]+/g, "-")
      .replace(/\s+/g, " ")
      .replace(/[. ]+$/g, "")
      .trim();
    return cleaned && cleaned !== "." && cleaned !== ".." ? cleaned : fallback;
  };

  return rows.map((row, idx) => {
    const file = cleanSeg(row.filename, `asset-${idx + 1}.jpg`);
    const folder = String(row.folder ?? "")
      .split(/[\\/]+/)
      .map((s) => cleanSeg(s, ""))
      .filter(Boolean)
      .join("/");
    const base = folder ? `${folder}/${file}` : file;

    if (!used.has(base.toLowerCase())) {
      used.add(base.toLowerCase());
      return base;
    }

    const dot = base.lastIndexOf(".");
    const slash = base.lastIndexOf("/");
    const hasExt = dot > slash;
    const stem = hasExt ? base.slice(0, dot) : base;
    const ext = hasExt ? base.slice(dot) : "";
    let n = 2;
    while (used.has(`${stem}-${n}${ext}`.toLowerCase())) n++;
    const unique = `${stem}-${n}${ext}`;
    used.add(unique.toLowerCase());
    return unique;
  });
}
Enter fullscreen mode Exit fullscreen mode

If you want to run this directly on a spreadsheet (or test a 3-row live demo), we wired it into an interactive workflow on How to Download Multiple Files from Excel URLs alongside a SKU-focused preset on Bulk Download Product Images by SKU—every batch also bundles a download-report.csv flagging any 404 or 403 vendor links by row number.

2. Simulating Shopify OS 2.0 Aspect Ratios & Mobile Banner Safe Zones

Most Dawn-based Shopify storefronts behave best when product galleries stick to a single master aspect ratio—typically 2048 × 2048 (1:1) for zoom-enabled general catalogs or 1600 × 2000 (4:5) for full-length apparel.

Where merchants get burned is wide hero banners (2000 × 800, 5:2) rendered inside mobile portrait containers (r_m = W_m / H_m ≈ 0.75) using CSS object-fit: cover:

export function computeMobileBannerSafeZone({
  bannerWidth = 2000,
  bannerHeight = 800,
  mobileAspect = 0.75,
}) {
  const safeWidth = Math.min(bannerWidth, Math.round(bannerHeight * mobileAspect));
  const croppedEachSide = Math.round((bannerWidth - safeWidth) / 2);
  return {
    safeWidth,          // 600px on a 2000x800 banner!
    croppedEachSide,    // 700px sliced off BOTH left and right edges
    safeWidthPercent: Number(((safeWidth / bannerWidth) * 100).toFixed(1)), // 30%
  };
}
Enter fullscreen mode Exit fullscreen mode

Only the central 600 px (30%) of a 2000 × 800 desktop hero survives uncropped on a 3:4 mobile slide! Keeping focal products and headline copy inside that central safe corridor—or letterbox-padding non-square supplier photos with custom theme background hex colors (#FAF8F5 cream, #FFFFFF white, or alpha WebP)—prevents staggered collection rows.

You can toggle that mobile safe-zone overlay interactively on the Shopify Image Size Guide & Studio or batch-pad catalog sets with custom SEO filename prefixes via the Shopify Bulk Image Resizer.

3. Detecting Non-White Bounding Boxes for Amazon's 85% Fill Rule

Amazon requires hero images to sit on pure RGB(255, 255, 255) with the product spanning at least 85% of the longest axis on a 1600 px+ (ideally 2000 × 2000) frame so pinch-to-zoom activates.

By downsampling the uploaded image onto an offscreen inspection canvas, we find the tightest bounding box (minX, minY, maxX, maxY) of pixels where R < 250 || G < 250 || B < 250 (and alpha > 16), map those coordinates back to natural pixel resolution, and center-scale onto a clean #FFFFFF 2000 × 2000 canvas:

export function computeAmazonMainImagePlacement({
  bboxWidth,
  bboxHeight,
  canvasSize = 2000,
  targetFill = 0.85,
}) {
  const maxProductSpan = Math.round(canvasSize * targetFill); // 1700px
  const scale = Math.min(maxProductSpan / bboxWidth, maxProductSpan / bboxHeight);
  const renderedWidth = Math.round(bboxWidth * scale);
  const renderedHeight = Math.round(bboxHeight * scale);
  return {
    renderedWidth,
    renderedHeight,
    offsetX: Math.round((canvasSize - renderedWidth) / 2),
    offsetY: Math.round((canvasSize - renderedHeight) / 2),
  };
}
Enter fullscreen mode Exit fullscreen mode

That turns a diagnostic check into a one-click fix: instead of just warning a seller that their bottle only fills 68% of the frame or touches the top edge, we render and export the compliant 2000 × 2000 pure-white JPEG right in the browser (live on Amazon Main Image Checker and Etsy Listing Photo Checker).

Open-Source Reference

All pure-JS reference functions (specs.mjs, zero dependencies, MIT licensed) are available at:

Top comments (1)

Collapse
 
tomerbarm profile image
Tomer Bar-Meir (Tom) •

Useful write-up. Three edge cases for the bounding-box check in section 3. First, white or very light products (ceramics, soap, supplements in white tubs) fail the R/G/B < 250 test because the product itself reads as background, so the box collapses; a second pass on local edge contrast helps. Second, a soft contact shadow is usually dark enough to count as product, so the box grows and the fill percentage is overstated: trim the shadow row or flag a wide, low-height box at the bottom, otherwise you can get a false 'compliant'. Third, normalize EXIF orientation before measuring: some phone photos carry a rotation tag that browsers apply on display but not when you read raw pixel dimensions, so width and height can come out swapped.