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