Google Sheets – Creating Class Roster from Split Results

google sheetsgoogle-apps-script

A lecturer from the European School of Physiotherapy in Amsterdam stumbled on a particular script I prepared, hoping it would resolve a problem he had.

After having had a little chit chat, it turned out that he wanted to have a class roster created, that needs to be exported into another program.

This is how the input sheet looks like (example file, Input sheet):

|     A    | ... |      G      |            H            |
|Opleiding | ... | Docent      | Klas                    |
|EPS       | ... | bakjj       | LP13-23                 |
|EPS       | ... | bakjj       | LP13-21;LP13-22;LP13-23 |
|...       | ... | ...         | ...                     |
|EPS       | ... | bakjj;elstm | LP13-40                 |

and his is how it needs to be (example file, Output 2 sheet):

|     A    | ... |      G      |            H            |
|Opleiding | ... | Docent      | Klas                    |
|EPS       | ... | bakjj       | LP13-23                 |
|EPS       | ... | bakjj       | LP13-21                 |
|          |     |             | LP13-22                 |
|          |     |             | LP13-23                 |
|...       | ... | ...         | ...                     |
|EPS       | ... | bakjj       | LP13-40                 |
|          |     | elstm       |                         |

How is that done?

See the first two sheets in the example file I prepared: ESP classes row split example

Best Answer

At first, I used plain old formula's to get the job done, see Output 2:

=JOIN(CHAR(10),SPLIT(Input!H3,";"))

Looking at the Output 2 sheet, it closely resembles the desired outcome. But, the outcome needs to have the empty lines in between !!

Then I revered to Google Apps Script.

Code

function myOutput3(range) {
  // create array 
  var output = [];

  // add header to array
  output.push(range[0]);

  // itterate through rows
  for(var i=1, iLen=range.length; i<iLen; i++) {

    // capture split results
    var spl7 = range[i][7].split(";"), spl6 = range[i][6].split(";"); 
    var len7 = spl7.length, len6 = spl6.length;
    if(len7 == 1 && len6 == 1) {
      output.push(range[i]);
    } else {
      // determine which column has the highest length
      var tLen;     
      if(len7 >= len6) {
        tLen = len7;
      } else {
        tLen = len6;
      }
      // prepare output array      
      for(var j=0; j<tLen; j++) {
        if(j == 0) {
          var arr = range[i];
          arr[arr.indexOf(range[i][7])] = spl7[j];
          arr[arr.indexOf(range[i][6])] = spl6[j];
          output.push(arr);          
        } else {
          output.push([,,,,,,spl6[j],spl7[j]]);
        }
      }        
    }
  }  
  return output;  
}

This custom function will do the trick, without any API calls.

Example

See example file I prepared: ESP classes row split example

Related Topic