How do I get respective column names along with values in my generated JSON?-CodePudding

I am using this app script code to get a JSON format of some data in a sheet in Google Sheets.

function doGet(request){
  var mode = request.parameter.mode;
  var search = request.parameter.q;
  var sheetname = "Members"; //sheet name
  var startcol = 1;
  var endcol = 10;
  var filtercol = 7;

  var doc = SpreadsheetApp.openById(PropertiesService.getScriptProperties().getProperty("key"));
  var sheet = doc.getSheetByName(sheetname);
  var lastrow = sheet.getLastRow();
  var rows = [];
  var range = sheet.getRange(2,startcol,lastrow, endcol);
  var values = range.getValues();
  
  for(var row in values) {
    rows.push([]); 
    for(var col in values[row]){
      rows[row].push(values[row][col]);
    }
  }
  //data retrieved, JSON formatting begins below (on the basis of presence of a filtering key)
  
  if(search != null) { //if there is something to filter
    var rowstoreturn = rows.filter(a => a[filtercol] == search);
    return ContentService.createTextOutput(JSON.stringify({"data":rowstoreturn,"error":false})).setMimeType(ContentService.MimeType.JSON);
  }

  return ContentService.createTextOutput(JSON.stringify({"data":rows,"error":false})).setMimeType(ContentService.MimeType.JSON);

}

function setup() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  PropertiesService.getScriptProperties().setProperty("key", doc.getId());
}

(In my above script, if I want to get filtered data, I'll send the parameter q, else not. So if I've sent it, I'd get a JSON which consits of filtered data.)

A successful run of the above code gives me this result -

{
  "data": [
    [
      "LM-6000",
      "Yes",
      "Mr.",
      "John",
      "Doe",
      1998,
      "B.Com",
      1234567890,
      "[email protected]",
      "NYC"
    ]
  ],
  "error": false
}

Now, as you can see, these are just the cell values - what I want to do is get respective column name along with the value. Something like this -

{
  "data": [
    [
      {
         "Membership No.": "LM-6000",
         "Logged In": "Yes",
         "Salutation": "Mr.",
         "FName": "John",
         "LName": "Doe",
         "GradYr": 1998,
         "Course": "B.Com",
         "Mobile No.": 1234567890,
         "Email ID": "[email protected]",
         "City": "NYC"
      }
    ]
  ],
  "error": false
}

How should I modify my code to get the expected result? Any help is appreciated :) Thanks!

CodePudding user response：

As far as I can tell you want to convert the array into an object, where keys of the object are the column headers and values of the object are the array elements. It can be done with a loop:

var headers = ['a','b','c'];
var row = ['cell1','cell2','cell3'];
var obj = {};
row.forEach((cell,i) => obj[headers[i]] = cell);
console.log(obj);

Expected output:

{
  "a": "cell1",
  "b": "cell2",
  "c": "cell3"
}

So, the last lines of the function doGet() should look something like this:

...

if (search != null) {
  var rows = rows.filter(a => a[filtercol] == search);
}

// get the first row of the table as a header
var header = sheet.getRange(1,startcol,1,endcol).getValues().flat();

// convert array of rows into the array of objects
rows = rows.map(row => {
  var obj = {};
  row.forEach((cell,i) => obj[headers[i]] = cell);
  return obj;
})

// return the array of objects
return ContentService
  .createTextOutput(JSON.stringify({"data":[rows],"error":false}))
  .setMimeType(ContentService.MimeType.JSON);

}