No results for query

Questions
1

I am trying to pull all catalog data for my query to build 3rd party tools to manage inventory and allow for export to 3rd party platforms like google merchant center and other use cases. Eventually leading to creating apps for my business as I understand what i am doing. However I do not code and do not know the reasons when something breaks or doesnt work the way I want.

Right Now I am trying to pull the data, and some variables are coming back empty. For example I

I can get the category ID, but not the name.

category_id category_name modifier_list_info
IJGPDZDOMZFH4WLFHNUFA45T 
// This function creates a custom menu in the Google Sheets UI
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Square API')
    .addItem('Start Processing', 'startProcessing')
    .addToUi();
}

// Main function to start processing
function startProcessing() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = 'API-Export';

  // Delete existing sheet if it exists
  var sheet = ss.getSheetByName(sheetName);
  if (sheet) {
    ss.deleteSheet(sheet);
  }
  // Create new sheet and set its name
  sheet = ss.insertSheet();
  sheet.setName(sheetName);

  // Similar steps for the Progress sheet
  var progressSheetName = 'Processing-Progress';
  var progressSheet = ss.getSheetByName(progressSheetName);
  if (progressSheet) {
    ss.deleteSheet(progressSheet);
  }
  progressSheet = ss.insertSheet();
  progressSheet.setName(progressSheetName);

  // Initialize progress indicators
  progressSheet.getRange('A1').setValue('Total Items:');
  progressSheet.getRange('A2').setValue('Items Processed:');
  progressSheet.getRange('A3').setValue('Progress (%):');
  progressSheet.getRange('A5').setValue('Type "STOP" in cell B5 to halt processing.');

  // Reset stop flag
  progressSheet.getRange('B5').setValue('');

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

  // Fetch all catalog items and variations
  var catalogData = fetchAllCatalogItems();
  if (catalogData.items.length === 0) {
    SpreadsheetApp.getUi().alert("No items found in the catalog.");
    return;
  }

  // Update total items in progress sheet
  progressSheet.getRange('B1').setValue(catalogData.items.length);

  // Fetch inventory counts for all variations
  var inventoryMap = fetchInventoryCountsPerVariation(catalogData.variationIds, locationIds);

  // Process items and write data to the sheet
  processAndWriteData(sheet, catalogData.items, catalogData.categoryMap, locationIds, inventoryMap, progressSheet);
}

// Function to fetch all catalog items and variations
function fetchAllCatalogItems() {
  var allItems = [];
  var allVariationIds = []; // Collect Variation IDs
  var categoryMap = {};
  var cursor = null;
  var listCatalogUrl = 'https://connect.squareup.com/v2/catalog/list';

  do {
    var response = fetchCatalogPage(listCatalogUrl, cursor);

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

      if (Array.isArray(jsonData.objects) && jsonData.objects.length > 0) {
        jsonData.objects.forEach(function(item) {
          if (item.type === 'ITEM') {
            allItems.push(item);

            // Collect variation IDs
            if (Array.isArray(item.item_data.variations)) {
              item.item_data.variations.forEach(function(variation) {
                allVariationIds.push(variation.id);
              });
            }
          }
        });
      }

      // Build category map from related objects
      if (Array.isArray(jsonData.related_objects) && jsonData.related_objects.length > 0) {
        jsonData.related_objects.forEach(function(obj) {
          if (obj.type === "CATEGORY") {
            categoryMap[obj.id] = obj.category_data.name;
          }
        });
      }

      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 { items: [], variationIds: [], categoryMap: {} };
    }

  } while (cursor);  // Keep fetching until no cursor is returned

  Logger.log("Total Items Retrieved: " + allItems.length);
  Logger.log("Total Variation IDs Retrieved: " + allVariationIds.length);

  return { items: allItems, variationIds: allVariationIds, categoryMap: categoryMap };
}

function fetchCatalogPage(listCatalogUrl, cursor) {
  // Headers for API request
  var headers = {
    "Square-Version": "2023-10-18",  // Ensure API version is at least 2018-09-18
    "Content-Type": "application/json"
  };

  // Append cursor to URL for pagination
  var urlWithCursor = cursor ? listCatalogUrl + "?cursor=" + cursor + "&include_related_objects=true" : listCatalogUrl + "?include_related_objects=true";

  var listOptions = {
    "method": "GET",
    "headers": headers,
    "muteHttpExceptions": true  // Allows capturing full response in case of an error
  };

  return makeApiRequest(urlWithCursor, listOptions);
}

// Function to fetch inventory counts per variation using the GET /v2/inventory/{catalog_object_id} endpoint
function fetchInventoryCountsPerVariation(variationIds, locationIds) {
  var inventoryMap = {};
  var headers = {
    "Square-Version": "2023-10-18",
    "Content-Type": "application/json"
  };

  var batchSize = 50; // Adjust batch size to avoid exceeding limits

  for (var i = 0; i < variationIds.length; i += batchSize) {
    var batchVariationIds = variationIds.slice(i, i + batchSize);

    batchVariationIds.forEach(function(variationId) {
      var url = 'https://connect.squareup.com/v2/inventory/' + encodeURIComponent(variationId);
      if (locationIds.length > 0) {
        url += '?location_ids=' + locationIds.join(',');
      }

      var options = {
        "method": "GET",
        "headers": headers,
        "muteHttpExceptions": true
      };

      var response = makeApiRequest(url, options);

      if (response.getResponseCode() === 200) {
        var data = JSON.parse(response.getContentText());
        if (Array.isArray(data.counts)) {
          data.counts.forEach(function(count) {
            var key = count.catalog_object_id + '_' + count.location_id;
            inventoryMap[key] = count.quantity || "0";
          });
        }
      } else {
        Logger.log("Error retrieving inventory counts: " + response.getContentText());
      }
    });
  }

  Logger.log("Total Inventory Counts Retrieved: " + Object.keys(inventoryMap).length);
  return inventoryMap;
}

// Function to process items and write data to the sheet
function processAndWriteData(sheet, items, categoryMap, locationIds, inventoryMap, progressSheet) {
  // Prepare the header row dynamically to include all the extra fields and inventory columns for each location
  var headerRow = [
    "Variation ID (ID-B)", "Item ID (ID-A)", "title", "link", "Description", "Variation Name", "Price (CAD)",
    "GTIN (UPC/EAN/ISBN)", "is_deleted", "catalog_v1_ids", "present_at_all_locations",
    "item_visibility", "category_id", "category_name", "modifier_list_info", "product_type",
    "skip_modifier_screen", "tax_ids", "item_options", "item_option_values"
  ];

  // Add columns for location overrides for each location
  locationIds.forEach(function(locationId) {
    headerRow.push("Track Inventory at " + locationId);
    headerRow.push("Inventory Alert Type at " + locationId);
    headerRow.push("Inventory Alert Threshold at " + locationId);
  });

  locationIds.forEach(function(locationId) {
    headerRow.push("Inventory at " + locationId);
  });

  headerRow.push("image link", "additional_image_link_1", "additional_image_link_2"); // Add extra image columns at the end

  // Write header row to the sheet
  sheet.appendRow(headerRow);

  var allRows = [];
  var itemsProcessed = 0;

  items.forEach(function(item) {
    // Check if the user requested to stop processing
    var stopFlag = progressSheet.getRange('B5').getValue().toString().toUpperCase();
    if (stopFlag === 'STOP') {
      SpreadsheetApp.getUi().alert('Processing halted by user.');
      // Write remaining data to the sheet
      if (allRows.length > 0) {
        var range = sheet.getRange(sheet.getLastRow() + 1, 1, allRows.length, headerRow.length);
        range.setValues(allRows);
      }
      return;
    }

    // Ensure that the item has the required fields
    if (item.item_data) {
      var itemId = item.id || ""; // Item ID (ID-A)
      var itemName = item.item_data.name || "";
      var description = item.item_data.description || "";
      var itemUrl = item.item_data.ecom_uri || "";

      // Get Image URLs (primary and additional)
      var imageUrls = [];
      if (Array.isArray(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 (Array.isArray(item.image_ids) && item.image_ids.length > 0) {
        imageUrls = getImageUrls(item.image_ids, item.related_objects || []);  // Fallback to image_ids
      }

      // Ensure the columns for image URLs
      var primaryImageUrl = imageUrls[0] || "";
      var secondaryImageUrl = imageUrls[1] || "";
      var tertiaryImageUrl = imageUrls[2] || "";

      // Additional fields to capture
      var isDeleted = item.is_deleted || false;
      var catalogV1Ids = Array.isArray(item.catalog_v1_ids) ? item.catalog_v1_ids.map(function(id) {
        return id.catalog_v1_id;
      }).join(", ") : "";
      var presentAtAllLocations = item.present_at_all_locations || false;
      var itemVisibility = item.item_data.visibility || "";
      var categoryId = item.item_data.category_id || "";
      var categoryName = categoryMap[categoryId] || "";
      var modifierListInfo = item.item_data.modifier_list_info ? JSON.stringify(item.item_data.modifier_list_info) : "";
      var productType = item.item_data.product_type || "";
      var skipModifierScreen = item.item_data.skip_modifier_screen || false;
      var taxIds = Array.isArray(item.item_data.tax_ids) ? item.item_data.tax_ids.join(", ") : "";
      var itemOptions = item.item_data.item_options ? JSON.stringify(item.item_data.item_options) : "";
      var itemOptionValues = ""; // Will extract from variations

      // Add location-specific overrides
      var locationData = [];
      locationIds.forEach(function(locationId) {
        var locationOverride = item.item_data.variations && item.item_data.variations[0] && item.item_data.variations[0].item_variation_data.location_overrides
          ? item.item_data.variations[0].item_variation_data.location_overrides.find(function(override) {
              return override.location_id === locationId;
            })
          : null;

        if (locationOverride) {
          locationData.push(locationOverride.track_inventory || false); // Track Inventory
          locationData.push(locationOverride.inventory_alert_type || ""); // Inventory Alert Type
          locationData.push(locationOverride.inventory_alert_threshold || ""); // Inventory Alert Threshold
        } else {
          locationData.push("", "", "");  // If no override for this location, leave blank
        }
      });

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

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

          // Extract item option values for this variation
          if (Array.isArray(variation.item_variation_data.item_option_values)) {
            itemOptionValues = JSON.stringify(variation.item_variation_data.item_option_values);
          }

          // Retrieve inventory counts for each location
          var inventoryCounts = locationIds.map(function(locationId) {
            var key = variationId + '_' + locationId;
            var inventoryCount = inventoryMap.hasOwnProperty(key) ? inventoryMap[key] : "0";

            return inventoryCount;
          });

          // Prepare the row data
          var rowData = [
            variationId, itemId, itemName, itemUrl, description, variationName, price, gtin,
            isDeleted, catalogV1Ids, presentAtAllLocations,
            itemVisibility, categoryId, categoryName, modifierListInfo, productType, skipModifierScreen,
            taxIds, itemOptions, itemOptionValues
          ].concat(locationData, inventoryCounts, [primaryImageUrl, secondaryImageUrl, tertiaryImageUrl]);

          allRows.push(rowData);
        });
      }
    }

    itemsProcessed++;

    // Update progress indicators every 10 items
    if (itemsProcessed % 10 === 0) {
      var progressPercent = Math.round((itemsProcessed / items.length) * 100);
      progressSheet.getRange('B2').setValue(itemsProcessed);
      progressSheet.getRange('B3').setValue(progressPercent);
      SpreadsheetApp.flush();
    }

    // Write data to the sheet in batches of 500 rows
    if (allRows.length >= 500) {
      var range = sheet.getRange(sheet.getLastRow() + 1, 1, allRows.length, headerRow.length);
      range.setValues(allRows);
      allRows = [];
    }
  });

  // Write any remaining data to the sheet
  if (allRows.length > 0) {
    var range = sheet.getRange(sheet.getLastRow() + 1, 1, allRows.length, headerRow.length);
    range.setValues(allRows);
  }

  // Update final progress indicators
  progressSheet.getRange('B2').setValue(itemsProcessed);
  progressSheet.getRange('B3').setValue(100);
  SpreadsheetApp.flush();

  SpreadsheetApp.getUi().alert("Processing complete.");
}

function getImageUrls(imageIds, relatedObjects) {
  if (Array.isArray(imageIds) && imageIds.length > 0) {
    return imageIds.map(function(imageId) {
      var imageObject = relatedObjects.find(function(obj) {
        return obj.id === imageId && obj.type === "IMAGE";
      });
      return imageObject ? imageObject.image_data.url : "";
    });
  } else {
    return [];
  }
}

// Fetch location IDs for the merchant and return as an array
function fetchLocationIds() {
  var locationApiUrl = 'https://connect.squareup.com/v2/locations';

  var headers = {
    "Square-Version": "2023-10-18",
    "Content-Type": "application/json"
  };

  var options = {
    "method": "GET",
    "headers": headers,
    "muteHttpExceptions": true
  };

  var response = makeApiRequest(locationApiUrl, options);
  var locationIds = [];

  if (response.getResponseCode() === 200) {
    var jsonData = JSON.parse(response.getContentText());
    if (Array.isArray(jsonData.locations) && jsonData.locations.length > 0) {
      locationIds = jsonData.locations.map(function(location) {
        return location.id;
      });
    } else {
      Logger.log("No locations found in the API response.");
      SpreadsheetApp.getUi().alert("No locations found for this merchant.");
    }
  } else {
    Logger.log("Error retrieving locations: " + response.getContentText());
    SpreadsheetApp.getUi().alert("Error retrieving locations. Check logs.");
  }

  return locationIds;
}

// Function to handle API requests and token management
function makeApiRequest(url, options) {
  var userProperties = PropertiesService.getUserProperties();
  var accessToken = userProperties.getProperty('SQUARE_ACCESS_TOKEN');

  if (!accessToken || accessToken === 'MY TOKEN HERE') {
    accessToken = Browser.inputBox('Enter your Square Production Access Token:', Browser.Buttons.OK_CANCEL);
    if (accessToken === 'cancel' || accessToken === '') {
      SpreadsheetApp.getUi().alert('Access token is required to proceed.');
      throw new Error('Access token is required to proceed.');
    } else {
      // Store the access token securely in user properties
      userProperties.setProperty('SQUARE_ACCESS_TOKEN', accessToken);
    }
  }

  // Ensure the Authorization header has the correct token
  options.headers["Authorization"] = "Bearer " + accessToken;

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

  if (response.getResponseCode() === 401) {
    // Unauthorized, token may be invalid or expired
    // Prompt for new token
    accessToken = Browser.inputBox('Access token is invalid or expired. Please enter a new Square Production Access Token:', Browser.Buttons.OK_CANCEL);
    if (accessToken === 'cancel' || accessToken === '') {
      SpreadsheetApp.getUi().alert('Access token is required to proceed.');
      throw new Error('Access token is required to proceed.');
    } else {
      // Store the access token securely in user properties
      userProperties.setProperty('SQUARE_ACCESS_TOKEN', accessToken);
      // Update the Authorization header
      options.headers["Authorization"] = "Bearer " + accessToken;
      // Retry the request
      response = UrlFetchApp.fetch(url, options);
      if (response.getResponseCode() === 200) {
        return response;
      } else {
        Logger.log("Error after retrying with new token: " + response.getContentText());
        SpreadsheetApp.getUi().alert("Error after retrying with new token. Check logs for details.");
        throw new Error("Error after retrying with new token.");
      }
    }
  } else {
    return response;
  }
}

type or paste code here

2

It looks like you’re able to retrieve the category ID but not the category name. This can happen if the related objects (like categories) are not being fetched or mapped correctly. Here’s a how to ensure you get all necessary data, including category names.

Ensuring Complete Data Retrieval

  1. Fetch All Catalog Items and Related Objects:
  • Ensure that when you fetch catalog items, you include related objects like categories.
  1. Map Category IDs to Category Names:
  • Build a map of category IDs to category names from the related objects and use this map to populate the category names.

Example Code for Fetching and Mapping Categories

Here’s a refined version of your code to ensure you fetch and map categories correctly:

// Function to fetch all catalog items and variations
function fetchAllCatalogItems() {
  var allItems = [];
  var allVariationIds = [];
  var categoryMap = {};
  var cursor = null;
  var listCatalogUrl = 'https://connect.squareup.com/v2/catalog/list';

  do {
    var response = fetchCatalogPage(listCatalogUrl, cursor);

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

      if (Array.isArray(jsonData.objects) && jsonData.objects.length > 0) {
        jsonData.objects.forEach(function(item) {
          if (item.type === 'ITEM') {
            allItems.push(item);

            if (Array.isArray(item.item_data.variations)) {
              item.item_data.variations.forEach(function(variation) {
                allVariationIds.push(variation.id);
              });
            }
          }
        });
      }

      if (Array.isArray(jsonData.related_objects) && jsonData.related_objects.length > 0) {
        jsonData.related_objects.forEach(function(obj) {
          if (obj.type === "CATEGORY") {
            categoryMap[obj.id] = obj.category_data.name;
          }
        });
      }

      cursor = jsonData.cursor || null;
    } else {
      Logger.log("Error details from List Catalog: " + response.getContentText());
      SpreadsheetApp.getUi().alert("Error retrieving catalog. Check logs for details.");
      return { items: [], variationIds: [], categoryMap: {} };
    }

  } while (cursor);

  Logger.log("Total Items Retrieved: " + allItems.length);
  Logger.log("Total Variation IDs Retrieved: " + allVariationIds.length);

  return { items: allItems, variationIds: allVariationIds, categoryMap: categoryMap };
}

// Function to fetch a catalog page
function fetchCatalogPage(listCatalogUrl, cursor) {
  var headers = {
    "Square-Version": "2023-10-18",
    "Content-Type": "application/json"
  };

  var urlWithCursor = cursor ? listCatalogUrl + "?cursor=" + cursor + "&include_related_objects=true" : listCatalogUrl + "?include_related_objects=true";

  var listOptions = {
    "method": "GET",
    "headers": headers,
    "muteHttpExceptions": true
  };

  return makeApiRequest(urlWithCursor, listOptions);
}

// Function to handle API requests and token management
function makeApiRequest(url, options) {
  var userProperties = PropertiesService.getUserProperties();
  var accessToken = userProperties.getProperty('SQUARE_ACCESS_TOKEN');

  if (!accessToken || accessToken === 'MY TOKEN HERE') {
    accessToken = Browser.inputBox('Enter your Square Production Access Token:', Browser.Buttons.OK_CANCEL);
    if (accessToken === 'cancel' || accessToken === '') {
      SpreadsheetApp.getUi().alert('Access token is required to proceed.');
      throw new Error('Access token is required to proceed.');
    } else {
      userProperties.setProperty('SQUARE_ACCESS_TOKEN', accessToken);
    }
  }

  options.headers["Authorization"] = "Bearer " + accessToken;

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

  if (response.getResponseCode() === 401) {
    accessToken = Browser.inputBox('Access token is invalid or expired. Please enter a new Square Production Access Token:', Browser.Buttons.OK_CANCEL);
    if (accessToken === 'cancel' || accessToken === '') {
      SpreadsheetApp.getUi().alert('Access token is required to proceed.');
      throw new Error('Access token is required to proceed.');
    } else {
      userProperties.setProperty('SQUARE_ACCESS_TOKEN', accessToken);
      options.headers["Authorization"] = "Bearer " + accessToken;
      response = UrlFetchApp.fetch(url, options);
      if (response.getResponseCode() === 200) {
        return response;
      } else {
        Logger.log("Error after retrying with new token: " + response.getContentText());
        SpreadsheetApp.getUi().alert("Error after retrying with new token. Check logs for details.");
        throw new Error("Error after retrying with new token.");
      }
    }
  } else {
    return response;
  }
}
  • Fetching Related Objects: Ensure that include_related_objects=true is appended to the URL to fetch related objects such as categories.
  • Mapping Categories: Use the related objects to build a map of category IDs to category names. :slightly_smiling_face:
3

Thank you so much for your help!