Google Sheets: Remove Backgrounds for a Whole Column of Image URLs (Apps Script)

Create an API key on the bgclear dashboard (10 free full-resolution credits; preview size is free without limit). In your sheet: column A = source image URL, column B = result URL, column C = preview, row 1 = headers. Open Extensions → Apps Script, paste the script below, then store the key with PropertiesService so it never sits in the code: run setKey() once from the editor.

function setKey() {
  PropertiesService.getScriptProperties().setProperty('BGCLEAR_API_KEY', 'bgc_live_PASTE_YOUR_KEY');
}

const API = 'https://www.bgclear.ai/api/v1/remove';

function removeBackgrounds() {
  const key = PropertiesService.getScriptProperties().getProperty('BGCLEAR_API_KEY');
  const sheet = SpreadsheetApp.getActiveSheet();
  const rows = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues(); // A:B
  const started = Date.now();

  rows.forEach(([src, done], i) => {
    if (!src || done) return;                                    // resumable: skip finished rows
    if (Date.now() - started > 5 * 60 * 1000) return;            // stay under the 6-min execution cap

    const res = UrlFetchApp.fetch(API, {
      method: 'post',
      contentType: 'application/json',
      headers: {
        Authorization: 'Bearer ' + key,
        Accept: 'application/json',
        'Idempotency-Key': 'sheet-' + Utilities.base64EncodeWebSafe(src), // safe re-runs
      },
      payload: JSON.stringify({ image_url: src, size: 'auto', format: 'jpg', bg_color: 'ffffff' }),
      muteHttpExceptions: true,
    });

    const code = res.getResponseCode();
    const body = JSON.parse(res.getContentText());
    const row = i + 2;
    if (code === 200) {
      sheet.getRange(row, 2).setValue(body.url);                      // result URL (valid 24 h)
      sheet.getRange(row, 3).setFormula('=IMAGE(B' + row + ')');      // preview in the sheet
    } else if (code === 429) {
      Utilities.sleep(1000 * Number(res.getHeaders()['X-RateLimit-Reset'] || 5));
    } else if (code === 402) {
      throw new Error('Out of credits — https://www.bgclear.ai/api-pricing/');
    } else {
      sheet.getRange(row, 2).setValue('ERROR ' + code + ': ' + (body.error && body.error.message));
    }
    Utilities.sleep(1100);                                            // ≤60 requests/minute
  });
}

Run removeBackgrounds() from the editor or add a menu item; rerun it to continue where it stopped — rows with a value in column B are skipped, and the idempotency key means a row that was interrupted mid-request is not charged twice.

Result URLs expire after 24 hours, which is fine for a marketplace upload the same day but not for a feed that is re-read weekly. Add a step that copies each result to Drive:

function archiveToDrive() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const folder = DriveApp.getFolderById('YOUR_FOLDER_ID');
  const rows = sheet.getRange(2, 2, sheet.getLastRow() - 1, 3).getValues(); // B:D
  rows.forEach(([url, , driveUrl], i) => {
    if (!url || driveUrl || !url.startsWith('http')) return;
    const file = folder.createFile(UrlFetchApp.fetch(url).getBlob().setName('row-' + (i + 2) + '.jpg'));
    file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
    sheet.getRange(i + 2, 4).setValue('https://drive.google.com/uc?id=' + file.getId());
  });
}

Column D then holds a stable link you can paste into Google Merchant Center, Shopify or a WooCommerce CSV import.

Change size: 'auto' to size: 'preview' for a free dry run (800 px results) on a new supplier's images, then switch back. Each full-resolution image is one credit — a 500-row sheet is the $39 pack ($0.078 per image); credits never expire and failed rows are not charged (pricing). Apps Script's own limits are the ones to watch: 6 minutes per execution (the script stops itself at 5 and resumes on the next run) and a daily UrlFetchApp quota that a 2,000-row sheet fits inside. Images must be publicly fetchable URLs; Drive links need "anyone with the link" sharing and the uc?id= form. Coming from a remove.bg script? The JSON body is the same; only the URL and the header changed — migration notes.