CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
lmcc
lmcc

Reputation: 73

How can I convert this data set into an object

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

enter image description here

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,

For those that may need it. Following one of the answers, I realised it would be better if I had a situation where only the days unique to the year group were stored in the year group object.

Below is the adapted code based on one of the answers by 'TheWizEd'.

let values = ws.getDataRange().getValues();
    let data = [];
    values.shift();  // remove headers
    // create year groups
    let yearGroups = [...new Set(values.map( row => row[1] ))];
      
    yearGroups.forEach((group) => {
      let sorted = values.filter( row => row[1] ===  group )
      let days = [...new Set(sorted.map((row)=> row[4] ))]
      let dayBlankArray = days.map( day => [] )
      sorted.forEach((e)=>{
        let index = days.indexOf(e[4]);
        if( index >= 0 ) dayBlankArray[index].push(e[0]);
      })
      let object = { "Year Group": group };
      days.forEach((day,index)=> {
        object[day] = dayBlankArray[index]
        
      })
      data.push(object);
    })
    return data
}

Upvotes: 0

Views: 67

Answers (1)

TheWizEd
TheWizEd

Reputation: 8606

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 = days.map( day => [] );
        // 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 };
        let numDays = days.length;
        days.forEach( (day,index) => {
            if( activities[index].length === 0 ) {
              activities[index].push("No clubs on this day");
              numDays--;
            }
            object[day] = activities[index];
          }
        );
        object["Num Days"] = numDays;
        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

Upvotes: 2

Related Questions