Google App Script - Spreadsheet - Export to JSON

GoogleAppScriptSpreadsheetExportToJson.gs

/**
 * Script to export Spreadsheet content as JSON 
 * 
 * The script, when onDocumentOpen is set up via triggers, creates an extra menu item "Actions" where the script can be run.
 * Originally designed to use Spreadsheets as web translation tool. Sheet contains columns for each language and first row is considered header column which is not exported.
 **/
 
//Global
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeSheet = ss.getActiveSheet();

function onDocumentOpen(e) {
  var menuEntries = [];
  menuEntries.push({name: "Export to JSON", functionName: "exportToJson"});
  ss.addMenu("☀ ACTIONS", menuEntries);
}

function exportToJson() {
  var dataRows = activeSheet.getDataRange().getValues();
  var header = dataRows[0];
  
  var translationObjects = [];
  
  if (header.length && header.length > 1) {
    var KEY_INDEX = 0;
    var numOfLanguages = header.length - 1; // -1 as key is not a language column
    
    // Create empty language object for each language
    for(var i = 0; i < numOfLanguages; i++) {
      translationObjects.push({});
    }
    
    // Start from 1 as 0 is header
    for (var rowNum = 1; rowNum < dataRows.length; rowNum++) {
      var row = dataRows[rowNum];
      var key = row[KEY_INDEX];
      if (key) {
        for (var langIndex = 1; langIndex < numOfLanguages + 1; langIndex++) {
          var value = row[langIndex];
          if (value) {
            var languageObject = translationObjects[langIndex-1];
            _assignProperty(languageObject, key, value);
          }
        }  
      }
    }
  }
  
  var output = '';
  
  for (var translationIndex = 0; translationIndex < translationObjects.length; translationIndex++) {
    output += '<h1>' + header[translationIndex+1] + '</h1>';
    output += '<textarea style="width:100%;height:250px;">' + JSON.stringify(translationObjects[translationIndex], null, 2) + '</textarea>';
  }
  
  var html = HtmlService
      .createHtmlOutput(output)
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setWidth(1280)
      .setHeight(900);;
  
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .showModalDialog(html, 'JSON');
}

function _assignProperty(obj, path, value) {
  var props = path.split(".")
  var i = 0
  var prop;

  for(; i < props.length - 1; i++) {
    prop = props[i];
    if(!obj[prop]) {
      obj[prop] = {};
    }
    obj = obj[prop];  
  }
  
  obj[props[i]] = value; 
}

Example of the Google Sheet used for translation and it's export popup.