I have a table like this:

And I want to convert it into a nested JSON (maybe not the right name).

I have this code:

function toJson(data,indexer){
  var cols = data[0];
  var index = cols.indexOf(indexer);
  var jsonData = {};
  for (i = 1; i < data.length; i  ) {
    if (!jsonData.hasOwnProperty(data[i][index])) {
      jsonData[data[i][index]] = [];
    }
    var jsonCandidate = {};
    for (j = 0; j < cols.length; j  ) {
      if (data[i][j] instanceof Date){
        jsonCandidate[cols[j]] = Utilities.formatDate(data[i][j], 'Europe/Madrid', 'yyyy-MM-dd');
      }else{
        jsonCandidate[cols[j]] = data[i][j];
      }
    }
    jsonData[data[i][index]].push(jsonCandidate);
  }
  return jsonData;
}

where data is the table above and indexer is 1st level key of the json. If I use the country as indexer then I get something like this:

{"ES="[{year=2022.0,eur=1.0,"country=ES"},{year=2021.0,eur=1.0,"country=ES"},],"SE="[{year=2021.0,eur=9.98,"country=SE"},{year=2022.0,eur=10.01,"country=SE"}}

The result I want to have is:

{"ES="["year="["2021=" {eur=1.0,"country=ES"},"2022=" {eur=1.0,"country=ES"}]],"SE="["year="["2021="{eur=9.98,"country=SE"},{eur=10.01,"country=SE"}]]}

The reason I want this is to be able to call the value I want based on dynamic fields like country and year, So i can do something like jsonData[country][year]['eur'] and get the fx rate i want for every value.

The best I can think of is this code:

function toJson3(data,indexer1,indexer2){
  var cols = data[0];
  var index1 = cols.indexOf(indexer1);
  var index2 = cols.indexOf(indexer2);
  var jsonData = {};
  for (i = 1; i < data.length; i  ) {
      if (!jsonData.hasOwnProperty(data[i][index1])) {
          jsonData[data[i][index1]] = {}; 
          jsonData[data[i][index1]][data[i][index2]] = [];
    }
    var jsonCandidate = {};
    for (j = 0; j < cols.length; j  ) {
        if (data[i][j] instanceof Date){
            jsonCandidate[cols[j]] = Utilities.formatDate(data[i][j], 'Europe/Madrid', 'yyyy-MM-dd');
        }else{
            jsonCandidate[cols[j]] = data[i][j];
      }
    }
    jsonData[data[i][index1]][data[i][index2]].push(jsonCandidate);
  }
  return jsonData;
}

But this fails saying that cannot read property 'push' of undefinded..

Thanks in advance for the help

CodePudding user response：

Your JSON doesn't look well. Probably you want something like this:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var [header, ...data] = range.getValues();
  var obj = {};

  while (data.length) {
    var [country, year, rate] = data.shift();
    try {
      obj[country][year] = {'eur': rate, 'country': country};
    } catch(e) {
      var y = {};
      y[year] = {'eur': rate, 'country': country};
      obj[country] = y;
    }
  }

  console.log(obj);
  console.log(obj['ES']['2021'].eur);
  console.log(obj['SE']['2022'].eur);
}

Sheet:

Output:

{ ES: 
   { '2021': { eur: 1, country: 'ES' },
     '2022': { eur: 1, country: 'ES' } },
  SE: 
   { '2021': { eur: 9.98, country: 'SE' },
     '2022': { eur: 10.01, country: 'SE' } } 
}

1

10.01

And probably you don't need the property 'country' after all.