Function: call API for JSON data, show rates of desired parameter --> IMPORTJSON(url,xpath) ex. IMPORTJSON(x.com, rates.f) --> 0.042

Question

So I'm using googlesheets. They have a scripteditor using google apps script https://developers.google.com/apps-script/guides/sheets.

Basically JavaScript.

I wanted to create a function that runs down a JSON file outputting what I specifically want. For example out of file:

{"meta":{"code":200,"disclaimer":"Usage subject to terms: https:\/\/fake-website.com\/terms"},"response":{"date":"2020-04-30T12:37:54Z","base":"X","rates":{"X":1,"F":0.9199812,"K":0.79896235,...}}}

I want to access the rates of F. So when I call my function it outputs 0.9199812.

The function I wrote looks like this:

function IMPORTJSON(url,xpath){
  try{
    // Funktion um in dem sheet das abzurufen /rates/F
    let res = UrlFetchApp.fetch(url);
    //get the url
    let content = res.getContentText();
    // get the content
    let json = Json.parse(content);
    //show content

    let patharray = xpath.split(".")
    //enables me to use dots to walk down the filepath

    for( let i=0;i<patharray.lenght;i++){
    //loop to only show what excactly I want out of the file
      //json becomes what I want out of the file
      json = json[patharray[i]];
    } 

  //check whether json is an object
    if(typeof(json) === "undefined"){
        return "Node Not Available"; // In case I don't get an response
      } else if(typeof(json) === "object"){
          let tempArr = [];
      //Creation of an array

          for (let obj in json){
        //filling the array with name and rate
            tempArr.push([obj, json[obj]]);
          }
          return tempArr;
      } else if(typeof(json) !== "object") {
          return json;
      }
     }

  catch(err){
    return "Error getting JSON data";
    }
}

I tried to call it like this: IMPORTJSON(myurl, rates.F) but my sheet told me there's an error parsing the formula... The link definitely works so there has to be an error with either my call (rates.F) or with my way of defining the call. Please help.

Makan · Answer 1 · 2020-04-30T16:08:48.030

Fetching is an async task and needs time to complete, so only after that time let res = UrlFetchApp.fetch(url); has value. But other codes will run immediately before res value resolved to something meaningful. To make function an async function and for async task use await keyword, basically await means wait here and don't go forward until you got the result, so you can write your function like this:

async function IMPORTJSON(url,xpath){
  try{
    // Funktion um in dem sheet das abzurufen /rates/F
    let res = await UrlFetchApp.fetch(url);
    //get the url
    let content = await res.getContentText();
    // get the content
    let json = JSON.parse(content);
    //show content

    let patharray = xpath.split(".")
    //enables me to use dots to walk down the filepath

    for( let i=0;i<patharray.lenght;i++){
    //loop to only show what excactly I want out of the file
      //json becomes what I want out of the file
      json = json[patharray[i]];
    } 

  //check whether json is an object
    if(typeof(json) === "undefined"){
        return "Node Not Available"; // In case I don't get an response
      } else if(typeof(json) === "object"){
          let tempArr = [];
      //Creation of an array

          for (let obj in json){
        //filling the array with name and rate
            tempArr.push([obj, json[obj]]);
          }
          return tempArr;
      } else if(typeof(json) !== "object") {
          return json;
      }
     }

  catch(err){
    return "Error getting JSON data";
    }
}

Function: call API for JSON data, show rates of desired parameter --> IMPORTJSON(url,xpath) ex. IMPORTJSON(x.com, rates.f) --> 0.042

1 Answers1