Question 298921 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Convert list of lists to JSON

I have some data in a Google sheet that I want to convert to a .json file using Google Apps Script, however I am struggling to figure out the best way to approach it. I have an implementation already that successfully converts data from sheets into a .json but doesn’t achieve the structure that I want.

The structure I want in my .json is as follows:
{ "name": "Adam", "options": { "option1": True, "option2": True, "option3": False }, "data": [ "data1", "data2", "data3" ] }

When I read the raw data from Google sheets using:
var data = sheet.getDataRange().getValues()

I get this stored in data:
[[name, Adam, ], [options, option1, true], [, option2, true], [, option3, false], [data, data1, ], [, data2, ], [, data3, ]]

The empty items in the lists are there as some rows don’t extend to the full range of the data, this is intentional as the number of columns on each row indicate the ‘level’ or ‘nest’ of the .json structure.

I cannot figure out a way to convert this list of lists to the desired .json format, I figured a recursive function could be sufficient but for the life of me cannot figure it out.

I’ve tried various ways to looping through the loop and checking for conditions, where it’ll create a dictionary or list depending on the surrounding data but can’t figure out an algorithm that can scale to bigger ranges of data.

Tags:

2

Answers


  1. 0

    From your showing sample value and question, I guessed that your Spreadsheet might be in the following situation.

    enter image description here

    And, you want to retrieve the object {"name":"Adam","options":{"option1":true,"option2":true,"option3":false},"data":["data1","data2","data3"]} from the above Spreadsheet.

    If my understanding is correct, how about the following sample script?

    Sample script:

    function myFunction() {
  const sheetName = "Sheet1"; // Please set your sheet name.
  const data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getDataRange().getValues();

  const { res } = data.reduce((o, [a, b, c]) => {
    if (a && a.toString() != "") {
      o.temp = a;
    }
    if (typeof c === "boolean" || (c && c.toString() != "")) {
      o.res[o.temp] = o.res[o.temp] ? { ...o.res[o.temp], [b]: c } : { [b]: c };
    } else {
      o.res[o.temp] = o.res[o.temp] ? [...o.res[o.temp], b] : (o.temp == "name" ? b : [b]);
    }
    return o;
  }, { res: {}, temp: "" });
  console.log(res);
}

    When this script is run, when the value of data is [["name","Adam",""],["options","option1",true],["","option2",true],["","option3",false],["data","data1",""],["","data2",""],["","data3",""]], {"name":"Adam","options":{"option1":true,"option2":true,"option3":false},"data":["data1","data2","data3"]} is returned.

    Testing:

    When this script is tested, the following result is obtained.

    const data = [["name","Adam",""],["options","option1",true],["","option2",true],["","option3",false],["data","data1",""],["","data2",""],["","data3",""]];
const { res } = data.reduce((o, [a, b, c]) => {
  if (a && a.toString() != "") {
    o.temp = a;
  }
  if (typeof c === "boolean" || (c && c.toString() != "")) {
    o.res[o.temp] = o.res[o.temp] ? { ...o.res[o.temp], [b]: c } : { [b]: c };
  } else {
    o.res[o.temp] = o.res[o.temp] ? [...o.res[o.temp], b] : (o.temp == "name" ? b : [b]);
  }
  return o;
}, { res: {}, temp: "" });
console.log(res);

    Reference:

    Login or Signup to reply.
  2. 0

    I assumed data as follows:

    enter image description here

    GS:

    function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const [hs, ...vs] = sh.getDataRange().getValues();
  const idx = hs.reduce((a, h, i) => (a[h] = i, a), {});
  const obj = vs.reduce((a, r, i) => {
    if (!a.hasOwnProperty(r[idx["Name"]])) {
      a[r[idx["Name"]]] = [{ name: r[idx["Name"]], options: { option1: r[idx["Option1"]], option2: r[idx["Option2"]], option3: r[idx["Option3"]] }, data: { data1: r[idx["Data1"]], data2: r[idx["Data2"]], data3: r[idx["Data3"]] } }];
    } else {
      a[r[idx["Name"]]].push({ name: r[idx["Name"]], options: { option1: r[idx["Option1"]], option2: r[idx["Option2"]], option3: r[idx["Option3"]] }, data: { data1: r[idx["Data1"]], data2: r[idx["Data2"]], data3: r[idx["Data3"]] } })
    }
    return a;
  }, {});
  Logger.log(JSON.stringify(obj).replace(/],/, '],n'));
}

    JSON:

    {"A2":[{"name":"A2","options":{"option1":true,"option2":false},"data":{"data1":"E2","data2":"F2","data3":"G2"}},{"name":"A2","options":{"option1":false,"option2":true},"data":{"data1":"E7","data2":"F7","data3":"G7"}}],
"A3":[{"name":"A3","options":{"option1":false,"option2":true},"data":{"data1":"E3","data2":"F3","data3":"G3"}},{"name":"A3","options":{"option1":false,"option2":false},"data":{"data1":"E8","data2":"F8","data3":"G8"}}],"A4":[{"name":"A4","options":{"option1":false,"option2":false},"data":{"data1":"E4","data2":"F4","data3":"G4"}},{"name":"A4","options":{"option1":false,"option2":true},"data":{"data1":"E9","data2":"F9","data3":"G9"}}],"A5":[{"name":"A5","options":{"option1":false,"option2":true},"data":{"data1":"E5","data2":"F5","data3":"G5"}},{"name":"A5","options":{"option1":true,"option2":false},"data":{"data1":"E10","data2":"F10","data3":"G10"}}],"A6":[{"name":"A6","options":{"option1":true,"option2":false},"data":{"data1":"E6","data2":"F6","data3":"G6"}}]}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search