// =======================================================
// 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.`);
}