Guest User

Square API Catalog Export Code for Google Sheets

a guest
Oct 28th, 2024
379
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
JavaScript 28.62 KB | Source Code | 0 0
  1. // This function creates a custom menu in the Google Sheets UI
  2. function onOpen() {
  3.   var ui = SpreadsheetApp.getUi();
  4.   ui.createMenu('Square API')
  5.     .addItem('Set API Key', 'setApiKey') // Menu item to set the API key
  6.     .addItem('Set Email Address', 'setEmailAddress') // Menu item to set the email address
  7.     .addItem('Start Processing', 'startProcessing')
  8.     .addItem('Set Daily Timer', 'createDailyTrigger')
  9.     .addToUi();
  10. }
  11.  
  12. // Function to prompt the user to enter their Square API key
  13. function setApiKey() {
  14.   var ui = SpreadsheetApp.getUi();
  15.  
  16.   // Prompt the user for the API key
  17.   var response = ui.prompt('Set Square API Key', 'Please enter your Square API access token:', ui.ButtonSet.OK_CANCEL);
  18.  
  19.   // Process the user's response
  20.   if (response.getSelectedButton() == ui.Button.OK) {
  21.     var apiKey = response.getResponseText().trim();
  22.  
  23.     if (apiKey) {
  24.       // Store the API key in document properties
  25.       var documentProperties = PropertiesService.getDocumentProperties();
  26.       documentProperties.setProperty('SQUARE_ACCESS_TOKEN', apiKey);
  27.  
  28.       // Inform the user that the key has been saved
  29.       ui.alert('Success', 'Your Square API access token has been saved securely.', ui.ButtonSet.OK);
  30.     } else {
  31.       ui.alert('Error', 'No API key entered. Please try again.', ui.ButtonSet.OK);
  32.     }
  33.   } else {
  34.     ui.alert('Operation cancelled.');
  35.   }
  36. }
  37.  
  38. // Function to prompt the user to enter their email address
  39. function setEmailAddress() {
  40.   var ui = SpreadsheetApp.getUi();
  41.  
  42.   // Prompt the user for the email address
  43.   var response = ui.prompt('Set Notification Email', 'Please enter your email address:', ui.ButtonSet.OK_CANCEL);
  44.  
  45.   // Process the user's response
  46.   if (response.getSelectedButton() == ui.Button.OK) {
  47.     var emailAddress = response.getResponseText().trim();
  48.  
  49.     if (emailAddress) {
  50.       // Store the email address in document properties
  51.       var documentProperties = PropertiesService.getDocumentProperties();
  52.       documentProperties.setProperty('NOTIFICATION_EMAIL', emailAddress);
  53.  
  54.       // Inform the user that the email address has been saved
  55.       ui.alert('Success', 'Your email address has been saved.', ui.ButtonSet.OK);
  56.     } else {
  57.       ui.alert('Error', 'No email address entered. Please try again.', ui.ButtonSet.OK);
  58.     }
  59.   } else {
  60.     ui.alert('Operation cancelled.');
  61.   }
  62. }
  63.  
  64. // Main function to start processing
  65. function startProcessing() {
  66.   var ss = SpreadsheetApp.getActiveSpreadsheet();
  67.   var sheetName = 'API-Export';
  68.  
  69.   try {
  70.     // Clear existing sheet if it exists, or create a new one if it doesn't
  71.     var sheet = ss.getSheetByName(sheetName);
  72.     if (sheet) {
  73.       sheet.clear(); // Clear the content but keep the sheet
  74.     } else {
  75.       sheet = ss.insertSheet();
  76.       sheet.setName(sheetName);
  77.     }
  78.  
  79.     // Similar steps for the Progress sheet
  80.     var progressSheetName = 'Processing-Progress';
  81.     var progressSheet = ss.getSheetByName(progressSheetName);
  82.     if (progressSheet) {
  83.       progressSheet.clear(); // Clear the content but keep the sheet
  84.     } else {
  85.       progressSheet = ss.insertSheet();
  86.       progressSheet.setName(progressSheetName);
  87.     }
  88.  
  89.     // Initialize progress indicators
  90.     progressSheet.getRange('A1').setValue('Total Variations:');
  91.     progressSheet.getRange('A2').setValue('Variations Processed:');
  92.     progressSheet.getRange('A3').setValue('Progress (%):');
  93.     progressSheet.getRange('A5').setValue('Type "STOP" in cell B5 to halt processing.');
  94.  
  95.     // Reset stop flag
  96.     progressSheet.getRange('B5').setValue('');
  97.  
  98.     // Fetch all location IDs automatically
  99.     var locationIds = fetchLocationIds();
  100.     if (!locationIds.length) {
  101.       Logger.log("No locations found for the merchant.");
  102.       displayAlert("No locations found for this merchant.");
  103.       return;
  104.     }
  105.  
  106.     // Fetch all catalog items and variations
  107.     var catalogData = fetchAllCatalogItems();
  108.     if (catalogData.items.length === 0) {
  109.       displayAlert("No items found in the catalog.");
  110.       return;
  111.     }
  112.  
  113.     // Fetch all categories
  114.     var categoryMap = fetchAllCategories();
  115.  
  116.     // Update total variations in progress sheet
  117.     progressSheet.getRange('B1').setValue(catalogData.variationCount);
  118.  
  119.     // Create a variation map
  120.     var variationMap = buildVariationMap(catalogData.items, categoryMap);
  121.  
  122.     // Fetch inventory counts for all variations
  123.     var inventoryMap = fetchInventoryCountsForAllVariations(variationMap, locationIds, progressSheet);
  124.  
  125.     // Process variations and write data to the sheet
  126.     processAndWriteData(sheet, variationMap, locationIds, inventoryMap, progressSheet);
  127.  
  128.     // Send success email
  129.     var documentProperties = PropertiesService.getDocumentProperties();
  130.     var emailAddress = documentProperties.getProperty('NOTIFICATION_EMAIL');
  131.  
  132.     if (emailAddress) {
  133.       MailApp.sendEmail({
  134.         to: emailAddress,
  135.         subject: "Square Data Refresh Successful",
  136.         body: "The daily refresh of Square data was completed successfully."
  137.       });
  138.     } else {
  139.       Logger.log('Notification email address is not set. Please use "Set Email Address" in the "Square API" menu.');
  140.     }
  141.  
  142.   } catch (error) {
  143.     // Send failure email
  144.     var documentProperties = PropertiesService.getDocumentProperties();
  145.     var emailAddress = documentProperties.getProperty('NOTIFICATION_EMAIL');
  146.  
  147.     if (emailAddress) {
  148.       MailApp.sendEmail({
  149.         to: emailAddress,
  150.         subject: "Square Data Refresh Failed",
  151.         body: "The daily Square data refresh failed with the following error: " + error.message
  152.       });
  153.     } else {
  154.       Logger.log('Notification email address is not set. Please use "Set Email Address" in the "Square API" menu.');
  155.     }
  156.  
  157.     // Log the error in the Spreadsheet
  158.     Logger.log("Error: " + error.message);
  159.     // Optionally, show an alert if running manually
  160.     displayAlert("An error occurred: " + error.message);
  161.   }
  162. }
  163.  
  164. // Function to create a time-driven trigger to refresh data daily
  165. function createDailyTrigger() {
  166.   // First, delete any existing triggers to avoid duplicates
  167.   deleteExistingTriggers();
  168.  
  169.   // Set a time-driven trigger to run the startProcessing function every day at 12 PM
  170.   ScriptApp.newTrigger('startProcessing')
  171.     .timeBased()
  172.     .atHour(16)  // Set the time here (4 PM in this case)
  173.     .everyDays(1)  // Run every day
  174.     .create();
  175.  
  176.   // Show confirmation to the user
  177.   SpreadsheetApp.getUi().alert("Daily timer has been set for 4 PM.");
  178. }
  179.  
  180. // Function to delete any existing time-based triggers for 'startProcessing' to avoid duplicates
  181. function deleteExistingTriggers() {
  182.   var triggers = ScriptApp.getProjectTriggers();
  183.   for (var i = 0; i < triggers.length; i++) {
  184.     if (triggers[i].getHandlerFunction() == 'startProcessing') {
  185.       ScriptApp.deleteTrigger(triggers[i]);
  186.     }
  187.   }
  188. }
  189.  
  190. // Function to fetch all catalog items and variations
  191. function fetchAllCatalogItems() {
  192.   var allItems = [];
  193.   var variationCount = 0;
  194.   var cursor = null;
  195.   var listCatalogUrl = 'https://connect.squareup.com/v2/catalog/list';
  196.  
  197.   do {
  198.     var response = fetchCatalogPage(listCatalogUrl, cursor);
  199.  
  200.     if (response.getResponseCode() === 200) {
  201.       var jsonData = JSON.parse(response.getContentText());
  202.  
  203.       if (Array.isArray(jsonData.objects) && jsonData.objects.length > 0) {
  204.         jsonData.objects.forEach(function(item) {
  205.           if (item.type === 'ITEM') {
  206.             allItems.push(item);
  207.  
  208.             if (Array.isArray(item.item_data.variations)) {
  209.               variationCount += item.item_data.variations.length;
  210.             }
  211.           }
  212.         });
  213.       }
  214.  
  215.       cursor = jsonData.cursor || null;
  216.     } else {
  217.       Logger.log("Error details from List Catalog: " + response.getContentText());
  218.       displayAlert("Error retrieving catalog. Check logs for details.");
  219.       return { items: [], variationCount: 0 };
  220.     }
  221.  
  222.   } while (cursor);
  223.  
  224.   Logger.log("Total Items Retrieved: " + allItems.length);
  225.   Logger.log("Total Variations Retrieved: " + variationCount);
  226.  
  227.   return { items: allItems, variationCount: variationCount };
  228. }
  229.  
  230. // Function to fetch a catalog page
  231. function fetchCatalogPage(listCatalogUrl, cursor) {
  232.   var headers = {
  233.     "Square-Version": "2023-10-18",
  234.     "Content-Type": "application/json"
  235.   };
  236.  
  237.   var urlWithCursor = cursor ? listCatalogUrl + "?cursor=" + cursor + "&include_related_objects=true" : listCatalogUrl + "?include_related_objects=true";
  238.  
  239.   var listOptions = {
  240.     "method": "GET",
  241.     "headers": headers,
  242.     "muteHttpExceptions": true
  243.   };
  244.  
  245.   return makeApiRequest(urlWithCursor, listOptions);
  246. }
  247.  
  248. // Function to fetch all categories and build a category map
  249. function fetchAllCategories() {
  250.   var allCategories = {};
  251.   var cursor = null;
  252.   var listCatalogUrl = 'https://connect.squareup.com/v2/catalog/list';
  253.  
  254.   do {
  255.     var urlWithCursor = cursor ? listCatalogUrl + "?cursor=" + cursor + "&types=CATEGORY" : listCatalogUrl + "?types=CATEGORY";
  256.     var headers = {
  257.       "Square-Version": "2023-10-18",
  258.       "Content-Type": "application/json"
  259.     };
  260.     var options = {
  261.       "method": "GET",
  262.       "headers": headers,
  263.       "muteHttpExceptions": true
  264.     };
  265.     var response = makeApiRequest(urlWithCursor, options);
  266.  
  267.     if (response.getResponseCode() === 200) {
  268.       var jsonData = JSON.parse(response.getContentText());
  269.  
  270.       if (Array.isArray(jsonData.objects) && jsonData.objects.length > 0) {
  271.         jsonData.objects.forEach(function(obj) {
  272.           if (obj.type === "CATEGORY") {
  273.             allCategories[obj.id] = obj.category_data.name;
  274.           }
  275.         });
  276.       }
  277.  
  278.       cursor = jsonData.cursor || null;
  279.     } else {
  280.       Logger.log("Error fetching categories: " + response.getContentText());
  281.       displayAlert("Error retrieving categories. Check logs for details.");
  282.       return {};
  283.     }
  284.  
  285.   } while (cursor);
  286.  
  287.   Logger.log("Total Categories Retrieved: " + Object.keys(allCategories).length);
  288.  
  289.   return allCategories;
  290. }
  291.  
  292. // Function to build a variation map
  293. function buildVariationMap(items, categoryMap) {
  294.   var variationMap = {};
  295.  
  296.   items.forEach(function(item) {
  297.     if (item.item_data && Array.isArray(item.item_data.variations)) {
  298.       var itemId = item.id || "";
  299.       var itemName = item.item_data.name || "";
  300.       var description = item.item_data.description || "";
  301.       var itemUrl = item.item_data.ecom_uri || "";
  302.  
  303.       // Get Image URLs (primary and additional)
  304.       var imageUrls = [];
  305.       if (Array.isArray(item.item_data.ecom_image_uris) && item.item_data.ecom_image_uris.length > 0) {
  306.         imageUrls = item.item_data.ecom_image_uris; // Use ecom_image_uris first
  307.       } else if (Array.isArray(item.image_ids) && item.image_ids.length > 0) {
  308.         imageUrls = getImageUrls(item.image_ids, item.related_objects || []);  // Fallback to image_ids
  309.       }
  310.  
  311.       // Ensure the columns for image URLs
  312.       var primaryImageUrl = imageUrls[0] || "";
  313.       var secondaryImageUrl = imageUrls[1] || "";
  314.       var tertiaryImageUrl = imageUrls[2] || "";
  315.  
  316.       // Additional fields to capture
  317.       var isDeleted = item.is_deleted || false;
  318.       var catalogV1Ids = Array.isArray(item.catalog_v1_ids) ? item.catalog_v1_ids.map(function(id) {
  319.         return id.catalog_v1_id;
  320.       }).join(", ") : "";
  321.       var presentAtAllLocationsItem = item.present_at_all_locations || false;
  322.       var presentAtLocationIdsItem = item.present_at_location_ids || [];
  323.       var absentAtLocationIdsItem = item.absent_at_location_ids || [];
  324.       var itemVisibility = item.item_data.visibility || "";
  325.       var categoryId = item.item_data.category_id || "";
  326.       var categoryName = categoryMap[categoryId] || "";
  327.       var modifierListInfo = item.item_data.modifier_list_info ? JSON.stringify(item.item_data.modifier_list_info) : "";
  328.       var productType = item.item_data.product_type || "";
  329.       var skipModifierScreen = item.item_data.skip_modifier_screen || false;
  330.       var taxIds = Array.isArray(item.item_data.tax_ids) ? item.item_data.tax_ids.join(", ") : "";
  331.       var itemOptions = item.item_data.item_options ? JSON.stringify(item.item_data.item_options) : "";
  332.       var availableOnline = item.item_data.available_online || false;
  333.       var availableForPickup = item.item_data.available_for_pickup || false;
  334.  
  335.       item.item_data.variations.forEach(function(variation) {
  336.         var variationId = variation.id || "";
  337.         var variationName = variation.item_variation_data.name || "";
  338.         var price = variation.item_variation_data.price_money
  339.           ? variation.item_variation_data.price_money.amount / 100
  340.           : "";
  341.  
  342.         // Retrieve the GTIN (UPC/EAN/ISBN)
  343.         var gtin = variation.item_variation_data.upc || "";
  344.  
  345.         // Extract item option values for this variation
  346.         var itemOptionValues = "";
  347.         if (Array.isArray(variation.item_variation_data.item_option_values)) {
  348.           itemOptionValues = JSON.stringify(variation.item_variation_data.item_option_values);
  349.         }
  350.  
  351.         // New fields
  352.         var sku = variation.item_variation_data.sku || "";
  353.         var customAttributes = variation.custom_attribute_values ? JSON.stringify(variation.custom_attribute_values) : "";
  354.         var modifierLists = modifierListInfo; // Already retrieved at item level
  355.         var measurementUnitId = variation.item_variation_data.measurement_unit_id || "";
  356.         var pricingType = variation.item_variation_data.pricing_type || "";
  357.         var visibility = itemVisibility; // Already retrieved at item level
  358.         var updatedAt = variation.updated_at || item.updated_at || "";
  359.  
  360.         // Availability fields
  361.         var presentAtAllLocations = variation.present_at_all_locations !== undefined ? variation.present_at_all_locations : presentAtAllLocationsItem;
  362.         var presentAtLocationIds = variation.present_at_location_ids || presentAtLocationIdsItem;
  363.         var absentAtLocationIds = variation.absent_at_location_ids || absentAtLocationIdsItem;
  364.  
  365.         // Add location-specific overrides
  366.         var locationData = {};
  367.         if (Array.isArray(variation.item_variation_data.location_overrides)) {
  368.           variation.item_variation_data.location_overrides.forEach(function(override) {
  369.             var locId = override.location_id;
  370.             locationData[locId] = {
  371.               track_inventory: override.track_inventory || false,
  372.               inventory_alert_type: override.inventory_alert_type || "",
  373.               inventory_alert_threshold: override.inventory_alert_threshold || ""
  374.             };
  375.           });
  376.         }
  377.  
  378.         variationMap[variationId] = {
  379.           variationId: variationId,
  380.           itemId: itemId,
  381.           itemName: itemName,
  382.           description: description,
  383.           itemUrl: itemUrl,
  384.           variationName: variationName,
  385.           price: price,
  386.           gtin: gtin,
  387.           isDeleted: isDeleted,
  388.           catalogV1Ids: catalogV1Ids,
  389.           presentAtAllLocations: presentAtAllLocations,
  390.           presentAtLocationIds: presentAtLocationIds,
  391.           absentAtLocationIds: absentAtLocationIds,
  392.           itemVisibility: visibility,
  393.           categoryId: categoryId,
  394.           categoryName: categoryName,
  395.           modifierListInfo: modifierLists,
  396.           productType: productType,
  397.           skipModifierScreen: skipModifierScreen,
  398.           taxIds: taxIds,
  399.           itemOptions: itemOptions,
  400.           itemOptionValues: itemOptionValues,
  401.           sku: sku,
  402.           customAttributes: customAttributes,
  403.           measurementUnitId: measurementUnitId,
  404.           pricingType: pricingType,
  405.           availableOnline: availableOnline,
  406.           availableForPickup: availableForPickup,
  407.           updatedAt: updatedAt,
  408.           locationData: locationData,
  409.           images: [primaryImageUrl, secondaryImageUrl, tertiaryImageUrl]
  410.         };
  411.       });
  412.     }
  413.   });
  414.  
  415.   return variationMap;
  416. }
  417.  
  418. // Function to fetch inventory counts for all variations using Retrieve Inventory Count endpoint
  419. function fetchInventoryCountsForAllVariations(variationMap, locationIds, progressSheet) {
  420.   var inventoryMap = {};
  421.   var variationIds = Object.keys(variationMap);
  422.   var headers = {
  423.     "Square-Version": "2023-10-18",
  424.     "Content-Type": "application/json"
  425.   };
  426.  
  427.   var variationsProcessed = 0;
  428.  
  429.   for (var i = 0; i < variationIds.length; i++) {
  430.     var variationId = variationIds[i];
  431.  
  432.     // Check if the user requested to stop processing
  433.     var stopFlag = progressSheet.getRange('B5').getValue().toString().toUpperCase();
  434.     if (stopFlag === 'STOP') {
  435.       Logger.log('Processing halted by user during inventory fetching.');
  436.       break;
  437.     }
  438.  
  439.     // Throttle requests to avoid rate limits
  440.     if (i > 0 && i % 100 == 0) {
  441.       Logger.log('Sleeping for 1 second to avoid rate limits...');
  442.       Utilities.sleep(1000); // Sleep for 1 second
  443.     }
  444.  
  445.     var cursor = null;
  446.     do {
  447.       var url = 'https://connect.squareup.com/v2/inventory/' + variationId;
  448.       if (locationIds.length > 0) {
  449.         url += '?location_ids=' + locationIds.join(',');
  450.       }
  451.       if (cursor) {
  452.         url += (locationIds.length > 0 ? '&' : '?') + 'cursor=' + cursor;
  453.       }
  454.  
  455.       var options = {
  456.         "method": "GET",
  457.         "headers": headers,
  458.         "muteHttpExceptions": true
  459.       };
  460.  
  461.       var response = makeApiRequest(url, options);
  462.  
  463.       if (response.getResponseCode() === 200) {
  464.         var data = JSON.parse(response.getContentText());
  465.         if (Array.isArray(data.counts)) {
  466.           data.counts.forEach(function(count) {
  467.             var key = count.catalog_object_id + '_' + count.location_id;
  468.             var quantity = parseInt(count.quantity || "0", 10);
  469.             inventoryMap[key] = {
  470.               quantity: quantity,
  471.               availability: quantity > 0 ? 'Available' : 'Unavailable'
  472.             };
  473.           });
  474.         } else {
  475.           Logger.log("No counts found for variation ID " + variationId);
  476.         }
  477.  
  478.         cursor = data.cursor || null;
  479.       } else {
  480.         Logger.log("Error retrieving inventory count for variation ID " + variationId + ": " + response.getContentText());
  481.         displayAlert("Error retrieving inventory count for variation ID " + variationId + ": " + response.getContentText());
  482.         break; // Exit the loop on error
  483.       }
  484.     } while (cursor);
  485.  
  486.     variationsProcessed++;
  487.  
  488.     // Update progress indicators every 100 variations
  489.     if (variationsProcessed % 100 === 0) {
  490.       var progressPercent = Math.round((variationsProcessed / variationIds.length) * 100);
  491.       progressSheet.getRange('B2').setValue(variationsProcessed);
  492.       progressSheet.getRange('B3').setValue(progressPercent);
  493.       SpreadsheetApp.flush();
  494.     }
  495.   }
  496.  
  497.   Logger.log("Total Inventory Counts Retrieved: " + Object.keys(inventoryMap).length);
  498.   return inventoryMap;
  499. }
  500.  
  501. // Function to process variations and write data to the sheet
  502. function processAndWriteData(sheet, variationMap, locationIds, inventoryMap, progressSheet) {
  503.   // Prepare the header row dynamically to include all the extra fields and inventory columns for each location
  504.   var headerRow = [
  505.     "Variation ID (ID-B)", "Item ID (ID-A)", "Title", "Link", "Description", "Variation Name", "Price (CAD)",
  506.     "GTIN (UPC/EAN/ISBN)", "SKU", "Custom Attributes", "Item Options", "Modifier Lists", "Product Type", "Measurement Unit",
  507.     "Pricing Type", "Visibility", "Available Online", "Available for Pickup", "Updated At", "is_deleted",
  508.     "catalog_v1_ids", "present_at_all_locations", "item_visibility", "category_id", "category_name",
  509.     "modifier_list_info", "product_type", "skip_modifier_screen", "tax_ids", "item_option_values"
  510.   ];
  511.  
  512.   // Add columns for location overrides for each location
  513.   locationIds.forEach(function(locationId) {
  514.     headerRow.push("Track Inventory at " + locationId);
  515.     headerRow.push("Inventory Alert Type at " + locationId);
  516.     headerRow.push("Inventory Alert Threshold at " + locationId);
  517.   });
  518.  
  519.   // Add columns for availability at each location based on catalog data (active/inactive)
  520.   locationIds.forEach(function(locationId) {
  521.     headerRow.push("Is Active at " + locationId);
  522.   });
  523.  
  524.   // Add columns for inventory counts at each location
  525.   locationIds.forEach(function(locationId) {
  526.     headerRow.push("Inventory at " + locationId);
  527.   });
  528.  
  529.   headerRow.push("Image Link", "Additional Image Link 1", "Additional Image Link 2"); // Add extra image columns at the end
  530.  
  531.   // Write header row to the sheet
  532.   sheet.appendRow(headerRow);
  533.  
  534.   var allRows = [];
  535.   var variationsProcessed = 0;
  536.   var stopProcessing = false; // Flag to control processing based on user input
  537.  
  538.   // Iterate over variationMap
  539.   for (var variationId in variationMap) {
  540.     if (variationMap.hasOwnProperty(variationId)) {
  541.       // Check if the user requested to stop processing
  542.       var stopFlag = progressSheet.getRange('B5').getValue().toString().toUpperCase();
  543.       if (stopFlag === 'STOP') {
  544.         Logger.log('Processing halted by user.');
  545.         stopProcessing = true;
  546.         break;
  547.       }
  548.  
  549.       var variationData = variationMap[variationId];
  550.  
  551.       // Retrieve inventory counts for each location
  552.       var inventoryCounts = [];
  553.       var availabilityStatuses = [];
  554.  
  555.       locationIds.forEach(function(locationId) {
  556.         var key = variationId + '_' + locationId;
  557.         var inventoryInfo = inventoryMap.hasOwnProperty(key) ? inventoryMap[key] : { quantity: 0, availability: 'Unavailable' };
  558.         inventoryCounts.push(inventoryInfo.quantity);
  559.         availabilityStatuses.push(inventoryInfo.availability);
  560.       });
  561.  
  562.       // Determine if variation is active at each location based on catalog data
  563.       var activeStatuses = [];
  564.       locationIds.forEach(function(locationId) {
  565.         var isActive = isVariationActiveAtLocation(variationData, locationId);
  566.         activeStatuses.push(isActive ? 'Active' : 'Inactive');
  567.       });
  568.  
  569.       // Add location-specific overrides
  570.       var locationOverrides = [];
  571.       locationIds.forEach(function(locationId) {
  572.         var locData = variationData.locationData[locationId] || {};
  573.         locationOverrides.push(locData.track_inventory || "");
  574.         locationOverrides.push(locData.inventory_alert_type || "");
  575.         locationOverrides.push(locData.inventory_alert_threshold || "");
  576.       });
  577.  
  578.       // Prepare the row data
  579.       var rowData = [
  580.         variationData.variationId, variationData.itemId, variationData.itemName, variationData.itemUrl,
  581.         variationData.description, variationData.variationName, variationData.price, variationData.gtin,
  582.         variationData.sku, variationData.customAttributes, variationData.itemOptions, variationData.modifierListInfo,
  583.         variationData.productType, variationData.measurementUnitId, variationData.pricingType, variationData.itemVisibility,
  584.         variationData.availableOnline, variationData.availableForPickup, variationData.updatedAt, variationData.isDeleted,
  585.         variationData.catalogV1Ids, variationData.presentAtAllLocations, variationData.itemVisibility,
  586.         variationData.categoryId, variationData.categoryName, variationData.modifierListInfo, variationData.productType,
  587.         variationData.skipModifierScreen, variationData.taxIds, variationData.itemOptionValues
  588.       ].concat(locationOverrides, activeStatuses, inventoryCounts, variationData.images);
  589.  
  590.       allRows.push(rowData);
  591.  
  592.       variationsProcessed++;
  593.  
  594.       // Update progress indicators every 100 variations
  595.       if (variationsProcessed % 100 === 0) {
  596.         var progressPercent = Math.round((variationsProcessed / Object.keys(variationMap).length) * 100);
  597.         progressSheet.getRange('B2').setValue(variationsProcessed);
  598.         progressSheet.getRange('B3').setValue(progressPercent);
  599.         SpreadsheetApp.flush();
  600.       }
  601.  
  602.       // Write data to the sheet in batches of 500 rows
  603.       if (allRows.length >= 500) {
  604.         var range = sheet.getRange(sheet.getLastRow() + 1, 1, allRows.length, headerRow.length);
  605.         range.setValues(allRows);
  606.         allRows = [];
  607.       }
  608.     }
  609.   }
  610.  
  611.   // Write any remaining data to the sheet
  612.   if (allRows.length > 0) {
  613.     var range = sheet.getRange(sheet.getLastRow() + 1, 1, allRows.length, headerRow.length);
  614.     range.setValues(allRows);
  615.   }
  616.  
  617.   // Update final progress indicators
  618.   progressSheet.getRange('B2').setValue(variationsProcessed);
  619.   progressSheet.getRange('B3').setValue(100);
  620.   SpreadsheetApp.flush();
  621.  
  622.   if (!stopProcessing) {
  623.     displayAlert("Processing complete.");
  624.   }
  625. }
  626.  
  627. // Helper function to determine if a variation is active at a specific location
  628. function isVariationActiveAtLocation(variationData, locationId) {
  629.   if (variationData.presentAtAllLocations) {
  630.     return true;
  631.   } else if (variationData.presentAtLocationIds && variationData.presentAtLocationIds.includes(locationId)) {
  632.     return true;
  633.   } else if (variationData.absentAtLocationIds && variationData.absentAtLocationIds.includes(locationId)) {
  634.     return false;
  635.   } else {
  636.     // If neither present nor absent lists include the location, default to inactive
  637.     return false;
  638.   }
  639. }
  640.  
  641. // Function to get image URLs from image IDs and related objects
  642. function getImageUrls(imageIds, relatedObjects) {
  643.   if (Array.isArray(imageIds) && imageIds.length > 0) {
  644.     return imageIds.map(function(imageId) {
  645.       var imageObject = relatedObjects.find(function(obj) {
  646.         return obj.id === imageId && obj.type === "IMAGE";
  647.       });
  648.       return imageObject ? imageObject.image_data.url : "";
  649.     });
  650.   } else {
  651.     return [];
  652.   }
  653. }
  654.  
  655. // Fetch location IDs for the merchant and return as an array
  656. function fetchLocationIds() {
  657.   var locationApiUrl = 'https://connect.squareup.com/v2/locations';
  658.  
  659.   var headers = {
  660.     "Square-Version": "2023-10-18",
  661.     "Content-Type": "application/json"
  662.   };
  663.  
  664.   var options = {
  665.     "method": "GET",
  666.     "headers": headers,
  667.     "muteHttpExceptions": true
  668.   };
  669.  
  670.   var response = makeApiRequest(locationApiUrl, options);
  671.   var locationIds = [];
  672.  
  673.   if (response.getResponseCode() === 200) {
  674.     var jsonData = JSON.parse(response.getContentText());
  675.     if (Array.isArray(jsonData.locations) && jsonData.locations.length > 0) {
  676.       locationIds = jsonData.locations.map(function(location) {
  677.         return location.id;
  678.       });
  679.     } else {
  680.       Logger.log("No locations found in the API response.");
  681.       displayAlert("No locations found for this merchant.");
  682.     }
  683.   } else {
  684.     Logger.log("Error retrieving locations: " + response.getContentText());
  685.     displayAlert("Error retrieving locations. Check logs.");
  686.   }
  687.  
  688.   return locationIds;
  689. }
  690.  
  691. // Function to handle API requests and token management
  692. function makeApiRequest(url, options) {
  693.   var documentProperties = PropertiesService.getDocumentProperties();
  694.   var accessToken = documentProperties.getProperty('SQUARE_ACCESS_TOKEN');
  695.  
  696.   if (!accessToken) {
  697.     // Prompt the user to set the API key
  698.     displayAlert('Access token is missing. Please use the "Set API Key" option in the "Square API" menu to provide your access token.');
  699.     throw new Error('Access token is required to proceed. Please set it using the "Set API Key" menu option.');
  700.   }
  701.  
  702.   // Ensure options.headers exists
  703.   if (!options.headers) {
  704.     options.headers = {};
  705.   }
  706.  
  707.   // Ensure the Authorization header has the correct token
  708.   options.headers["Authorization"] = "Bearer " + accessToken;
  709.  
  710.   var response = UrlFetchApp.fetch(url, options);
  711.   var statusCode = response.getResponseCode();
  712.  
  713.   if (statusCode === 401) {
  714.     // Unauthorized, token may be invalid or expired
  715.     // Send email notification instead of prompting
  716.     var emailAddress = documentProperties.getProperty('NOTIFICATION_EMAIL');
  717.  
  718.     if (emailAddress) {
  719.       MailApp.sendEmail({
  720.         to: emailAddress,
  721.         subject: "Square Data Refresh Failed - Invalid Access Token",
  722.         body: "The access token used for the Square API is invalid or expired. Please update it using the 'Set API Key' option in the 'Square API' menu."
  723.       });
  724.     } else {
  725.       Logger.log('Notification email address is not set. Please use "Set Email Address" in the "Square API" menu.');
  726.     }
  727.     throw new Error('Access token is invalid or expired.');
  728.   } else if (statusCode >= 200 && statusCode < 300) {
  729.     // Success
  730.     return response;
  731.   } else {
  732.     // Other errors
  733.     Logger.log('API request failed with status code ' + statusCode + ': ' + response.getContentText());
  734.     throw new Error('API request failed with status code ' + statusCode);
  735.   }
  736. }
  737.  
  738. // Helper function to display alerts only when UI is available
  739. function displayAlert(message) {
  740.   try {
  741.     SpreadsheetApp.getUi().alert(message);
  742.   } catch (e) {
  743.     // UI not available (e.g., running via trigger), so we skip showing the alert
  744.     Logger.log("Alert: " + message);
  745.   }
  746. }
  747.  
Advertisement
Add Comment
Please, Sign In to add comment