-
-
Save chrislkeller/5719258 to your computer and use it in GitHub Desktop.
/** | |
* 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; | |
} |
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
I see that the data imports into the sheet I want but if I have another cell with say "=MAX(A10:A20) and my data is pulled into A10 through A20, Max will return "0" no matter what data is in the cells. Any ideas of why this is?
@DavidARivkin the problem is that the resulting values from importjson are Strings, you can use something like this:
=MAX(arrayformula(value(A10:A20)))
FYI, I've made an add-on called ImportJSON. I didn't know about this script at that time which is pretty cool too.
However the add-on is more powerful:
1- It smartly pivots a dimension to make a table whenever it can
2- It accepts cUrl requests or even JSON files stored on Google Drive
3- Filters are more flexible
4- It caches results so it doesn't fetch the API at any change in the spreadsheet (which is useful when you use APIs that have quotas)
5- No AppsScript. Just install and use it!
It's available here
https://gsuite.google.com/marketplace/app/importjson_json_to_table_from_any_source/782573720506
Thanks so much for the excellent script.
Any chance you have a copy of this for MS Excel (2019)?
@fred915 ... I have not tried, and haven't come back to this in quite some time....
One of these solutions might work?
https://support.microsoft.com/en-us/office/import-data-from-external-data-sources-power-query-be4330b3-5356-486c-a168-b68e9e616f5a
I can't see it clear
Update: some API's need header submitted with URL which carries the API key. This can be done in "params" of UrlFetchApp.fetch(url, params). https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app
I was able to modify code adding a new "params" header for each API key, and then rename each function to =ImportJSON1(), =ImportJSON2(), etc.
Is there any way to add the location of a data element (e.g. [2,0]) to the end of the query? I am using the following API (https://commonchemistry.cas.org/api/detail?cas_rn=112-30-1) and want to get a specific element of a JSON array. The data I can get with =IMPORTJSON(C15,"/experimentalProperties/property","noInherit,noTruncate,noHeaders,debugLocation") looks like this:
[1,0]232.9 °C @ Press: 760 Torr
[2,0]6.4 °C
[3,0]0.8297 g/cm³ @ Temp: 20 °C
Hi, here's my modified version https://gist.github.com/allenyllee/c764c86ed722417948fc256b7a5077c4
Changelog:
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.