Advertisement
tanin

IMPORTJSON

Jan 29th, 2022
946
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /**
  2. * Imports JSON data to your spreadsheet Ex: IMPORTJSON("http://myapisite.com","city/population")
  3. * @param url URL of your JSON data as string
  4. * @param xpath simplified xpath as string
  5. * @customfunction
  6. */
  7. function IMPORTJSON(url,xpath){
  8.  
  9.   try{
  10.     // /rates/EUR
  11.     var res = UrlFetchApp.fetch(url);
  12.     var content = res.getContentText();
  13.     var json = JSON.parse(content);
  14.    
  15.     var patharray = xpath.split("/");
  16.     //Logger.log(patharray);
  17.    
  18.     for(var i=0;i<patharray.length;i++){
  19.       json = json[patharray[i]];
  20.     }
  21.    
  22.     //Logger.log(typeof(json));
  23.    
  24.     if(typeof(json) === "undefined"){
  25.       return "Node Not Available";
  26.     } else if(typeof(json) === "object"){
  27.       var tempArr = [];
  28.      
  29.       for(var obj in json){
  30.         tempArr.push([obj,json[obj]]);
  31.       }
  32.       return tempArr;
  33.     } else if(typeof(json) !== "object") {
  34.       return json;
  35.     }
  36.   }
  37.   catch(err){
  38.       return "Error getting data";  
  39.   }
  40.  
  41. }
Advertisement
Advertisement
Advertisement
RAW Paste Data Copied
Advertisement