2

I have been able to export data from google sheets into a pretty awesome JSON file, just not in the format I need (oops:) ).

Basically, I have data where the column name is the value header (in this case: location, infected, deaths, recovered, etc.) and row name are the values respectively. enter image description here

Is there a way I can reformat this into multiple objects so I have:

 var death_data = {
      cn: "132",
      th: "0",
      mo: "0",
      au: "0",
      sg: "0",

    };


 var infected_data = {
      cn: "4415",
      th: "8",
      mo: "7",
      au: "5",
      sg: "5",

    };

(The 2 letters are the Country Codes. CN: 3554+277+296+..other china values+...152+108+84...+13+6.)(132=125+2+1+1+1+1+1)

I found a similar resource here, however it is in R. Is there a similar method for JavaScript?

In case your wondering how I have tried to generate the objects:

function doGet() {
  var result = {};

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

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

  result = makeObject(infected);

 // result.death = makeObject(death);

//Logger.log(result);
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;
}

Using the above code, the JSON looks like this (Using JSON Viewer chrome extension): enter image description here Where each key then has values, like this: enter image description here and this enter image description here

Maybe there is a better way than trying to convert the formats after the fact, and instead just generate them correctly the first time.

Sorry in advance for my basic knowledge of JavaScript, i'm just getting started.

Any help would be greatly appreciated.

Kind Regards, Camden

CWrecker
  • 509
  • 7
  • 14
  • Show expected result for the given data and not some random sample result. – TheMaster Jan 29 '20 at 20:56
  • As TheMaster has said, a example of the desired output from your data would be useful. Coudln't you just [transpose the matrix](https://stackoverflow.com/questions/17428587/transposing-a-2d-array-in-javascript)? – Raserhin Jan 30 '20 at 09:26
  • The desired output is the first text highlighted in code format. – CWrecker Jan 30 '20 at 13:05
  • You should the exact result from your data. For example, how did you get 2863 for China? . The result should represent the exact output from your sample data. – TheMaster Jan 30 '20 at 18:21

1 Answers1

3

You could create such a object by looping over the array and adding relevant values:

Sample script:

function makeObject(multiArr) {
  var obj = {deaths:{}, infected:{}};
  var headers = multiArr.shift();

   multiArr.forEach(function(row){
    var [_,country,_,infected,deaths,_] = row;
    obj.deaths[country] = obj.deaths[country] || 0;
    obj.infected[country] = obj.infected[country] || 0;
    obj.deaths[country] = obj.deaths[country] + Number(deaths);
    obj.infected[country] = obj.infected[country] + Number(infected);
   })

  return obj;
}
Community
  • 1
  • 1
TheMaster
  • 45,448
  • 6
  • 62
  • 85