How do I translate this google sheet data structure into an object?

Liam McCormick 0
Viewed 42

I am looking to convert a set of data in this format into an object that I can use to help update google form choices. However, I am stumped.

As per a comment below, I would like to add some clarity. The values in row 1 define a year group and until another number appears in row 1, all columns belong to that year group.

However, the only data that needs to be returned for each day (row 2) is the column that contains 'Form Display'.

Columns containing 'capacity' and '#registered' are to be ignored and there may be some values underneath those letters.

Here is the google sheet.

enter image description here

I would like this to be converted into an object as follows:

[
  {'Year group': Year 5,
   'Sunday': ['Girls football', 'Mixed Gaelic football', 'Science experiments', 'library club'...],
   'Monday': ['Boys football','Netball],
   'Wednesday': ['Taekwondo', 'School choir', 'Mixed basketball', 'Lego', 'Board games']
},
{'Year group': Year 6,
   'Sunday': ['Girls football', 'mixed gaelic football', 'science experiments', 'library club'...],
},

Thank you!

google-sheets google-apps-script
1 Answers

Here is a protype of a script that will create an object from the data set shown in the OP.

I am basically using the row 1 yearLabels to determine the year group and row 2 dayLabels to determine the day and simply walking through each column to see what the "header" is. If there is extraneous data beyond the last year group I didn't test for errors. In my look ahead if( dayLabels[column+1] ) I'm looking to see if it is undefined.

function testObject() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("TestData");
    let values = sheet.getDataRange().getValues();
    let yearGroups = [];
    let yearLabels = values.shift(); // get the first row, year labels
    let dayLabels = values.shift(); // get the second row, day labels
    let yearGroup = null;
    let day = null;
    yearLabels.forEach( (label,column) => {
        if( label !== "" ) {
          if( yearGroup ) yearGroups.push(yearGroup);
          yearGroup = { "Year group": label };
          day = dayLabels[column];
          yearGroup[day] = [];
        }
        else if( dayLabels[column] === "Form Display") {
          values.some( row => {
              if( row[column] !== "" ) {
                yearGroup[day].push(row[column]);
                return false;
              }
              else {
                return true;
              }
            }
          );
          // look ahead to see if there is another day
          if( dayLabels[column+1] ) {
            if( yearLabels[column+1] === "" ) {
              day = dayLabels[column+1];
              yearGroup[day] = [];
            }
          }
        }
      }
    );
    // need to store the last yearGroup
    yearGroups.push(yearGroup);
    console.log(JSON.stringify(yearGroups));
  }
  catch(err) {
    console.log(err);
  }
}

Execution log

6:51:02 AM  Notice  Execution started
6:51:04 AM  Info    [{"Year group":"Year 5","Sunday":["Girls Football","Mixed Gaelic Football","Science Experiments","Library Club","Rapid Maths"],"Tuesday":["Boys Football","Netball"],"Wednesday":["Taekwondo","Shool Choir","Mixed Basketball","Lego","Board Games"]},{"Year group":"Year 6","Sunday":["Girls Football","Mixed Gaelic Football","Science Experiments","Library Club","Newspaper Club/Reading Club","Rapid Maths"]}]
6:51:03 AM  Notice  Execution completed

References

TheWizEd0
Related