Script for On hand Inventory with LOT + Exp. Date

Devs Needed & Available
1

Hi Team,

I would like to get a script for exporting out inventory lot + exp date what I have in the system.
May I ask someone help how I can do with app script?

I have this script which works for the inventory, but can’t scrape lot + exp. date with it.

Thank you!

code:

// Function to fetch the inventory report
function fetchInventoryReport() {
const token = getAuthToken(); // Get the token
const query = query { products { data(first: 1000) { edges { node { id sku name warehouse_products { warehouse { identifier } on_hand allocated value } } } } } } ;

// Send the GraphQL query
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());
const products = json.data.products.data.edges;

// Load the target sheet
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘SHIPHERO_LIVE’);
if (!sheet) {
throw new Error(‘Sheet “SHIPHERO_LIVE” not found.’);
}

// Clear the sheet and add headers
sheet.clear();
sheet.appendRow([‘Warehouse’, ‘Name’, ‘SKU’, ‘On Hand’, ‘Allocated’, ‘Available’, ‘Value’]);

// Populate the sheet with data
products.forEach(product => {
product.node.warehouse_products.forEach(warehouse_product => {
sheet.appendRow([
warehouse_product.warehouse.identifier,
product.node.name,
product.node.sku,
warehouse_product.on_hand,
warehouse_product.allocated,
warehouse_product.on_hand - warehouse_product.allocated, // Calculate available
warehouse_product.value,
]);
});
});
}

// Function to retrieve the authentication token
function getAuthToken() {
if (!accessToken) {
// Authenticate with username and password if no token is available
const authResponse = UrlFetchApp.fetch(AUTH_ENDPOINT, {
method: ‘post’,
contentType: ‘application/json’,
headers: {
‘auth0-forwarded-for’: ‘127.0.0.1’,
},
payload: JSON.stringify({
username: SHIPHERO_USERNAME,
password: SHIPHERO_PASSWORD,
}),
});

const authJson = JSON.parse(authResponse.getContentText());
accessToken = authJson.access_token;
refreshToken = authJson.refresh_token;

} else {
// Check if the token has expired and refresh if necessary
try {
UrlFetchApp.fetch(GRAPHQL_ENDPOINT, {
method: ‘post’,
contentType: ‘application/json’,
headers: {
‘Authorization’: 'Bearer ’ + accessToken,
‘auth0-forwarded-for’: ‘127.0.0.1’,
},
payload: JSON.stringify({ query: ‘{ warehouses { id } }’ }),
});
} catch (error) {
// Refresh the token if the request fails
const refreshResponse = UrlFetchApp.fetch(REFRESH_ENDPOINT, {
method: ‘post’,
contentType: ‘application/json’,
headers: {
‘auth0-forwarded-for’: ‘127.0.0.1’,
},
payload: JSON.stringify({
refresh_token: refreshToken,
}),
});

  const refreshJson = JSON.parse(refreshResponse.getContentText());
  accessToken = refreshJson.access_token;
}

}

return accessToken;
}