Download customized json from google drive spreadsheet

Google Drive Limitation

Google drive provide option to export spreadsheet as json,but sometimes user need customized structure of json. To generate desirable json, we need to write spreadsheet script which read data from cell and build json object.

Consider following Spreadsheet

The original json output for above spreadsheet is here. But we need only data json.

Approach

1. First we need to read data from cell, and create Json object 
2. Make another spreadsheet and dump json data to that, so that it can be download as plain txt 

Add following Google script

 
/*
 * create a simple container that shows the string argument
 * in a text box
*/
 
function dumpDataIntoUI(data){
 
  //the white container is the app
  var app = UiApp.createApplication();
  app.setHeight(480);
  app.setWidth(640);
 
  //we want a nice label with the title of the script
  var label = app.createLabel("All Rows to JSON");
  label.setStyleAttribute("font-size","24");
  label.setStyleAttribute("font-weight","bold");
  label.setStyleAttribute("padding-bottom", "25px");
 
  //next, we will add the text box and fill it with the string argument  
  var text = app.createTextArea();
  text.setHeight(400);
  text.setWidth(640);
  text.setValue(data);
  text.setSelectionRange(0, data.length);
 
  //add all the widgets into the app and show it
  app.add(label);
  app.add(text);
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadsheet.show(app);
}
 

/*
 * Grap data to Json
 */
 
function dataToJson() {
 
  //grab the rows from the active spreadsheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
 
  //the first row contains the titles or json keys
  var firstRowTitles = values[0];
 
 //JSON object contains data
  var jsonobj = {};
  
  var k="";
  for(var i = 1; i < numRows; i++){
    var items = [];
    var row = values[i];
    record = {};
    for(var j = 1; j < row.length; j++){
      if(row[j]!=""){
        
        record[firstRowTitles[j]] = [row[j]];
      }
    }
    
        
    if(row[0] == ""){ //if having data of previous items
      if(jsonobj[k] == null){
        jsonobj[k] = record;
      }else{
        var item = jsonobj[k];        
        for (var key in record) { //merge to previous json array
          item[key].push(record[key][0]);
        }
        jsonobj[k] = item;
      }      
    }else{
      k = row[0];
      jsonobj[k] = record;      
    }
       
  }  
  
  dumpDataIntoUI(Utilities.jsonStringify(jsonobj)); 
  
  
  //Save JSON to different, spreasheet, which can serve as export data API  
  var doc = SpreadsheetApp.openById("");  
  doc.getActiveSheet().getRange('A1').setValue(Utilities.jsonStringify(jsonobj));
};
 
/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {
      name : "Convert to JSON",
      functionName : "dataToJson"
    }    
  ]; 
  sheet.addMenu("Json Service", entries);
};

Json Output from Script

 
{
   "Android":{
      "Colors":[
         "white",
         "black",
         "grey"
      ],
      "Version":[
         "jellybean",
         "gingerbread",
         "honeycomb"
      ]
   },
   "iphone":{
      "Colors":[
         "white",
         "black"
      ],
      "Version":[
         "3G",
         4,
         "4s"
      ]
   }
}

Auto generated json output can be found here.