I want to write a google app script to build a multi-level JSON

Question

I have a table like this:

country	year	fx_rate
ES	2021	1
ES	2022	1
SE	2021	9.98
SE	2022	10.01

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

user_14265469 · Answer

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.

I want to write a google app script to build a multi-level JSON

1 Answers