Inbound shipment API script

Devs Needed & Available
1

Hi Team

I have the following script which works, but it’s give back limited data. I would like to see all the Inbound shipments what we have where the Quantity Ordered is NOT the same as Quantity Received (so basically Inbound).

If I expand the script then it fails or I reach my credit limit.

May I ask someones help how I can get all the “Open” Purchase orders out from the system?

Thanks!
Adam

my script:

function fetchPOsWithPendingItems() {
const token = getAuthToken();

const query = query { purchase_orders { data(last: 10) { edges { node { po_number created_at line_items { edges { node { sku quantity quantity_received } } } } } } } } ;

const response = UrlFetchApp.fetch(GRAPHQL_ENDPOINT, {
method: ‘post’,
contentType: ‘application/json’,
headers: {
‘Authorization’: 'Bearer ’ + token,
‘auth0-forwarded-for’: ‘127.0.0.1’,
},
payload: JSON.stringify({ query: query }),
});

const json = JSON.parse(response.getContentText());

if (!json.data || !json.data.purchase_orders || !json.data.purchase_orders.data) {
throw new Error(‘API returned no purchase orders data.’);
}

const purchaseOrders = json.data.purchase_orders.data.edges;

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘SHIPHERO_OPEN_POs’);
if (!sheet) throw new Error(‘Sheet “SHIPHERO_OPEN_POs” not found.’);

sheet.clear();
sheet.appendRow([‘PO Number’, ‘Created At’, ‘SKU’, ‘Quantity Ordered’, ‘Quantity Received’]);

purchaseOrders.forEach(po => {
const poNode = po.node;
const poNumber = poNode.po_number;
const createdAt = poNode.created_at;

poNode.line_items.edges.forEach(item => {
  const itemNode = item.node;
  const sku = itemNode.sku || 'N/A';
  const quantityOrdered = itemNode.quantity || 0;
  const quantityReceived = itemNode.quantity_received || 0;

  if (quantityOrdered !== quantityReceived) {
    sheet.appendRow([poNumber, createdAt, sku, quantityOrdered, quantityReceived]);
  }
});

});
}

2

Hi Adam,

To accomplish this you will need to use pagination (documentation here). Typically when consumed in javascript as above you would use a while loop to pass the end cursor back into the call to get the next page while the hasNextPage value is equal to True. To prevent the script from halting due to credit usage I recommend building in a wait time to each loop equal to the throttling_cost/30 in seconds.

Hope this helps!

Thanks

3

It does, thanks you so much! Now the challange I face is that I could get all the data I need and I added a status column, so I can see the status for each PO. If I change a status in the ShipHero website/portal and I run the script after, then the status is still the previous (old) status. I have waited 8 hours run again and still the same. Do you know whow I can fetch the most up to date data?

Thank you!

My current script:

function fetchPOsWithAllStatuses() {
const token = getAuthToken();
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘SHIPHERO_OPEN_POs’);
if (!sheet) throw new Error(‘Sheet “SHIPHERO_OPEN_POs” not found.’);

sheet.clear();
sheet.appendRow([‘PO Number’, ‘Created At’, ‘Status’, ‘SKU’, ‘Quantity Ordered’, ‘Quantity Received’]);

let hasNextPage = true;
let afterCursor = null;

while (hasNextPage) {
const query = query { purchase_orders { data(first: 10${afterCursor ?, after: “${afterCursor}”: ''}) { edges { node { po_number created_at line_items { edges { node { sku quantity quantity_received } } } } } pageInfo { endCursor hasNextPage } } } } ;

const response = UrlFetchApp.fetch(GRAPHQL_ENDPOINT, {
  method: 'post',
  contentType: 'application/json',
  headers: {
    'Authorization': 'Bearer ' + token,
    'auth0-forwarded-for': '127.0.0.1',
  },
  payload: JSON.stringify({ query: query }),
});

const json = JSON.parse(response.getContentText());

if (
  !json.data ||
  !json.data.purchase_orders ||
  !json.data.purchase_orders.data ||
  !json.data.purchase_orders.data.edges
) {
  throw new Error('API returned no purchase orders data.');
}

const purchaseOrders = json.data.purchase_orders.data.edges;
hasNextPage = json.data.purchase_orders.data.pageInfo.hasNextPage;
afterCursor = json.data.purchase_orders.data.pageInfo.endCursor;

purchaseOrders.forEach(po => {
  const poNode = po.node;
  const poNumber = poNode.po_number;
  const createdAt = poNode.created_at;

  let status = 'Unknown';
  let hasPendingItems = false;
  let isCanceled = true;

  poNode.line_items.edges.forEach(item => {
    const itemNode = item.node;
    const sku = itemNode.sku || 'N/A';
    const quantityOrdered = itemNode.quantity || 0;
    const quantityReceived = itemNode.quantity_received || 0;

    // Determine status based on quantities
    if (quantityOrdered > 0) {
      isCanceled = false;
    }
    if (quantityReceived < quantityOrdered) {
      hasPendingItems = true;
      sheet.appendRow([poNumber, createdAt, 'Pending', sku, quantityOrdered, quantityReceived]);
    } else {
      sheet.appendRow([poNumber, createdAt, 'Complete', sku, quantityOrdered, quantityReceived]);
    }
  });

  // Finalize status
  if (isCanceled) {
    status = 'Canceled';
    sheet.appendRow([poNumber, createdAt, status, '', '', '']);
  } else if (!hasPendingItems) {
    status = 'Complete';
  } else {
    status = 'Pending';
  }
});

}
}

4

This sounds like a potential bug that would be best addressed in a support ticket here. When doing so assign the category “Public API” and please include the request and response body, example PO Numbers, and if possible request IDs related to the issue.

Thanks

5

Got it, thank you!
Well actually I found a workaround, if I delete the line item from the PO then it goes “really” cancelled so in my script it shows cancelled. If I just change to cancelled and keep the line item, it’s still pending.

Thanks anyways!