/** | |
* 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 | |
**/ | |
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; | |
} |
This comment has been minimized.
Show comment
Hide comment
This comment has been minimized.
Show comment Hide comment
davelalande
commented
Jan 4, 2014
Fantastic... Works like a champ, first time out. It took <5 minutes to setup a new sheet, create the script (copy and paste) and import 514 from a URL =IMPORTJSON("https://myURL.com/directory.json/") Thanks so much for the excellent script. |
This comment has been minimized.
Show comment
Hide comment
This comment has been minimized.
Show comment Hide comment
paulgambill
Mar 14, 2014
Love this script, thank you!
I wrote a quick tutorial on on exactly how to use this in a Google Spreadsheet: https://medium.com/p/a3fede1a014a
paulgambill
commented
Mar 14, 2014
Love this script, thank you! I wrote a quick tutorial on on exactly how to use this in a Google Spreadsheet: https://medium.com/p/a3fede1a014a |
This comment has been minimized.
Show comment
Hide comment
This comment has been minimized.
Show comment Hide comment
kimaernoudt
Jan 20, 2015
Hi, thanks for your work.
Could someone please help me with this:
- what is the refresh rate of the data ?
- how would i let this script refresh the data say once a day ?
thanks!
kimaernoudt
commented
Jan 20, 2015
Hi, thanks for your work.
|
This comment has been minimized.
Show comment
Hide comment
This comment has been minimized.
Show comment Hide comment
kenorb
commented
Jan 29, 2015
Check out: https://github.com/fastfedora/google-docs for the latest version. |
This comment has been minimized.
Show comment
Hide comment
This comment has been minimized.
Show comment Hide comment
mngisguy
commented
Jun 24, 2016
How about GeoJSON? |
This comment has been minimized.
Show comment
Hide comment
This comment has been minimized.
Show comment Hide comment
Rjrajiv
May 23, 2017
If we want o filter these output by header, how can we do this? I am successfully pulling api data and the script is returning with unwanted data too. I want to filter out these by header. kindly help me on this.
Rjrajiv
commented
May 23, 2017
If we want o filter these output by header, how can we do this? I am successfully pulling api data and the script is returning with unwanted data too. I want to filter out these by header. kindly help me on this. |
This comment has been minimized.
Show comment
Hide comment
This comment has been minimized.
Show comment Hide comment
crishernandezmaps
Jun 30, 2017
Great Job! I'm designing a workshops for no coders about using public data stores using their APIs and your script is very useful. Thanks!
crishernandezmaps
commented
Jun 30, 2017
Great Job! I'm designing a workshops for no coders about using public data stores using their APIs and your script is very useful. Thanks! |
This comment has been minimized.
Show comment
Hide comment
This comment has been minimized.
Show comment Hide comment
steverob1066
Jul 7, 2017
This is great -thank you - but can I use the function to pull in a URL from another cell? I have a list of URLs in columns, and would like to use the cell address in the function. It doesn't seem to work, so I guess the answer is no? (I used concatenate to create cells with the URLs in quotes marks)
steverob1066
commented
Jul 7, 2017
This is great -thank you - but can I use the function to pull in a URL from another cell? I have a list of URLs in columns, and would like to use the cell address in the function. It doesn't seem to work, so I guess the answer is no? (I used concatenate to create cells with the URLs in quotes marks) |
This comment has been minimized.
Show comment
Hide comment
This comment has been minimized.
Show comment Hide comment
scbert
Sep 5, 2017
@Steve the below works for me, i guess you could shorten it to the "https" if you wanted to
=ImportJSON("https://ad-api.yoururl.net/api/v1/stats/charts/"&A17)
scbert
commented
Sep 5, 2017
@Steve the below works for me, i guess you could shorten it to the "https" if you wanted to |
This comment has been minimized.
Show comment
Hide comment
This comment has been minimized.
Show comment Hide comment
allenyllee
Nov 30, 2017
Hi, here's my modified version https://gist.github.com/allenyllee/c764c86ed722417948fc256b7a5077c4
Changelog:
- Add the ability to query array elements by using xpath like "/array[n]/member" where "n" is array index
- 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.
allenyllee
commented
Nov 30, 2017
Hi, here's my modified version https://gist.github.com/allenyllee/c764c86ed722417948fc256b7a5077c4 Changelog:
|
This comment has been minimized.
Show comment
Hide comment
This comment has been minimized.
Show comment Hide comment
tordans
Feb 4, 2018
Current version at https://github.com/bradjasper/ImportJSON
JFYI, if you are looking for the current version, its over at https://github.com/bradjasper/ImportJSON
tordans
commented
Feb 4, 2018
Current version at https://github.com/bradjasper/ImportJSONJFYI, if you are looking for the current version, its over at https://github.com/bradjasper/ImportJSON |
Fantastic... Works like a champ, first time out. It took <5 minutes to setup a new sheet, create the script (copy and paste) and import 514 from a URL =IMPORTJSON("https://myURL.com/directory.json/")
Thanks so much for the excellent script.