Guest User

Untitled

a guest
Feb 20th, 2017
74
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.80 KB | None | 0 0
  1. // Includes functions for exporting active sheet or all sheets as JSON object (also Python object syntax compatible).
  2. // Tweak the makePrettyJSON_ function to customize what kind of JSON to export.
  3.  
  4. var FORMAT_ONELINE = 'One-line';
  5. var FORMAT_MULTILINE = 'Multi-line';
  6. var FORMAT_PRETTY = 'Pretty';
  7.  
  8. var LANGUAGE_JS = 'JavaScript';
  9. var LANGUAGE_PYTHON = 'Python';
  10.  
  11. var STRUCTURE_LIST = 'List';
  12. var STRUCTURE_HASH = 'Hash (keyed by "id" column)';
  13.  
  14. /* Defaults for this particular spreadsheet, change as desired */
  15. var DEFAULT_FORMAT = FORMAT_PRETTY;
  16. var DEFAULT_LANGUAGE = LANGUAGE_JS;
  17. var DEFAULT_STRUCTURE = STRUCTURE_LIST;
  18.  
  19.  
  20. function onOpen() {
  21. var ss = SpreadsheetApp.getActiveSpreadsheet();
  22. var menuEntries = [
  23. {name: "Export JSON for this sheet", functionName: "exportSheet"},
  24. {name: "Export JSON for all sheets", functionName: "exportAllSheets"},
  25. {name: "Configure export", functionName: "exportOptions"},
  26. ];
  27. ss.addMenu("Export JSON", menuEntries);
  28. }
  29.  
  30.  
  31. function exportOptions() {
  32. var doc = SpreadsheetApp.getActiveSpreadsheet();
  33. var app = UiApp.createApplication().setTitle('Export JSON');
  34.  
  35. var grid = app.createGrid(4, 2);
  36. grid.setWidget(0, 0, makeLabel(app, 'Language:'));
  37. grid.setWidget(0, 1, makeListBox(app, 'language', [LANGUAGE_JS, LANGUAGE_PYTHON]));
  38. grid.setWidget(1, 0, makeLabel(app, 'Format:'));
  39. grid.setWidget(1, 1, makeListBox(app, 'format', [FORMAT_PRETTY, FORMAT_MULTILINE, FORMAT_ONELINE]));
  40. grid.setWidget(2, 0, makeLabel(app, 'Structure:'));
  41. grid.setWidget(2, 1, makeListBox(app, 'structure', [STRUCTURE_LIST, STRUCTURE_HASH]));
  42. grid.setWidget(3, 0, makeButton(app, grid, 'Export Active Sheet', 'exportSheet'));
  43. grid.setWidget(3, 1, makeButton(app, grid, 'Export All Sheets', 'exportAllSheets'));
  44. app.add(grid);
  45.  
  46. doc.show(app);
  47. }
  48.  
  49. function makeLabel(app, text, id) {
  50. var lb = app.createLabel(text);
  51. if (id) lb.setId(id);
  52. return lb;
  53. }
  54.  
  55. function makeListBox(app, name, items) {
  56. var listBox = app.createListBox().setId(name).setName(name);
  57. listBox.setVisibleItemCount(1);
  58.  
  59. var cache = CacheService.getPublicCache();
  60. var selectedValue = cache.get(name);
  61. Logger.log(selectedValue);
  62. for (var i = 0; i < items.length; i++) {
  63. listBox.addItem(items[i]);
  64. if (items[1] == selectedValue) {
  65. listBox.setSelectedIndex(i);
  66. }
  67. }
  68. return listBox;
  69. }
  70.  
  71. function makeButton(app, parent, name, callback) {
  72. var button = app.createButton(name);
  73. app.add(button);
  74. var handler = app.createServerClickHandler(callback).addCallbackElement(parent);;
  75. button.addClickHandler(handler);
  76. return button;
  77. }
  78.  
  79. function makeTextBox(app, name) {
  80. var textArea = app.createTextArea().setWidth('100%').setHeight('200px').setId(name).setName(name);
  81. return textArea;
  82. }
  83.  
  84. function exportAllSheets(e) {
  85.  
  86. var ss = SpreadsheetApp.getActiveSpreadsheet();
  87. var sheets = ss.getSheets();
  88. var sheetsData = {};
  89. for (var i = 0; i < sheets.length; i++) {
  90. var sheet = sheets[i];
  91. var rowsData = getRowsData_(sheet, getExportOptions(e));
  92. var sheetName = sheet.getName();
  93. sheetsData[sheetName] = rowsData;
  94. }
  95. var json = makeJSON_(sheetsData, getExportOptions(e));
  96. return displayText_(json);
  97. }
  98.  
  99. function exportSheet(e) {
  100. var ss = SpreadsheetApp.getActiveSpreadsheet();
  101. var sheet = ss.getActiveSheet();
  102. var rowsData = getRowsData_(sheet, getExportOptions(e));
  103. var json = makeJSON_(rowsData, getExportOptions(e));
  104. return displayText_(json);
  105. }
  106.  
  107. function getExportOptions(e) {
  108. var options = {};
  109.  
  110. options.language = e && e.parameter.language || DEFAULT_LANGUAGE;
  111. options.format = e && e.parameter.format || DEFAULT_FORMAT;
  112. options.structure = e && e.parameter.structure || DEFAULT_STRUCTURE;
  113.  
  114. var cache = CacheService.getPublicCache();
  115. cache.put('language', options.language);
  116. cache.put('format', options.format);
  117. cache.put('structure', options.structure);
  118.  
  119. Logger.log(options);
  120. return options;
  121. }
  122.  
  123. function makeJSON_(object, options) {
  124. if (options.format == FORMAT_PRETTY) {
  125. var jsonString = JSON.stringify(object, null, 4);
  126. } else if (options.format == FORMAT_MULTILINE) {
  127. var jsonString = Utilities.jsonStringify(object);
  128. jsonString = jsonString.replace(/},/gi, '},\n');
  129. jsonString = prettyJSON.replace(/":\[{"/gi, '":\n[{"');
  130. jsonString = prettyJSON.replace(/}\],/gi, '}],\n');
  131. } else {
  132. var jsonString = Utilities.jsonStringify(object);
  133. }
  134. if (options.language == LANGUAGE_PYTHON) {
  135. // add unicode markers
  136. jsonString = jsonString.replace(/"([a-zA-Z]*)":\s+"/gi, '"$1": u"');
  137. }
  138. return jsonString;
  139. }
  140.  
  141. function displayText_(text) {
  142. var app = UiApp.createApplication().setTitle('Exported JSON');
  143. app.add(makeTextBox(app, 'json'));
  144. app.getElementById('json').setText(text);
  145. var ss = SpreadsheetApp.getActiveSpreadsheet();
  146. ss.show(app);
  147. return app;
  148. }
  149.  
  150. // getRowsData iterates row by row in the input range and returns an array of objects.
  151. // Each object contains all the data for a given row, indexed by its normalized column name.
  152. // Arguments:
  153. // - sheet: the sheet object that contains the data to be processed
  154. // - range: the exact range of cells where the data is stored
  155. // - columnHeadersRowIndex: specifies the row number where the column names are stored.
  156. // This argument is optional and it defaults to the row immediately above range;
  157. // Returns an Array of objects.
  158. function getRowsData_(sheet, options) {
  159. var headersRange = sheet.getRange(1, 1, sheet.getFrozenRows(), sheet.getMaxColumns());
  160. var headers = headersRange.getValues()[0];
  161. var dataRange = sheet.getRange(sheet.getFrozenRows()+1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
  162. var objects = getObjects_(dataRange.getValues(), normalizeHeaders_(headers));
  163. if (options.structure == STRUCTURE_HASH) {
  164. var objectsById = {};
  165. objects.forEach(function(object) {
  166. objectsById[object.id] = object;
  167. });
  168. return objectsById;
  169. } else {
  170. return objects;
  171. }
  172. }
  173.  
  174. // getColumnsData iterates column by column in the input range and returns an array of objects.
  175. // Each object contains all the data for a given column, indexed by its normalized row name.
  176. // Arguments:
  177. // - sheet: the sheet object that contains the data to be processed
  178. // - range: the exact range of cells where the data is stored
  179. // - rowHeadersColumnIndex: specifies the column number where the row names are stored.
  180. // This argument is optional and it defaults to the column immediately left of the range;
  181. // Returns an Array of objects.
  182. function getColumnsData_(sheet, range, rowHeadersColumnIndex) {
  183. rowHeadersColumnIndex = rowHeadersColumnIndex || range.getColumnIndex() - 1;
  184. var headersTmp = sheet.getRange(range.getRow(), rowHeadersColumnIndex, range.getNumRows(), 1).getValues();
  185. var headers = normalizeHeaders_(arrayTranspose_(headersTmp)[0]);
  186. return getObjects(arrayTranspose_(range.getValues()), headers);
  187. }
  188.  
  189.  
  190. // For every row of data in data, generates an object that contains the data. Names of
  191. // object fields are defined in keys.
  192. // Arguments:
  193. // - data: JavaScript 2d array
  194. // - keys: Array of Strings that define the property names for the objects to create
  195. function getObjects_(data, keys) {
  196. var objects = [];
  197. for (var i = 0; i < data.length; ++i) {
  198. var object = {};
  199. var hasData = false;
  200. for (var j = 0; j < data[i].length; ++j) {
  201. var cellData = data[i][j];
  202. if (isCellEmpty_(cellData)) {
  203. continue;
  204. }
  205. object[keys[j]] = cellData;
  206. hasData = true;
  207. }
  208. if (hasData) {
  209. objects.push(object);
  210. }
  211. }
  212. return objects;
  213. }
  214.  
  215. // Returns an Array of normalized Strings.
  216. // Arguments:
  217. // - headers: Array of Strings to normalize
  218. function normalizeHeaders_(headers) {
  219. var keys = [];
  220. for (var i = 0; i < headers.length; ++i) {
  221. var key = normalizeHeader_(headers[i]);
  222. if (key.length > 0) {
  223. keys.push(key);
  224. }
  225. }
  226. return keys;
  227. }
  228.  
  229. // Normalizes a string, by removing all alphanumeric characters and using mixed case
  230. // to separate words. The output will always start with a lower case letter.
  231. // This function is designed to produce JavaScript object property names.
  232. // Arguments:
  233. // - header: string to normalize
  234. // Examples:
  235. // "First Name" -> "firstName"
  236. // "Market Cap (millions) -> "marketCapMillions
  237. // "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
  238. function normalizeHeader_(header) {
  239. var key = "";
  240. var upperCase = false;
  241. for (var i = 0; i < header.length; ++i) {
  242. var letter = header[i];
  243. if (letter == " " && key.length > 0) {
  244. upperCase = true;
  245. continue;
  246. }
  247. if (!isAlnum_(letter)) {
  248. continue;
  249. }
  250. if (key.length == 0 && isDigit_(letter)) {
  251. continue; // first character must be a letter
  252. }
  253. if (upperCase) {
  254. upperCase = false;
  255. key += letter.toUpperCase();
  256. } else {
  257. key += letter.toLowerCase();
  258. }
  259. }
  260. return key;
  261. }
  262.  
  263. // Returns true if the cell where cellData was read from is empty.
  264. // Arguments:
  265. // - cellData: string
  266. function isCellEmpty_(cellData) {
  267. return typeof(cellData) == "string" && cellData == "";
  268. }
  269.  
  270. // Returns true if the character char is alphabetical, false otherwise.
  271. function isAlnum_(char) {
  272. return char >= 'A' && char <= 'Z' ||
  273. char >= 'a' && char <= 'z' ||
  274. isDigit_(char);
  275. }
  276.  
  277. // Returns true if the character char is a digit, false otherwise.
  278. function isDigit_(char) {
  279. return char >= '0' && char <= '9';
  280. }
  281.  
  282. // Given a JavaScript 2d Array, this function returns the transposed table.
  283. // Arguments:
  284. // - data: JavaScript 2d Array
  285. // Returns a JavaScript 2d Array
  286. // Example: arrayTranspose([[1,2,3],[4,5,6]]) returns [[1,4],[2,5],[3,6]].
  287. function arrayTranspose_(data) {
  288. if (data.length == 0 || data[0].length == 0) {
  289. return null;
  290. }
  291.  
  292. var ret = [];
  293. for (var i = 0; i < data[0].length; ++i) {
  294. ret.push([]);
  295. }
  296.  
  297. for (var i = 0; i < data.length; ++i) {
  298. for (var j = 0; j < data[i].length; ++j) {
  299. ret[j][i] = data[i][j];
  300. }
  301. }
  302.  
  303. return ret;
  304. }
Add Comment
Please, Sign In to add comment