Developer_Bastian

Import JSON into Google Sheet

Oct 24th, 2023
103
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
JavaScript 14.66 KB | Source Code | 0 0
  1. /**
  2.  * Retrieves all the rows in the active spreadsheet that contain data and logs the
  3.  * values for each row.
  4.  * For more information on using the Spreadsheet API, see
  5.  * https://developers.google.com/apps-script/service_spreadsheet
  6.  */
  7. function readRows() {
  8.   var sheet = SpreadsheetApp.getActiveSheet();
  9.   var rows = sheet.getDataRange();
  10.   var numRows = rows.getNumRows();
  11.   var values = rows.getValues();
  12.  
  13.   for (var i = 0; i <= numRows - 1; i++) {
  14.     var row = values[i];
  15.     Logger.log(row);
  16.   }
  17. };
  18.  
  19. /**
  20.  * Adds a custom menu to the active spreadsheet, containing a single menu item
  21.  * for invoking the readRows() function specified above.
  22.  * The onOpen() function, when defined, is automatically invoked whenever the
  23.  * spreadsheet is opened.
  24.  * For more information on using the Spreadsheet API, see
  25.  * https://developers.google.com/apps-script/service_spreadsheet
  26.  */
  27. function onOpen() {
  28.   var sheet = SpreadsheetApp.getActiveSpreadsheet();
  29.   var entries = [{
  30.     name : "Read Data",
  31.     functionName : "readRows"
  32.   }];
  33.   sheet.addMenu("Script Center Menu", entries);
  34. };
  35.  
  36. /*====================================================================================================================================*
  37.   ImportJSON by Trevor Lohrbeer (@FastFedora)
  38.   ====================================================================================================================================
  39.   Version:      1.1
  40.   Project Page: http://blog.fastfedora.com/projects/import-json
  41.   Copyright:    (c) 2012 by Trevor Lohrbeer
  42.   License:      GNU General Public License, version 3 (GPL-3.0)
  43.                 http://www.opensource.org/licenses/gpl-3.0.html
  44.   ------------------------------------------------------------------------------------------------------------------------------------
  45.   A library for importing JSON feeds into Google spreadsheets. Functions include:
  46.  
  47.      ImportJSON            For use by end users to import a JSON feed from a URL
  48.      ImportJSONAdvanced    For use by script developers to easily extend the functionality of this library
  49.  
  50.   Future enhancements may include:
  51.  
  52.    - Support for a real XPath like syntax similar to ImportXML for the query parameter
  53.    - Support for OAuth authenticated APIs
  54.  
  55.   Or feel free to write these and add on to the library yourself!
  56.   ------------------------------------------------------------------------------------------------------------------------------------
  57.   Changelog:
  58.  
  59.   1.1    Added support for the noHeaders option
  60.   1.0    Initial release
  61.  *====================================================================================================================================*/
  62. /**
  63.  * Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create
  64.  * a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in
  65.  * the JSON feed. The remaining rows contain the data.
  66.  *
  67.  * By default, data gets transformed so it looks more like a normal data import. Specifically:
  68.  *
  69.  *   - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values
  70.  *      of the rows representing their parent elements.
  71.  *   - Values longer than 256 characters get truncated.
  72.  *   - Headers have slashes converted to spaces, common prefixes removed and the resulting text converted to title case.
  73.  *
  74.  * To change this behavior, pass in one of these values in the options parameter:
  75.  *
  76.  *    noInherit:     Don't inherit values from parent elements
  77.  *    noTruncate:    Don't truncate values
  78.  *    rawHeaders:    Don't prettify headers
  79.  *    noHeaders:     Don't include headers, only the data
  80.  *    debugLocation: Prepend each value with the row & column it belongs in
  81.  *
  82.  * For example:
  83.  *
  84.  *   =ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json", "/feed/entry/title,/feed/entry/content",
  85.  *               "noInherit,noTruncate,rawHeaders")
  86.  *
  87.  * @param {url} the URL to a public JSON feed
  88.  * @param {query} a comma-separated lists of paths to import. Any path starting with one of these paths gets imported.
  89.  * @param {options} a comma-separated list of options that alter processing of the data
  90.  *
  91.  * @return a two-dimensional array containing the data, with the first row containing headers
  92.  * @customfunction
  93.  **/
  94. function ImportJSON(url, query, options) {
  95.   return ImportJSONAdvanced(url, query, options, includeXPath_, defaultTransform_);
  96. }
  97.  
  98. /**
  99.  * An advanced version of ImportJSON designed to be easily extended by a script. This version cannot be called from within a
  100.  * spreadsheet.
  101.  *
  102.  * Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create
  103.  * a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in
  104.  * the JSON feed. The remaining rows contain the data.
  105.  *
  106.  * Use the include and transformation functions to determine what to include in the import and how to transform the data after it is
  107.  * imported.
  108.  *
  109.  * For example:
  110.  *
  111.  *   =ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json",
  112.  *               "/feed/entry",
  113.  *                function (query, path) { return path.indexOf(query) == 0; },
  114.  *                function (data, row, column) { data[row][column] = data[row][column].toString().substr(0, 100); } )
  115.  *
  116.  * In this example, the import function checks to see if the path to the data being imported starts with the query. The transform
  117.  * function takes the data and truncates it. For more robust versions of these functions, see the internal code of this library.
  118.  *
  119.  * @param {url}           the URL to a public JSON feed
  120.  * @param {query}         the query passed to the include function
  121.  * @param {options}       a comma-separated list of options that may alter processing of the data
  122.  * @param {includeFunc}   a function with the signature func(query, path, options) that returns true if the data element at the given path
  123.  *                        should be included or false otherwise.
  124.  * @param {transformFunc} a function with the signature func(data, row, column, options) where data is a 2-dimensional array of the data
  125.  *                        and row & column are the current row and column being processed. Any return value is ignored. Note that row 0
  126.  *                        contains the headers for the data, so test for row==0 to process headers only.
  127.  *
  128.  * @return a two-dimensional array containing the data, with the first row containing headers
  129.  **/
  130. function ImportJSONAdvanced(url, query, options, includeFunc, transformFunc) {
  131.   var jsondata = UrlFetchApp.fetch(url);
  132.   var object   = JSON.parse(jsondata.getContentText());
  133.  
  134.   return parseJSONObject_(object, query, options, includeFunc, transformFunc);
  135. }
  136.  
  137. /**
  138.  * Encodes the given value to use within a URL.
  139.  *
  140.  * @param {value} the value to be encoded
  141.  *
  142.  * @return the value encoded using URL percent-encoding
  143.  */
  144. function URLEncode(value) {
  145.   return encodeURIComponent(value.toString());  
  146. }
  147.  
  148. /**
  149.  * Parses a JSON object and returns a two-dimensional array containing the data of that object.
  150.  */
  151. function parseJSONObject_(object, query, options, includeFunc, transformFunc) {
  152.   var headers = new Array();
  153.   var data    = new Array();
  154.  
  155.   if (query && !Array.isArray(query) && query.toString().indexOf(",") != -1) {
  156.     query = query.toString().split(",");
  157.   }
  158.  
  159.   if (options) {
  160.     options = options.toString().split(",");
  161.   }
  162.    
  163.   parseData_(headers, data, "", 1, object, query, options, includeFunc);
  164.   parseHeaders_(headers, data);
  165.   transformData_(data, options, transformFunc);
  166.  
  167.   return hasOption_(options, "noHeaders") ? (data.length > 1 ? data.slice(1) : new Array()) : data;
  168. }
  169.  
  170. /**
  171.  * Parses the data contained within the given value and inserts it into the data two-dimensional array starting at the rowIndex.
  172.  * 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,
  173.  * array or scalar value.
  174.  *
  175.  * If the value is an object, it's properties are iterated through and passed back into this function with the name of each
  176.  * property extending the path. For instance, if the object contains the property "entry" and the path passed in was "/feed",
  177.  * this function is called with the value of the entry property and the path "/feed/entry".
  178.  *
  179.  * If the value is an array containing other arrays or objects, each element in the array is passed into this function with
  180.  * the rowIndex incremeneted for each element.
  181.  *
  182.  * If the value is an array containing only scalar values, those values are joined together and inserted into the data array as
  183.  * a single value.
  184.  *
  185.  * If the value is a scalar, the value is inserted directly into the data array.
  186.  */
  187. function parseData_(headers, data, path, rowIndex, value, query, options, includeFunc) {
  188.   var dataInserted = false;
  189.  
  190.   if (isObject_(value)) {
  191.     for (key in value) {
  192.       if (parseData_(headers, data, path + "/" + key, rowIndex, value[key], query, options, includeFunc)) {
  193.         dataInserted = true;
  194.       }
  195.     }
  196.   } else if (Array.isArray(value) && isObjectArray_(value)) {
  197.     for (var i = 0; i < value.length; i++) {
  198.       if (parseData_(headers, data, path, rowIndex, value[i], query, options, includeFunc)) {
  199.         dataInserted = true;
  200.         rowIndex++;
  201.       }
  202.     }
  203.   } else if (!includeFunc || includeFunc(query, path, options)) {
  204.     // Handle arrays containing only scalar values
  205.     if (Array.isArray(value)) {
  206.       value = value.join();
  207.     }
  208.    
  209.     // Insert new row if one doesn't already exist
  210.     if (!data[rowIndex]) {
  211.       data[rowIndex] = new Array();
  212.     }
  213.    
  214.     // Add a new header if one doesn't exist
  215.     if (!headers[path] && headers[path] != 0) {
  216.       headers[path] = Object.keys(headers).length;
  217.     }
  218.    
  219.     // Insert the data
  220.     data[rowIndex][headers[path]] = value;
  221.     dataInserted = true;
  222.   }
  223.  
  224.   return dataInserted;
  225. }
  226.  
  227. /**
  228.  * Parses the headers array and inserts it into the first row of the data array.
  229.  */
  230. function parseHeaders_(headers, data) {
  231.   data[0] = new Array();
  232.  
  233.   for (key in headers) {
  234.     data[0][headers[key]] = key;
  235.   }
  236. }
  237.  
  238. /**
  239.  * Applies the transform function for each element in the data array, going through each column of each row.
  240.  */
  241. function transformData_(data, options, transformFunc) {
  242.   for (var i = 0; i < data.length; i++) {
  243.     for (var j = 0; j < data[i].length; j++) {
  244.       transformFunc(data, i, j, options);
  245.     }
  246.   }
  247. }
  248.  
  249. /**
  250.  * Returns true if the given test value is an object; false otherwise.
  251.  */
  252. function isObject_(test) {
  253.   return Object.prototype.toString.call(test) === '[object Object]';
  254. }
  255.  
  256. /**
  257.  * Returns true if the given test value is an array containing at least one object; false otherwise.
  258.  */
  259. function isObjectArray_(test) {
  260.   for (var i = 0; i < test.length; i++) {
  261.     if (isObject_(test[i])) {
  262.       return true;
  263.     }
  264.   }  
  265.  
  266.   return false;
  267. }
  268.  
  269. /**
  270.  * Returns true if the given query applies to the given path.
  271.  */
  272. function includeXPath_(query, path, options) {
  273.   if (!query) {
  274.     return true;
  275.   } else if (Array.isArray(query)) {
  276.     for (var i = 0; i < query.length; i++) {
  277.       if (applyXPathRule_(query[i], path, options)) {
  278.         return true;
  279.       }
  280.     }  
  281.   } else {
  282.     return applyXPathRule_(query, path, options);
  283.   }
  284.  
  285.   return false;
  286. };
  287.  
  288. /**
  289.  * Returns true if the rule applies to the given path.
  290.  */
  291. function applyXPathRule_(rule, path, options) {
  292.   return path.indexOf(rule) == 0;
  293. }
  294.  
  295. /**
  296.  * By default, this function transforms the value at the given row & column so it looks more like a normal data import. Specifically:
  297.  *
  298.  *   - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values
  299.  *     of the rows representing their parent elements.
  300.  *   - Values longer than 256 characters get truncated.
  301.  *   - Values in row 0 (headers) have slashes converted to spaces, common prefixes removed and the resulting text converted to title
  302. *      case.
  303.  *
  304.  * To change this behavior, pass in one of these values in the options parameter:
  305.  *
  306.  *    noInherit:     Don't inherit values from parent elements
  307.  *    noTruncate:    Don't truncate values
  308.  *    rawHeaders:    Don't prettify headers
  309.  *    debugLocation: Prepend each value with the row & column it belongs in
  310.  */
  311. function defaultTransform_(data, row, column, options) {
  312.   if (!data[row][column]) {
  313.     if (row < 2 || hasOption_(options, "noInherit")) {
  314.       data[row][column] = "";
  315.     } else {
  316.       data[row][column] = data[row-1][column];
  317.     }
  318.   }
  319.  
  320.   if (!hasOption_(options, "rawHeaders") && row == 0) {
  321.     if (column == 0 && data[row].length > 1) {
  322.       removeCommonPrefixes_(data, row);  
  323.     }
  324.    
  325.     data[row][column] = toTitleCase_(data[row][column].toString().replace(/[\/\_]/g, " "));
  326.   }
  327.  
  328.   if (!hasOption_(options, "noTruncate") && data[row][column]) {
  329.     data[row][column] = data[row][column].toString().substr(0, 256);
  330.   }
  331.  
  332.   if (hasOption_(options, "debugLocation")) {
  333.     data[row][column] = "[" + row + "," + column + "]" + data[row][column];
  334.   }
  335. }
  336.  
  337. /**
  338.  * If all the values in the given row share the same prefix, remove that prefix.
  339.  */
  340. function removeCommonPrefixes_(data, row) {
  341.   var matchIndex = data[row][0].length;
  342.  
  343.   for (var i = 1; i < data[row].length; i++) {
  344.     matchIndex = findEqualityEndpoint_(data[row][i-1], data[row][i], matchIndex);
  345.  
  346.     if (matchIndex == 0) {
  347.       return;
  348.     }
  349.   }
  350.  
  351.   for (var i = 0; i < data[row].length; i++) {
  352.     data[row][i] = data[row][i].substring(matchIndex, data[row][i].length);
  353.   }
  354. }
  355.  
  356. /**
  357.  * Locates the index where the two strings values stop being equal, stopping automatically at the stopAt index.
  358.  */
  359. function findEqualityEndpoint_(string1, string2, stopAt) {
  360.   if (!string1 || !string2) {
  361.     return -1;
  362.   }
  363.  
  364.   var maxEndpoint = Math.min(stopAt, string1.length, string2.length);
  365.  
  366.   for (var i = 0; i < maxEndpoint; i++) {
  367.     if (string1.charAt(i) != string2.charAt(i)) {
  368.       return i;
  369.     }
  370.   }
  371.  
  372.   return maxEndpoint;
  373. }
  374.  
  375.  
  376. /**
  377.  * Converts the text to title case.
  378.  */
  379. function toTitleCase_(text) {
  380.   if (text == null) {
  381.     return null;
  382.   }
  383.  
  384.   return text.replace(/\w\S*/g, function(word) { return word.charAt(0).toUpperCase() + word.substr(1).toLowerCase(); });
  385. }
  386.  
  387. /**
  388.  * Returns true if the given set of options contains the given option.
  389.  */
  390. function hasOption_(options, option) {
  391.   return options && options.indexOf(option) >= 0;
  392. }
Add Comment
Please, Sign In to add comment