How to extract information from JSON and make calculations ?

Hello,

I’m trying to extract some info from a JSON result that comes into a variable and make a calculation.

My goal is to calculate how many sales I make per day, so far this is my code :

set {year:yr, month:mn, day:dy} to (current date)

set theYear to yr
set theMonth to do shell script "date +'%m'"
set theDay to dy
set TheFirstDay to dy - 1

set a to "https://editedurl.tld/admin/orders.json?status=any&created_at_min="
set b to "T12:00:00-00:00&created_at_max="
set c to "T11:59:59-00:00&fields=id,total_discounts,refunds,total_price,subtotal_price,total_tax,total_shipping_price_set,created_at,total_line_items_price,total_discounts&limit=250"
set myUrl to a & theYear & "-" & theMonth & "-0" & TheFirstDay & b & theYear & "-" & theMonth & "-0" & theDay & c

tell application "JSON Helper"
	set mySales to (fetch JSON from myUrl)
end tell

mySales is coming out like this :

What I want is to calculate how many sales and for how much I have made, for example :

50 Sales 1200 EUR

For that I need to count the items in my JSON and addition them then calculate all the sales.

My ultimate goal is to show this on my macbook touch bar.

Thanks.

Hi. Welcome to MacScripter.

It’s not clear which figures you want summed for the sales total, but the following should get you started. It sums the total_price values and assumes they’re all in euros:

-- Sample result from JSON Helper.
set mySales to {orders:{{total_line_items_price:"29.90", |id|:1.128527102016E+12, total_discounts:"0.00", created_at:"2019-06-06T06:47:57+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.128461041728E+12, total_discounts:"0.00", created_at:"2019-06-06T06:15:22+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"59.80", |id|:1.128348090432E+12, total_discounts:"0.00", created_at:"2019-06-06T05:27:35+07:00", refunds:{}, total_price:"59.80", subtotal_price:"59.80", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.128266104896E+12, total_discounts:"0.00", created_at:"2019-06-06T04:53:18+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.128199979072E+12, total_discounts:"0.00", created_at:"2019-06-06T04:32:48+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.128126480448E+12, total_discounts:"0.00", created_at:"2019-06-06T04:14:00+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.128063664192E+12, total_discounts:"0.00", created_at:"2019-06-06T03:58:52+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.128059469888E+12, total_discounts:"0.00", created_at:"2019-06-06T03:57:54+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"59.80", |id|:1.1280327312E+12, total_discounts:"0.00", created_at:"2019-06-06T03:51:58+07:00", refunds:{}, total_price:"59.80", subtotal_price:"59.80", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.127699447872E+12, total_discounts:"0.00", created_at:"2019-06-06T01:49:29+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.12757184928E+12, total_discounts:"0.00", created_at:"2019-06-06T01:01:25+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.127494877248E+12, total_discounts:"0.00", created_at:"2019-06-06T00:34:08+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.127480852544E+12, total_discounts:"0.00", created_at:"2019-06-06T00:28:59+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.127430586432E+12, total_discounts:"0.00", created_at:"2019-06-06T00:10:18+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.12733690272E+12, total_discounts:"0.00", created_at:"2019-06-05T23:36:02+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"59.80", |id|:1.127247151168E+12, total_discounts:"0.00", created_at:"2019-06-05T23:03:25+07:00", refunds:{}, total_price:"59.80", subtotal_price:"59.80", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.12721238432E+12, total_discounts:"0.00", created_at:"2019-06-05T22:50:29+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"79.70", |id|:1.127208747072E+12, total_discounts:"0.00", created_at:"2019-06-05T22:49:08+07:00", refunds:{}, total_price:"79.70", subtotal_price:"79.70", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.127202783296E+12, total_discounts:"0.00", created_at:"2019-06-05T22:47:03+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.127173914688E+12, total_discounts:"0.00", created_at:"2019-06-05T22:36:00+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.127137738816E+12, total_discounts:"0.00", created_at:"2019-06-05T22:22:31+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.127091601472E+12, total_discounts:"0.00", created_at:"2019-06-05T22:04:03+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.1270537872E+12, total_discounts:"0.00", created_at:"2019-06-05T21:50:08+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.127008534592E+12, total_discounts:"0.00", created_at:"2019-06-05T21:32:07+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.126935953472E+12, total_discounts:"0.00", created_at:"2019-06-05T21:04:08+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.126869925952E+12, total_discounts:"0.00", created_at:"2019-06-05T20:38:23+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}, {total_line_items_price:"29.90", |id|:1.126839844928E+12, total_discounts:"0.00", created_at:"2019-06-05T20:25:23+07:00", refunds:{}, total_price:"29.90", subtotal_price:"29.90", total_shipping_price_set:{shop_money:{amount:"0.00", currency_code:"EUR"}, presentment_money:{amount:"0.00", currency_code:"EUR"}}, total_tax:"0.00"}}}

set allOrders to mySales's orders
-- Count the orders.
set numberOfSales to (count allOrders)
-- Sum the total_price values, assuming the currency to be euros in all cases.
set totalPrice to 0
repeat with thisOrder in allOrders
	set totalPrice to totalPrice + (thisOrder's total_price)
end repeat

-- Convert the price total to text, padding (if necessary) to two decimal places. The total must be at least 1 euro!
set myDecimalPoint to character 2 of (1 / 2 as text)
tell (totalPrice * 100 as integer as text) to set totalPrice to text 1 thru -3 & myDecimalPoint & text -2 thru -1

return (numberOfSales as text) & " Sales " & totalPrice & " EUR"

Woaw works perfectly ! Thank you !

Hello Nigel

I’m puzzled.
When I run the original script supposed to fetch the datas I get this log:

tell current application
	current date given «class Krtn»:{year:"yr", month:"mn", day:"dy"}
		--> date "jeudi 6 juin 2019 à  11:35:07"
	do shell script "date +'%m'"
		--> "06"
end tell
tell application "JSON Helper"
	fetch JSON from "https://editedurl.tld/admin/orders.json?status=any&created_at_min=2019-06-05T12:00:00-00:00&created_at_max=2019-06-06T11:59:59-00:00&fields=id,total_discounts,refunds,total_price,subtotal_price,total_tax,total_shipping_price_set,created_at,total_line_items_price,total_discounts&limit=250"
		--> ""
end tell
Résultat :
""

When I run your script I get :

Résultat :
error “Impossible de convertir "29.90" en type number.” number -1700 from “29.90” to number

It seems that something is wrong with the way the script treats the decimal separator.

To get your script to work I must edit the active part as:


set allOrders to mySales's orders
-- Count the orders.
set numberOfSales to (count allOrders)
-- Sum the total_price values, assuming the currency to be euros in all cases.
set totalPrice to 0
set myDecimalPoint to character 2 of (1 / 2 as text)
repeat with thisOrder in allOrders
	set aPrice to my remplace(thisOrder's total_price, ".", myDecimalPoint) as number
	set totalPrice to totalPrice + aPrice
end repeat

-- Convert the price total to text, padding (if necessary) to two decimal places. The total must be at least 1 euro!
tell (totalPrice * 100 as integer as text) to set totalPrice to text 1 thru -3 & myDecimalPoint & text -2 thru -1

return (numberOfSales as text) & " Sales " & totalPrice & " EUR"


#=====
(*
replaces every occurences of d1 by d2 in the text t
*)
on remplace(t, d1, d2)
	local oTIDs, l
	set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d1}
	set l to text items of t
	set AppleScript's text item delimiters to d2
	set t to l as text
	set AppleScript's text item delimiters to oTIDs
	return t
end remplace

#=====

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) jeudi 6 juin 2019 11:45:39

Hi Yvan.

Thanks. I wondered about that. At first I didn’t notice that the values were text! By the time I did, Johan had reported that the script was working for him, so I didn’t pursue the matter any further. I’ll look at it again later on, but I think your solution’s probably as good a fix as there’s likely to be.

The blank result from Johan’s script may be because he altered the URL in order not to reveal it here. Or he may be in a part of the world where it was still yesterday when you tested the script.

Thanks Nigel

I was wondering if Jason Helper return a set of datas using the local decimal separator in which case changing the delimiter on the fly wasn’t useful.

It’s to learn that that I tried to run the original script.

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) jeudi 6 juin 2019 15:44:44

You could always try avoiding it. Assuming the URL worked and that the user’s currency was also EUR, it could be done something like:

use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use scripting additions

set {year:yr, month:mn, day:dy} to (current date)

set theYear to yr
set theMonth to do shell script "date +'%m'"
set theDay to dy
set TheFirstDay to dy - 1

set a to "https://editedurl.tld/admin/orders.json?status=any&created_at_min="
set b to "T12:00:00-00:00&created_at_max="
set c to "T11:59:59-00:00&fields=id,total_discounts,refunds,total_price,subtotal_price,total_tax,total_shipping_price_set,created_at,total_line_items_price,total_discounts&limit=250"
set myUrl to a & theYear & "-" & theMonth & "-0" & TheFirstDay & b & theYear & "-" & theMonth & "-0" & theDay & c

set theURL to current application's NSURL's URLWithString:myUrl
set theData to current application's NSData's dataWithContentsOfURL:theURL
set mySales to (current application's NSJSONSerialization's JSONObjectWithData:theData options:0 |error|:(missing value))
set theOrders to mySales's objectForKey:"orders"
set numberOfSales to theOrders's |count|()
set totalPrice to theOrders's valueForKeyPath:"@sum.total_price"
set totalPrice to current application's NSNumberFormatter's localizedStringFromNumber:totalPrice numberStyle:(current application's NSNumberFormatterCurrencyISOCodeStyle)
return (numberOfSales as text) & " Sales " & totalPrice

Hi Shane.

Are you saying that the total_price values wouldn’t be strings if obtained with the NSJSONSerialization method? Or that they wouldn’t have commas as decimal separators? Or that valueForKeyPath:“@sum.total_price is locale-sensitive? If I create an NSDictionary directly from the record used above, your summing code works on my British English machine. But if I change all the decimal separators to commas, only the whole-number parts of the values are summed.

No, I’m assuming they’d be the same.

I’m assuming they would still have . as separators. But as the URL is not a real one, we only have the record posted to go on.

No, I’m assuming it’s definitely not. So while this fails for Yvan:

"22.90" as number

it’s fine as far as @sum is concerned.

My message was misleading, though, in that its suggested Jason Helper was the cause of Yvan’s problem, when it’s really an AppleScript/localization issue.

Ah. I see. Thanks, Shane. So if Yvan somehow gets the example record as an NSDictionary and then uses your code from set theOrders… down, he’ll be able to get the required results on his machine without the need for all the individual text manipulations of the vanilla method.

Right. In the absence of a real link, that’s how I tested it.