Jsonatags is a library that enables the use of the Jsonata.js library in Google Apps Script. API docs are available here.
To add the library to your project:
- Click "Add a library" in the Libraries section of the left pane in the Apps Script IDE.
- Enter the project key (
1jH4Denh3jCoqRxDV8V2VLxG2xVzkoe4B57Jg0D66RpG1MMgjFZEeG6Kd) in the "Script ID" field, and click "Look up". - Select the highest version number, and choose
Jsonataas the identifier. (Do not turn on Development Mode unless you know what you are doing. The development version may not work) - Press "Add". You can now use the Jsonata library in your code.
The sample code #2 below is available in my test project with the installed library.
function getSampleJson_() {
return {
"Account": {
"Account Name": "Firefly",
"Order": [
{
"OrderID": "order103",
"Product": [
{
"Product Name": "Bowler Hat",
"ProductID": 858383,
"SKU": "0406654608",
"Description": {
"Colour": "Purple",
"Width": 300,
"Height": 200,
"Depth": 210,
"Weight": 0.75
},
"Price": 34.45,
"Quantity": 2
},
{
"Product Name": "Trilby hat",
"ProductID": 858236,
"SKU": "0406634348",
"Description": {
"Colour": "Orange",
"Width": 300,
"Height": 200,
"Depth": 210,
"Weight": 0.6
},
"Price": 21.67,
"Quantity": 1
}
]
},
{
"OrderID": "order104",
"Product": [
{
"Product Name": "Bowler Hat",
"ProductID": 858383,
"SKU": "040657863",
"Description": {
"Colour": "Purple",
"Width": 300,
"Height": 200,
"Depth": 210,
"Weight": 0.75
},
"Price": 34.45,
"Quantity": 4
},
{
"ProductID": 345664,
"SKU": "0406654603",
"Product Name": "Cloak",
"Description": {
"Colour": "Black",
"Width": 30,
"Height": 20,
"Depth": 210,
"Weight": 2
},
"Price": 107.99,
"Quantity": 1
}
]
}
]
}
};
}// try live here
// https://try.jsonata.org/
function test_jsonata() {
var json = getSampleJson_();
var request = '$sum(Account.Order.Product.(Price * Quantity))';
var result = Jsonata.jsonata(request).evaluate(json);
console.log(result);
}function test_jsonata() {
var json = getSampleJson_();
var requests = [
// Get array of JSONs
"Account.Order.Product.{" +
"'product': `Product Name`," +
"'color': Description.Colour}",
// Get array of arrays
"Account.Order.Product.[" +
"`Product Name`," +
"Description.Colour]"
];
var result;
for (var i = 0; i < requests.length; i++) {
result = Jsonata.jsonata(requests[i]).evaluate(json);
console.log(result);
}
}V1. Copied the version 1.8.6 of the original library and adapted it for google-apps-script
V2. Commented lines 523-532, starting from if (!arrayOK) {. This resolved the issue with the usage of the join function.
MIT