IMPORTJSONAPI

IMPORTJSONAPI copied to clipboard

I have a json order file. Some orders have single items but the potential exists for an order to have more than one item. Please see the attached example. What I would like to do is to import to Google Sheets and have a new row for each order/item combination. I am fine with the order information repeating on each line.
json_orders.pdf

Something like this should do the trick:

=IMPORTJSONAPI(<URL>, "$..*[?(@property === 'ProductsId')]^", "OrdersId, ProductsId, ProductsTotal")

Thank You! I just gave it a try and I am getting an error. Error is as follows:
ERROR: Unexpected token < in JSON at position 0

Please disregard, I had a syntax issue on my end. Thanks so much for the help!

@qeet I am now trying to bring some other data elements that exist at the order level. Everything seems to work fine except for the orders that have more than one item on them. For example, I am trying to bring in the Order Creation Date and display it on each line. I am doing so like so and as mentioned it works for those orders that have one item. .^^OrderCreatedDate

Is there a different syntax I should be using that would work for both ? Any help you can provide is appreciated.

There is currently not a syntax that will work for both. The only way I can think of to do this is to have two columns that handles each case: e.g

.^^OrderCreatedDate, .^^^OrderCreatedDate

Then one of the cells will be blank while the other will have a date. Now in your spreadsheet create another column which uses the IF(ISBLANK(firstcol), secondcol, firstcol) to combine them.