CODEWITHSUNDEEP
javascriptarraysjsongoogle-apps-scriptfiltering
Monochrome
Monochrome

Reputation: 17

Filtering the JSON data which is received from UrlFetchApp

In the script, I am importing the JSON data from the API's URL, using the UrlFetchApp.fetch

This part of code looks next:

var jsondata = UrlFetchApp.fetch(url, fetchOptions);

And here is a shortened example of how the JSON i work with looks like:

[
  {
    "average": 5.76,
    "date": "2021-03-16",
    "highest": 5.88,
    "lowest": 5.41,
    "order_count": 2214,
    "volume": 5093879501
  },
  {
    "average": 5.88,
    "date": "2021-03-17",
    "highest": 5.92,
    "lowest": 5.46,
    "order_count": 2285,
    "volume": 3968980696
  },
  {
    "average": 5.68,
    "date": "2021-03-18",
    "highest": 5.91,
    "lowest": 5.6,
    "order_count": 2219,
    "volume": 3695305537
  },
  {
    "average": 5.65,
    "date": "2021-03-19",
    "highest": 5.98,
    "lowest": 5.59,
    "order_count": 2192,
    "volume": 3948557942
  }
]

I need to filter the above data before it goes further in a script, and my problem is that I'm getting this JSON data as an jsondata object, with no actual JSON code.

I consider that the most convenient way to filter data will be turning the jsondata into an array (in order to use .slice and .map for my filtering), and then converting the resulting array back to object to use further in a script, but I don't know how to do that.

Upvotes: 0

Views: 1557

Answers (3)

Turtles
Turtles

Reputation: 504

so, you always get a JSON string object from any http request as above. Then you need to parse it:

let object = JSON.parse(UrlFetchApp.fetch(url, fetchOptions));

As soon as you have your object you can do everything you want. So, you can copy this object to another one using a destructor (...) operator:

let object2 = [ ...object ]

Here, the object2 is an exact copy of the object 1. The filter command works as a copy but returns only the value that match the sentence. As showed in my answer above.

If you want to return to you a number of cases that the item has highest higher than let say 5.9, you can use the methods: map and filter together: Case 1:

const json = '[{"average":5.76,"date":"2021-03-16","highest":5.88,"lowest":5.41,"order_count":2214,"volume":5093879501},{"average":5.88,"date":"2021-03-17","highest":5.92,"lowest":5.46,"order_count":2285,"volume":3968980696},{"average":5.68,"date":"2021-03-18","highest":5.91,"lowest":5.6,"order_count":2219,"volume":3695305537},{"average":5.65,"date":"2021-03-19","highest":5.98,"lowest":5.59,"order_count":2192,"volume":3948557942}]';

let obj = JSON.parse(json);


const obj2 = obj.map(obj => obj.highest).filter((item) => item > 5.9).length;

It will return the number 3. If you want the answer to be an array with the values you just remove length:

const obj2 = obj.map(obj => obj.highest).filter((item) => item > 5.9);

It will return: [5.92, 5.91, 5.98]

So, these are only some examples. You can look more on the internet.

Cheers, Marcelo

Upvotes: 1

Cooper
Cooper

Reputation: 64062

    function jtoa() {
  const json = '[{"average":5.76,"date":"2021-03-16","highest":5.88,"lowest":5.41,"order_count":2214,"volume":5093879501},{"average":5.88,"date":"2021-03-17","highest":5.92,"lowest":5.46,"order_count":2285,"volume":3968980696},{"average":5.68,"date":"2021-03-18","highest":5.91,"lowest":5.6,"order_count":2219,"volume":3695305537},{"average":5.65,"date":"2021-03-19","highest":5.98,"lowest":5.59,"order_count":2192,"volume":3948557942}]';
  let obj = JSON.parse(json);
  let a = obj.map(o => {return [o.average, o.date, o.highest, o.lowest, o.order_count, o.volume];} );
  Logger.log(JSON.stringify(a));
}

Upvotes: 1

Turtles
Turtles

Reputation: 504

I believe you are getting a JSON string and you need to use the function JSON.parse() to convert it to an object.

So, use this:

let jsondata = JSON.parse(UrlFetchApp.fetch(url, fetchOptions));

JSON.parse is a secure function to parse JSON strings and convert them to objects. Now you will have an object that you can do everything with it.

With an object you can use many JS native functions as:
Object.keys(obj) to extract the keys
Object.values(obj) to extract the values
Object.entries(obj) to extract both

And you can use direct filter to do what you want.

Example: 

const array = [your array here]

const filterarray = array.filter((item) => item.highest > 5.9))
console.log(filterarray)

Answer: you will get only 3 array back that has high higher than 5.9.

(3)[{...},{...},{...}

If you want to convert the array to string you can use also JSON.stringify.

I hope I could help!

Cheers, Marcelo

Upvotes: 1

Related Questions