Get Specific value from external API - Google sheet

user_12862368 0
Viewed 103

i use google sheet and i want get specific value from external api. I call the API for each specific object (stocks). I want the first (10.2222) and fourth value (18.08795)of this json. i can't use the number of th ID because, it change for each object . I can just use the position (1° and 4°).

{"data":[{"id":"158379","type":"metric","attributes":{"value":10.2222,"meaningful":true,"grade":3},"relationships":{"metric_type":{"data":{"id":"110","type":"metric_type"}}}},{"id":"224845","type":"metric","attributes":{"value":7.663466,"meaningful":true,"grade":6},"relationships":{"metric_type":{"data":{"id":"95","type":"metric_type"}}}},{"id":"54342","type":"metric","attributes":{"value":3.6891754,"meaningful":true,"grade":0},"relationships":{"metric_type":{"data":{"id":"96","type":"metric_type"}}}},{"id":"224846","type":"metric","attributes":{"value":19.084248,"meaningful":true,"grade":0},"relationships":{"metric_type":{"data":{"id":"99","type":"metric_type"}}}},{"id":"162575","type":"metric","attributes":{"value":18.08795,"meaningful":true,"grade":0},"relationships":{"metric_type":{"data":{"id":"100","type":"metric_type"}}}},{"id":"170262","type":"metric","attributes":{"value":21.21323,"meaningful":true,"grade":0},"relationships":{"metric_type":{"data":{"id":"27","type":"metric_type"}}}},{"id":"38556","type":"metric","attributes":{"value":15.68155,"meaningful":true,"grade":0},"relationships":{"metric_type":{"data":{"id":"29","type":"metric_type"}}}},{"id":"68101","type":"metric","attributes":{"value":27.522194,"meaningful":true,"grade":0},"relationships":{"metric_type":{"data":{"id":"31","type":"metric_type"}}}},{"id":"68100","type":"metric","attributes":{"value":20.490818,"meaningful":true,"grade":0},"relationships":{"metric_type":{"data":{"id":"39","type":"metric_type"}}}},{"id":"188788","type":"metric","attributes":{"value":24.493673,"meaningful":true,"grade":0},"relationships":{"metric_type":{"data":{"id":"28","type":"metric_type"}}}}],"included":[{"id":"110","type":"metric_type","attributes":{"field":"dps_yoy"}},{"id":"95","type":"metric_type","attributes":{"field":"dividend_per_share_change_dislpay"}},{"id":"96","type":"metric_type","attributes":{"field":"dividend_lt_fwd_growth"}},{"id":"99","type":"metric_type","attributes":{"field":"div_grow_rate3"}},{"id":"100","type":"metric_type","attributes":{"field":"div_grow_rate5"}},{"id":"27","type":"metric_type","attributes":{"field":"revenue_change_display"}},{"id":"29","type":"metric_type","attributes":{"field":"eps_change_display"}},{"id":"31","type":"metric_type","attributes":{"field":"fcf_per_share_change_display"}},{"id":"39","type":"metric_type","attributes":{"field":"ebitda_change_display"}},{"id":"28","type":"metric_type","attributes":{"field":"ebit_change_display"}}]}

REGEX function in google script

    function importRegex(url, regex_string) {
  var html, content = '';
  var response = UrlFetchApp.fetch(url);
  if (response) {
    html = response.getContentText();
    if (html.length && regex_string.length) {
      var regex = new RegExp( regex_string, "i" );
      content = html.match(regex)[1];
    }
  }
  content = unescapeHTML(content);
  Utilities.sleep(1000); // avoid call limit by adding a delay
  return content;  
}

thanks

google-sheets api regex
1 Answers

Since your input is a valid JSON, you can use

j_obj = JSON.parse(html)
return j_obj["data"][n]["attributes"]["value"]

where n is the id of the value you need to retrieve.

Full method:

function rss(url, n) {
    var params = { 
        headers: { 
            'Content-Type': "application/json", 'Accept':           "application/json",
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36'
        },
        muteHttpExceptions: true,
        method: "GET",
        contentType: "application/json",
        validateHttpsCertificates: false,
    };

    var response = UrlFetchApp.fetch(url, params);
    html = response.getContentText();
    const j_obj = JSON.parse(html);
    return j_obj["data"][n]["attributes"]["value"];
}
user_38329700
Related