Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- // Includes functions for exporting active sheet or all sheets as JSON object (also Python object syntax compatible).
- // Tweak the makePrettyJSON_ function to customize what kind of JSON to export.
- var FORMAT_ONELINE = 'One-line';
- var FORMAT_MULTILINE = 'Multi-line';
- var FORMAT_PRETTY = 'Pretty';
- var LANGUAGE_JS = 'JavaScript';
- var LANGUAGE_PYTHON = 'Python';
- var STRUCTURE_LIST = 'List';
- var STRUCTURE_HASH = 'Hash (keyed by "id" column)';
- /* Defaults for this particular spreadsheet, change as desired */
- var DEFAULT_FORMAT = FORMAT_PRETTY;
- var DEFAULT_LANGUAGE = LANGUAGE_JS;
- var DEFAULT_STRUCTURE = STRUCTURE_LIST;
- /* Numero de fila (empezando en 0) donde se encuentran los encabezados expresados en formato camel case */
- var FILA_ENCABEZADOS_CAMEL_CASE = 1;
- function onOpen() {
- var ss = SpreadsheetApp.getActiveSpreadsheet();
- var menuEntries = [
- {name: "Export JSON for this sheet", functionName: "exportSheet"},
- {name: "Export JSON for all sheets", functionName: "exportAllSheets"},
- {name: "Configure export", functionName: "exportOptions"},
- ];
- ss.addMenu("Export JSON", menuEntries);
- }
- function exportOptions() {
- var doc = SpreadsheetApp.getActiveSpreadsheet();
- var app = UiApp.createApplication().setTitle('Export JSON');
- var grid = app.createGrid(4, 2);
- grid.setWidget(0, 0, makeLabel(app, 'Language:'));
- grid.setWidget(0, 1, makeListBox(app, 'language', [LANGUAGE_JS, LANGUAGE_PYTHON]));
- grid.setWidget(1, 0, makeLabel(app, 'Format:'));
- grid.setWidget(1, 1, makeListBox(app, 'format', [FORMAT_PRETTY, FORMAT_MULTILINE, FORMAT_ONELINE]));
- grid.setWidget(2, 0, makeLabel(app, 'Structure:'));
- grid.setWidget(2, 1, makeListBox(app, 'structure', [STRUCTURE_LIST, STRUCTURE_HASH]));
- grid.setWidget(3, 0, makeButton(app, grid, 'Export Active Sheet', 'exportSheet'));
- grid.setWidget(3, 1, makeButton(app, grid, 'Export All Sheets', 'exportAllSheets'));
- app.add(grid);
- doc.show(app);
- }
- function makeLabel(app, text, id) {
- var lb = app.createLabel(text);
- if (id) lb.setId(id);
- return lb;
- }
- function makeListBox(app, name, items) {
- var listBox = app.createListBox().setId(name).setName(name);
- listBox.setVisibleItemCount(1);
- var cache = CacheService.getPublicCache();
- var selectedValue = cache.get(name);
- Logger.log(selectedValue);
- for (var i = 0; i < items.length; i++) {
- listBox.addItem(items[i]);
- if (items[1] == selectedValue) {
- listBox.setSelectedIndex(i);
- }
- }
- return listBox;
- }
- function makeButton(app, parent, name, callback) {
- var button = app.createButton(name);
- app.add(button);
- var handler = app.createServerClickHandler(callback).addCallbackElement(parent);;
- button.addClickHandler(handler);
- return button;
- }
- function makeTextBox(app, name) {
- var textArea = app.createTextArea().setWidth('100%').setHeight('200px').setId(name).setName(name);
- return textArea;
- }
- function exportAllSheets(e) {
- var ss = SpreadsheetApp.getActiveSpreadsheet();
- var sheets = ss.getSheets();
- var sheetsData = {};
- for (var i = 0; i < sheets.length; i++) {
- var sheet = sheets[i];
- var rowsData = getRowsData_(sheet, getExportOptions(e));
- var sheetName = sheet.getName();
- sheetsData[sheetName] = rowsData;
- }
- var json = makeJSON_(sheetsData, getExportOptions(e));
- return displayText_(json);
- }
- function exportSheet(e) {
- var ss = SpreadsheetApp.getActiveSpreadsheet();
- var sheet = ss.getActiveSheet();
- var rowsData = getRowsData_(sheet, getExportOptions(e));
- var json = makeJSON_(rowsData, getExportOptions(e));
- return displayText_(json);
- }
- function getExportOptions(e) {
- var options = {};
- options.language = e && e.parameter.language || DEFAULT_LANGUAGE;
- options.format = e && e.parameter.format || DEFAULT_FORMAT;
- options.structure = e && e.parameter.structure || DEFAULT_STRUCTURE;
- var cache = CacheService.getPublicCache();
- cache.put('language', options.language);
- cache.put('format', options.format);
- cache.put('structure', options.structure);
- Logger.log(options);
- return options;
- }
- function makeJSON_(object, options) {
- if (options.format == FORMAT_PRETTY) {
- var jsonString = JSON.stringify(object, null, 4);
- } else if (options.format == FORMAT_MULTILINE) {
- var jsonString = Utilities.jsonStringify(object);
- jsonString = jsonString.replace(/},/gi, '},\n');
- jsonString = prettyJSON.replace(/":\[{"/gi, '":\n[{"');
- jsonString = prettyJSON.replace(/}\],/gi, '}],\n');
- } else {
- var jsonString = Utilities.jsonStringify(object);
- }
- if (options.language == LANGUAGE_PYTHON) {
- // add unicode markers
- jsonString = jsonString.replace(/"([a-zA-Z]*)":\s+"/gi, '"$1": u"');
- }
- return jsonString;
- }
- function displayText_(text) {
- var app = UiApp.createApplication().setTitle('Exported JSON');
- app.add(makeTextBox(app, 'json'));
- app.getElementById('json').setText(text);
- var ss = SpreadsheetApp.getActiveSpreadsheet();
- ss.show(app);
- return app;
- }
- // getRowsData iterates row by row in the input range and returns an array of objects.
- // Each object contains all the data for a given row, indexed by its normalized column name.
- // Arguments:
- // - sheet: the sheet object that contains the data to be processed
- // - range: the exact range of cells where the data is stored
- // - columnHeadersRowIndex: specifies the row number where the column names are stored.
- // This argument is optional and it defaults to the row immediately above range;
- // Returns an Array of objects.
- function getRowsData_(sheet, options) {
- var headersRange = sheet.getRange(1, 1, sheet.getFrozenRows(), sheet.getMaxColumns());
- var headers = headersRange.getValues()[FILA_ENCABEZADOS_CAMEL_CASE];
- var dataRange = sheet.getRange(sheet.getFrozenRows()+1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
- var objects = getObjects_(dataRange.getValues(), headers);
- if (options.structure == STRUCTURE_HASH) {
- var objectsById = {};
- objects.forEach(function(object) {
- objectsById[object.id] = object;
- });
- return objectsById;
- } else {
- return objects;
- }
- }
- // getColumnsData iterates column by column in the input range and returns an array of objects.
- // Each object contains all the data for a given column, indexed by its normalized row name.
- // Arguments:
- // - sheet: the sheet object that contains the data to be processed
- // - range: the exact range of cells where the data is stored
- // - rowHeadersColumnIndex: specifies the column number where the row names are stored.
- // This argument is optional and it defaults to the column immediately left of the range;
- // Returns an Array of objects.
- function getColumnsData_(sheet, range, rowHeadersColumnIndex) {
- rowHeadersColumnIndex = rowHeadersColumnIndex || range.getColumnIndex() - 1;
- var headersTmp = sheet.getRange(range.getRow(), rowHeadersColumnIndex, range.getNumRows(), 1).getValues();
- var headers = normalizeHeaders_(arrayTranspose_(headersTmp)[0]);
- return getObjects(arrayTranspose_(range.getValues()), headers);
- }
- // For every row of data in data, generates an object that contains the data. Names of
- // object fields are defined in keys.
- // Arguments:
- // - data: JavaScript 2d array
- // - keys: Array of Strings that define the property names for the objects to create
- function getObjects_(data, keys) {
- var objects = [];
- for (var i = 0; i < data.length; ++i) {
- var object = {};
- var hasData = false;
- for (var j = 0; j < data[i].length; ++j) {
- var cellData = data[i][j];
- if (isCellEmpty_(cellData)) {
- continue;
- }
- object[keys[j]] = cellData;
- hasData = true;
- }
- if (hasData) {
- objects.push(object);
- }
- }
- return objects;
- }
- // Returns true if the cell where cellData was read from is empty.
- // Arguments:
- // - cellData: string
- function isCellEmpty_(cellData) {
- return typeof(cellData) == "string" && cellData == "";
- }
- // Returns true if the character char is alphabetical, false otherwise.
- function isAlnum_(char) {
- return char >= 'A' && char <= 'Z' ||
- char >= 'a' && char <= 'z' ||
- isDigit_(char);
- }
- // Returns true if the character char is a digit, false otherwise.
- function isDigit_(char) {
- return char >= '0' && char <= '9';
- }
- // Given a JavaScript 2d Array, this function returns the transposed table.
- // Arguments:
- // - data: JavaScript 2d Array
- // Returns a JavaScript 2d Array
- // Example: arrayTranspose([[1,2,3],[4,5,6]]) returns [[1,4],[2,5],[3,6]].
- function arrayTranspose_(data) {
- if (data.length == 0 || data[0].length == 0) {
- return null;
- }
- var ret = [];
- for (var i = 0; i < data[0].length; ++i) {
- ret.push([]);
- }
- for (var i = 0; i < data.length; ++i) {
- for (var j = 0; j < data[i].length; ++j) {
- ret[j][i] = data[i][j];
- }
- }
- return ret;
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement