Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/**
* Retrieves all the rows in the active spreadsheet that contain data and logs the
* values for each row.
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/
function readRows() {
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
for (var i = 0; i <= numRows - 1; i++) {
var row = values[i];
Logger.log(row);
}
};
/**
* Adds a custom menu to the active spreadsheet, containing a single menu item
* for invoking the readRows() function specified above.
* The onOpen() function, when defined, is automatically invoked whenever the
* spreadsheet is opened.
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Read Data",
functionName : "readRows"
}];
sheet.addMenu("Script Center Menu", entries);
};
/*====================================================================================================================================*
ImportJSON by Trevor Lohrbeer (@FastFedora)
====================================================================================================================================
Version: 1.1
Project Page: http://blog.fastfedora.com/projects/import-json
Copyright: (c) 2012 by Trevor Lohrbeer
License: GNU General Public License, version 3 (GPL-3.0)
http://www.opensource.org/licenses/gpl-3.0.html
------------------------------------------------------------------------------------------------------------------------------------
A library for importing JSON feeds into Google spreadsheets. Functions include:
ImportJSON For use by end users to import a JSON feed from a URL
ImportJSONAdvanced For use by script developers to easily extend the functionality of this library
Future enhancements may include:
- Support for a real XPath like syntax similar to ImportXML for the query parameter
- Support for OAuth authenticated APIs
Or feel free to write these and add on to the library yourself!
------------------------------------------------------------------------------------------------------------------------------------
Changelog:
1.1 Added support for the noHeaders option
1.0 Initial release
*====================================================================================================================================*/
/**
* Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create
* a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in
* the JSON feed. The remaining rows contain the data.
*
* By default, data gets transformed so it looks more like a normal data import. Specifically:
*
* - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values
* of the rows representing their parent elements.
* - Values longer than 256 characters get truncated.
* - Headers have slashes converted to spaces, common prefixes removed and the resulting text converted to title case.
*
* To change this behavior, pass in one of these values in the options parameter:
*
* noInherit: Don't inherit values from parent elements
* noTruncate: Don't truncate values
* rawHeaders: Don't prettify headers
* noHeaders: Don't include headers, only the data
* debugLocation: Prepend each value with the row & column it belongs in
*
* For example:
*
* =ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json", "/feed/entry/title,/feed/entry/content",
* "noInherit,noTruncate,rawHeaders")
*
* @param {url} the URL to a public JSON feed
* @param {query} a comma-separated lists of paths to import. Any path starting with one of these paths gets imported.
* @param {options} a comma-separated list of options that alter processing of the data
*
* @return a two-dimensional array containing the data, with the first row containing headers
* @customfunction
**/
function ImportJSON(url, query, options) {
return ImportJSONAdvanced(url, query, options, includeXPath_, defaultTransform_);
}
/**
* An advanced version of ImportJSON designed to be easily extended by a script. This version cannot be called from within a
* spreadsheet.
*
* Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create
* a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in
* the JSON feed. The remaining rows contain the data.
*
* Use the include and transformation functions to determine what to include in the import and how to transform the data after it is
* imported.
*
* For example:
*
* =ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json",
* "/feed/entry",
* function (query, path) { return path.indexOf(query) == 0; },
* function (data, row, column) { data[row][column] = data[row][column].toString().substr(0, 100); } )
*
* In this example, the import function checks to see if the path to the data being imported starts with the query. The transform
* function takes the data and truncates it. For more robust versions of these functions, see the internal code of this library.
*
* @param {url} the URL to a public JSON feed
* @param {query} the query passed to the include function
* @param {options} a comma-separated list of options that may alter processing of the data
* @param {includeFunc} a function with the signature func(query, path, options) that returns true if the data element at the given path
* should be included or false otherwise.
* @param {transformFunc} a function with the signature func(data, row, column, options) where data is a 2-dimensional array of the data
* and row & column are the current row and column being processed. Any return value is ignored. Note that row 0
* contains the headers for the data, so test for row==0 to process headers only.
*
* @return a two-dimensional array containing the data, with the first row containing headers
**/
function ImportJSONAdvanced(url, query, options, includeFunc, transformFunc) {
var jsondata = UrlFetchApp.fetch(url);
var object = JSON.parse(jsondata.getContentText());
return parseJSONObject_(object, query, options, includeFunc, transformFunc);
}
/**
* Encodes the given value to use within a URL.
*
* @param {value} the value to be encoded
*
* @return the value encoded using URL percent-encoding
*/
function URLEncode(value) {
return encodeURIComponent(value.toString());
}
/**
* Parses a JSON object and returns a two-dimensional array containing the data of that object.
*/
function parseJSONObject_(object, query, options, includeFunc, transformFunc) {
var headers = new Array();
var data = new Array();
if (query && !Array.isArray(query) && query.toString().indexOf(",") != -1) {
query = query.toString().split(",");
}
if (options) {
options = options.toString().split(",");
}
parseData_(headers, data, "", 1, object, query, options, includeFunc);
parseHeaders_(headers, data);
transformData_(data, options, transformFunc);
return hasOption_(options, "noHeaders") ? (data.length > 1 ? data.slice(1) : new Array()) : data;
}
/**
* Parses the data contained within the given value and inserts it into the data two-dimensional array starting at the rowIndex.
* If the data is to be inserted into a new column, a new header is added to the headers array. The value can be an object,
* array or scalar value.
*
* If the value is an object, it's properties are iterated through and passed back into this function with the name of each
* property extending the path. For instance, if the object contains the property "entry" and the path passed in was "/feed",
* this function is called with the value of the entry property and the path "/feed/entry".
*
* If the value is an array containing other arrays or objects, each element in the array is passed into this function with
* the rowIndex incremeneted for each element.
*
* If the value is an array containing only scalar values, those values are joined together and inserted into the data array as
* a single value.
*
* If the value is a scalar, the value is inserted directly into the data array.
*/
function parseData_(headers, data, path, rowIndex, value, query, options, includeFunc) {
var dataInserted = false;
if (isObject_(value)) {
for (key in value) {
if (parseData_(headers, data, path + "/" + key, rowIndex, value[key], query, options, includeFunc)) {
dataInserted = true;
}
}
} else if (Array.isArray(value) && isObjectArray_(value)) {
for (var i = 0; i < value.length; i++) {
if (parseData_(headers, data, path, rowIndex, value[i], query, options, includeFunc)) {
dataInserted = true;
rowIndex++;
}
}
} else if (!includeFunc || includeFunc(query, path, options)) {
// Handle arrays containing only scalar values
if (Array.isArray(value)) {
value = value.join();
}
// Insert new row if one doesn't already exist
if (!data[rowIndex]) {
data[rowIndex] = new Array();
}
// Add a new header if one doesn't exist
if (!headers[path] && headers[path] != 0) {
headers[path] = Object.keys(headers).length;
}
// Insert the data
data[rowIndex][headers[path]] = value;
dataInserted = true;
}
return dataInserted;
}
/**
* Parses the headers array and inserts it into the first row of the data array.
*/
function parseHeaders_(headers, data) {
data[0] = new Array();
for (key in headers) {
data[0][headers[key]] = key;
}
}
/**
* Applies the transform function for each element in the data array, going through each column of each row.
*/
function transformData_(data, options, transformFunc) {
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j++) {
transformFunc(data, i, j, options);
}
}
}
/**
* Returns true if the given test value is an object; false otherwise.
*/
function isObject_(test) {
return Object.prototype.toString.call(test) === '[object Object]';
}
/**
* Returns true if the given test value is an array containing at least one object; false otherwise.
*/
function isObjectArray_(test) {
for (var i = 0; i < test.length; i++) {
if (isObject_(test[i])) {
return true;
}
}
return false;
}
/**
* Returns true if the given query applies to the given path.
*/
function includeXPath_(query, path, options) {
if (!query) {
return true;
} else if (Array.isArray(query)) {
for (var i = 0; i < query.length; i++) {
if (applyXPathRule_(query[i], path, options)) {
return true;
}
}
} else {
return applyXPathRule_(query, path, options);
}
return false;
};
/**
* Returns true if the rule applies to the given path.
*/
function applyXPathRule_(rule, path, options) {
return path.indexOf(rule) == 0;
}
/**
* By default, this function transforms the value at the given row & column so it looks more like a normal data import. Specifically:
*
* - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values
* of the rows representing their parent elements.
* - Values longer than 256 characters get truncated.
* - Values in row 0 (headers) have slashes converted to spaces, common prefixes removed and the resulting text converted to title
* case.
*
* To change this behavior, pass in one of these values in the options parameter:
*
* noInherit: Don't inherit values from parent elements
* noTruncate: Don't truncate values
* rawHeaders: Don't prettify headers
* debugLocation: Prepend each value with the row & column it belongs in
*/
function defaultTransform_(data, row, column, options) {
if (!data[row][column]) {
if (row < 2 || hasOption_(options, "noInherit")) {
data[row][column] = "";
} else {
data[row][column] = data[row-1][column];
}
}
if (!hasOption_(options, "rawHeaders") && row == 0) {
if (column == 0 && data[row].length > 1) {
removeCommonPrefixes_(data, row);
}
data[row][column] = toTitleCase_(data[row][column].toString().replace(/[\/\_]/g, " "));
}
if (!hasOption_(options, "noTruncate") && data[row][column]) {
data[row][column] = data[row][column].toString().substr(0, 256);
}
if (hasOption_(options, "debugLocation")) {
data[row][column] = "[" + row + "," + column + "]" + data[row][column];
}
}
/**
* If all the values in the given row share the same prefix, remove that prefix.
*/
function removeCommonPrefixes_(data, row) {
var matchIndex = data[row][0].length;
for (var i = 1; i < data[row].length; i++) {
matchIndex = findEqualityEndpoint_(data[row][i-1], data[row][i], matchIndex);
if (matchIndex == 0) {
return;
}
}
for (var i = 0; i < data[row].length; i++) {
data[row][i] = data[row][i].substring(matchIndex, data[row][i].length);
}
}
/**
* Locates the index where the two strings values stop being equal, stopping automatically at the stopAt index.
*/
function findEqualityEndpoint_(string1, string2, stopAt) {
if (!string1 || !string2) {
return -1;
}
var maxEndpoint = Math.min(stopAt, string1.length, string2.length);
for (var i = 0; i < maxEndpoint; i++) {
if (string1.charAt(i) != string2.charAt(i)) {
return i;
}
}
return maxEndpoint;
}
/**
* Converts the text to title case.
*/
function toTitleCase_(text) {
if (text == null) {
return null;
}
return text.replace(/\w\S*/g, function(word) { return word.charAt(0).toUpperCase() + word.substr(1).toLowerCase(); });
}
/**
* Returns true if the given set of options contains the given option.
*/
function hasOption_(options, option) {
return options && options.indexOf(option) >= 0;
}
@manuelfink

This comment has been minimized.

Copy link

@manuelfink manuelfink commented Oct 14, 2014

Hi Paul,

thx for your awesome script,

Just wondering, if I understood your script right. noTruncate keeps old records and is not deleting them? So basically I'm getting an list of records, with the newest at top? Are changed rows updated?

So basically I'm trying to build a small dashboard for facebook post performance pulling in records, categorizing them and than aggregating stats across my own categories.

I'm using it with: =ImportJSON("https://graph.facebook.com/pagename/posts?access_token=xxxx";""; "noInherit, noTruncate").

Is this clever or would you rather run a cron script to check for new posts and another for pulling in performance data for each post?

thx, I really appreciate your work!
Manu

@cesckat

This comment has been minimized.

Copy link

@cesckat cesckat commented Dec 7, 2014

Hi @paulgambill, thanks for sharing the ImportJSON.gs tutorial with us in your site.

I just want to say something in case anyone have problems with it. If you for example write:
=ImportJSON(“http://date.jsontest.com", “/date”, “noInherit, noTruncate”)

Maybe you're going to have problems with Google Spreadsheet (while it's reading the code that you've entered) and that's because of the comma. Try to separate it with ";" instead of "," like that one:
=ImportJSON("http://date.jsontest.com"; "/date"; "noInherit,noTruncate")

Hope to help anyone with problems :)

@patricknowlan

This comment has been minimized.

Copy link

@patricknowlan patricknowlan commented Dec 13, 2014

Thanks Paul. This is a cool script and quick tutorial!

@kenorb

This comment has been minimized.

Copy link

@kenorb kenorb commented Jan 29, 2015

Check out: https://github.com/fastfedora/google-docs for the latest version.

@Randall94

This comment has been minimized.

Copy link

@Randall94 Randall94 commented Jan 28, 2016

Hi Paul,
is there a specific reason why I am getting an "Error - Formula parse error" ? I followed your tutorial, but can't seem to get past this issue.
Thanks for help.

@KatVonKlone

This comment has been minimized.

Copy link

@KatVonKlone KatVonKlone commented Mar 22, 2016

same here Randall94

@olliegilbert

This comment has been minimized.

Copy link

@olliegilbert olliegilbert commented Apr 2, 2016

@Randall94 / @KatVonKlone,
I had the same issue.

Check out @cesckat's answer. It corrects it!

@qm3ster

This comment has been minimized.

Copy link

@qm3ster qm3ster commented Apr 11, 2016

Does anyone know how to access array elements inside the json? ("example.json", "/data/list/0") does not work

@g5codyswartz

This comment has been minimized.

Copy link

@g5codyswartz g5codyswartz commented May 16, 2016

Is there a way to prevent it from crawling into multi-dimensional arrays?
For instance, the object has a property which contains an array. It seems that the array gets crawled out on the last object, I would prefer it to not even crawl into there unless I designated it to do so. I know as stated in the todo/future about here being better querying, but it'd be awesome if it had an option to not crawl children collections.

I'm thinking I will not be able to accomplish what I would like to with this though : (
I have an object with a property of "locations" so my query is "/locations"
but then each location has 3 collections which only expand on the last location; I would rather them not try to expand at all.
Then after having the collections I would want to have a separate query that queries the "locations[i]/pages"

Overall I think the massive object I'm looking at is too complex for this.

@hernandezrivera

This comment has been minimized.

Copy link

@hernandezrivera hernandezrivera commented May 23, 2016

@berndjungfer

This comment has been minimized.

Copy link

@berndjungfer berndjungfer commented Jun 10, 2016

Hi, I was wondering if I could get the score and the pass values
Since I am not a coder I just gave it a go, but the script only gives me "kind", "id" or "responseCode".
I believe its because the script doesnt go to the second array?

can anybody please help?

Thanks a lot!

{
"kind": "pagespeedonline#result",
"id": "http://www.123.de/",
"responseCode": 200,
"ruleGroups": {
"USABILITY": {
"score": 64,
"pass": false
}
}

@flinhardt

This comment has been minimized.

Copy link

@flinhardt flinhardt commented Aug 12, 2016

Hi, somehow the first result of my JSON feed is ignored. The header shows up perfectly, first result does not appear, second to last show up again. Any idea? Thanks, Flo

@sidburgess

This comment has been minimized.

Copy link

@sidburgess sidburgess commented Aug 26, 2016

How best to import if you don't have a list of the paths?

@Leooo

This comment has been minimized.

Copy link

@Leooo Leooo commented Oct 24, 2016

For deeply nested arrays compatibility, use:

function parseData_(headers, data, path, rowIndex, value, query, options, includeFunc) {
       ...
       // line 200
       rowIndex = rowIndex + Math.max(0, (data.length - rowIndex));
      ...
}
@sujeshkumaruv

This comment has been minimized.

Copy link

@sujeshkumaruv sujeshkumaruv commented Nov 15, 2016

How I can post data to a url in this script?

@toochtooch

This comment has been minimized.

Copy link

@toochtooch toochtooch commented Dec 2, 2016

Wow You sir saved me, and I am sure many others a lot of time. Thank you very much for sharing this!

@JHRDM

This comment has been minimized.

Copy link

@JHRDM JHRDM commented Jan 12, 2017

(Using updated fastFedora version) getting this:

"Error
SyntaxError: Unexpected token: < (line 165)."

There are no "<" on line 165 of any of the files associated, unless there is one in Google.

Really looking forward to making this work.

@MikeD123

This comment has been minimized.

Copy link

@MikeD123 MikeD123 commented Jan 17, 2017

Thanks, worked well with the edit you suggested!

@niranjanrao1

This comment has been minimized.

Copy link

@niranjanrao1 niranjanrao1 commented Apr 20, 2017

First of all, thanks to @paulgambill for the work.
I did exactly as the tutorial said, but I'm getting a "#ERROR!" message on the Google sheet. I also tried using semicolons instead of commas as suggested by @cesckat
Appreciate any assistance on this, please. I'm not a developer, but just trying to put some self-updating stock prices on an excel sheet for my grandpa. The prices are in a JSON feed from an API.

@openjck

This comment has been minimized.

Copy link

@openjck openjck commented Jun 14, 2017

These lines can be added at the very bottom of defaultTransform_ (between lines 334 and 335) to make the script return the values as true numbers when appropriate, so that the number formats (Scientific, Accounting, Currency, etc.) work as expected.

  if (parseFloat(data[row][column])) {
    data[row][column] = parseFloat(data[row][column]);
  }

There shouldn't be any harm in adding these lines, so I'd suggest they be considered for the official script.

@Rjrajiv

This comment has been minimized.

Copy link

@Rjrajiv Rjrajiv commented Jun 20, 2017

I am using " =ImportJSON("https://api.example.com/feed/json","/keyword","noInherit, noTruncate") I am importing all data successfully.
But i want only certain header to be called. how to add filter to get only required header?
or is there any way to import only selected row with a fixed header and corresponding column?
Any help would be appreciated.

@rushilakare

This comment has been minimized.

Copy link

@rushilakare rushilakare commented Nov 26, 2017

Hi,
Sorry a complete newb here. My API requires authentication to provide response when hit on its URL, can someone let me know how to pass username and password for the same through this script or maybe directly in the URL.
Thanks in advance for help

@radres

This comment has been minimized.

Copy link

@radres radres commented Nov 30, 2017

I have used this is google sheets yet the imported value does not refresh. I changed the json file in the server, but I have literally no idea how to refresh the value in the cell (other than deleting it and rewriting it). refreshing the page doesn't do it.

@allenyllee

This comment has been minimized.

Copy link

@allenyllee allenyllee commented Nov 30, 2017

Hi, here's my modified version https://gist.github.com/allenyllee/c764c86ed722417948fc256b7a5077c4

Changelog:

  1. Add the ability to query array elements by using xpath like "/array[n]/member" where "n" is array index
  2. Fixed Issue: when the path and the rule partially matched but not under the same xpath, it will still print that value under that xpath.
    For example: when path = /data/asset_longname and rule = /data/asset, it will print both /data/asset_longname and /data/asset rather then just print /data/asset.
    Solution: check the first remaining charater, if it's empty "" or slash "/" the function should return true. The slash "/" means that there are members under the same xpath, so do not remove it.
@zenepay

This comment has been minimized.

Copy link

@zenepay zenepay commented Dec 25, 2017

Need more sample how to parse the 2 dimentional json data.

@tylus9086

This comment has been minimized.

Copy link

@tylus9086 tylus9086 commented Jan 24, 2018

Can anyone help me?

I'm trying to import jsons such as http://poe.ninja/api/Data/GetUniqueWeaponOverview?league=Abyss, but when trying to import a value of 0, such as ("links":0), I instead get the previous value. It means that I end up with lists of 5s and 6s, when they should be zero.

@KrashLeviathan

This comment has been minimized.

Copy link

@KrashLeviathan KrashLeviathan commented May 30, 2018

Nice work! One suggestion: In your Medium article you should put the example code in code format and fix the quotation marks. When copied into Sheets they had to be deleted and re-typed. Minor inconvenience though. Everything else worked great!

@isaque777

This comment has been minimized.

Copy link

@isaque777 isaque777 commented Jul 16, 2018

Thank you, it works like a symphony

@jpiaggio

This comment has been minimized.

Copy link

@jpiaggio jpiaggio commented Aug 18, 2018

Nice Work, the Code is brilliant, I have one issue when trying to import something with the special char " on the URL. Is there any way to tackle this.
Example https://exampleURL/value={"Type": "Bug"}

Thanks!

@ofmarconi

This comment has been minimized.

Copy link

@ofmarconi ofmarconi commented Sep 11, 2018

Does this work with Firebase?

Anyone with any experience?

@ofmarconi

This comment has been minimized.

Copy link

@ofmarconi ofmarconi commented Sep 12, 2018

Hello,
I’m experimenting with the Facebook API and I’m watching a strange behaviour. I have following API-String to get informations for two facebook pages at once):

=ImportJSON(“http://graph.facebook.com/?ids=15765808054,116335138405853″; “”; “noHeaders”))

The result of this call is put in only one lines of my spreadshet although I get results for two pages. The results of the second page is put in the same line after the results of the first line. I expected these data of the second page under the first page in a second line.

The JSON output looks good for me I cannot find any error.

The problem is not with the script, but with the way Facebook is returning multiple objects in the API. Rather than returning the objects as a top-level array, which ImportJSON would correctly parse as multiple rows, Facebook is returning the objects as a key-value map where the key is the id of each object.

I found this post, but no one answers.
bradjasper/ImportJSON#2

Cheers,

@pradeep910

This comment has been minimized.

Copy link

@pradeep910 pradeep910 commented Nov 21, 2018

Thanks for the script @paulgambill. Worked fine for me. :) 👍

@fedex29

This comment has been minimized.

Copy link

@fedex29 fedex29 commented Dec 5, 2018

I have used this is google sheets yet the imported value does not refresh. I changed the json file in the server, but I have literally no idea how to refresh the value in the cell (other than deleting it and rewriting it). refreshing the page doesn't do it.

I have the same question, how do you do to automatically update the data?

@edcfour

This comment has been minimized.

Copy link

@edcfour edcfour commented Dec 5, 2018

I've been trying to get this to work with flight data from http://www.flymsy.com/Flight-info but I'm continually running into "SyntaxError: Unexpected token: < (line 143)." What do?

@mberrueta

This comment has been minimized.

Copy link

@mberrueta mberrueta commented Dec 11, 2018

thanks man!

@nikkoboy

This comment has been minimized.

Copy link

@nikkoboy nikkoboy commented Jan 22, 2019

Great workaround! I'm trying to use this with a url which features an API key and token to returns the JSON code. However I'm getting an error: "Request failed for https://www.mydomain.com/xxx-api/stats/?key=xxxxxxxxxxxxxxxxxxxxxxxxxxxx&token=xxxxxxxxxxxxxxxxxxxxxxxxxxxx returned code 401. Truncated server response: { "error": "You must specify both a token and an API key!" } "
Since both the token and API key are specified (and working), I'm not sure what the problem is. Has anyone a clue of what may cause this issue? This url works since I can access the JSON from a web browser.

@Bazze

This comment has been minimized.

Copy link

@Bazze Bazze commented Jan 31, 2019

If one would want Basic Auth support, one could modify the ImportJSONAdvanced function like this:

function ImportJSONAdvanced(url, query, options, includeFunc, transformFunc) {
  var urlOptions = {};
  
  var basicAuth = url.match(/^https?:\/\/(.*?:.*?)@/);
  if (basicAuth) {
    urlOptions.headers = {
      "Authorization": "Basic " + Utilities.base64Encode(basicAuth[1], Utilities.Charset.UTF_8)
    };
    
    url = url.replace(/^(https?):\/\/.*?:.*?@/, "$1://");
  }
  
  var jsondata = UrlFetchApp.fetch(url, urlOptions);
  var object   = JSON.parse(jsondata.getContentText());
  
  return parseJSONObject_(object, query, options, includeFunc, transformFunc);
}

With this you can put username & password in the url, like http://username:password@date.jsontest.com.

@djmiles73

This comment has been minimized.

Copy link

@djmiles73 djmiles73 commented Feb 2, 2019

I have feeds which are output by page - how could I edit this script so that it pulls all data, not just from the first page?

@CelesteJain

This comment has been minimized.

Copy link

@CelesteJain CelesteJain commented Mar 27, 2019

@JHRDM I have the same problem. Did you find the solution? Thanks in advance

@eswright

This comment has been minimized.

Copy link

@eswright eswright commented Apr 5, 2019

I'm trying this to pull into Sheets with a large json file. Any suggestions to adjust code as in Sheets I get the error "Results too large." ?

@ladyanarki

This comment has been minimized.

Copy link

@ladyanarki ladyanarki commented Apr 22, 2019

I'm getting Error
DNS error: http://coinmarketcap-nexuist.rhcloud.com/api/btc (line 131).
Is there an updated link?

@funykcheff

This comment has been minimized.

Copy link

@funykcheff funykcheff commented Apr 27, 2019

Can please @paulgambill or someone explain or even better provide an example how to send request which uses "POST" and raw data as an input. In my case I have:

Lets say:

@superselector

This comment has been minimized.

Copy link

@superselector superselector commented May 23, 2019

This code doesn't seems to be working with my api link. It gives only half the data. Ideally I should get a list of 20 rows but I am getting only 12. Can someone tell me what mistake I am doing? I am having this url in cell B1 https://cricapi.com/api/fantasySummary?apikey=rrWedCKeclf1GqiY0pCBy05mdd23&unique_id=1152843 and the formula I am using is =importjson(B1,"/data/team/players/name", "noInherit, noTruncate",)

@HedonieBrazil

This comment has been minimized.

Copy link

@HedonieBrazil HedonieBrazil commented Jun 17, 2019

to get the current bitcoin price i used that function instead, don't need import script.

=VALUE(SUBSTITUTE(SUBSTITUTE(INDEX(IMPORTDATA("https://min-api.cryptocompare.com/data/price?fsym=BTC&tsyms=BTC,USD,EUR"),0,2), "USD:",""), """", ""))

@MisterCreosote

This comment has been minimized.

Copy link

@MisterCreosote MisterCreosote commented Jan 3, 2020

I am getting "Result too large" error. Is there a limit to the number of fields/records returned?
Will the google sheet expand columns to allow for more fields?

@mariopattan

This comment has been minimized.

Copy link

@mariopattan mariopattan commented Apr 16, 2020

Hi @paulgambill , I need to add http headers : referer in order to access data from some API.

var options = { 'headers' : { 'referer' : 'origin' } };
I tried a simple script a simple script and it works
var options = { 'headers' : { 'referer' : 'origin' } }; var response = UrlFetchApp.fetch("https://shopee.co.id/api/v2/search_items/?by=pop&limit=10&match_id=42361077&newest=0&order=desc&page_type=shop&version=2", options);

I tried to include it into your code, but it returns error. Do I need to change it into "options2" or you have another solutions? Thanks in advance

@SeoYeonii

This comment has been minimized.

Copy link

@SeoYeonii SeoYeonii commented May 13, 2020

Thank You Paul!!
I REALLY REALLY appreciate your work!!

@AdamSaw

This comment has been minimized.

Copy link

@AdamSaw AdamSaw commented May 14, 2020

Edit: Deleted last post, figured out auth headers and POST

What I cannot figure out is what function to choose to set up a trigger to automate when the importJSON triggers. I know its currently onOpen, but that doesn't seem to work via a trigger. Which function can I use to automate when the tables get updated?

@dev-mansonthomas

This comment has been minimized.

Copy link

@dev-mansonthomas dev-mansonthomas commented May 18, 2020

Hi,

It would be nice to be able to parse the JSON that would be in a cell of the spreadsheet.
It's simple to copy/paste the result from Developper tools, and on the contrary, figuring out the authentication mechanism is hard or not supported.

I've added to your script the following function and it worked like a charm, using cell A1 as a data placeholder :

function parseLocalJSONObject(query, options)
{
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  //Logger.log("First cell content : "+data[0][0]);
  return parseJSONObject_(JSON.parse(data[0][0]),query,options, includeXPath_, defaultTransform_);
}

Thanks a lot :)

edit: an alternative version that takes a cell ref as data placeholder.
there's surerly a better way to get the cell reference working, but I found this for now

function parseLocalJSONObject(reference)
{ 
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i)[1].split('!');
  
  try 
  {
    if (args.length == 1) 
    {
      var range = sheet.getRange(args[0]);
    }
    else 
    {
      sheet = ss.getSheetByName(args[0].replace(/'/g, ''));
      range = sheet.getRange(args[1]);
    }
  }
  catch(e) 
  {
    throw new Error(args.join('!') + ' is not a valid range');
  }
  
  var data = range.getValues();
  
  
  //Logger.log("First cell content : "+data[0][0]);
  return parseJSONObject_(JSON.parse(data[0][0]),query,options, includeXPath_, defaultTransform_);
}
@AdamSaw

This comment has been minimized.

Copy link

@AdamSaw AdamSaw commented May 19, 2020

I have used this is google sheets yet the imported value does not refresh. I changed the json file in the server, but I have literally no idea how to refresh the value in the cell (other than deleting it and rewriting it). refreshing the page doesn't do it.

I have the same question, how do you do to automatically update the data?

Di you ever figure this out? I have the same issue and nothing seems to trigger it that I've tried.

@hishoss

This comment has been minimized.

Copy link

@hishoss hishoss commented May 24, 2020

Hi,

It would be nice to be able to parse the JSON that would be in a cell of the spreadsheet.
It's simple to copy/paste the result from Developper tools, and on the contrary, figuring out the authentication mechanism is hard or not supported.

I've added to your script the following function and it worked like a charm, using cell A1 as a data placeholder :

function parseLocalJSONObject(query, options)
{
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  //Logger.log("First cell content : "+data[0][0]);
  return parseJSONObject_(JSON.parse(data[0][0]),query,options, includeXPath_, defaultTransform_);
}

Thanks a lot :)

edit: an alternative version that takes a cell ref as data placeholder.
there's surerly a better way to get the cell reference working, but I found this for now

function parseLocalJSONObject(reference)
{ 
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i)[1].split('!');
  
  try 
  {
    if (args.length == 1) 
    {
      var range = sheet.getRange(args[0]);
    }
    else 
    {
      sheet = ss.getSheetByName(args[0].replace(/'/g, ''));
      range = sheet.getRange(args[1]);
    }
  }
  catch(e) 
  {
    throw new Error(args.join('!') + ' is not a valid range');
  }
  
  var data = range.getValues();
  
  
  //Logger.log("First cell content : "+data[0][0]);
  return parseJSONObject_(JSON.parse(data[0][0]),query,options, includeXPath_, defaultTransform_);
}

Hi dev-mansonthomas, can you elaborate more on how to use your functions. I haven't been successful with it.

@dev-mansonthomas

This comment has been minimized.

Copy link

@dev-mansonthomas dev-mansonthomas commented May 24, 2020

Hi,
It would be nice to be able to parse the JSON that would be in a cell of the spreadsheet.
It's simple to copy/paste the result from Developper tools, and on the contrary, figuring out the authentication mechanism is hard or not supported.
I've added to your script the following function and it worked like a charm, using cell A1 as a data placeholder :

function parseLocalJSONObject(query, options)
{
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  //Logger.log("First cell content : "+data[0][0]);
  return parseJSONObject_(JSON.parse(data[0][0]),query,options, includeXPath_, defaultTransform_);
}

Thanks a lot :)
edit: an alternative version that takes a cell ref as data placeholder.
there's surerly a better way to get the cell reference working, but I found this for now

function parseLocalJSONObject(reference)
{ 
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i)[1].split('!');
  
  try 
  {
    if (args.length == 1) 
    {
      var range = sheet.getRange(args[0]);
    }
    else 
    {
      sheet = ss.getSheetByName(args[0].replace(/'/g, ''));
      range = sheet.getRange(args[1]);
    }
  }
  catch(e) 
  {
    throw new Error(args.join('!') + ' is not a valid range');
  }
  
  var data = range.getValues();
  
  
  //Logger.log("First cell content : "+data[0][0]);
  return parseJSONObject_(JSON.parse(data[0][0]),query,options, includeXPath_, defaultTransform_);
}

Hi dev-mansonthomas, can you elaborate more on how to use your functions. I haven't been successful with it.

Hi,

I copy the json into a cell, for example B2, and then call the function in another cell (below B2)

=parseLocalJSONObject(B2)

You simply have to copy/paste the code in this gist and add the code of the method "function parseLocalJSONObject(reference)", then you're able to use it.

@hditano

This comment has been minimized.

@eapapp

This comment has been minimized.

Copy link

@eapapp eapapp commented Jun 16, 2020

I ran into the same problem today as @tylus9086, here is the fix.

Can anyone help me?

I'm trying to import jsons such as http://poe.ninja/api/Data/GetUniqueWeaponOverview?league=Abyss, but when trying to import a value of 0, such as ("links":0), I instead get the previous value. It means that I end up with lists of 5s and 6s, when they should be zero.

The problem was that the defaultTransform function was not able to distinguish zero from null, and so the previous values were re-used (zero was not treated as a value). Instead of !data[row][column], check explicitly for null:

function defaultTransform_(data, row, column, options) {
  if (data[row][column] === null) {
    if (row < 2 || hasOption_(options, "noInherit")) { ...
@AndreiPrystupchyk

This comment has been minimized.

@rodrigogalvaopatriota

This comment has been minimized.

Copy link

@rodrigogalvaopatriota rodrigogalvaopatriota commented Jul 2, 2020

Excelent!!! very good!!

@tomazov

This comment has been minimized.

Copy link

@tomazov tomazov commented Jul 7, 2020

Thanks for the script!
Now the script parses json and the result is placed on the cell below the formula ↧
Tell me what you need to do so that the result is saved to the right of the cell from the formula ↦

@cristianbogdan

This comment has been minimized.

Copy link

@cristianbogdan cristianbogdan commented Jul 25, 2020

Thanks for this great script!

I worked with this dataset
https://www.graphs.ro/json.php

I had to use noInherit but then the data became very sparse. It seemed to be just one row but then I realized that I need to scroll down.

In order for the script to work with this hierarchical data I had to apply the patch mentioned by @Leooo above:

For deeply nested arrays compatibility, use:

function parseData_(headers, data, path, rowIndex, value, query, options, includeFunc) {
       ...
       // line 200
       rowIndex = rowIndex + Math.max(0, (data.length - rowIndex));
      ...
}

Also I applied the patch by @eapapp

Thanks again!

@RevathyRamanan123

This comment has been minimized.

Copy link

@RevathyRamanan123 RevathyRamanan123 commented Aug 18, 2020

Thank you very much indeed helpful

@jetsloth

This comment has been minimized.

Copy link

@jetsloth jetsloth commented Aug 20, 2020

Nice one! This looks perfect for our current project. Is there a limit to the number of ImportJSON calls in a single spreadsheet? Am asking because the native IMPORTDATA for example, has a limit of 50.

@IronKnight36

This comment has been minimized.

Copy link

@IronKnight36 IronKnight36 commented Sep 10, 2020

I have used this is google sheets yet the imported value does not refresh. I changed the json file in the server, but I have literally no idea how to refresh the value in the cell (other than deleting it and rewriting it). refreshing the page doesn't do it.

I have the same question, how do you do to automatically update the data?

Di you ever figure this out? I have the same issue and nothing seems to trigger it that I've tried.

To refresh the link programmatically, you have to clear the contents of the cell and reset the value of the cell as the link. You can set this to kick off on open, or create a menu item and run it manually.

Example:

function updateLink {
  var ss = SpreadsheetApp.getActive()
  var sheetData = ss.getSheetByName('SHEET_NAME');
  var sourceValue = '=ImportJSON("URL_TO_JSON")';
  var targetRange = sheetData.getRange('A1');
  targetRange.clearContent();
  targetRange.setValue(sourceValue);
}
@jcorp11

This comment has been minimized.

Copy link

@jcorp11 jcorp11 commented Sep 16, 2020

Hey sorry if this is too noob of a question, the API that Im calling has multiple fields that start with the same characters :, market_cap , market_cap_24hrchange, market_cap_rank. I'm only interested in marketcap but when I use the ImportJson function all 3 are returned because they start from the same root. Is there a way to be more selective ?

@jetsloth

This comment has been minimized.

Copy link

@jetsloth jetsloth commented Sep 16, 2020

@romanoLT

This comment has been minimized.

Copy link

@romanoLT romanoLT commented Sep 16, 2020

Hey, thanks for sharing your script.
I'm trying to collect such json structure:

{
    "api": {
        "results": 1,
        "standings": [
            [
                {
                    "rank": 1,
                    "team_id": 85,
                    "teamName": "Paris Saint Germain",
                    "logo": "https://media.api-sports.io/football/teams/85.png",
                    "group": "Ligue 1",
                    "forme": "DLWLL",
                    "status": "same",
                    "description": "Promotion - Champions League (Group Stage)",
                    "all": {
                        "matchsPlayed": 35,
                        "win": 27,
                        "draw": 4,
                        "lose": 4,
                        "goalsFor": 98,
                        "goalsAgainst": 31
                    },
                    "home": {
                        "matchsPlayed": 18,
                        "win": 16,
                        "draw": 2,
                        "lose": 0,
                        "goalsFor": 59,
                        "goalsAgainst": 10
                    },
                    "away": {
                        "matchsPlayed": 17,
                        "win": 11,
                        "draw": 2,
                        "lose": 4,
                        "goalsFor": 39,
                        "goalsAgainst": 21
                    },
                    "goalsDiff": 67,
                    "points": 85,
                    "lastUpdate": "2019-05-04"
                }
            ]
      [..]...
...
        ]
    }
}
...

however running importjson() gives me something like:

Results | Standings
1 | [object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],...

any idea why?

@mattlgardner

This comment has been minimized.

Copy link

@mattlgardner mattlgardner commented Sep 25, 2020

Hi there! Noob question here but does anyone have an idea as to why I might be getting this Error in google sheet:
"Error
Exception: Invalid argument: https://api.coronavirus.data.gov.uk/v1/data (line 127)."

I added in the script above to the script editor and am then using

=ImportJSON("https://api.coronavirus.data.gov.uk/v1/data
")

to try and pull in the data. Wonder if this is working for anyone else?

@jakemathai

This comment has been minimized.

Copy link

@jakemathai jakemathai commented Oct 8, 2020

Thank you! Just what I needed... +1

@djahngir

This comment has been minimized.

Copy link

@djahngir djahngir commented Oct 28, 2020

I am fairly new to importing JSON or even recognising what format the data is that I am trying to pull, I use this URL (https://www.fantasypros.com/nfl/rankings/dynasty-overall.php)

I used to pull those rankings into Google Sheets using the below formula

=IMPORTHTML("https://www.fantasypros.com/nfl/rankings/dynasty-overall.php", "table", 0)

This is no longer working due to a change in the format of the data, I have asked Dynasty Pros but they have not been very helpful at all

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.