JavaScript for operations on arrays not working in Glide

Ask for Help
1

TL;DR: I used ChatGPT to generate some simple code that is supposed to help me run a calculation on a couple of arrays (to obtain total daily sales), but it’s not working. Any ideas as to why?

Some context: Using an API call, I’m retrieving sales data from a Shopify store and I want to calculate the daily total sales. I have two variables as a result: prices and quantities. Each of these is an array of the prices and quantities for each line item in each order for the day. (Each order’s data is a row).

Since we can’t perform calculations on arrays, I thought the way to go was to use code. I cheated and had ChatGPT do it for me. I have a basic understanding of what this code is doing, and as far as I (with my limited knowledge) am concerned, and most importantly, as far as ChatGPT (with its infinite knowledge) is concerned, the code should work.

The screenshots show a couple of versions of this code (they both should be doing the same, with different methods) in template columns to replace the variables, as well as an example of what the data looks like. Then there is a JavaScript column that’s supposed to execute the code, where I’m using the template.

However, for some reason I can’t yet understand, it’s returning nothing. I got that error message but I can’t find the syntax error. Any help would be greatly appreciated. If more context/screenshots are needed to get a better idea of how this is set up, I’ll share them.

CleanShot Nov 12
CleanShot Nov 12868×1376 109 KB

CleanShot Nov 12 (1).
CleanShot Nov 12 (1).890×1326 121 KB

CleanShot Nov 12 (2).
CleanShot Nov 12 (2).904×744 70.4 KB

CleanShot Nov 12 (3).
CleanShot Nov 12 (3).880×942 54.5 KB

2

Rather than posting screen shots of your code, can you please copy/paste the actual code (use triple-backticks for code blocks). Then others can help you debug your code.

Anyway, two things stick out at me from a cursory glance.

  • you are trying to use a joined list as an array. That won’t work.
  • your code doesn’t actually return anything.
3

You can do this directly with the Query JSON column using JSONata syntax.
If you can share a sample of your JSON, I’ll show you how.

4

Thanks for your response, @Darren_Murphy. Here’s the actual code, at least one of the variations of it:

const pa = p1;
const q2 = p2;

function calculateDailySales(pa, q2) {
  if (pa.length !== q2.length) {
    throw new Error("Arrays must be of the same length.");
  }

  let dailyTotal = pa.reduce((total, price, index) => {
    let numericPrice = parseFloat(price);
    return total + (numericPrice * q2[index]);
  }, 0);

  return dailyTotal;
}

const dailySalesTotal = calculateDailySales(pa, q2);```
5

The columns I am trying to use as arrays are Query JSON columns. Are these considered joined lists? Because I made arrays out of them as well with a Make Array column and tried to use that instead but it doesn’t work either.

CleanShot 2023-11-13 at 07.48.32@2x
CleanShot 2023-11-13 at 07.48.32@2x724×604 26.1 KB

CleanShot 2023-11-13 at 07.48.55@2x

6

You should be able to get the sum directly with the Query JSON column. Try the following:

$sum(line_items.price)
1 Like
7

Here’s a sample of the JSON:

{
  "orders": [
    {
      "id": 5618443485462,
      "admin_graphql_api_id": "gid://shopify/Order/56184dam485462",
      "app_id": 580111,
      "browser_ip": "172.249.170.125",
      "buyer_accepts_marketing": false,
      "cancel_reason": null,
      "cancelled_at": null,
      "cart_token": "c1-e90a43944deeaejfk926559bbf3b901f807",
      "checkout_id": 37118539563286,
      "checkout_token": "b8034f491dece50b15cef148f632f940",
      "client_details": {
        "accept_language": "en-US",
        "browser_height": null,
        "browser_ip": "324",
        "browser_width": null,
        "session_hash": null,
        "user_agent": "Mozilla/5.0 (iPhone; CPU iPhone OS 17_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148 Phantom/ios/23.16.1.11008"
      },
      "closed_at": null,
      "confirmation_number": "PX5JXRZZT",
      "confirmed": true,
      "contact_email": "aaron.yeah@gmail.com",
      "created_at": "2023-11-11T14:16:19-05:00",
      "currency": "USD",
      "current_subtotal_price": "25.00",
      "current_subtotal_price_set": {
        "shop_money": {
          "amount": "25.00",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "25.00",
          "currency_code": "USD"
        }
      },
      "current_total_additional_fees_set": null,
      "current_total_discounts": "0.00",
      "current_total_discounts_set": {
        "shop_money": {
          "amount": "0.00",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "0.00",
          "currency_code": "USD"
        }
      },
      "current_total_duties_set": null,
      "current_total_price": "32.50",
      "current_total_price_set": {
        "shop_money": {
          "amount": "32.50",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "32.50",
          "currency_code": "USD"
        }
      },
      "current_total_tax": "0.00",
      "current_total_tax_set": {
        "shop_money": {
          "amount": "0.00",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "0.00",
          "currency_code": "USD"
        }
      },
      "customer_locale": "en-US",
      "device_id": null,
      "discount_codes": [],
      "email": "aaron.yeah@gmail.com",
      "estimated_taxes": false,
      "financial_status": "paid",
      "fulfillment_status": null,
      "landing_site": "/products/short-sleeve-t-shirt",
      "landing_site_ref": null,
      "location_id": null,
      "merchant_of_record_app_id": null,
      "name": "#1535",
      "note": null,
      "note_attributes": [],
      "number": 535,
      "order_number": 1535,
      "order_status_url": "https://store.solana.com/77289390358/orders/05ca34846fb735ab6d4b2e0009b44fc1/authenticate?key=4a128b6aa5f121f7ef943ec881c0f30b",
      "original_total_additional_fees_set": null,
      "original_total_duties_set": null,
      "payment_gateway_names": [
        "Pay"
      ],
      "phone": null,
      "po_number": null,
      "presentment_currency": "USD",
      "processed_at": "2023-11-11T14:16:17-05:00",
      "reference": "dfc6252f18camdki7073f20af47f87fe5",
      "referring_site": "",
      "source_identifier": "dfc6252f18cejjkqi7073f20af47f87fe5",
      "source_name": "web",
      "source_url": null,
      "subtotal_price": "25.00",
      "subtotal_price_set": {
        "shop_money": {
          "amount": "25.00",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "25.00",
          "currency_code": "USD"
        }
      },
      "tags": "Infoplus-487288.000",
      "tax_exempt": false,
      "tax_lines": [],
      "taxes_included": true,
      "test": false,
      "token": "05ca34846fb735qqejnj834b2e0009b44fc1",
      "total_discounts": "0.00",
      "total_discounts_set": {
        "shop_money": {
          "amount": "0.00",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "0.00",
          "currency_code": "USD"
        }
      },
      "total_line_items_price": "25.00",
      "total_line_items_price_set": {
        "shop_money": {
          "amount": "25.00",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "25.00",
          "currency_code": "USD"
        }
      },
      "total_outstanding": "0.00",
      "total_price": "32.50",
      "total_price_set": {
        "shop_money": {
          "amount": "32.50",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "32.50",
          "currency_code": "USD"
        }
      },
      "total_shipping_price_set": {
        "shop_money": {
          "amount": "7.50",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "7.50",
          "currency_code": "USD"
        }
      },
      "total_tax": "0.00",
      "total_tax_set": {
        "shop_money": {
          "amount": "0.00",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "0.00",
          "currency_code": "USD"
        }
      },
      "total_tip_received": "0.00",
      "total_weight": 226,
      "updated_at": "2023-11-11T14:16:52-05:00",
      "user_id": null,
      "billing_address": {
        "first_name": "aaron",
        "address1": "464 North Something Yeah Boulevard",
        "phone": "(714) 356-9689",
        "city": "Los Angeles",
        "zip": "90048",
        "province": "California",
        "country": "United States",
        "last_name": "Milligan",
        "address2": null,
        "company": null,
        "latitude": null,
        "longitude": null,
        "name": "aaron Milligan",
        "country_code": "US",
        "province_code": "CA"
      },
      "customer": {
        "id": 7623863107862,
        "email": "aaron.yeah@gmail.com",
        "accepts_marketing": false,
        "created_at": "2023-11-11T14:15:58-05:00",
        "updated_at": "2023-11-11T14:16:20-05:00",
        "first_name": "aaron",
        "last_name": "Milligan",
        "state": "disabled",
        "note": null,
        "verified_email": true,
        "multipass_identifier": null,
        "tax_exempt": false,
        "phone": null,
        "email_marketing_consent": {
          "state": "not_subscribed",
          "opt_in_level": "single_opt_in",
          "consent_updated_at": null
        },
        "sms_marketing_consent": null,
        "tags": "",
        "currency": "USD",
        "accepts_marketing_updated_at": "2023-11-11T14:15:58-05:00",
        "marketing_opt_in_level": null,
        "tax_exemptions": [],
        "admin_graphql_api_id": "gid://shopify/Customer/7623863107862",
        "default_address": {
          "id": 9698964144406,
          "customer_id": 7623863107862,
          "first_name": "aaron",
          "last_name": "Milligan",
          "company": null,
          "address1": "464 North SOMETHING Heights Boulevard",
          "address2": null,
          "city": "Los Angeles",
          "province": "California",
          "country": "United States",
          "zip": "90048",
          "phone": "(714) 356-7483",
          "name": "aaron Milligan",
          "province_code": "CA",
          "country_code": "US",
          "country_name": "United States",
          "default": true
        }
      },
      "discount_applications": [],
      "fulfillments": [],
      "line_items": [
        {
          "id": 14473828696342,
          "admin_graphql_api_id": "gid://shopify/LineItem/14473828696342",
          "attributed_staffs": [],
          "fulfillable_quantity": 1,
          "fulfillment_service": "infoplus-14",
          "fulfillment_status": null,
          "gift_card": false,
          "grams": 227,
          "name": "Solana T-shirt - L",
          "price": "25.00",
          "price_set": {
            "shop_money": {
              "amount": "25.00",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "25.00",
              "currency_code": "USD"
            }
          },
          "product_exists": true,
          "product_id": 8320410910998,
          "properties": [],
          "quantity": 1,
          "requires_shipping": true,
          "sku": "19814-1-2-905-8050",
          "taxable": true,
          "title": "Solana T-shirt",
          "total_discount": "0.00",
          "total_discount_set": {
            "shop_money": {
              "amount": "0.00",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "0.00",
              "currency_code": "USD"
            }
          },
          "variant_id": 44953257017622,
          "variant_inventory_management": "infoplus-14",
          "variant_title": "L",
          "vendor": "Merch Store",
          "tax_lines": [],
          "duties": [],
          "discount_allocations": []
        }
      ],
      "payment_terms": null,
      "refunds": [],
      "shipping_address": {
        "first_name": "aaron",
        "address1": "464 N SOMETHING HEIGHTS BLVD",
        "phone": "(714) 356-9622",
        "city": "LOS ANGELES",
        "zip": "90048",
        "province": "California",
        "country": "United States",
        "last_name": "Milligan",
        "address2": "",
        "company": null,
        "latitude": 34.0800161,
        "longitude": -118.3656287,
        "name": "aaron Milligan",
        "country_code": "US",
        "province_code": "CA"
      },
      "shipping_lines": [
        {
          "id": 4571613856022,
          "carrier_identifier": "650f1a14fa979ec5c74d063e968411d4",
          "code": "Flat Rate Domestic Shipping",
          "discounted_price": "7.50",
          "discounted_price_set": {
            "shop_money": {
              "amount": "7.50",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "7.50",
              "currency_code": "USD"
            }
          },
          "phone": null,
          "price": "7.50",
          "price_set": {
            "shop_money": {
              "amount": "7.50",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "7.50",
              "currency_code": "USD"
            }
          },
          "requested_fulfillment_service_id": null,
          "source": "shopify",
          "title": "Flat Rate Domestic Shipping",
          "tax_lines": [],
          "discount_allocations": []
        }
      ]
    },
    {
      "id": 5618175443222,
      "admin_graphql_api_id": "gid://shopify/Order/5618175443222",
      "app_id": 580111,
      "browser_ip": "172.225.210.204",
      "buyer_accepts_marketing": false,
      "cancel_reason": null,
      "cancelled_at": null,
      "cart_token": "c1-98d5bf80d9f872214af57d2b5960e2c8",
      "checkout_id": 37101113049366,
      "checkout_token": "c2753bca774fec3e1a27db43cecf7c08",
      "client_details": {
        "accept_language": "en-SE",
        "browser_height": null,
        "browser_ip": "172.225.198.204",
        "browser_width": null,
        "session_hash": null,
        "user_agent": "Mozilla/5.0 (iPhone; CPU iPhone OS 17_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/17.1 Mobile/15E148 Safari/604.1"
      },
      "closed_at": null,
      "confirmation_number": "HDX2IIQ58",
      "confirmed": true,
      "contact_email": "fredrik@something.io",
      "created_at": "2023-11-11T11:56:12-05:00",
      "currency": "USD",
      "current_subtotal_price": "224.91",
      "current_subtotal_price_set": {
        "shop_money": {
          "amount": "224.91",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "2451.00",
          "currency_code": "SEK"
        }
      },
      "current_total_additional_fees_set": null,
      "current_total_discounts": "0.00",
      "current_total_discounts_set": {
        "shop_money": {
          "amount": "0.00",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "0.00",
          "currency_code": "SEK"
        }
      },
      "current_total_duties_set": null,
      "current_total_price": "235.10",
      "current_total_price_set": {
        "shop_money": {
          "amount": "235.10",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "2562.00",
          "currency_code": "SEK"
        }
      },
      "current_total_tax": "0.00",
      "current_total_tax_set": {
        "shop_money": {
          "amount": "0.00",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "0.00",
          "currency_code": "SEK"
        }
      },
      "customer_locale": "en-SE",
      "device_id": null,
      "discount_codes": [],
      "email": "fredrik@srcful.io",
      "estimated_taxes": false,
      "financial_status": "paid",
      "fulfillment_status": null,
      "landing_site": "/products/solana-dad-hat",
      "landing_site_ref": null,
      "location_id": null,
      "merchant_of_record_app_id": null,
      "name": "#1534",
      "note": null,
      "note_attributes": [],
      "number": 534,
      "order_number": 1534,
      "order_status_url": "https://store.solana.com/77289390358/orders/1e98e4369a588abf1f416ce52c52c84c/authenticate?key=2b3df9c97a5654accbb6ce832fd865b1",
      "original_total_additional_fees_set": null,
      "original_total_duties_set": null,
      "payment_gateway_names": [
        "shopify_payments"
      ],
      "phone": null,
      "po_number": null,
      "presentment_currency": "SEK",
      "processed_at": "2023-11-11T11:56:09-05:00",
      "reference": "61e9d90a902dee02a9b6ee469ba6bce3",
      "referring_site": "https://www.google.com/",
      "source_identifier": "61e9d90a902dee02a9b6ee469ba6bce3",
      "source_name": "web",
      "source_url": null,
      "subtotal_price": "224.91",
      "subtotal_price_set": {
        "shop_money": {
          "amount": "224.91",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "2451.00",
          "currency_code": "SEK"
        }
      },
      "tags": "Infoplus-487272.000",
      "tax_exempt": false,
      "tax_lines": [],
      "taxes_included": true,
      "test": false,
      "token": "1e98e4369a588abf1f416ce52c52c84c",
      "total_discounts": "0.00",
      "total_discounts_set": {
        "shop_money": {
          "amount": "0.00",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "0.00",
          "currency_code": "SEK"
        }
      },
      "total_line_items_price": "224.91",
      "total_line_items_price_set": {
        "shop_money": {
          "amount": "224.91",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "2451.00",
          "currency_code": "SEK"
        }
      },
      "total_outstanding": "0.00",
      "total_price": "235.10",
      "total_price_set": {
        "shop_money": {
          "amount": "235.10",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "2562.00",
          "currency_code": "SEK"
        }
      },
      "total_shipping_price_set": {
        "shop_money": {
          "amount": "10.19",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "111.00",
          "currency_code": "SEK"
        }
      },
      "total_tax": "0.00",
      "total_tax_set": {
        "shop_money": {
          "amount": "0.00",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "0.00",
          "currency_code": "SEK"
        }
      },
      "total_tip_received": "0.00",
      "total_weight": 2260,
      "updated_at": "2023-11-11T11:56:59-05:00",
      "user_id": null,
      "billing_address": {
        "first_name": "Fredrik",
        "address1": "Gizella Pallays gata 5B",
        "phone": "072-304 39 40",
        "city": "Kalmar",
        "zip": "393 60",
        "province": null,
        "country": "Sweden",
        "last_name": "Ahlgren",
        "address2": null,
        "company": null,
        "latitude": null,
        "longitude": null,
        "name": "Fredrik Ahlgren",
        "country_code": "SE",
        "province_code": null
      },
      "customer": {
        "id": 7623493550358,
        "email": "fredrik@srcful.io",
        "accepts_marketing": false,
        "created_at": "2023-11-11T11:56:09-05:00",
        "updated_at": "2023-11-11T11:56:13-05:00",
        "first_name": "Fredrik",
        "last_name": "Ahlgren",
        "state": "disabled",
        "note": null,
        "verified_email": true,
        "multipass_identifier": null,
        "tax_exempt": false,
        "phone": null,
        "email_marketing_consent": {
          "state": "not_subscribed",
          "opt_in_level": "single_opt_in",
          "consent_updated_at": null
        },
        "sms_marketing_consent": null,
        "tags": "",
        "currency": "SEK",
        "accepts_marketing_updated_at": "2023-11-11T11:56:09-05:00",
        "marketing_opt_in_level": null,
        "tax_exemptions": [],
        "admin_graphql_api_id": "gid://shopify/Customer/7623493550358",
        "default_address": {
          "id": 9698360394006,
          "customer_id": 7623493550358,
          "first_name": "Fredrik",
          "last_name": "Ahlgren",
          "company": null,
          "address1": "Gizella Pallays gata 5B",
          "address2": null,
          "city": "Kalmar",
          "province": null,
          "country": "Sweden",
          "zip": "393 60",
          "phone": "072-304 39 40",
          "name": "Fredrik Ahlgren",
          "province_code": null,
          "country_code": "SE",
          "country_name": "Sweden",
          "default": true
        }
      },
      "discount_applications": [],
      "fulfillments": [],
      "line_items": [
        {
          "id": 14473350349078,
          "admin_graphql_api_id": "gid://shopify/LineItem/14473350349078",
          "attributed_staffs": [],
          "fulfillable_quantity": 3,
          "fulfillment_service": "infoplus-14",
          "fulfillment_status": null,
          "gift_card": false,
          "grams": 227,
          "name": "Dad Hat",
          "price": "20.37",
          "price_set": {
            "shop_money": {
              "amount": "20.37",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "222.00",
              "currency_code": "SEK"
            }
          },
          "product_exists": true,
          "product_id": 8397849854230,
          "properties": [],
          "quantity": 3,
          "requires_shipping": true,
          "sku": "19814-1-2-000-3780",
          "taxable": true,
          "title": "Dad Hat",
          "total_discount": "0.00",
          "total_discount_set": {
            "shop_money": {
              "amount": "0.00",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "0.00",
              "currency_code": "SEK"
            }
          },
          "variant_id": 45287984169238,
          "variant_inventory_management": "infoplus-14",
          "variant_title": null,
          "vendor": "Merch Store",
          "tax_lines": [],
          "duties": [],
          "discount_allocations": []
        },
        {
          "id": 14473350381846,
          "admin_graphql_api_id": "gid://shopify/LineItem/14473350381846",
          "attributed_staffs": [],
          "fulfillable_quantity": 1,
          "fulfillment_service": "infoplus-14",
          "fulfillment_status": null,
          "gift_card": false,
          "grams": 227,
          "name": "T-shirt - S",
          "price": "25.42",
          "price_set": {
            "shop_money": {
              "amount": "25.42",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "277.00",
              "currency_code": "SEK"
            }
          },
          "product_exists": true,
          "product_id": 8320410910998,
          "properties": [],
          "quantity": 1,
          "requires_shipping": true,
          "sku": "19814-1-2-903-8050",
          "taxable": true,
          "title": "Solana T-shirt",
          "total_discount": "0.00",
          "total_discount_set": {
            "shop_money": {
              "amount": "0.00",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "0.00",
              "currency_code": "SEK"
            }
          },
          "variant_id": 44953256952086,
          "variant_inventory_management": "infoplus-14",
          "variant_title": "S",
          "vendor": "Merch Store",
          "tax_lines": [],
          "duties": [],
          "discount_allocations": []
        },
        {
          "id": 14473350414614,
          "admin_graphql_api_id": "gid://shopify/LineItem/14473350414614",
          "attributed_staffs": [],
          "fulfillable_quantity": 4,
          "fulfillment_service": "infoplus-14",
          "fulfillment_status": null,
          "gift_card": false,
          "grams": 227,
          "name": "T-shirt - M",
          "price": "25.42",
          "price_set": {
            "shop_money": {
              "amount": "25.42",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "277.00",
              "currency_code": "SEK"
            }
          },
          "product_exists": true,
          "product_id": 8320410910998,
          "properties": [],
          "quantity": 4,
          "requires_shipping": true,
          "sku": "19814-1-2-904-8050",
          "taxable": true,
          "title": "Solana T-shirt",
          "total_discount": "0.00",
          "total_discount_set": {
            "shop_money": {
              "amount": "0.00",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "0.00",
              "currency_code": "SEK"
            }
          },
          "variant_id": 44953256984854,
          "variant_inventory_management": "infoplus-14",
          "variant_title": "M",
          "vendor": "Merch Store",
          "tax_lines": [],
          "duties": [],
          "discount_allocations": []
        },
        {
          "id": 14473350447382,
          "admin_graphql_api_id": "gid://shopify/LineItem/14473350447382",
          "attributed_staffs": [],
          "fulfillable_quantity": 2,
          "fulfillment_service": "infoplus-14",
          "fulfillment_status": null,
          "gift_card": false,
          "grams": 227,
          "name": "Beanie",
          "price": "18.35",
          "price_set": {
            "shop_money": {
              "amount": "18.35",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "200.00",
              "currency_code": "SEK"
            }
          },
          "product_exists": true,
          "product_id": 8397841662230,
          "properties": [],
          "quantity": 2,
          "requires_shipping": true,
          "sku": "19814-1-2-000-3779",
          "taxable": true,
          "title": "Solana Beanie",
          "total_discount": "0.00",
          "total_discount_set": {
            "shop_money": {
              "amount": "0.00",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "0.00",
              "currency_code": "SEK"
            }
          },
          "variant_id": 45287952908566,
          "variant_inventory_management": "infoplus-14",
          "variant_title": null,
          "vendor": "Solana Merch Store",
          "tax_lines": [],
          "duties": [],
          "discount_allocations": []
        }
      ],
      "payment_terms": null,
      "refunds": [],
      "shipping_address": {
        "first_name": "Fredrik",
        "address1": "GIZELLA PALLAYS GATA 5B",
        "phone": "072-304 39 40",
        "city": "KALMAR",
        "zip": "393 60",
        "province": null,
        "country": "Sweden",
        "last_name": "Ahlgren",
        "address2": "",
        "company": null,
        "latitude": 56.7053935,
        "longitude": 16.3386606,
        "name": "Fredrik Ahlgren",
        "country_code": "SE",
        "province_code": null
      },
      "shipping_lines": [
        {
          "id": 4571404468502,
          "carrier_identifier": "650f1a14fa979ec5c74d063e968411d4",
          "code": "Flat Rate International Shipping",
          "discounted_price": "10.19",
          "discounted_price_set": {
            "shop_money": {
              "amount": "10.19",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "111.00",
              "currency_code": "SEK"
            }
          },
          "phone": null,
          "price": "10.19",
          "price_set": {
            "shop_money": {
              "amount": "10.19",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "111.00",
              "currency_code": "SEK"
            }
          },
          "requested_fulfillment_service_id": null,
          "source": "shopify",
          "title": "Flat Rate International Shipping",
          "tax_lines": [],
          "discount_allocations": []
        }
      ]
    }
  ]
}```
9

The thing is it doesn’t work with the prices one because they are returned as strings. I guess I should first try to convert them to numbers.

But still, the sum of the prices is not enough since there is also an array of quantities which needs to be considered. I need to multiply each price with its corresponding quantity.

Would there be a way to write a more complex formula that does this with JSONata syntax?

10

Just so I’m clear on the goal…

That sample JSON that you gave contains an array of two orders.
What should the end result be?
Are you wanting to come up with a total price for all orders, or should each order be treated separately?

Anyway, assuming the above is the goal, here is a JavaScript snippet that will do it:

const json = JSON.parse(p1);
const orders = json.orders;
let total = 0;
for (let i in orders) {
  for (let j in orders[i].line_items) {
    total += Number(orders[i].line_items[j].price) * orders[i].line_items[j].quantity;
  }
}
return total;

CleanShot 2023-11-13 at 21.50.09@2x
CleanShot 2023-11-13 at 21.50.09@2x1372×934 61.4 KB

1 Like
11

Correct. So I’m first using a table where I call the API based on date ranges and then I get a result. In another table I am querying the resulting JSON using indexes to disaggregate the results per order. Although I’m gonna sum them up in the end, I also need the results of each order for analytics purposes.

The JavaScript snippet you provided works great for the full/all orders JSON. What would it look like if I wanted to apply it on a per order basis and then rollup (sum) these results?

A JSON with only one order looks like this:

{
  "id": 5618443485462,
  "admin_graphql_api_id": "gid://shopify/Order/5618443485462",
  "app_id": 580111,
  "browser_ip": "172.249.170.125",
  "buyer_accepts_marketing": false,
  "cancel_reason": null,
  "cancelled_at": null,
  "cart_token": "c1-e90a43944deea9726559bbf3b901f807",
  "checkout_id": 37118539563286,
  "checkout_token": "b8034f491dece50b15cef148f632f940",
  "client_details": {
    "accept_language": "en-US",
    "browser_height": null,
    "browser_ip": "172.249.170.125",
    "browser_width": null,
    "session_hash": null,
    "user_agent": "Mozilla/5.0 (iPhone; CPU iPhone OS 17_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148 Phantom/ios/23.16.1.11008"
  },
  "closed_at": null,
  "confirmation_number": "PX5JXRZZT",
  "confirmed": true,
  "contact_email": "aaron.milligan1996@gmail.com",
  "created_at": "2023-11-11T14:16:19-05:00",
  "currency": "USD",
  "current_subtotal_price": "25.00",
  "current_subtotal_price_set": {
    "shop_money": {
      "amount": "25.00",
      "currency_code": "USD"
    },
    "presentment_money": {
      "amount": "25.00",
      "currency_code": "USD"
    }
  },
  "current_total_additional_fees_set": null,
  "current_total_discounts": "0.00",
  "current_total_discounts_set": {
    "shop_money": {
      "amount": "0.00",
      "currency_code": "USD"
    },
    "presentment_money": {
      "amount": "0.00",
      "currency_code": "USD"
    }
  },
  "current_total_duties_set": null,
  "current_total_price": "32.50",
  "current_total_price_set": {
    "shop_money": {
      "amount": "32.50",
      "currency_code": "USD"
    },
    "presentment_money": {
      "amount": "32.50",
      "currency_code": "USD"
    }
  },
  "current_total_tax": "0.00",
  "current_total_tax_set": {
    "shop_money": {
      "amount": "0.00",
      "currency_code": "USD"
    },
    "presentment_money": {
      "amount": "0.00",
      "currency_code": "USD"
    }
  },
  "customer_locale": "en-US",
  "device_id": null,
  "discount_codes": [],
  "email": "aaron.milligan1996@gmail.com",
  "estimated_taxes": false,
  "financial_status": "paid",
  "fulfillment_status": null,
  "landing_site": "/products/short-sleeve-t-shirt",
  "landing_site_ref": null,
  "location_id": null,
  "merchant_of_record_app_id": null,
  "name": "#1535",
  "note": null,
  "note_attributes": [],
  "number": 535,
  "order_number": 1535,
  "order_status_url": "https://store.solana.com/77289390358/orders/05ca34846fb735ab6d4b2e0009b44fc1/authenticate?key=4a128b6aa5f121f7ef943ec881c0f30b",
  "original_total_additional_fees_set": null,
  "original_total_duties_set": null,
  "payment_gateway_names": [
    "Solana Pay"
  ],
  "phone": null,
  "po_number": null,
  "presentment_currency": "USD",
  "processed_at": "2023-11-11T14:16:17-05:00",
  "reference": "dfc6252f18c952a7073f20af47f87fe5",
  "referring_site": "",
  "source_identifier": "dfc6252f18c952a7073f20af47f87fe5",
  "source_name": "web",
  "source_url": null,
  "subtotal_price": "25.00",
  "subtotal_price_set": {
    "shop_money": {
      "amount": "25.00",
      "currency_code": "USD"
    },
    "presentment_money": {
      "amount": "25.00",
      "currency_code": "USD"
    }
  },
  "tags": "Infoplus-487288.000",
  "tax_exempt": false,
  "tax_lines": [],
  "taxes_included": true,
  "test": false,
  "token": "05ca34846fb735ab6d4b2e0009b44fc1",
  "total_discounts": "0.00",
  "total_discounts_set": {
    "shop_money": {
      "amount": "0.00",
      "currency_code": "USD"
    },
    "presentment_money": {
      "amount": "0.00",
      "currency_code": "USD"
    }
  },
  "total_line_items_price": "25.00",
  "total_line_items_price_set": {
    "shop_money": {
      "amount": "25.00",
      "currency_code": "USD"
    },
    "presentment_money": {
      "amount": "25.00",
      "currency_code": "USD"
    }
  },
  "total_outstanding": "0.00",
  "total_price": "32.50",
  "total_price_set": {
    "shop_money": {
      "amount": "32.50",
      "currency_code": "USD"
    },
    "presentment_money": {
      "amount": "32.50",
      "currency_code": "USD"
    }
  },
  "total_shipping_price_set": {
    "shop_money": {
      "amount": "7.50",
      "currency_code": "USD"
    },
    "presentment_money": {
      "amount": "7.50",
      "currency_code": "USD"
    }
  },
  "total_tax": "0.00",
  "total_tax_set": {
    "shop_money": {
      "amount": "0.00",
      "currency_code": "USD"
    },
    "presentment_money": {
      "amount": "0.00",
      "currency_code": "USD"
    }
  },
  "total_tip_received": "0.00",
  "total_weight": 226,
  "updated_at": "2023-11-11T14:16:52-05:00",
  "user_id": null,
  "billing_address": {
    "first_name": "aaron",
    "address1": "464 North Crescent Heights Boulevard",
    "phone": "(714) 356-9689",
    "city": "Los Angeles",
    "zip": "90048",
    "province": "California",
    "country": "United States",
    "last_name": "Milligan",
    "address2": null,
    "company": null,
    "latitude": null,
    "longitude": null,
    "name": "aaron Milligan",
    "country_code": "US",
    "province_code": "CA"
  },
  "customer": {
    "id": 7623863107862,
    "email": "aaron.milligan1996@gmail.com",
    "accepts_marketing": false,
    "created_at": "2023-11-11T14:15:58-05:00",
    "updated_at": "2023-11-11T14:16:20-05:00",
    "first_name": "aaron",
    "last_name": "Milligan",
    "state": "disabled",
    "note": null,
    "verified_email": true,
    "multipass_identifier": null,
    "tax_exempt": false,
    "phone": null,
    "email_marketing_consent": {
      "state": "not_subscribed",
      "opt_in_level": "single_opt_in",
      "consent_updated_at": null
    },
    "sms_marketing_consent": null,
    "tags": "",
    "currency": "USD",
    "accepts_marketing_updated_at": "2023-11-11T14:15:58-05:00",
    "marketing_opt_in_level": null,
    "tax_exemptions": [],
    "admin_graphql_api_id": "gid://shopify/Customer/7623863107862",
    "default_address": {
      "id": 9698964144406,
      "customer_id": 7623863107862,
      "first_name": "aaron",
      "last_name": "Milligan",
      "company": null,
      "address1": "464 North Crescent Heights Boulevard",
      "address2": null,
      "city": "Los Angeles",
      "province": "California",
      "country": "United States",
      "zip": "90048",
      "phone": "(714) 356-9689",
      "name": "aaron Milligan",
      "province_code": "CA",
      "country_code": "US",
      "country_name": "United States",
      "default": true
    }
  },
  "discount_applications": [],
  "fulfillments": [],
  "line_items": [
    {
      "id": 14473828696342,
      "admin_graphql_api_id": "gid://shopify/LineItem/14473828696342",
      "attributed_staffs": [],
      "fulfillable_quantity": 1,
      "fulfillment_service": "infoplus-14",
      "fulfillment_status": null,
      "gift_card": false,
      "grams": 227,
      "name": "Solana T-shirt - L",
      "price": "25.00",
      "price_set": {
        "shop_money": {
          "amount": "25.00",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "25.00",
          "currency_code": "USD"
        }
      },
      "product_exists": true,
      "product_id": 8320410910998,
      "properties": [],
      "quantity": 1,
      "requires_shipping": true,
      "sku": "19814-1-2-905-8050",
      "taxable": true,
      "title": "Solana T-shirt",
      "total_discount": "0.00",
      "total_discount_set": {
        "shop_money": {
          "amount": "0.00",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "0.00",
          "currency_code": "USD"
        }
      },
      "variant_id": 44953257017622,
      "variant_inventory_management": "infoplus-14",
      "variant_title": "L",
      "vendor": "Solana Merch Store",
      "tax_lines": [],
      "duties": [],
      "discount_allocations": []
    }
  ],
  "payment_terms": null,
  "refunds": [],
  "shipping_address": {
    "first_name": "aaron",
    "address1": "464 N CRESCENT HEIGHTS BLVD",
    "phone": "(714) 356-9689",
    "city": "LOS ANGELES",
    "zip": "90048",
    "province": "California",
    "country": "United States",
    "last_name": "Milligan",
    "address2": "",
    "company": null,
    "latitude": 34.0800161,
    "longitude": -118.3656287,
    "name": "aaron Milligan",
    "country_code": "US",
    "province_code": "CA"
  },
  "shipping_lines": [
    {
      "id": 4571613856022,
      "carrier_identifier": "650f1a14fa979ec5c74d063e968411d4",
      "code": "Flat Rate Domestic Shipping",
      "discounted_price": "7.50",
      "discounted_price_set": {
        "shop_money": {
          "amount": "7.50",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "7.50",
          "currency_code": "USD"
        }
      },
      "phone": null,
      "price": "7.50",
      "price_set": {
        "shop_money": {
          "amount": "7.50",
          "currency_code": "USD"
        },
        "presentment_money": {
          "amount": "7.50",
          "currency_code": "USD"
        }
      },
      "requested_fulfillment_service_id": null,
      "source": "shopify",
      "title": "Flat Rate Domestic Shipping",
      "tax_lines": [],
      "discount_allocations": []
    }
  ]
}```
12
  • Add a Row Number column to your table, starting at zero
  • Make sure your JSON is in a template column, so it’s available on every row
  • Use the following:
const json = JSON.parse(p1);
const order = json.orders[p2];
let total = 0;
for (let j in order.line_items) {
  total += Number(order.line_items[j].price) * order.line_items[j].quantity;
}
return total;
  • p1 is your templated JSON, p2 is the row number
  • this will give you a separate total per order on each row
  • you won’t be able to roll that up however, as the JavaScript column always returns a string
  • but if you use a math column to multiply it by 1, you’ll then be able to roll up the math column.
2 Likes
13

This worked, @Darren_Murphy. Thanks so much!

Your solution was very succinct and elegant. I guess I better start a JavaScript crash course. Any suggestions, perhaps?

14

On a closely related note, I am now struggling with the following:

I am trying to pull data on how many of each unique items were purchased. The JSON I have includes variant_ids and quantities. I was trying this JavaScript snippet but the column where I was running this keeps loading and loading a result and doesn’t return anything.

Here’s the code I tried:

const ordersData = JSON.parse(p1);
const itemCounts = {};

ordersData.orders.forEach(order => {
  order.line_items.forEach(item => {
    const key = item.variant_id;
    if (!itemCounts[key]) {
      itemCounts[key] = {
        title: item.title,
        quantity: 0
      };
    }
    
    itemCounts[key].quantity += item.quantity;
  });
});
return(itemCounts)

Any suggestions? I’d really appreciate it.

15

The problem is in your return statement. You are trying to return a JSON Object, which won’t work.
Change the return to the following:

return(JSON.stringify(itemCounts));
2 Likes
16

Thanks @Darren_Murphy! This worked perfectly.
A final related question, I hope you can give me some light:
I need to use the returned data (sold items and their quantities) in a dashboard, perhaps in a chart or a list.
Is there a way to extract these data? I’m thinking maybe querying this returned object. Or is it necessary for each item to be in its own row? If so, how could I do that?

17

If you want a list where each individual item is clickable, then yes you’ll need one item per row. Check the below to learn how to expand the items into rows:

If you just want a table, then you could modify the JavaScript to output a HTML table, and then use that in a Rich Text component. You could do the same thing to build a parameter list for a quickchart.io chart.

2 Likes
18

This was really helpful, Darren. I managed to modify the JavaScript as you suggested and I was able to have it output both a HTML table and a parameter list for a chart. Thanks for your help again!

1 Like
19

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.