Hey guys,
I am starting this topic as a normal basic ecommerce platform that most of us are from. In this chain, can we please pose the queries that have been successfully answered? For example, below is an easy code to get instant results for the returns that were processed in given time intervals:
PLEASE REPLY TO THIS CHAIN TO MAKE LIVES EASIER
RETURNS QUERY:
// =======================================================
// PART 1: THE FOUNDATION (NEVER DELETE THIS)
// =======================================================
// ** ACTION REQUIRED **
// Your access token goes here. This is the only line you should
// need to update in the future (when you get a new token).
const ACCESS_TOKEN = ‘ACCESSCODE_HERE’;
/**
* This is your reusable “engine”. It sends any query to ShipHero.
* All other functions will use this.
* @param {string} graphQLQuery The GraphQL query string to send.
* @return {object} The JSON response from the server.
*/
function sendShipHeroRequest(graphQLQuery) {
const API_URL = ‘https://public-api.shiphero.com/graphql’;
const options = {
method: ‘post’,
contentType: ‘application/json’,
headers: { “Authorization”: "Bearer " + ACCESS_TOKEN },
payload: JSON.stringify({ query: graphQLQuery }),
muteHttpExceptions: true
};
const response = UrlFetchApp.fetch(API_URL, options);
Logger.log("Raw Response from ShipHero: " + response.getContentText());
return JSON.parse(response.getContentText());
}
// =======================================================
// PART 2: THE FINAL, INTERACTIVE DAILY REPORT
// =======================================================
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('ShipHero Reports')
.addItem('Run "Inventory Added" Report', 'getCleanInventoryAddedReport_Final')
.addToUi();
}
/**
* Gets a clean report of inventory changes, automatically handling rate limits.
*/
function getCleanInventoryAddedReport_Final() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheetName = “Inventory Added Report”;
let sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
sheet = spreadsheet.insertSheet(sheetName);
sheet.getRange(“A1:B2”).setValues([[“Start Date:”, new Date()], [“End Date:”, new Date()]]);
sheet.getRange(“A1:A2”).setFontWeight(“bold”); sheet.getRange(“B1:B2”).setNumberFormat(“m/d/yyyy”);
}
const startDate = sheet.getRange(“B1”).getValue();
const endDate = sheet.getRange(“B2”).getValue();
if (!startDate || !endDate) { SpreadsheetApp.getUi().alert(“Please fill in B1 and B2.”); return; }
sheet.getRange(“A4”).setValue(“Status: Building warehouse lookup table…”);
SpreadsheetApp.flush();
let warehouseLookup = {};
const warehouseQuery = `query { account { data { warehouses { id profile } } } }`;
const warehouseResponse = sendShipHeroRequest(warehouseQuery);
if (warehouseResponse.data) {
warehouseResponse.data.account.data.warehouses.forEach(wh => { warehouseLookup[wh.id] = wh.profile; });
}
const tz = spreadsheet.getSpreadsheetTimeZone();
const dateFrom = Utilities.formatDate(new Date(startDate), tz, “yyyy-MM-dd”);
const dateTo = Utilities.formatDate(new Date(endDate), tz, “yyyy-MM-dd”);
sheet.getRange(“A4”).setValue(“Status: Fetching log data…”);
let myQuery = `query { inventory_changes(date_from: “${dateFrom}”, date_to: “${dateTo}”) { data(first: 250) { pageInfo { hasNextPage endCursor } edges { node { created_at reason warehouse_id product { sku name } location { name } } } } } }`;
let allChanges = ;
let hasNextPage = true;
let endCursor = null;
while(hasNextPage) {
const jsonResponse = sendShipHeroRequest(myQuery);
// — THIS IS THE NEW “SMART” ERROR HANDLING —
if (jsonResponse.errors) {
let errorMessage = jsonResponse.errors[0].message;
if (errorMessage.includes(“enough credits”)) {
let timeToWait = parseInt(errorMessage.match(/(\d+)\sseconds/)[1]) + 1; // Add 1 second for safety.
sheet.getRange(“A4”).setValue(`Status: Rate limit hit. Pausing for ${timeToWait} seconds…`);
SpreadsheetApp.flush();
Utilities.sleep(timeToWait * 1000); // Wait for the required time.
continue; // Go back to the start of the loop and try the SAME request again.
} else {
sheet.getRange(“A4”).setValue("API Error: " + errorMessage);
return;
}
}
// — END OF SMART ERROR HANDLING —
if (!jsonResponse.data.inventory_changes) { break; }
const changesData = jsonResponse.data.inventory_changes.data;
allChanges = allChanges.concat(changesData.edges);
hasNextPage = changesData.pageInfo.hasNextPage;
endCursor = changesData.pageInfo.endCursor;
if (!hasNextPage) { break; }
myQuery = `query { inventory_changes(date_from: “${dateFrom}”, date_to: “${dateTo}”) { data(first: 250, after: “${endCursor}”) { pageInfo { hasNextPage endCursor } edges { node { created_at reason warehouse_id product { sku name } location { name } } } } } }`;
}
// (The rest of the script for filtering and writing the report is the same)
let outputData = ;
allChanges.forEach(changeEdge => {
const c = changeEdge.node;
const reason = c.reason || “”;
if (reason.toLowerCase().startsWith(“added from”)) {
const cleanReason = reason.replace(/<[^>]+>/g, ‘’);
const warehouseName = warehouseLookup[c.warehouse_id] || c.warehouse_id;
outputData.push([ new Date(c.created_at).toLocaleString(), warehouseName, c.product ? c.product.sku : “N/A”, c.product ? c.product.name : “N/A”, c.location ? c.location.name : “N/A”, cleanReason ]);
}
});
sheet.getRange(“A5:F” + sheet.getMaxRows()).clearContent();
const headers = [“Date”, “Warehouse Name”, “SKU”, “Product Name”, “Location Name”, “Clean Reason”];
sheet.getRange(5, 1, 1, headers.length).setValues([headers]);
if (outputData.length > 0) {
sheet.getRange(6, 1, outputData.length, headers.length).setValues(outputData);
}
sheet.getRange(“A4”).setValue(`Report Complete. Found ${outputData.length} ‘added from’ records.`);
}