Splitting and Trimming Columns in Google Sheets Script?

Question

I'm working on a Google Spreadsheet modification web application and currently have a column of values formatted like so "#, name" and I need to programmatically split the # and the name into separate columns using Google Scripts. The results are coming from Forms, which is why they need modified. I have it partially working but I'm stuck on how to get it to fully work. Here is what I am doing so far:

output = splitColumn(initialRange.getValues()); //Initial range is the column I"m working with
      m = 0;
      var ids= [];
      var names= [];
      for(k = 0; k <= output.length; k+=2)
      {
        ids[m] = output[k][0];
        m += 1;
      }
      m=0;
      for(n = 1; n < output.length; n+=2)
      {
        names[m] = output[n][0];
        m += 1;
      }

      schoolName = aSheet.getRange(2, i+2, aSheet.getLastRow());
      schoolName.setValue(names[0]);
      schoolIDs = aSheet.getRange(2, i+1, aSheet.getLastRow());
      schoolIDs.setValue(ids[0]);

...

function splitColumn(range) {
  var output = [];

  for(var i in range) {
    var split = range[i][0].split(",");
    if(split.length == 1) {
      output.push([split[0]]);
    } else {
      for(var j in split) {
        output.push([split[j]]);
      }
    }
  }
  return output;  
}

To see my overall objective, these two screenshots should show it.

Original Spreadsheet from Form: Formatted Version of Spreadsheet:

Either a modification to my existing code to make it work or suggesting a different approach would be appreciated. Current problems are actually setting the values, as it doesn't like the type I am using, and that the split does not get rid of the preceding white space between the "," and the "name" so my array has [space]name otherpartofname.

You can use Logger.log('my variable value: ' + varName) to print variable values to the LOG, then VIEW the LOG. Put the Logger.log() in key places to verify that you are getting the expected results. — Alan Wells
If the Google Form format is settled and this is the only wrangling you need to do, then you can do these manipulations direct in the form results sheet with no need to use Apps Script at all. — JSDBroughton
Right, I knew how to do those manipulations within the sheet. Thing is we are modifying 8+ different sheets and doing more than just this modification to it, so we decided a full web app with a few functions that do what we need would be best. Your statement is true though. — B-Stewart
Obviously the snippets we see on SO never tell the full story, but I ask only because i see overuse of GAS and VBA in place of straightforward formula work all the time. — JSDBroughton

JSDBroughton JSDBroughton · Accepted Answer · 2014-12-30T10:51:41

There are a few ways of doing this but modifying your existing code, you can split() using strings of more than single characters. Whatever you use is omitted.

var split = range[i][0].split(', '); // including space
// ['10', 'Africa High School'] — no white space

Other options would be regular expressions with matched groups like /(\d{1,2}),\s([A-Z\s]) but is overkill here but a good thing to learn about if this becomes a work pattern more frequently.

Splitting and Trimming Columns in Google Sheets Script?

2 Answers