Skip to content

Instantly share code, notes, and snippets.

@pamelafox

pamelafox/exportjson.js

Last active June 11, 2025 12:29
Download ZIP

Revisions

  1. pamelafox revised this gist May 3, 2019. 1 changed file with 0 additions and 2 deletions.
    2 changes: 0 additions & 2 deletions exportjson.js
    View file
    Original file line number Diff line number Diff line change
    @@ -18,8 +18,6 @@ var DEFAULT_STRUCTURE = STRUCTURE_LIST;


    function onOpen() {


    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [
    {name: "Export JSON for this sheet", functionName: "exportSheet"},
  2. pamelafox revised this gist May 3, 2019. 1 changed file with 11 additions and 30 deletions.
    41 changes: 11 additions & 30 deletions exportjson.js
    View file
    Original file line number Diff line number Diff line change
    @@ -18,34 +18,16 @@ var DEFAULT_STRUCTURE = STRUCTURE_LIST;


    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"},
    {name: "Export JSON for all sheets", functionName: "exportAllSheets"}
    ];
    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);
    @@ -93,15 +75,15 @@ function exportAllSheets(e) {
    sheetsData[sheetName] = rowsData;
    }
    var json = makeJSON_(sheetsData, getExportOptions(e));
    return displayText_(json);
    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);
    displayText_(json);
    }

    function getExportOptions(e) {
    @@ -139,12 +121,11 @@ function makeJSON_(object, options) {
    }

    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;
    var output = HtmlService.createHtmlOutput("<textarea style='width:100%;' rows='20'>" + text + "</textarea>");
    output.setWidth(400)
    output.setHeight(300);
    SpreadsheetApp.getUi()
    .showModalDialog(output, 'Exported JSON');
    }

    // getRowsData iterates row by row in the input range and returns an array of objects.
  3. pamelafox revised this gist Jun 7, 2013. 1 changed file with 1 addition and 9 deletions.
    10 changes: 1 addition & 9 deletions exportjson.js
    View file
    Original file line number Diff line number Diff line change
    @@ -20,8 +20,8 @@ var DEFAULT_STRUCTURE = STRUCTURE_LIST;
    function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [
    {name: "Export JSON for all sheets", functionName: "exportAllSheets"},
    {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);
    @@ -43,8 +43,6 @@ function exportOptions() {
    grid.setWidget(3, 1, makeButton(app, grid, 'Export All Sheets', 'exportAllSheets'));
    app.add(grid);

    app.add(makeLabel(app, '', 'status'));
    app.add(makeTextBox(app, 'json'));
    doc.show(app);
    }

    @@ -83,11 +81,6 @@ function makeTextBox(app, name) {
    return textArea;
    }

    function updateStatus(text) {
    var app = UiApp.getActiveApplication();
    if (app) app.getElementById('status').setText(text);
    }

    function exportAllSheets(e) {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    @@ -104,7 +97,6 @@ function exportAllSheets(e) {
    }

    function exportSheet(e) {
    updateStatus('Exported current sheet.');
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var rowsData = getRowsData_(sheet, getExportOptions(e));
  4. pamelafox revised this gist Jun 7, 2013. 1 changed file with 24 additions and 17 deletions.
    41 changes: 24 additions & 17 deletions exportjson.js
    View file
    Original file line number Diff line number Diff line change
    @@ -11,6 +11,23 @@ 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;


    function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [
    {name: "Export JSON for all sheets", functionName: "exportAllSheets"},
    {name: "Export JSON for this sheet", functionName: "exportSheet"},
    {name: "Configure export", functionName: "exportOptions"},
    ];
    ss.addMenu("Export JSON", menuEntries);
    }


    function exportOptions() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var app = UiApp.createApplication().setTitle('Export JSON');
    @@ -72,7 +89,6 @@ function updateStatus(text) {
    }

    function exportAllSheets(e) {
    updateStatus('Exported all sheets.');

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();
    @@ -99,9 +115,9 @@ function exportSheet(e) {
    function getExportOptions(e) {
    var options = {};

    options.language = e && e.parameter.language || LANGUAGE_JS;
    options.format = e && e.parameter.format || FORMAT_PRETTY;
    options.structure = e && e.parameter.structure || STRUCTURE_LIST;
    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);
    @@ -131,20 +147,11 @@ function makeJSON_(object, options) {
    }

    function displayText_(text) {
    var needToShow = false;
    var app = UiApp.getActiveApplication();

    if (!app) {
    app = UiApp.createApplication().setTitle('Exported JSON');
    app.add(makeTextBox(app, 'json'));
    needToShow = true;
    }
    var app = UiApp.createApplication().setTitle('Exported JSON');
    app.add(makeTextBox(app, 'json'));
    app.getElementById('json').setText(text);

    if (needToShow) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    ss.show(app);
    }
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    ss.show(app);
    return app;
    }

  5. pamelafox revised this gist Sep 3, 2012. 1 changed file with 23 additions and 7 deletions.
    30 changes: 23 additions & 7 deletions exportjson.js
    View file
    Original file line number Diff line number Diff line change
    @@ -8,17 +8,22 @@ var FORMAT_PRETTY = 'Pretty';
    var LANGUAGE_JS = 'JavaScript';
    var LANGUAGE_PYTHON = 'Python';

    var STRUCTURE_LIST = 'List';
    var STRUCTURE_HASH = 'Hash (keyed by "id" column)';

    function exportOptions() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var app = UiApp.createApplication().setTitle('Export JSON');

    var grid = app.createGrid(3, 2);
    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, makeButton(app, grid, 'Export Active Sheet', 'exportSheet'));
    grid.setWidget(2, 1, makeButton(app, grid, 'Export All Sheets', 'exportAllSheets'));
    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);

    app.add(makeLabel(app, '', 'status'));
    @@ -74,7 +79,7 @@ function exportAllSheets(e) {
    var sheetsData = {};
    for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    var rowsData = getRowsData_(sheet);
    var rowsData = getRowsData_(sheet, getExportOptions(e));
    var sheetName = sheet.getName();
    sheetsData[sheetName] = rowsData;
    }
    @@ -86,7 +91,7 @@ function exportSheet(e) {
    updateStatus('Exported current sheet.');
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var rowsData = getRowsData_(sheet);
    var rowsData = getRowsData_(sheet, getExportOptions(e));
    var json = makeJSON_(rowsData, getExportOptions(e));
    return displayText_(json);
    }
    @@ -96,10 +101,12 @@ function getExportOptions(e) {

    options.language = e && e.parameter.language || LANGUAGE_JS;
    options.format = e && e.parameter.format || FORMAT_PRETTY;
    options.structure = e && e.parameter.structure || STRUCTURE_LIST;

    var cache = CacheService.getPublicCache();
    cache.put('language', options.language);
    cache.put('format', options.format);
    cache.put('structure', options.structure);

    Logger.log(options);
    return options;
    @@ -149,11 +156,20 @@ function displayText_(text) {
    // - 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) {
    function getRowsData_(sheet, options) {
    var headersRange = sheet.getRange(1, 1, sheet.getFrozenRows(), sheet.getMaxColumns());
    var headers = headersRange.getValues()[0];
    var dataRange = sheet.getRange(sheet.getFrozenRows()+1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
    return getObjects_(dataRange.getValues(), normalizeHeaders_(headers));
    var objects = getObjects_(dataRange.getValues(), normalizeHeaders_(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.
  6. pamelafox revised this gist Feb 29, 2012. 1 changed file with 117 additions and 25 deletions.
    142 changes: 117 additions & 25 deletions exportjson.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,74 @@
    // 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.

    function exportAllSheets() {
    var FORMAT_ONELINE = 'One-line';
    var FORMAT_MULTILINE = 'Multi-line';
    var FORMAT_PRETTY = 'Pretty';

    var LANGUAGE_JS = 'JavaScript';
    var LANGUAGE_PYTHON = 'Python';

    function exportOptions() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var app = UiApp.createApplication().setTitle('Export JSON');

    var grid = app.createGrid(3, 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, makeButton(app, grid, 'Export Active Sheet', 'exportSheet'));
    grid.setWidget(2, 1, makeButton(app, grid, 'Export All Sheets', 'exportAllSheets'));
    app.add(grid);

    app.add(makeLabel(app, '', 'status'));
    app.add(makeTextBox(app, 'json'));
    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 updateStatus(text) {
    var app = UiApp.getActiveApplication();
    if (app) app.getElementById('status').setText(text);
    }

    function exportAllSheets(e) {
    updateStatus('Exported all sheets.');

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();
    var sheetsData = {};
    @@ -11,42 +78,67 @@ function exportAllSheets() {
    var sheetName = sheet.getName();
    sheetsData[sheetName] = rowsData;
    }
    var json = makePrettyJSON_(sheetsData);
    displayText_(json);
    var json = makeJSON_(sheetsData, getExportOptions(e));
    return displayText_(json);
    }

    function exportSheet() {
    function exportSheet(e) {
    updateStatus('Exported current sheet.');
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var rowsData = getRowsData_(sheet);
    var json = makePrettyJSON_(rowsData);
    displayText_(json);
    var json = makeJSON_(rowsData, getExportOptions(e));
    return displayText_(json);
    }

    function getExportOptions(e) {
    var options = {};

    options.language = e && e.parameter.language || LANGUAGE_JS;
    options.format = e && e.parameter.format || FORMAT_PRETTY;

    var cache = CacheService.getPublicCache();
    cache.put('language', options.language);
    cache.put('format', options.format);

    Logger.log(options);
    return options;
    }

    // Can either output fully indented JSON, newlined JSON, or single-line JSON
    var JSON_STYLE = 'indented';
    function makePrettyJSON_(object) {
    if (JSON_STYLE == 'indented') {
    return JSON.stringify(object, null, 4);
    } else if (JSON_STYLE == 'newlines') {
    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);
    var prettyJSON = jsonString.replace(/},/gi, '},\n');
    prettyJSON = prettyJSON.replace(/":\[{"/gi, '":\n[{"');
    prettyJSON = prettyJSON.replace(/}\],/gi, '}],\n');
    return prettyJSON;
    jsonString = jsonString.replace(/},/gi, '},\n');
    jsonString = prettyJSON.replace(/":\[{"/gi, '":\n[{"');
    jsonString = prettyJSON.replace(/}\],/gi, '}],\n');
    } else {
    return Utilities.jsonStringify(object);
    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 ss = SpreadsheetApp.getActiveSpreadsheet();
    var app = UiApp.createApplication().setTitle('Results');;
    var scrollPanel = app.createScrollPanel();
    var textArea = app.createTextArea().setText(text).setWidth('100%').setHeight('100%');
    scrollPanel.add(textArea);
    app.add(scrollPanel);
    ss.show(app);
    var needToShow = false;
    var app = UiApp.getActiveApplication();

    if (!app) {
    app = UiApp.createApplication().setTitle('Exported JSON');
    app.add(makeTextBox(app, 'json'));
    needToShow = true;
    }
    app.getElementById('json').setText(text);

    if (needToShow) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    ss.show(app);
    }
    return app;
    }

    // getRowsData iterates row by row in the input range and returns an array of objects.
    @@ -164,7 +256,7 @@ function isCellEmpty_(cellData) {
    function isAlnum_(char) {
    return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    isDigit(char);
    isDigit_(char);
    }

    // Returns true if the character char is a digit, false otherwise.
  7. pamelafox revised this gist Feb 29, 2012. 1 changed file with 2 additions and 3 deletions.
    5 changes: 2 additions & 3 deletions exportjson.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,5 @@
    // Exports current sheet as JSON and displays in message box.


    // 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.

    function exportAllSheets() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
  8. pamelafox revised this gist Feb 29, 2012. 1 changed file with 43 additions and 9 deletions.
    52 changes: 43 additions & 9 deletions exportjson.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,16 +1,50 @@
    // Exports current sheet as JSON and displays in message box.
    function exportJSON() {



    function exportAllSheets() {
    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);
    var sheetName = sheet.getName();
    sheetsData[sheetName] = rowsData;
    }
    var json = makePrettyJSON_(sheetsData);
    displayText_(json);
    }

    function exportSheet() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var rowsData = getRowsData_(sheet);
    var jsonString = Utilities.jsonStringify(rowsData).replace(/},/gi, '},\n');

    var app = UiApp.createApplication().setTitle('Exported JSON');
    var json = makePrettyJSON_(rowsData);
    displayText_(json);
    }

    // Can either output fully indented JSON, newlined JSON, or single-line JSON
    var JSON_STYLE = 'indented';
    function makePrettyJSON_(object) {
    if (JSON_STYLE == 'indented') {
    return JSON.stringify(object, null, 4);
    } else if (JSON_STYLE == 'newlines') {
    var jsonString = Utilities.jsonStringify(object);
    var prettyJSON = jsonString.replace(/},/gi, '},\n');
    prettyJSON = prettyJSON.replace(/":\[{"/gi, '":\n[{"');
    prettyJSON = prettyJSON.replace(/}\],/gi, '}],\n');
    return prettyJSON;
    } else {
    return Utilities.jsonStringify(object);
    }
    }

    function displayText_(text) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var app = UiApp.createApplication().setTitle('Results');;
    var scrollPanel = app.createScrollPanel();
    var textArea = app.createTextArea();
    textArea.setText(jsonString);
    textArea.setWidth('100%');
    textArea.setHeight('100%');
    var textArea = app.createTextArea().setText(text).setWidth('100%').setHeight('100%');
    scrollPanel.add(textArea);
    app.add(scrollPanel);
    ss.show(app);
    @@ -161,4 +195,4 @@ function arrayTranspose_(data) {
    }

    return ret;
    }
    }
  9. pamelafox revised this gist Feb 28, 2012. 1 changed file with 2 additions and 3 deletions.
    5 changes: 2 additions & 3 deletions exportjson.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,10 +1,10 @@
    // Exports current sheet as JSON and displays.
    // Exports current sheet as JSON and displays in message box.
    function exportJSON() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var rowsData = getRowsData_(sheet);
    var jsonString = Utilities.jsonStringify(rowsData).replace(/},/gi, '},\n');
    // Create the UiApp object myapp and set the title text

    var app = UiApp.createApplication().setTitle('Exported JSON');
    var scrollPanel = app.createScrollPanel();
    var textArea = app.createTextArea();
    @@ -13,7 +13,6 @@ function exportJSON() {
    textArea.setHeight('100%');
    scrollPanel.add(textArea);
    app.add(scrollPanel);
    // Use the show() method on the Spreadsheet to display the UiApp object and all elements associated with it.
    ss.show(app);
    }

  10. pamelafox revised this gist Feb 28, 2012. 1 changed file with 30 additions and 19 deletions.
    49 changes: 30 additions & 19 deletions exportjson.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,9 +1,20 @@
    // Exports current sheet as JSON and displays in message box.
    // Exports current sheet as JSON and displays.
    function exportJSON() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var rowsData = getRowsData(sheet);
    ss.msgBox(Utilities.jsonStringify(rowsData));
    var rowsData = getRowsData_(sheet);
    var jsonString = Utilities.jsonStringify(rowsData).replace(/},/gi, '},\n');
    // Create the UiApp object myapp and set the title text
    var app = UiApp.createApplication().setTitle('Exported JSON');
    var scrollPanel = app.createScrollPanel();
    var textArea = app.createTextArea();
    textArea.setText(jsonString);
    textArea.setWidth('100%');
    textArea.setHeight('100%');
    scrollPanel.add(textArea);
    app.add(scrollPanel);
    // Use the show() method on the Spreadsheet to display the UiApp object and all elements associated with it.
    ss.show(app);
    }

    // getRowsData iterates row by row in the input range and returns an array of objects.
    @@ -14,11 +25,11 @@ function exportJSON() {
    // - 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) {
    function getRowsData_(sheet) {
    var headersRange = sheet.getRange(1, 1, sheet.getFrozenRows(), sheet.getMaxColumns());
    var headers = headersRange.getValues()[0];
    var dataRange = sheet.getRange(sheet.getFrozenRows()+1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
    return getObjects(dataRange.getValues(), normalizeHeaders(headers));
    return getObjects_(dataRange.getValues(), normalizeHeaders_(headers));
    }

    // getColumnsData iterates column by column in the input range and returns an array of objects.
    @@ -29,11 +40,11 @@ function getRowsData(sheet) {
    // - 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) {
    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);
    var headers = normalizeHeaders_(arrayTranspose_(headersTmp)[0]);
    return getObjects(arrayTranspose_(range.getValues()), headers);
    }


    @@ -42,14 +53,14 @@ function getColumnsData(sheet, range, rowHeadersColumnIndex) {
    // Arguments:
    // - data: JavaScript 2d array
    // - keys: Array of Strings that define the property names for the objects to create
    function getObjects(data, keys) {
    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)) {
    if (isCellEmpty_(cellData)) {
    continue;
    }
    object[keys[j]] = cellData;
    @@ -65,10 +76,10 @@ function getObjects(data, keys) {
    // Returns an Array of normalized Strings.
    // Arguments:
    // - headers: Array of Strings to normalize
    function normalizeHeaders(headers) {
    function normalizeHeaders_(headers) {
    var keys = [];
    for (var i = 0; i < headers.length; ++i) {
    var key = normalizeHeader(headers[i]);
    var key = normalizeHeader_(headers[i]);
    if (key.length > 0) {
    keys.push(key);
    }
    @@ -85,7 +96,7 @@ function normalizeHeaders(headers) {
    // "First Name" -> "firstName"
    // "Market Cap (millions) -> "marketCapMillions
    // "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
    function normalizeHeader(header) {
    function normalizeHeader_(header) {
    var key = "";
    var upperCase = false;
    for (var i = 0; i < header.length; ++i) {
    @@ -94,10 +105,10 @@ function normalizeHeader(header) {
    upperCase = true;
    continue;
    }
    if (!isAlnum(letter)) {
    if (!isAlnum_(letter)) {
    continue;
    }
    if (key.length == 0 && isDigit(letter)) {
    if (key.length == 0 && isDigit_(letter)) {
    continue; // first character must be a letter
    }
    if (upperCase) {
    @@ -113,19 +124,19 @@ function normalizeHeader(header) {
    // Returns true if the cell where cellData was read from is empty.
    // Arguments:
    // - cellData: string
    function isCellEmpty(cellData) {
    function isCellEmpty_(cellData) {
    return typeof(cellData) == "string" && cellData == "";
    }

    // Returns true if the character char is alphabetical, false otherwise.
    function isAlnum(char) {
    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) {
    function isDigit_(char) {
    return char >= '0' && char <= '9';
    }

    @@ -134,7 +145,7 @@ function isDigit(char) {
    // - 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) {
    function arrayTranspose_(data) {
    if (data.length == 0 || data[0].length == 0) {
    return null;
    }
  11. pamelafox created this gist Feb 21, 2012.
    154 changes: 154 additions & 0 deletions exportjson.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,154 @@
    // Exports current sheet as JSON and displays in message box.
    function exportJSON() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var rowsData = getRowsData(sheet);
    ss.msgBox(Utilities.jsonStringify(rowsData));
    }

    // 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) {
    var headersRange = sheet.getRange(1, 1, sheet.getFrozenRows(), sheet.getMaxColumns());
    var headers = headersRange.getValues()[0];
    var dataRange = sheet.getRange(sheet.getFrozenRows()+1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
    return getObjects(dataRange.getValues(), normalizeHeaders(headers));
    }

    // 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 an Array of normalized Strings.
    // Arguments:
    // - headers: Array of Strings to normalize
    function normalizeHeaders(headers) {
    var keys = [];
    for (var i = 0; i < headers.length; ++i) {
    var key = normalizeHeader(headers[i]);
    if (key.length > 0) {
    keys.push(key);
    }
    }
    return keys;
    }

    // Normalizes a string, by removing all alphanumeric characters and using mixed case
    // to separate words. The output will always start with a lower case letter.
    // This function is designed to produce JavaScript object property names.
    // Arguments:
    // - header: string to normalize
    // Examples:
    // "First Name" -> "firstName"
    // "Market Cap (millions) -> "marketCapMillions
    // "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
    function normalizeHeader(header) {
    var key = "";
    var upperCase = false;
    for (var i = 0; i < header.length; ++i) {
    var letter = header[i];
    if (letter == " " && key.length > 0) {
    upperCase = true;
    continue;
    }
    if (!isAlnum(letter)) {
    continue;
    }
    if (key.length == 0 && isDigit(letter)) {
    continue; // first character must be a letter
    }
    if (upperCase) {
    upperCase = false;
    key += letter.toUpperCase();
    } else {
    key += letter.toLowerCase();
    }
    }
    return key;
    }

    // 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;
    }