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
Creating a class roster in Google Spreadsheets from a split result
google spreadsheets;google apps script
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.Codefunction 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.ExampleSee example file I prepared: ESP classes row split example