CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
danedelions_swift
danedelions_swift

Reputation: 125

Google Sheets: Group by and Format from list of data

I have a Google Sheet that I want to format. The sheet contains multiple rows with unique values. enter image description here

I want to be able to format the data into this: enter image description here

I have tried using query but I am only successful in getting the student id and am unable to for the rest of the data.

In my sample sheet, I filled out the formatted table without formula.

Here is the link to my sheet: https://docs.google.com/spreadsheets/d/1lebcSlt-k7-Ub71n7iGq9pZFmM9q4fHPLpw4pAvXAs4/edit?usp=sharing

Upvotes: 0

Views: 481

Answers (1)

NightEye
NightEye

Reputation: 11214

To merge the cells, you will need a script. See the script below that also outputs the data and formatting:

Script:

function populateMasterSchedule() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var scheduleReport = spreadsheet.getSheetByName('Schedule Report');
  var outputSheet = spreadsheet.getSheetByName('MASTER SCHEDULE');
  // filter rows with student id starting from row 2
  var dataRows = scheduleReport.getDataRange().getDisplayValues().slice(1).filter(row => row[3]);
  var lastStudent, lastMerge = 0, startRow = 3, counter = 1, output = [];

  dataRows.map((row, index) => {
    // if current row's student id is not equal to previous or the last row
    if((index != 0 && lastStudent != row[3]) || index == dataRows.length - 1) {
      // need adjustments when it comes to the last iteration, add 1 row to merge
      if (index == dataRows.length - 1) 
        index++;
      
      // merge first 6 and the 8th columns vertically
      outputSheet.getRange(lastMerge + startRow, 1, index - lastMerge, 6).mergeVertically();
      outputSheet.getRange(lastMerge + startRow, 8, index - lastMerge, 1).mergeVertically();
      
      lastMerge = index;
      counter++;
    }
    lastStudent = row[3];
    output.push([counter, row[3], row[1], row[2], '', row[9], '', '', row[7] + ', ' + row[8], 
                 row[6], '', '', '', '', '', '', '', row[12]]);
  });

  var outputRange = outputSheet.getRange(startRow, 1, output.length, output[0].length);
  outputRange.setValues(output);
  outputRange.setBorder(true, true, true, true, true, true);
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
    .addItem('Populate Master Schedule', 'populateMasterSchedule')
    .addToUi();
}

Output:

output

Changes:

  • The original spreadsheet used 'MASTER SHEDULE' instead of 'MASTER SCHEDULE'. The script above uses the corrected name which is the latter. Just be sure to fix your sheet name so the script will capture the sheet properly.
  • I have merged the last and first names vertically as it makes sense rather than leaving out succeeding rows to be blank like your sample output above.
  • I have left out class week, class frequency and university enrollment columns as blank as I still have no idea where they are gotten from.

Notes:

  • Read the reference below on how to set it up.
  • After setting it up, refresh the spreadsheet.
  • After refreshing/reopening the spreadsheet, just use the custom menu I added to the menu to trigger the function (as shown in the gif above).

References:

Upvotes: 1

Related Questions