CODEWITHSUNDEEP
jsongoogle-apps-scriptgoogle-sheets
Jessica Hunter
Jessica Hunter

Reputation: 23

Taking data from JSON into google sheets using specific info

I'm so bad at titles and I don't know the technical terms, sorry about that.

I've managed to take the data from here, but how do I make it only pull data from "hq":true? This is what I have so far:

=ImportJSON("https://universalis.app/api/Ultros/36105","/listings/pricePerUnit","noHeaders")

I assume I can't just /"hq":true because the hq and pricePerUnit is in the same...row? If that makes sense. So do I comma it? /pricePerUnit,"hq":true? I only want it to pull the hq data to my google sheet. Is this possible? Thank you.

Upvotes: 0

Views: 394

Answers (1)

Tanaike
Tanaike

Reputation: 201388

I believe your goal is as follows.

  • You want to retrieve the values of the property of listings from the URL of https://universalis.app/api/Ultros/36105. At that time, you want to retrieve the values with "hq":true.

In your situation, how about directly preparing a Google Apps Script for retrieving the values? When this is reflected in a script, it becomes as follows.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet and save the script. And, when you use this script, please put a custom function of =SAMPLE("https://universalis.app/api/Ultros/36105") to a cell.

function SAMPLE(url) {
  const res = UrlFetchApp.fetch(url);
  const obj = JSON.parse(res.getContentText());
  const v = obj.listings.filter(({hq}) => hq === true);
  const header = Object.keys(v[0]);
  return [header, ...v.map(o => header.map(h => Array.isArray(o[h]) ? o[h].join(",") : o[h]))];
}

Result:

When this script is run, the following result is obtained.

enter image description here

Note:

  • When you want to change the order of columns, please replace Object.keys(v[0]) with the array including the header values you want to use.

  • If you don't want to add the header, please modify return [header, ...v.map(o => header.map(h => Array.isArray(o[h]) ? o[h].join(",") : o[h]))]; to return v.map(o => header.map(h => Array.isArray(o[h]) ? o[h].join(",") : o[h]));

  • This sample script is for https://universalis.app/api/Ultros/36105 of your question. So when you change the URL, this script might not be able to be used. Please be careful about this.

  • From /listings/pricePerUnit, when you want to retrieve the value of pricePerUnit when "hq":true, you can also use the following script.

      function SAMPLE(url) {
    const res = UrlFetchApp.fetch(url);
    const obj = JSON.parse(res.getContentText());
    return obj.listings.filter(({hq}) => hq === true).map(({pricePerUnit}) => [pricePerUnit]);
  }

References:

Upvotes: 2

Related Questions