Struggling with importJSON function to fetch inventory list from cloud database into Google Sheets

[JackWolf69]

I’m trying to use the importJSON script to get product information from my online inventory management system into a Google Sheet. When I pull data for just one product, everything works fine. But when I try to get a complete list of all products, I get back an array with objects that I can’t figure out how to display properly.

Working formula for single item:

=ImportJSONBasicAuth("https://api.inventorymanager.com/v2/products.json?search=prod-2045","myApiKey","myPassword")

Problem formula for multiple items:

=ImportJSONBasicAuth("https://api.inventorymanager.com/v2/products.json?limit=50&page=1","myApiKey","myPassword","/products")

The second formula gives me this result:

Total	CurrentPage	TotalPages	Products	IsCached
2456	1	25	[object Object],[object Object],[object Object]	FALSE

I can see the data I need is inside those objects but I don’t know how to extract it. Here’s what the JSON structure looks like:

{
  "total": "2456",
  "currentPage": "1", 
  "totalPages": "25",
  "products": [
    {
      "title": "Battery Pack - TITAN - Golf Cart Model - Lithium Ion",
      "stock": 5,
      "reserved": 1,
      "incoming": 0,
      "minLevel": 3,
      "costPrice": 285,
      "sellPrice": 650,
      "details": "Battery Pack - TITAN - Golf Cart Model - Lithium Ion",
      "partNumber": "TIT-405070030-LITHIUM",
      "serialNum": "",
      "productId": 3247,
      "upc": "TIT-405070030-LITHIUM",
      "isActive": 1,
      "uuid": "2a5c7439-45b0-3d2c-bc18-2ef4281534e8",
      "categoryUuid": "51032310-05a7-3284-8670-37840b7e0bcd",
      "warehouseUuid": "4e602afc-cb63-3e4b-a7c5-260c1b7e100a",
      "lastUpdated": "2025-05-15T02:25:18+00:00",
      "dateAdded": "2025-05-15T02:25:08+00:00"
    }
  ]
}

How can I modify my formula or the script to properly display the individual product data instead of getting those object references?

[scarlettturner]

ImportJSON treats the products array as one column instead of expanding each product into its own row. I’ve hit this same issue before.

You could try /products/0/title,/products/1/title,/products/2/title to manually grab each array index, but that gets messy fast.

I found a better fix - modify the ImportJSON script to add an allItems parameter that forces it to loop through array elements. But honestly, the easiest solution is just using Google Apps Script with a custom function. Use UrlFetchApp.fetch() to grab the data, JSON.parse() to handle the JSON, then loop through the products array and write each object’s properties to specific cells. You get full control over how the nested data flattens into your sheet.

[MarkSeeker91]

Yeah, I’ve hit this with ImportJSON before - it chokes on nested arrays. Try tacking on noInherit and noTruncate at the end: =ImportJSONBasicAuth("url","key","pass","/products","noInherit,noTruncate"). Also might need to tweak the query path - test /products/0 first to see if it grabs the first product object correctly.

[davidw]

Your importJSON function isn’t going deep enough into the nested array. Your products sit inside a “products” array in the main response, so you need to fix the query path. Try this:

=ImportJSONBasicAuth("https://api.inventorymanager.com/v2/products.json?limit=50&page=1","myApiKey","myPassword","/products/title,/products/stock,/products/sellPrice,/products/partNumber")

This grabs the specific product fields instead of returning whole object references. I’ve hit this same problem with nested API responses in Sheets - you have to be super specific about which nested properties you want.

If that doesn’t work, you’ll probably need to modify the importJSON script to handle arrays of objects better, or just use Google Apps Script to parse the JSON manually and populate the sheet with a custom function.