CODEWITHSUNDEEP
jsongoogle-apps-scriptgoogle-sheets
Bernardo
Bernardo

Reputation: 63

How do i get a JSON file out of my SpreadSheet

I want to get a JSON out my spreadsheet that looks like this(SpreadSheet) and to extract a JSON file like this:

[{    "module": "ModuleA",
      "Questions" : [{
                      "Question": "xxxx",
                      "Answers" : ["1","2","3"],
                      "right answer" : "2",
            }
    ]

This is what i have by now but its not in the coreect hierarchy

function doGet(){

 var ss = SpreadsheetApp.openById('14-vl75N4mAv9FdZNHIhX_lhtQ-XY17lOvEnLIjiSwDc');
 var result={};

 var sheetA = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheetA')
 .getDataRange()
 .getValues();

 var sheetB = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheetB') 
 .getDataRange()
 .getValues();

 result.sheetA = makeObject(sheetA);
 result.sheetB = makeObject(sheetB);

 Logger.log(result.sheetA)
 //return ContentService.createTextOutput(JSON. stringify(result))
 //.setMimeType(ContentService.MimeType.JSON);

 }

 function makeObject(multiArr) {
   var obj = {};

   var headers = multiArr.shift()
   for(var i = 0; i< headers.length; i++){
   obj[headers[i]]= multiArr.map(function(app){
     return app[i];
   });
   }

   return obj;

 }

Upvotes: 0

Views: 102

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

What about this solution?

function doGet(){

 var ss = SpreadsheetApp.openById('14-vl75N4mAv9FdZNHIhX_lhtQ-XY17lOvEnLIjiSwDc');
 var result={};

 var sheetA = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheetA')
 .getDataRange()
 .getValues();

 var sheetB = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheetB') 
 .getDataRange()
 .getValues();

result.sheetA = makeObject(sheetA);
result.sheetB = makeObject(sheetB);
Logger.log(makeObject(sheetA))
}  

  function makeObject(multiArr) {
    var obj = [];
  for(var i=1;i<multiArr.length;i++)
  {
    obj.push( {
                      "Question": multiArr[i][1],
                      "Answers" : [multiArr[i][2],multiArr[i][3],multiArr[i][4]],
                      "right answer" : multiArr[i][5],
    }
                    )
  }  
  var myJSON=[{    "module": multiArr[1][0],
                   "Questions" : obj
               }]
  return JSON.stringify(myJSON);
}

You can push into an array your data formatted according to the hierarchy of your choice, populate it within a loop with the contents of each row in your sheet and then JSON.stringify it.

This works as above provided you have only one module per sheet. Otherwise, you need to implement an additional function which would detect in which row ModuleA ends and ModuleB starts.

Upvotes: 1

Related Questions