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