API tools faq
paste
rmalcoriza

importJSON.gs

rmalcoriza
Jun 15th, 2018
184
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 26.15 KB | None | 0 0
raw download clone embed print report
  1. /*====================================================================================================================================*
  2. ImportJSON by Brad Jasper and Trevor Lohrbeer
  3. ====================================================================================================================================
  4. Version: 1.4.0
  5. Project Page: https://github.com/bradjasper/ImportJSON
  6. Copyright: (c) 2017 by Brad Jasper
  7. (c) 2012-2017 by Trevor Lohrbeer
  8. License: GNU General Public License, version 3 (GPL-3.0)
  9. http://www.opensource.org/licenses/gpl-3.0.html
  10. ------------------------------------------------------------------------------------------------------------------------------------
  11. A library for importing JSON feeds into Google spreadsheets. Functions include:
  12.  
  13. ImportJSON For use by end users to import a JSON feed from a URL
  14. ImportJSONFromSheet For use by end users to import JSON from one of the Sheets
  15. ImportJSONViaPost For use by end users to import a JSON feed from a URL using POST parameters
  16. ImportJSONAdvanced For use by script developers to easily extend the functionality of this library
  17. ImportJSONBasicAuth For use by end users to import a JSON feed from a URL with HTTP Basic Auth (added by Karsten Lettow)
  18.  
  19. For future enhancements see https://github.com/bradjasper/ImportJSON/issues?q=is%3Aissue+is%3Aopen+label%3Aenhancement
  20.  
  21. For bug reports see https://github.com/bradjasper/ImportJSON/issues
  22.  
  23. ------------------------------------------------------------------------------------------------------------------------------------
  24. Changelog:
  25.  
  26. 1.4.0 (July 23, 2017) Transfer project to Brad Jasper. Fixed off-by-one array bug. Fixed previous value bug. Added custom annotations. Added ImportJSONFromSheet and ImportJSONBasicAuth.
  27. 1.3.0 Adds ability to import the text from a set of rows containing the text to parse. All cells are concatenated
  28. 1.2.1 Fixed a bug with how nested arrays are handled. The rowIndex counter wasn't incrementing properly when parsing.
  29. 1.2.0 Added ImportJSONViaPost and support for fetchOptions to ImportJSONAdvanced
  30. 1.1.1 Added a version number using Google Scripts Versioning so other developers can use the library
  31. 1.1.0 Added support for the noHeaders option
  32. 1.0.0 Initial release
  33. *====================================================================================================================================*/
  34.  
  35. /**
  36. * Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create
  37. * a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in
  38. * the JSON feed. The remaining rows contain the data.
  39. *
  40. * By default, data gets transformed so it looks more like a normal data import. Specifically:
  41. *
  42. * - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values
  43. * of the rows representing their parent elements.
  44. * - Values longer than 256 characters get truncated.
  45. * - Headers have slashes converted to spaces, common prefixes removed and the resulting text converted to title case.
  46. *
  47. * To change this behavior, pass in one of these values in the options parameter:
  48. *
  49. * noInherit: Don't inherit values from parent elements
  50. * noTruncate: Don't truncate values
  51. * rawHeaders: Don't prettify headers
  52. * noHeaders: Don't include headers, only the data
  53. * debugLocation: Prepend each value with the row & column it belongs in
  54. *
  55. * For example:
  56. *
  57. * =ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json", "/feed/entry/title,/feed/entry/content",
  58. * "noInherit,noTruncate,rawHeaders")
  59. *
  60. * @param {url} the URL to a public JSON feed
  61. * @param {query} a comma-separated list of paths to import. Any path starting with one of these paths gets imported.
  62. * @param {parseOptions} a comma-separated list of options that alter processing of the data
  63. * @customfunction
  64. *
  65. * @return a two-dimensional array containing the data, with the first row containing headers
  66. **/
  67. function ImportJSON(url, query, parseOptions) {
  68. return ImportJSONAdvanced(url, null, query, parseOptions, includeXPath_, defaultTransform_);
  69. }
  70.  
  71. /**
  72. * Imports a JSON feed via a POST request and returns the results to be inserted into a Google Spreadsheet. The JSON feed is
  73. * flattened to create a two-dimensional array. The first row contains the headers, with each column header indicating the path to
  74. * that data in the JSON feed. The remaining rows contain the data.
  75. *
  76. * To retrieve the JSON, a POST request is sent to the URL and the payload is passed as the content of the request using the content
  77. * type "application/x-www-form-urlencoded". If the fetchOptions define a value for "method", "payload" or "contentType", these
  78. * values will take precedent. For example, advanced users can use this to make this function pass XML as the payload using a GET
  79. * request and a content type of "application/xml; charset=utf-8". For more information on the available fetch options, see
  80. * https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app . At this time the "headers" option is not supported.
  81. *
  82. * By default, the returned data gets transformed so it looks more like a normal data import. Specifically:
  83. *
  84. * - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values
  85. * of the rows representing their parent elements.
  86. * - Values longer than 256 characters get truncated.
  87. * - Headers have slashes converted to spaces, common prefixes removed and the resulting text converted to title case.
  88. *
  89. * To change this behavior, pass in one of these values in the options parameter:
  90. *
  91. * noInherit: Don't inherit values from parent elements
  92. * noTruncate: Don't truncate values
  93. * rawHeaders: Don't prettify headers
  94. * noHeaders: Don't include headers, only the data
  95. * debugLocation: Prepend each value with the row & column it belongs in
  96. *
  97. * For example:
  98. *
  99. * =ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json", "user=bob&apikey=xxxx",
  100. * "validateHttpsCertificates=false", "/feed/entry/title,/feed/entry/content", "noInherit,noTruncate,rawHeaders")
  101. *
  102. * @param {url} the URL to a public JSON feed
  103. * @param {payload} the content to pass with the POST request; usually a URL encoded list of parameters separated by ampersands
  104. * @param {fetchOptions} a comma-separated list of options used to retrieve the JSON feed from the URL
  105. * @param {query} a comma-separated list of paths to import. Any path starting with one of these paths gets imported.
  106. * @param {parseOptions} a comma-separated list of options that alter processing of the data
  107. * @customfunction
  108. *
  109. * @return a two-dimensional array containing the data, with the first row containing headers
  110. **/
  111. function ImportJSONViaPost(url, payload, fetchOptions, query, parseOptions) {
  112. var postOptions = parseToObject_(fetchOptions);
  113.  
  114. if (postOptions["method"] == null) {
  115. postOptions["method"] = "POST";
  116. }
  117.  
  118. if (postOptions["payload"] == null) {
  119. postOptions["payload"] = payload;
  120. }
  121.  
  122. if (postOptions["contentType"] == null) {
  123. postOptions["contentType"] = "application/x-www-form-urlencoded";
  124. }
  125.  
  126. convertToBool_(postOptions, "validateHttpsCertificates");
  127. convertToBool_(postOptions, "useIntranet");
  128. convertToBool_(postOptions, "followRedirects");
  129. convertToBool_(postOptions, "muteHttpExceptions");
  130.  
  131. return ImportJSONAdvanced(url, postOptions, query, parseOptions, includeXPath_, defaultTransform_);
  132. }
  133.  
  134. /**
  135. * Imports a JSON text from a named Sheet and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create
  136. * a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in
  137. * the JSON feed. The remaining rows contain the data.
  138. *
  139. * By default, data gets transformed so it looks more like a normal data import. Specifically:
  140. *
  141. * - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values
  142. * of the rows representing their parent elements.
  143. * - Values longer than 256 characters get truncated.
  144. * - Headers have slashes converted to spaces, common prefixes removed and the resulting text converted to title case.
  145. *
  146. * To change this behavior, pass in one of these values in the options parameter:
  147. *
  148. * noInherit: Don't inherit values from parent elements
  149. * noTruncate: Don't truncate values
  150. * rawHeaders: Don't prettify headers
  151. * noHeaders: Don't include headers, only the data
  152. * debugLocation: Prepend each value with the row & column it belongs in
  153. *
  154. * For example:
  155. *
  156. * =ImportJSONFromSheet("Source", "/feed/entry/title,/feed/entry/content",
  157. * "noInherit,noTruncate,rawHeaders")
  158. *
  159. * @param {sheetName} the name of the sheet containg the text for the JSON
  160. * @param {query} a comma-separated lists of paths to import. Any path starting with one of these paths gets imported.
  161. * @param {options} a comma-separated list of options that alter processing of the data
  162. *
  163. * @return a two-dimensional array containing the data, with the first row containing headers
  164. * @customfunction
  165. **/
  166. function ImportJSONFromSheet(sheetName, query, options) {
  167.  
  168. var object = getDataFromNamedSheet_(sheetName);
  169.  
  170. return parseJSONObject_(object, query, options, includeXPath_, defaultTransform_);
  171. }
  172.  
  173.  
  174. /**
  175. * An advanced version of ImportJSON designed to be easily extended by a script. This version cannot be called from within a
  176. * spreadsheet.
  177. *
  178. * Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create
  179. * a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in
  180. * the JSON feed. The remaining rows contain the data.
  181. *
  182. * The fetchOptions can be used to change how the JSON feed is retrieved. For instance, the "method" and "payload" options can be
  183. * set to pass a POST request with post parameters. For more information on the available parameters, see
  184. * https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app .
  185. *
  186. * Use the include and transformation functions to determine what to include in the import and how to transform the data after it is
  187. * imported.
  188. *
  189. * For example:
  190. *
  191. * ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json",
  192. * new Object() { "method" : "post", "payload" : "user=bob&apikey=xxxx" },
  193. * "/feed/entry",
  194. * "",
  195. * function (query, path) { return path.indexOf(query) == 0; },
  196. * function (data, row, column) { data[row][column] = data[row][column].toString().substr(0, 100); } )
  197. *
  198. * In this example, the import function checks to see if the path to the data being imported starts with the query. The transform
  199. * function takes the data and truncates it. For more robust versions of these functions, see the internal code of this library.
  200. *
  201. * @param {url} the URL to a public JSON feed
  202. * @param {fetchOptions} an object whose properties are options used to retrieve the JSON feed from the URL
  203. * @param {query} the query passed to the include function
  204. * @param {parseOptions} a comma-separated list of options that may alter processing of the data
  205. * @param {includeFunc} a function with the signature func(query, path, options) that returns true if the data element at the given path
  206. * should be included or false otherwise.
  207. * @param {transformFunc} a function with the signature func(data, row, column, options) where data is a 2-dimensional array of the data
  208. * and row & column are the current row and column being processed. Any return value is ignored. Note that row 0
  209. * contains the headers for the data, so test for row==0 to process headers only.
  210. *
  211. * @return a two-dimensional array containing the data, with the first row containing headers
  212. * @customfunction
  213. **/
  214. function ImportJSONAdvanced(url, fetchOptions, query, parseOptions, includeFunc, transformFunc) {
  215. var jsondata = UrlFetchApp.fetch(url, fetchOptions);
  216. var object = JSON.parse(jsondata.getContentText());
  217.  
  218. return parseJSONObject_(object, query, parseOptions, includeFunc, transformFunc);
  219. }
  220.  
  221. /**
  222. * Helper function to authenticate with basic auth informations using ImportJSONAdvanced
  223. *
  224. * Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create
  225. * a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in
  226. * the JSON feed. The remaining rows contain the data.
  227. *
  228. * The fetchOptions can be used to change how the JSON feed is retrieved. For instance, the "method" and "payload" options can be
  229. * set to pass a POST request with post parameters. For more information on the available parameters, see
  230. * https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app .
  231. *
  232. * Use the include and transformation functions to determine what to include in the import and how to transform the data after it is
  233. * imported.
  234. *
  235. * @param {url} the URL to a http basic auth protected JSON feed
  236. * @param {username} the Username for authentication
  237. * @param {password} the Password for authentication
  238. * @param {query} the query passed to the include function (optional)
  239. * @param {parseOptions} a comma-separated list of options that may alter processing of the data (optional)
  240. *
  241. * @return a two-dimensional array containing the data, with the first row containing headers
  242. * @customfunction
  243. **/
  244. function ImportJSONBasicAuth(url, username, password, query, parseOptions) {
  245. var encodedAuthInformation = Utilities.base64Encode(username + ":" + password);
  246. var header = {headers: {Authorization: "Basic " + encodedAuthInformation}};
  247. return ImportJSONAdvanced(url, header, query, parseOptions, includeXPath_, defaultTransform_);
  248. }
  249.  
  250. /**
  251. * Encodes the given value to use within a URL.
  252. *
  253. * @param {value} the value to be encoded
  254. *
  255. * @return the value encoded using URL percent-encoding
  256. */
  257. function URLEncode(value) {
  258. return encodeURIComponent(value.toString());
  259. }
  260.  
  261. /**
  262. * Adds an oAuth service using the given name and the list of properties.
  263. *
  264. * @note This method is an experiment in trying to figure out how to add an oAuth service without having to specify it on each
  265. * ImportJSON call. The idea was to call this method in the first cell of a spreadsheet, and then use ImportJSON in other
  266. * cells. This didn't work, but leaving this in here for further experimentation later.
  267. *
  268. * The test I did was to add the following into the A1:
  269. *
  270. * =AddOAuthService("twitter", "https://api.twitter.com/oauth/access_token",
  271. * "https://api.twitter.com/oauth/request_token", "https://api.twitter.com/oauth/authorize",
  272. * "<my consumer key>", "<my consumer secret>", "", "")
  273. *
  274. * Information on obtaining a consumer key & secret for Twitter can be found at https://dev.twitter.com/docs/auth/using-oauth
  275. *
  276. * Then I added the following into A2:
  277. *
  278. * =ImportJSONViaPost("https://api.twitter.com/1.1/statuses/user_timeline.json?screen_name=fastfedora&count=2", "",
  279. * "oAuthServiceName=twitter,oAuthUseToken=always", "/", "")
  280. *
  281. * I received an error that the "oAuthServiceName" was not a valid value. [twl 18.Apr.13]
  282. */
  283. function AddOAuthService__(name, accessTokenUrl, requestTokenUrl, authorizationUrl, consumerKey, consumerSecret, method, paramLocation) {
  284. var oAuthConfig = UrlFetchApp.addOAuthService(name);
  285.  
  286. if (accessTokenUrl != null && accessTokenUrl.length > 0) {
  287. oAuthConfig.setAccessTokenUrl(accessTokenUrl);
  288. }
  289.  
  290. if (requestTokenUrl != null && requestTokenUrl.length > 0) {
  291. oAuthConfig.setRequestTokenUrl(requestTokenUrl);
  292. }
  293.  
  294. if (authorizationUrl != null && authorizationUrl.length > 0) {
  295. oAuthConfig.setAuthorizationUrl(authorizationUrl);
  296. }
  297.  
  298. if (consumerKey != null && consumerKey.length > 0) {
  299. oAuthConfig.setConsumerKey(consumerKey);
  300. }
  301.  
  302. if (consumerSecret != null && consumerSecret.length > 0) {
  303. oAuthConfig.setConsumerSecret(consumerSecret);
  304. }
  305.  
  306. if (method != null && method.length > 0) {
  307. oAuthConfig.setMethod(method);
  308. }
  309.  
  310. if (paramLocation != null && paramLocation.length > 0) {
  311. oAuthConfig.setParamLocation(paramLocation);
  312. }
  313. }
  314.  
  315. /**
  316. * Parses a JSON object and returns a two-dimensional array containing the data of that object.
  317. */
  318. function parseJSONObject_(object, query, options, includeFunc, transformFunc) {
  319. var headers = new Array();
  320. var data = new Array();
  321.  
  322. if (query && !Array.isArray(query) && query.toString().indexOf(",") != -1) {
  323. query = query.toString().split(",");
  324. }
  325.  
  326. if (options) {
  327. options = options.toString().split(",");
  328. }
  329.  
  330. parseData_(headers, data, "", {rowIndex: 1}, object, query, options, includeFunc);
  331. parseHeaders_(headers, data);
  332. transformData_(data, options, transformFunc);
  333.  
  334. return hasOption_(options, "noHeaders") ? (data.length > 1 ? data.slice(1) : new Array()) : data;
  335. }
  336.  
  337. /**
  338. * Parses the data contained within the given value and inserts it into the data two-dimensional array starting at the rowIndex.
  339. * 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,
  340. * array or scalar value.
  341. *
  342. * If the value is an object, it's properties are iterated through and passed back into this function with the name of each
  343. * property extending the path. For instance, if the object contains the property "entry" and the path passed in was "/feed",
  344. * this function is called with the value of the entry property and the path "/feed/entry".
  345. *
  346. * If the value is an array containing other arrays or objects, each element in the array is passed into this function with
  347. * the rowIndex incremeneted for each element.
  348. *
  349. * If the value is an array containing only scalar values, those values are joined together and inserted into the data array as
  350. * a single value.
  351. *
  352. * If the value is a scalar, the value is inserted directly into the data array.
  353. */
  354. function parseData_(headers, data, path, state, value, query, options, includeFunc) {
  355. var dataInserted = false;
  356.  
  357. if (Array.isArray(value) && isObjectArray_(value)) {
  358. for (var i = 0; i < value.length; i++) {
  359. if (parseData_(headers, data, path, state, value[i], query, options, includeFunc)) {
  360. dataInserted = true;
  361.  
  362. if (data[state.rowIndex]) {
  363. state.rowIndex++;
  364. }
  365. }
  366. }
  367. } else if (isObject_(value)) {
  368. for (key in value) {
  369. if (parseData_(headers, data, path + "/" + key, state, value[key], query, options, includeFunc)) {
  370. dataInserted = true;
  371. }
  372. }
  373. } else if (!includeFunc || includeFunc(query, path, options)) {
  374. // Handle arrays containing only scalar values
  375. if (Array.isArray(value)) {
  376. value = value.join();
  377. }
  378.  
  379. // Insert new row if one doesn't already exist
  380. if (!data[state.rowIndex]) {
  381. data[state.rowIndex] = new Array();
  382. }
  383.  
  384. // Add a new header if one doesn't exist
  385. if (!headers[path] && headers[path] != 0) {
  386. headers[path] = Object.keys(headers).length;
  387. }
  388.  
  389. // Insert the data
  390. data[state.rowIndex][headers[path]] = value;
  391. dataInserted = true;
  392. }
  393.  
  394. return dataInserted;
  395. }
  396.  
  397. /**
  398. * Parses the headers array and inserts it into the first row of the data array.
  399. */
  400. function parseHeaders_(headers, data) {
  401. data[0] = new Array();
  402.  
  403. for (key in headers) {
  404. data[0][headers[key]] = key;
  405. }
  406. }
  407.  
  408. /**
  409. * Applies the transform function for each element in the data array, going through each column of each row.
  410. */
  411. function transformData_(data, options, transformFunc) {
  412. for (var i = 0; i < data.length; i++) {
  413. for (var j = 0; j < data[i].length; j++) {
  414. transformFunc(data, i, j, options);
  415. }
  416. }
  417. }
  418.  
  419. /**
  420. * Returns true if the given test value is an object; false otherwise.
  421. */
  422. function isObject_(test) {
  423. return Object.prototype.toString.call(test) === '[object Object]';
  424. }
  425.  
  426. /**
  427. * Returns true if the given test value is an array containing at least one object; false otherwise.
  428. */
  429. function isObjectArray_(test) {
  430. for (var i = 0; i < test.length; i++) {
  431. if (isObject_(test[i])) {
  432. return true;
  433. }
  434. }
  435.  
  436. return false;
  437. }
  438.  
  439. /**
  440. * Returns true if the given query applies to the given path.
  441. */
  442. function includeXPath_(query, path, options) {
  443. if (!query) {
  444. return true;
  445. } else if (Array.isArray(query)) {
  446. for (var i = 0; i < query.length; i++) {
  447. if (applyXPathRule_(query[i], path, options)) {
  448. return true;
  449. }
  450. }
  451. } else {
  452. return applyXPathRule_(query, path, options);
  453. }
  454.  
  455. return false;
  456. };
  457.  
  458. /**
  459. * Returns true if the rule applies to the given path.
  460. */
  461. function applyXPathRule_(rule, path, options) {
  462. return path.indexOf(rule) == 0;
  463. }
  464.  
  465. /**
  466. * By default, this function transforms the value at the given row & column so it looks more like a normal data import. Specifically:
  467. *
  468. * - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values
  469. * of the rows representing their parent elements.
  470. * - Values longer than 256 characters get truncated.
  471. * - Values in row 0 (headers) have slashes converted to spaces, common prefixes removed and the resulting text converted to title
  472. * case.
  473. *
  474. * To change this behavior, pass in one of these values in the options parameter:
  475. *
  476. * noInherit: Don't inherit values from parent elements
  477. * noTruncate: Don't truncate values
  478. * rawHeaders: Don't prettify headers
  479. * debugLocation: Prepend each value with the row & column it belongs in
  480. */
  481. function defaultTransform_(data, row, column, options) {
  482. if (data[row][column] == null) {
  483. if (row < 2 || hasOption_(options, "noInherit")) {
  484. data[row][column] = "";
  485. } else {
  486. data[row][column] = data[row-1][column];
  487. }
  488. }
  489.  
  490. if (!hasOption_(options, "rawHeaders") && row == 0) {
  491. if (column == 0 && data[row].length > 1) {
  492. removeCommonPrefixes_(data, row);
  493. }
  494.  
  495. data[row][column] = toTitleCase_(data[row][column].toString().replace(/[\/\_]/g, " "));
  496. }
  497.  
  498. if (!hasOption_(options, "noTruncate") && data[row][column]) {
  499. data[row][column] = data[row][column].toString().substr(0, 256);
  500. }
  501.  
  502. if (hasOption_(options, "debugLocation")) {
  503. data[row][column] = "[" + row + "," + column + "]" + data[row][column];
  504. }
  505. }
  506.  
  507. /**
  508. * If all the values in the given row share the same prefix, remove that prefix.
  509. */
  510. function removeCommonPrefixes_(data, row) {
  511. var matchIndex = data[row][0].length;
  512.  
  513. for (var i = 1; i < data[row].length; i++) {
  514. matchIndex = findEqualityEndpoint_(data[row][i-1], data[row][i], matchIndex);
  515.  
  516. if (matchIndex == 0) {
  517. return;
  518. }
  519. }
  520.  
  521. for (var i = 0; i < data[row].length; i++) {
  522. data[row][i] = data[row][i].substring(matchIndex, data[row][i].length);
  523. }
  524. }
  525.  
  526. /**
  527. * Locates the index where the two strings values stop being equal, stopping automatically at the stopAt index.
  528. */
  529. function findEqualityEndpoint_(string1, string2, stopAt) {
  530. if (!string1 || !string2) {
  531. return -1;
  532. }
  533.  
  534. var maxEndpoint = Math.min(stopAt, string1.length, string2.length);
  535.  
  536. for (var i = 0; i < maxEndpoint; i++) {
  537. if (string1.charAt(i) != string2.charAt(i)) {
  538. return i;
  539. }
  540. }
  541.  
  542. return maxEndpoint;
  543. }
  544.  
  545.  
  546. /**
  547. * Converts the text to title case.
  548. */
  549. function toTitleCase_(text) {
  550. if (text == null) {
  551. return null;
  552. }
  553.  
  554. return text.replace(/\w\S*/g, function(word) { return word.charAt(0).toUpperCase() + word.substr(1).toLowerCase(); });
  555. }
  556.  
  557. /**
  558. * Returns true if the given set of options contains the given option.
  559. */
  560. function hasOption_(options, option) {
  561. return options && options.indexOf(option) >= 0;
  562. }
  563.  
  564. /**
  565. * Parses the given string into an object, trimming any leading or trailing spaces from the keys.
  566. */
  567. function parseToObject_(text) {
  568. var map = new Object();
  569. var entries = (text != null && text.trim().length > 0) ? text.toString().split(",") : new Array();
  570.  
  571. for (var i = 0; i < entries.length; i++) {
  572. addToMap_(map, entries[i]);
  573. }
  574.  
  575. return map;
  576. }
  577.  
  578. /**
  579. * Parses the given entry and adds it to the given map, trimming any leading or trailing spaces from the key.
  580. */
  581. function addToMap_(map, entry) {
  582. var equalsIndex = entry.indexOf("=");
  583. var key = (equalsIndex != -1) ? entry.substring(0, equalsIndex) : entry;
  584. var value = (key.length + 1 < entry.length) ? entry.substring(key.length + 1) : "";
  585.  
  586. map[key.trim()] = value;
  587. }
  588.  
  589. /**
  590. * Returns the given value as a boolean.
  591. */
  592. function toBool_(value) {
  593. return value == null ? false : (value.toString().toLowerCase() == "true" ? true : false);
  594. }
  595.  
  596. /**
  597. * Converts the value for the given key in the given map to a bool.
  598. */
  599. function convertToBool_(map, key) {
  600. if (map[key] != null) {
  601. map[key] = toBool_(map[key]);
  602. }
  603. }
  604.  
  605. function getDataFromNamedSheet_(sheetName) {
  606. var ss = SpreadsheetApp.getActiveSpreadsheet();
  607. var source = ss.getSheetByName(sheetName);
  608.  
  609. var jsonRange = source.getRange(1,1,source.getLastRow());
  610. var jsonValues = jsonRange.getValues();
  611.  
  612. var jsonText = "";
  613. for (var row in jsonValues) {
  614. for (var col in jsonValues[row]) {
  615. jsonText +=jsonValues[row][col];
  616. }
  617. }
  618. Logger.log(jsonText);
  619. return JSON.parse(jsonText);
  620. }
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!