Build a Storefront Stalking Sheet with AI

/**
 * KEEPA SELLER STOREFRONT COUNT
 *
 * Column A: Marketplace
 * Column B: SellerID
 * Column C: # ASINs
 *
 * Supported marketplace values:
 * CA = Amazon Canada
 * COM = Amazon USA
 */

const KEEPA_CONFIG = {
  FIRST_DATA_ROW: 2,
  MARKETPLACE_COLUMN: 1, // Column A
  SELLER_ID_COLUMN: 2,   // Column B
  ASIN_COUNT_COLUMN: 3,     // Column C
  API_KEY_PROPERTY: 'KEEPA_API_KEY'
};


/**
 * Creates the spreadsheet menu whenever the file opens.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Keepa Tools')
    .addItem('Update ASIN Counts', 'updateAsinCounts')
    .addToUi();
}


/**
 * Reads marketplace and seller IDs, then writes storefront
 * counts into column C.
 */
function updateAsinCounts() {
  const ui = SpreadsheetApp.getUi();
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getActiveSheet();
  const lastRow = sheet.getLastRow();

 if (lastRow < KEEPA_CONFIG.FIRST_DATA_ROW) {
   ui.alert('No seller IDs were found.');
   return;
 }

 const rowCount =
  lastRow - KEEPA_CONFIG.FIRST_DATA_ROW + 1;

/*
 * Read columns A and B together:
 * A = Marketplace
 * B = SellerID
 */
const inputRows = sheet
  .getRange(
    KEEPA_CONFIG.FIRST_DATA_ROW,
    KEEPA_CONFIG.MARKETPLACE_COLUMN,
    rowCount,
    2
  )
  .getDisplayValues();

let apiKey = getSavedOrNewKeepaKey_();

if (!apiKey) {
  return;
}

/*
 * Start with the existing values in column C.
 *
 * That way, if one row has an invalid marketplace or an API error,
 * the script will not erase a count that was already there.
 */
const output = sheet
  .getRange(
    KEEPA_CONFIG.FIRST_DATA_ROW,
    KEEPA_CONFIG.ASIN_COUNT_COLUMN,
    rowCount,
    1
  )
  .getValues();

let checkedCount = 0;
let updatedCount = 0;
let skippedCount = 0;
const problems = [];

spreadsheet.toast(
  'Checking seller storefronts...',
  'Keepa Tools',
  10
);

for (let i = 0; i < inputRows.length; i++) {
 const sheetRow = KEEPA_CONFIG.FIRST_DATA_ROW + i;

 const marketplace = String(inputRows[i][0])
  .trim()
  .toUpperCase();

 const sellerId = String(inputRows[i][1])
  .trim()
  .toUpperCase();

 /*
  * Ignore completely blank rows.
  */
 if (!marketplace && !sellerId) {
   continue;
 }

 if (!sellerId) {
   skippedCount++;
   problems.push(
     'Row ' + sheetRow + ': Seller ID is blank.'
   );

    continue;
}

const domain = getKeepaDomain_(marketplace);

if (domain === null) {
  skippedCount++;
  problems.push(
    'Row ' + sheetRow +
    ': Unsupported marketplace "' +
    marketplace +
    '".'
  );
  continue;
}

try {
  let result = requestSellerCount_(
    apiKey,
    domain,
    sellerId
  );

    /*
     * If the saved API key is rejected, ask for a replacement
     * and retry the current seller once.
     */
    if (result.invalidApiKey) {
      deleteSavedKeepaKey_();

        apiKey = promptForKeepaKey_(
          'The saved Keepa API key was rejected. ' +
          'Enter a valid Keepa API key.'
        );

        if (!apiKey) {
          /*
           * Preserve everything completed so far.
           */
          writeResults_(sheet, output);
          return;
        }

        result = requestSellerCount_(
          apiKey,
          domain,
          sellerId
        );

        if (result.invalidApiKey) {
          deleteSavedKeepaKey_();
          writeResults_(sheet, output);

            ui.alert(
              'Keepa rejected the new API key. ' +
              'Results completed before the error were saved.'
            );

            return;
        }
    }

    checkedCount++;

    if (result.count !== '') {
      output[i][0] = result.count;
      updatedCount++;
    } else {
      problems.push(

                  'Row ' + sheetRow +
                  ': Keepa did not return a storefront count.'
                );
            }

        } catch (error) {
          skippedCount++;

            problems.push(
              'Row ' + sheetRow +
              ': ' +
              (error.message || String(error))
            );
        }

        /*
         * Save every five completed requests so progress is not lost
         * if the script is interrupted.
         */
        if (checkedCount > 0 && checkedCount % 5 === 0) {
          writeResults_(sheet, output);

            spreadsheet.toast(
              'Checked ' + checkedCount + ' sellers...',
              'Keepa Tools',
              5
            );
        }
    }

    writeResults_(sheet, output);

    let completionMessage =
      updatedCount +
      ' storefront counts were written to column C.';

    if (skippedCount > 0 || problems.length > 0) {
      completionMessage +=
        '\n\n' +
        problems.length +
        ' row(s) had a problem.';

        /*
         * Show only the first few problems so the alert does not
         * become enormous.
         */
        if (problems.length > 0) {
          completionMessage +=
            '\n\n' +
            problems.slice(0, 5).join('\n');

            if (problems.length > 5) {
              completionMessage +=
                '\n...and ' +
                (problems.length - 5) +
                ' more.';
            }
        }
    }

    ui.alert(
      'Finished',
      completionMessage,
      ui.ButtonSet.OK
    );
}


/**

 * Converts the marketplace abbreviation into a Keepa domain.
 */
function getKeepaDomain_(marketplace) {
  const domains = {
    CA: 6,
    COM: 1
  };

    return Object.prototype.hasOwnProperty.call(
      domains,
      marketplace
    )
      ? domains[marketplace]
      : null;
}


/**
 * Requests the storefront count for one seller.
 *
 * Keepa requires one seller per request when storefront=1.
 */
function requestSellerCount_(apiKey, domain, sellerId) {
  const url =
    'https://api.keepa.com/seller' +
    '?key=' + encodeURIComponent(apiKey) +
    '&domain=' + encodeURIComponent(domain) +
    '&seller=' + encodeURIComponent(sellerId) +
    '&storefront=1';

    const response = UrlFetchApp.fetch(url, {
      method: 'get',
      muteHttpExceptions: true
    });

    const responseCode = response.getResponseCode();
    const responseText = response.getContentText();

    let data;

    try {
      data = JSON.parse(responseText);
    } catch (error) {
      throw new Error(
        'Keepa returned an unreadable response. HTTP ' +
        responseCode +
        '.'
      );
    }

    if (
      responseCode === 401 ||
      responseCode === 403 ||
      hasApiKeyError_(data)
    ){
      return {
         invalidApiKey: true,
         count: ''
      };
    }

    if (responseCode < 200 || responseCode >= 300) {
      throw new Error(
        getKeepaErrorMessage_(data) ||
        'Keepa request failed with HTTP ' +
        responseCode +
        '.'
      );
    }

    if (
      !data.sellers ||
      typeof data.sellers !== 'object'
    ){
      return {
         invalidApiKey: false,
         count: ''
      };
    }

    /*
     * Keepa normally keys the seller object by Seller ID.
     * The fallback handles differences in capitalization or formatting.
     */
    const seller =
      data.sellers[sellerId] ||
      data.sellers[Object.keys(data.sellers)[0]];

    if (!seller) {
      return {
        invalidApiKey: false,
        count: ''
      };
    }

    return {
      invalidApiKey: false,
      count: extractStorefrontCount_(seller)
    };
}


/**
 * Extracts the most recent total storefront listing count.
 */
function extractStorefrontCount_(seller) {
  const total = seller.totalStorefrontAsins;

    if (Array.isArray(total) && total.length >= 2) {
      const count = Number(total[total.length - 1]);

        if (Number.isFinite(count) && count >= 0) {
          return count;
        }
    }

    if (
      typeof total === 'number' &&
      Number.isFinite(total) &&
      total >= 0
    ){
      return total;
    }

    const history = seller.totalStorefrontAsinsCSV;

    if (Array.isArray(history) && history.length >= 2) {
      for (let i = history.length - 1; i >= 1; i -= 2) {
        const count = Number(history[i]);

            if (Number.isFinite(count) && count >= 0) {
              return count;
            }
        }
    }

    return '';
}

/**
 * Writes the result array to column C.
 */
function writeResults_(sheet, output) {
  if (output.length === 0) {
    return;
  }

    sheet
     .getRange(
       KEEPA_CONFIG.FIRST_DATA_ROW,
       KEEPA_CONFIG.ASIN_COUNT_COLUMN,
       output.length,
       1
     )
     .setValues(output);

    SpreadsheetApp.flush();
}


/**
 * Retrieves the saved key or asks for one.
 */
function getSavedOrNewKeepaKey_() {
  const savedKey = PropertiesService
    .getUserProperties()
    .getProperty(
      KEEPA_CONFIG.API_KEY_PROPERTY
    );

    if (savedKey) {
      return savedKey.trim();
    }

    return promptForKeepaKey_(
      'Enter your Keepa API key. It will be saved for your Google account.'
    );
}


/**
 * Prompts for and saves a Keepa API key.
 */
function promptForKeepaKey_(message) {
  const ui = SpreadsheetApp.getUi();

    const response = ui.prompt(
      'Keepa API Key',
      message,
      ui.ButtonSet.OK_CANCEL
    );

    if (response.getSelectedButton() !== ui.Button.OK) {
      return null;
    }

    const apiKey = response.getResponseText().trim();

    if (!apiKey) {
      ui.alert('No Keepa API key was entered.');
      return null;
    }

    PropertiesService
     .getUserProperties()
     .setProperty(

       KEEPA_CONFIG.API_KEY_PROPERTY,
       apiKey
     );

    return apiKey;
}


/**
 * Removes a rejected API key.
 */
function deleteSavedKeepaKey_() {
  PropertiesService
    .getUserProperties()
    .deleteProperty(
      KEEPA_CONFIG.API_KEY_PROPERTY
    );
}


/**
 * Detects API-key-related errors.
 */
function hasApiKeyError_(data) {
  const message =
    getKeepaErrorMessage_(data).toLowerCase();

    return (
      message.includes('api key') ||
      message.includes('authentication') ||
      message.includes('unauthorized') ||
      message.includes('invalid key')
    );
}


/**
 * Gets a readable message from a Keepa error response.
 */
function getKeepaErrorMessage_(data) {
  if (!data || !data.error) {
    return '';
  }

    if (typeof data.error === 'string') {
      return data.error;
    }

    if (data.error.message) {
      return String(data.error.message);
    }

    return JSON.stringify(data.error);
}