How can I convert this data set into an object-CodePudding

I would like to convert this data set into an object.

Intended outcome

let data = [
  {
    "Year Group": "Year Six",
    "Sunday": [Girl's football],
    "Monday": [No clubs on this day],
    "Tuesday": [Girls Netball] 
  },
  {
    "Year Group": "Year Five",
    "Sunday": [Boys football],
    "Monday": [No clubs on this day],
    "Tuesday": [Girls Netball]
}
]

This data will then be used to create a branching Google form. If there is a better object structure in order to achieve this, please do let me know.

Thank you,

CodePudding user response：

Here is an example of how to sort and create groups by year.

Code.gs

function createGroups() {
  try {
    let days = ["Sunday","Monday","Tuesday","Wednesday"];
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Sheet1");
    let values = sheet.getDataRange().getValues();
    let data = [];
    values.shift();  // remove headers
    // create year groups
    let groups = [...new Set(values.map( row => row[1] ))];
    console.log("groups = " groups)
    groups.forEach( group => {
        let activities = [[],[],[],[]];
        // sort by group
        let sorted = values.filter( row => row[1] ===  group );
        // nest sort by day
        sorted.forEach( activity => {
            let index = days.indexOf(activity[4]);
            if( index >= 0 ) activities[index].push(activity[0]);
          }
        );
        // now construct the object
        let object = { "Year Group": group };
        days.forEach( (day,index) => {
            if( activities[index].length === 0 ) {
              activities[index].push("No clubs on this day")
            }
            object[day] = activities[index];
          }
        );
        data.push(object);
      }
    );
    console.log(data);
  }
  catch(err) {
    console.log(err)
  }
}

Execution log

9:34:27 AM  Notice  Execution started
9:34:28 AM  Info    groups = Year Five,Year Six,Year Seven,Year One,Year Two
9:34:28 AM  Info    [ { 'Year Group': 'Year Five',
    Sunday: [ 'Girls Football', 'Boys Football' ],
    Monday: [ 'No clubs on this day' ],
    Tuesday: [ 'Girls Netball' ],
    Wednesday: [ 'No clubs on this day' ] },
  { 'Year Group': 'Year Six',
    Sunday: [ 'Girls Football' ],
    Monday: [ 'No clubs on this day' ],
    Tuesday: [ 'Girls Netball' ],
    Wednesday: [ 'No clubs on this day' ] },
  { 'Year Group': 'Year Seven',
    Sunday: [ 'Girls Football', 'Boys Football' ],
    Monday: [ 'No clubs on this day' ],
    Tuesday: [ 'Girls Netball' ],
    Wednesday: [ 'No clubs on this day' ] },
  { 'Year Group': 'Year One',
    Sunday: [ 'No clubs on this day' ],
    Monday: [ 'No clubs on this day' ],
    Tuesday: [ 'Multi Sports' ],
    Wednesday: [ 'Gymnastics' ] },
  { 'Year Group': 'Year Two',
    Sunday: [ 'No clubs on this day' ],
    Monday: [ 'No clubs on this day' ],
    Tuesday: [ 'Multi Sports' ],
    Wednesday: [ 'Gymnastics' ] } ]
9:34:28 AM  Notice  Execution completed

Reference