Friday, March 1, 2013

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.


  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();
  //we want a nice label with the title of the script
  var label = app.createLabel("All Rows to JSON");
  label.setStyleAttribute("padding-bottom", "25px");
  //next, we will add the text box and fill it with the string argument  
  var text = app.createTextArea();
  text.setSelectionRange(0, data.length);
  //add all the widgets into the app and show it
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();;

 * 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++){
        record[firstRowTitles[j]] = [row[j]];
    if(row[0] == ""){ //if having data of previous items
      if(jsonobj[k] == null){
        jsonobj[k] = record;
        var item = jsonobj[k];        
        for (var key in record) { //merge to previous json array
        jsonobj[k] = item;
      k = row[0];
      jsonobj[k] = record;      
  //Save JSON to different, spreasheet, which can serve as export data API  
  var doc = SpreadsheetApp.openById("");  
 * 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
Auto generated json output can be found here.

1 comment:

  1. Hi Arink,

    I am using and tweaking your script. Thanks for this! I try to figure out why the JSON output does not follow the sorting of the spreadsheet. In your example, why is Android output first and iphone next. I cannot figure out the logic of this. Thanks