IMPORTJSON Function - Google Sheets Tutorial - How to Import JSON feed to Spreadsheets
Why Stack Overflow recommends it
Stack Overflow users link it as a reference when answering how to import JSON feeds into Google Sheets with a custom IMPORTJSON function.
Recommended by 0 people across 0 different months, 2019 to 2022.
What it is
A short tutorial showing how to pull JSON data from an API into Google Sheets using a custom IMPORTJSON Apps Script function. The presenter walks through adding the script and calling it from a cell with a URL and a simplified path.
What people said
Cites player0 · 2019-02 · Stack Overflow · on “How do I add data from multiple JSON feeds to a Google Sheet?”
you can use custom fx: =IMPORTJSON("url-here", "xpath-here") function IMPORTJSON(url,xpath){ try{ // /rates/EUR var res = UrlFetchApp.fetch(url); var content = res.getContentText(); var json = JSON.parse(content); var patharray = xpath.split("/"); //Logger.log(patharray); for(var i=0;i<patharray.length;i++){ json = json[patharray[i]]; } //Logger.log(typeof(json)); if(typeof(json) === "undefined"){ return "Node Not Available"; } else if(typeof(json) === "object"){ var tempArr = []; for(var obj in json){ tempArr.push([obj,json[obj]]); } return tempArr; } else if(typeof(json) !== "object") { return json; } } catch(err){ return "Error getting data"; } } [this video] [LINK]
Cites player0 · 2019-02 · Stack Overflow · on “How to ImportJSON, specifically NBA stats, into Google Sheets”
you can try to use this custom fx: =IMPORTJSON("url-here", "xpath-here") function IMPORTJSON(url,xpath){ try{ // /rates/EUR var res = UrlFetchApp.fetch(url); var content = res.getContentText(); var json = JSON.parse(content); var patharray = xpath.split("/"); //Logger.log(patharray); for(var i=0;i<patharray.length;i++){ json = json[patharray[i]]; } //Logger.log(typeof(json)); if(typeof(json) === "undefined"){ return "Node Not Available"; } else if(typeof(json) === "object"){ var tempArr = []; for(var obj in json){ tempArr.push([obj,json[obj]]); } return tempArr; } else if(typeof(json) !== "object") { return json; } } catch(err){ return "Error getting data"; } } [this video] [LINK]
Cites Mir · 2020-01 · Stack Overflow · on “Need to search API output in JSON format for value and print entire array content”
I have googlesheets functions that parse json and import to sheets, you can find the code for the function in ImportJson file. function IMPORTJSON(url,xpath){ try{ // /rates/EUR var res = UrlFetchApp.fetch(url); var content = res.getContentText(); var json = JSON.parse(content); var patharray = xpath.split("/"); //Logger.log(patharray); for(var i=0;i<patharray.length;i++){ json = json[patharray[i]]; } //Logger.log(typeof(json)); if(typeof(json) === "undefined"){ return "Node Not Available"; } else if(typeof(json) === "object"){ var tempArr = []; for(var obj in json){ tempArr.push([obj,json[obj]]); } return tempArr; } else if(typeof(json) !== "object") { return json; } } catch(err){ return "E
Cites Putnik · 2022-02 · Stack Overflow · on “Google sheets custom function data disappears when viewing site as html after 20 minutes”
I am trying to understand why a custom google sheet script can initially pull in JSON data when I have the sheet open, as defined in cell(A1). 20 minutes after closing the google sheet in the browser, I access the published html page of that sheet and the data is not displayed. I wanted to see a proof of concept for a google-apps-script function that is run within google sheets and fetches a JSON page and displays the data as a google sheets table in html. I went through this tutorial which gives an example function [this video] * Imports JSON data to your spreadsheet Ex: IMPORTJSON(" [LINK] ","city/population") * @param url URL of your JSON data as string * @param xpath simplified xpath as strin