exportjson.js

// This script assumes that there will be several groups of data in each row and create a distinct collection of JSON for
// each group. The group data is contained in the frozen headers, with all but the bottom header cell being the same for 
// a given group. The labels for the group header data will be in the first column. The row id data, common to all data 
// groups in the row, is found in first several columns, and ends with a blank column. A data group ends with a change in 
// the group header data or a blank column.

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


function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [
    {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');
  
  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 exportSheet(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var rowsData = getGroupsData_(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; 
}

// getGroupsData iterates by header groups row by row in the input range and returns an array of objects.
// Each object contains all the data for a given row group, indexed by its normalized column name.
// 
// Arguments:
//   - sheet: the sheet object that contains the data to be processed
// Intermediate variables: 
//   - headersRange: the range of cells of the headers
//   - headers, the bottom row values of the header, the first several are the rowIdHeaders
//   - rowIdHeader, the header labels for the rowIds, the initial row cells that are shared by the various group in a row
//   - groupHeadersData, the values of the header, which in our data model includes the rowIdHeaders, groupHeaders and 
//       the groupData.
//   - groupHeadersObj, the object containing both the groupData and the groupIndex
//   - groupIndex[i], the object containing the start and stop index for the groups of each row
//   - groupData[i], the object of the object data for each group
// Returns an Array of objects.
function getGroupsData_(sheet, options) {
  var headersRange = sheet.getRange(2, 1, sheet.getFrozenRows()-1, sheet.getMaxColumns());
  var headers = headersRange.getValues()[headersRange.getHeight()-1];
  var rowIdHeaders = getRowIdHeaders_(headers);
  var groupHeadersData = headersRange.getValues();
  var groupHeadersObj = getGroupHeaders_(groupHeadersData);
  var groupRowHeader = groupHeadersObj.groupRowHeader
  var groupIndex = groupHeadersObj.groupIndex
  var groupData = groupHeadersObj.groupData
  var dataRange = sheet.getRange(sheet.getFrozenRows()+1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
  var objects = getGroupObjects_(rowIdHeaders, groupRowHeader, groupIndex, groupData, dataRange.getValues());
  return objects;
}

// 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 array
//   - keys: Array of Strings that define the property names for the objects to create
function getObjectsRow_(data, keys) {
  var object = [];
  for (var i = 0; i < data.length; ++i) {
    var cellData = data[i];
    if (isCellEmpty_(cellData)) {
      continue;
    }
    object[keys[i]] = cellData;
  }
  return object;
}

// For every row of data in data, generates an object that contains the data. Names of
// object fields are defined in keys.
// Arguments:
//   - rowKeys: keys for the row data shared by all groups
//   - groupRowHeaders[i]: the data for the row headers
//   - groupIndex[i]: [startIndex, stopIndex] for the ith group
//   - groupData[i]: the object data common to the group
//   - data: JavaScript 2d array
function getGroupObjects_(rowKeys, groupRowHeaders, groupIndex, groupData, data) {
  // use the groupIndex to pull the groups
  var objects = [];
  for (var r = 0; r < data.length; ++r) {
    // Process a row of data
    var rowObj = {};
    // Prep the rowObj for all groups in the row
    for (var j = 0; j < rowKeys.length; ++j) {
      var cellData = data[r][j];
      if (isCellEmpty_(cellData)) {
        continue;
      }
      rowObj[rowKeys[j]] = cellData;
    }
    for (var i = 0; i < groupIndex.length; ++i) {
      // Prep the groupObject for each ot the group of the row
      var groupObject = {};
      var hasData = false;
      for (var j = 0; j < groupIndex[i][1]-groupIndex[i][0]+1; ++j) {
        var k = groupIndex[i][0]
        var cellData = data[r][j+k];
        if (isCellEmpty_(cellData)) {
          continue;
        }
        groupObject[groupRowHeaders[i][j]] = cellData;
        hasData = true;
      }
      if (hasData) {
        var copy = merge_obj(groupObject, groupData[i], rowObj);
        objects.push(copy);
      }
    }
  }
  return objects;
}

// For the sheet, generates header and group information.
// Arguments:
//   - headers: The headers for the group data
// Results:
//   - groupIndex: The [startIndex, stopIndex] for each group
//   - groupData[i]: An array of objects of group data
//   - groupRowHeader: An array of headers of the bottom line
function getGroupHeaders_(headers) {
  // Transpose the array so we can step throught the columns like rows
  var groupDataCR = headers[0].map(function (_, c) { return headers.map(function (r) { return r[c]; }); });
  // Initialize the return variables
  var groupIndex = [];
  var groupRowHeader = []
  var groupData = [];
  // The first column (transposed to row) contains the group header labels
  var groupHeaders = normalizeHeaders_(groupDataCR[0]);
  // The last member of the column (transposed to row) will be the id header, collected elsewhere
  groupHeaders.pop();
  // Logger.log("These are the group headers %s", JSON.stringify(groupHeaders));
  var startIndex = 1
  // Process the columns (transposed into rows)
  var groupRowHeaderObj = [];
  var CRlength = groupDataCR.length
  var CRdepth = groupDataCR[0].length
  for (var i = 1; i < CRlength; ++i) {
      var thisCol = groupDataCR[i].slice(0,(CRdepth-1));
      if (i != groupDataCR.length - 1) {
        var nextCol = groupDataCR[(i+1)].slice(0,(CRdepth-1));
        // Logger.log("For col(row) " + i + " this col " + JSON.stringify(thisCol) + " next col " + JSON.stringify(nextCol));
      }
      // Is the column i first cell empty? Skip this column
      // Logger.log("Not this col eq next col " + !(thisCol==nextCol))
      if (isCellEmpty_(groupDataCR[i][0])) {
        // Advance index, ignore row header and data
        var startIndex = i + 1;
        groupRowHeaderObj = []
      // Does the next column equal this one?
      } else if (thisCol.toString() !== nextCol.toString()) {
        // End this group by pushing the index and the data object              
        var thisGroup = [startIndex, i];
        groupIndex.push(thisGroup);
        var startIndex = i + 1;
        groupData.push(getObjectsRow_(thisCol, groupHeaders));
        groupRowHeaderObj.push(groupDataCR[i][CRdepth-1]);
        groupRowHeader.push(groupRowHeaderObj);
        groupRowHeaderObj = []
      } else {
        groupRowHeaderObj.push(groupDataCR[i][CRdepth-1]);
      }
  }
  return {groupIndex: groupIndex, groupRowHeader: groupRowHeader, groupData: groupData};
}


// For the sheet, generate the rowIdHeaders. Will end at first empty key
// Arguments:
//    - headers: The last row of data for the header
function getRowIdHeaders_(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    var key = normalizeHeader_(headers[i]);
    if (key.length > 0) {
      keys.push(key);
    } else {
      break;
    }
  }
  return keys;
}

// 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 == "") || cellData == 0;
}

// Returns true if the character char is alphabetical, false otherwise.
function isAlnum_(char) {
  return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    isDigit_(char) || char == "_";
}

// Returns true if the character char is a digit, false otherwise.
function isDigit_(char) {
  return char >= '0' && char <= '9';
}

// Merges two objects
function merge_obj(obj1,obj2, obj3){
  var obj4 = {};
  for (var attrname in obj1) { obj4[attrname] = obj1[attrname]; }
  for (var attrname in obj2) { obj4[attrname] = obj2[attrname]; }
  for (var attrname in obj3) { obj4[attrname] = obj3[attrname]; }
  return obj4;
}