Creating a JSON object from Google Sheets

Please try:

function getJsonArrayFromData(data){  var obj = {};  var result = [];  var headers = data[0];  var cols = headers.length;  var row = [];  for (var i = 1, l = data.length; i < l; i++)  {    // get a row to fill the object    row = data[i];    // clear object    obj = {};    for (var col = 0; col < cols; col++)     {      // fill object with new values      obj[headers[col]] = row[col];        }    // add object in a final result    result.push(obj);    }  return result;  }

Test function:

function test_getJsonArrayFromData(){  var data =       [      ['Planet', 'Mainland', 'Country', 'City'],      ['Earth', 'Europe', 'Britain', 'London'],      ['Earth', 'Europe', 'Britain', 'Manchester'],      ['Earth', 'Europe', 'Britain', 'Liverpool'],      ['Earth', 'Europe', 'France', 'Paris'],      ['Earth', 'Europe', 'France', 'Lion']    ];    Logger.log(getJsonArrayFromData(data));    // =>  [{Mainland=Europe, Country=Britain, Planet=Earth, City=London}, {Mainland=Europe, Country=Britain, Planet=Earth, City=Manchester}, {Mainland=Europe, Country=Britain, Planet=Earth, City=Liverpool}, {Mainland=Europe, Country=France, Planet=Earth, City=Paris}, {Mainland=Europe, Country=France, Planet=Earth, City=Lion}]}

I know it's been a while, but I came up with a slightly more flexible solution. I've created an aptly named function that converts spreadsheet data to objects, given a set of headers and a set of rows.

function convertToObjects(headers, rows){  return rows.reduce((ctx, row) => {    ctx.objects.push(ctx.headers.reduce((item, header, index) => {      item[header] = row[index];      return item;    }, {}));    return ctx;  }, { objects: [], headers}).objects;}

You can call the above function like this:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YourSheetName');var [headers, ...rows] = sheet.getDataRange().getValues();var objects = convertToObjects(headers, rows);

If you need to ignore the first row and get headers from the second row (like I do), then you can do this instead:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YourSheetName');var [_, headers, ...rows] = sheet.getDataRange().getValues();var objects = convertToObjects(headers, rows);

If you have any improvements to this, please let me know in the comments below.