import_json_appsscript.js

/**
 * 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;
}

Hi there! Script works like a charm!
Could you please help me with newbie questions...

1. I am having multiple URLS for JSON import, when I try to use the script it says: Array result was not expanded because it would overwrite data.
2. Some JSONs have multiple values, is there a way to put all the data in one cell (not 5)

Thanks in advance!

Hi, any idea on how to update the cell contents automatically? Only way I can do it is to delete the contents of the cell and enter the formula again. Any way to set a trigger or something similar?

How can i get this data where the selected text is wild

thanks! How would I modify this to import from a file stored locally, instead of an api endpoint? (noob btw!)

Noob here, just trying to understand APIs... I want to basiclly get the result of this api call into a Google Sheet using this url:

https://api.coronavirus.data.gov.uk/v1/data?filters=areaType=nation;areaName=wales&structure={"date":"date","areaName":"areaName","areaCode":"areaCode","newPeopleVaccinatedFirstDoseByPublishDate":"newPeopleVaccinatedFirstDoseByPublishDate","newPeopleVaccinatedSecondDoseByPublishDate":"newPeopleVaccinatedSecondDoseByPublishDate"}

...but alas, I just get 'Error formula parse error' when I use the =importJSON formula and paste in the above - any idea what I'm doing wrong?

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 :)

Thx

Hello.
Pre-Warning: I don't know what I am really doing but copy & pasting magnets and using the result with my excel/sheets wisdom.

I set up a test sheet with this script, and it worked wonderful. I could get api data and organize according to my needs.
I then created a duplicate and began the proper work. All worked as wished.

But this morning when opening the project file I the cell with the JSON commands remain in "Loading..." state ("Error Loading Data" when hovering above them)
I just doublechecked the old test sheet, and it still works there. I can even copy and paste pages from the now no more loading sheet in to the old test one and it loads successfully there.

I didn't really change anything in the Apps Script, when I open that one via Tools I can still see the pasted code. I only worked with sheet commands to shift around the api generated data to my needs. So why did it possibly stop working? Any hints?

Hi Paul,

Thanks for your wonderful script,

I Just wondering I got an error message when I ran your JSON script.

I keep getting the following error 75% of the time (the rest of the time it works great):
"Error SyntaxError: Unexpected token: < (line 132)".

I believe this is because the url I'm fetching goes to an html page for half a second before continuing to the JSON. How can I account for this?

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 ↦

Try like this =ImportJSON("http://date.jsontest.com", "/date", "noHeaders")

@JHRDM @edcfour @kchamb1999

Regarding: "Error SyntaxError: Unexpected token: < ..."
This error is generated when the string returned by the site is HTML instead of JSON. I have encountered this error under the following conditions:

1. An error page was returned by the site instead of a JSON string. For example "Forbidden" when authentication was required and none was given.
2. The path was not found and a 404 was generated.
3. if the JSON is wrapped in HTML for some reason
4. if a redirect was used such that an HTML "Redirecting.." message was displayed before being forwarded to the JSON page.

To troubleshoot your specific issue I suggest using a tool like Postmaster to first verify that what the ImportJSON function is getting as input from the site is actually JSON.

Hope that helps,
Susanne

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.

THIS SHOULD BE THE ANSWER!!! WORKS WITH ARRAYS!

Hi there! This is super helpful and works very well in google sheets. I had a noob question that I am not able to figure out. I tried googling it but couldn't find an answer. I am using this with Google Sheets to pull in data from Keepa. By default this truncates to 256 chars and I am wondering if there is a way to increase the limit to a customer number e.g. 1000 chars or 1200 chars? If I use noTRuncate, it brings more than 50K chars for a particular column that I need so I cannot use noTruncate. I need to get data for a particular columns for up to 1000 chars and not able to figure out how to set the limit to 1000.

I don't know coding so piecing things together slowly and appreciate any help.

Very useful! Thanks!

Greetings!
Guys, help me figure it out, I did not find information ...
When the script is requested, it returns data in one line, it looks like this (applied the TRANSPOSE formula to make it easier to watch, and marked where the columns should end):

With a similar query, the data is displayed normally, as it should be, in columns and rows:

The data comes in this form:

1. Which is on the first screen
   

2. Which is on the second screen, as it should be.
   

I roughly understand what the problem might be, but, unfortunately, I cannot solve it, I do not have enough knowledge. Maybe someone has already encountered a similar problem and knows its solution, I ask for help ...

Thanks for sharing this, @paulgambill! I found the following issue with the script:

The JSON

[
    { "id": 1, "items": [ { "itemId": 11 }, { "itemId": 12 } ] },
    { "id": 2, "items": [ { "itemId": 21 }, { "itemId": 22 } ] }
]

is imported as

/id	/items/itemId
1	11
2	21
2	22

with itemId 12 missing.

I'm posting this here in case anyone has time to investigate this, especially those requiring the script to work reliably for their use case. Or maybe there's already a fork where this has been fixed, which could be pulled in to this gist.

The same issue is also present in @allenyllee's fork.

Hello. I try to make a binance wallet data stats. But i need to refresh many API request. Is here any posibility to do that for whole sheet ?

I love this script, thank you so much for posting it! I have run into an issue though where only part of the data is being populated. There is a field in the data that can contain multiple options, but those options are not being separated out into separate rows.

"outcomes":[{"name":"Buffalo Bills","price":2.12},{"name":"Kansas City Chiefs","price":1.79}]

Any ideas how to get both of these outcomes to populate in separate rows?

Thanks!!!

Hello friends

I've been using this script for 2 years and yesterday (02/03/2022), it stopped working! I've done several tests, but it doesn't work, it just displays this message "Loading" (print).

Can you help me please #

Hello friends

I've been using this script for 2 years and yesterday (02/03/2022), it stopped working! I've done several tests, but it doesn't work, it just displays this message "Loading" (print).

Can you help me please #

Same for me since today, please could you help ?

Same for me since yesterday and solved opening the GSheet from GDrive instead clicking in a saved bookmark. It may sound a bit simple but I found everything working fine when opening my sheet from App Script > Executions > Open container (three vertical dots), and realized the URL was different, so I tried to open it from GDrive and it works perfectly now.
BTW, in Executions I have every execution with status Completed.

I had the same issue last evening, but when I reopened my sheet this morning it worked again.
I have no technical answer, but it seems to me there were some connection issues yesterday.
If you look above, I had the same trouble for a day or two last year. And it ressolved without doing anything.

Same issue here, script has been working for months and now it just stopped working. I have tried what @frettor suggested without success.

guys, it started working again by itself without making any adjustments to the codes!!!

wait that yours should also work again

now working anymore, it does this from time to time, and now it doesn't fix anymore (i usually waited for it to fix by it self, or had to delete and paste again)

Same problem here!

Same issue here, script has been working for months and now it just stopped working. I have tried what @frettor suggested without success.

On Friday 11/3 the issue solved by itself, now its been several days working fine. I did nothing.

I have a problème he makes an error every time.

How can fix that ?

Hello guys!
First of all thanks for this great work, I really appreciate it! The script is working fine for me, but I need your help. I just need the data to be automatically transposed, please see the screenshot.
Thanks.