Pulling catalog information and getting ID's to match

Questions
1

I am trying to pull catalog data, I am trying to get the ID of the item that matches the catalog output. I am not a coder and made it this far with chatGPT, any insight into what i am missing is helpful. RIght now I am getting output for ID that differs that from the catalog.
Same items:
Catalog download: VUDRJ3PEQHW6XFSDRRDO7CYW
API output: ZTLRALKJOYYJJ4IEZUXOOU7N

function listCatalogAndBatchRetrieve() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Production API URL for listing catalog
  var listCatalogUrl = 'https://connect.squareup.com/v2/catalog/list';  // Production URL
  
  // Production Access Token
  var accessToken = 'your_production_access_token_here';  // Replace with your production access token

  Logger.log("Using Access Token: " + accessToken);  // Log the token to verify

  // Fetch all location IDs automatically
  var locationIds = fetchLocationIds(accessToken);
  if (!locationIds.length) {
    Logger.log("No locations found for the merchant.");
    SpreadsheetApp.getUi().alert("No locations found for this merchant.");
    return;
  }

  var allObjectIds = [];
  var cursor = null;
  
  // Fetch paginated results until no cursor is returned
  do {
    var response = fetchCatalogPage(listCatalogUrl, accessToken, cursor);
    
    if (response.getResponseCode() === 200) {
      var jsonData = JSON.parse(response.getContentText());
      
      if (jsonData.objects && jsonData.objects.length > 0) {
        var objectIds = jsonData.objects.map(function(item) {
          return item.id;
        });
        allObjectIds = allObjectIds.concat(objectIds);
      }
      
      cursor = jsonData.cursor || null;  // Update cursor for pagination
    } else {
      Logger.log("Error details from List Catalog: " + response.getContentText());
      SpreadsheetApp.getUi().alert("Error retrieving catalog. Check logs for details.");
      return;
    }
    
  } while (cursor);  // Keep fetching until no cursor is returned

  Logger.log("Total Object IDs Retrieved: " + allObjectIds.length);
  
  if (allObjectIds.length > 0) {
    // Call the batchRetrieveCatalog function with all retrieved object IDs, token, and location IDs
    batchRetrieveCatalog(allObjectIds, accessToken, locationIds);
  } else {
    SpreadsheetApp.getUi().alert("No items found in the catalog.");
  }
}

function fetchCatalogPage(listCatalogUrl, accessToken, cursor) {
  // Headers for API request
  var headers = {
    "Authorization": "Bearer " + accessToken,
    "Square-Version": "2024-08-21",  // Current Square API version
    "Content-Type": "application/json"
  };

  // Append cursor to URL for pagination
  var urlWithCursor = cursor ? listCatalogUrl + "?cursor=" + cursor : listCatalogUrl;
  
  var listOptions = {
    "method": "GET",
    "headers": headers,
    "muteHttpExceptions": true  // Allows capturing full response in case of an error
  };

  return UrlFetchApp.fetch(urlWithCursor, listOptions);
}

function batchRetrieveCatalog(objectIds, accessToken, locationIds) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Production API URL for batch-retrieve
  var apiUrl = 'https://connect.squareup.com/v2/catalog/batch-retrieve';  // Production URL

  Logger.log("Using Access Token for Batch Retrieve: " + accessToken);  // Log token again
  
  // Fetch items in smaller batches to avoid exceeding limits (max batch size: 1,000)
  var batchSize = 1000;
  var currentBatch = 0;
  
  // Prepare the header row dynamically to include inventory columns for each location
  var headerRow = ["Item ID", "Item Name", "Item URL", "Description", "Variation Name", "Price (CAD)", "GTIN (UPC/EAN/ISBN)"];
  locationIds.forEach(function(locationId) {
    headerRow.push("Inventory at " + locationId);
  });
  headerRow.push("Primary Image URL", "Secondary Image URL", "Tertiary Image URL"); // Add extra image columns at the end

  while (currentBatch < objectIds.length) {
    var batchIds = objectIds.slice(currentBatch, currentBatch + batchSize);

    // JSON payload for the batch retrieve request using the retrieved object_ids
    var payload = {
      "object_ids": batchIds,
      "include_related_objects": true
    };

    var headers = {
      "Authorization": "Bearer " + accessToken,
      "Square-Version": "2024-08-21",  // Current Square API version
      "Content-Type": "application/json"
    };

    var options = {
      "method": "POST",
      "headers": headers,
      "muteHttpExceptions": true,  // Allows capturing full response in case of an error
      "payload": JSON.stringify(payload)
    };

    var response = UrlFetchApp.fetch(apiUrl, options);

    // Log the response to check if data is returned
    Logger.log("Batch Retrieve Response: " + response.getContentText());

    if (response.getResponseCode() === 200) {
      var jsonData = JSON.parse(response.getContentText());

      if (jsonData.objects && jsonData.objects.length > 0) {
        
        // Clear the sheet only for the first batch and set headers
        if (currentBatch === 0) {
          sheet.clear();
          sheet.appendRow(headerRow);
        }

        jsonData.objects.forEach(function(item) {
          // Ensure that the item has the required fields
          if (item.item_data) {
            var itemId = item.id || "No item ID";
            var itemName = item.item_data.name || "No name";
            var description = item.item_data.description || "No description";
            var itemUrl = item.item_data.ecom_uri || "No URL";
            
            // Get Image URLs (primary and additional)
            var imageUrls = [];
            if (item.item_data.ecom_image_uris && item.item_data.ecom_image_uris.length > 0) {
              imageUrls = item.item_data.ecom_image_uris; // Use ecom_image_uris first
            } else if (item.image_ids && item.image_ids.length > 0) {
              imageUrls = getImageUrls(item.image_ids, jsonData.related_objects);  // Fallback to image_ids
            }

            // Ensure we have at least 3 columns for image URLs (fill missing ones with "No image URL")
            var primaryImageUrl = imageUrls[0] || "No image URL";
            var secondaryImageUrl = imageUrls[1] || "No image URL";
            var tertiaryImageUrl = imageUrls[2] || "No image URL";

            // Iterate over variations and get inventory for each variation at each location
            if (item.item_data.variations && item.item_data.variations.length > 0) {
              item.item_data.variations.forEach(function(variation) {
                var variationId = variation.id || "No variation ID";  // Ensure we get the variation ID for inventory
                var variationName = variation.item_variation_data.name || "No variation name";
                var price = variation.item_variation_data.price_money 
                  ? variation.item_variation_data.price_money.amount / 100 
                  : "No price";

                // Retrieve the GTIN (UPC/EAN/ISBN)
                var gtin = variation.item_variation_data.upc || "No GTIN";

                // Retrieve inventory counts for each location
                var inventoryCounts = locationIds.map(function(locationId) {
                  return getInventoryByLocation(variationId, accessToken, locationId);
                });

                // Append the row with inventory data and image URLs
                var rowData = [itemId, itemName, itemUrl, description, variationName, price, gtin].concat(inventoryCounts, [primaryImageUrl, secondaryImageUrl, tertiaryImageUrl]);
                sheet.appendRow(rowData);
              });
            }
          }
        });

      } else {
        Logger.log("No items found in batch retrieve response.");
        SpreadsheetApp.getUi().alert("No items found for these object IDs.");
      }
    } else {
      Logger.log("Error details from Batch Retrieve: " + response.getContentText());
      SpreadsheetApp.getUi().alert("Error details logged. Check logs for more information.");
    }

    currentBatch += batchSize;
  }
}

function getImageUrls(imageIds, relatedObjects) {
  return imageIds.map(function(imageId) {
    var imageObject = relatedObjects.find(function(obj) {
      return obj.id === imageId && obj.type === "IMAGE";
    });
    return imageObject ? imageObject.image_data.url : "No image URL";
  });
}

// Fetch inventory count by location for each variation ID
function getInventoryByLocation(variationId, accessToken, locationId) {
  var inventoryApiUrl = 'https://connect.squareup.com/v2/inventory/counts/batch-retrieve';

  var payload = {
    "catalog_object_ids": [variationId],  // The ID of the item variation we are fetching inventory for
    "location_ids": [locationId],  // Location you want to pull inventory for
    "updated_after": "2016-11-16T00:00:00.000Z"
  };

  var headers = {
    "Authorization": "Bearer " + accessToken,
    "Square-Version": "2024
2

:wave: I’m Square Dev AI, here to help answer your question!
Based on the information you’ve provided, the following documentation will help answer your question:

Additional Documentation

Retrieve Inventory Quantities
Catalog API
Inventory API

If this documentation helped you find a solution, please check the box indicating that this reply solves the problem.
Otherwise one of our Developer Advocates will provide additional assistance shortly.

3

The discrepancy in the IDs you’re seeing could be due to the fact that the Catalog API and the Inventory API use different IDs to reference the same item. The Catalog API uses a CatalogObject ID, while the Inventory API uses a CatalogItemVariation ID.

When you’re pulling catalog data, you’re likely seeing the CatalogObject ID. However, when you’re pulling inventory data, you’re seeing the CatalogItemVariation ID.

To match these up, you can use the Catalog API’s RetrieveCatalogObject endpoint with the include_related_objects parameter set to true. This will return the CatalogObject along with its related objects, including its item variations. You can then match the CatalogItemVariation ID from the Inventory API with the item variation IDs from the Catalog API.

Remember to replace the placeholder in the accessToken variable with your actual access token. Here’s a sample code snippet:

var accessToken = 'your_access_token_here';  // Replace with your access token
var catalogObjectId = 'VUDRJ3PEQHW6XFSDRRDO7CYW';  // Replace with your CatalogObject ID

var url = 'https://connect.squareup.com/v2/catalog/object/' + catalogObjectId + '?include_related_objects=true';
var options = {
  'headers': {
    'Authorization': 'Bearer ' + accessToken,
    'Square-Version': '2022-01-20'  // Replace with the current version
  }
};

var response = UrlFetchApp.fetch(url, options);
var data = JSON.parse(response.getContentText());

Logger.log(data);

This will log the CatalogObject along with its related objects. You can then inspect the logged data to find the item variation IDs and match them with the IDs from the Inventory API.

This answer was reviewed by @Bryan-Square.

4

Appreciate the reply, I think I can use this information to fix my problem. Thanks, and yes I am aware I need to change the ID, I have the script running successfully, just trying to refine to what i need. Thanks!