Extract portions of Google Docs form data and write it on separate lines

Question

Let's say I have a Google Docs Form that gathers the following info:

• Timestamp (default field)
• Names
• Ref#

The form data then appears on the spreadsheet as follows:

4/10/2013 16:20:31  |  Jack, Jill, Oscar  |  Ref6656X

(Note: the number of names may be anywhere from 1 to many)

I need the data to appear on the spreadsheet as follows:

4/10/2013 16:20:31  |  Jack  |  Ref6656X
4/10/2013 16:20:31  |  Jill  |  Ref6656X
4/10/2013 16:20:31  |  Oscar |  Ref6656X

I can often decipher and edit Google Apps Script (JavaScript?), but I don't know how to think in that language in order to create it for myself (especially with an unknown number of names in the Name field). How can I get started on solving this?

Answer 1

First of all, you've got some choices to make before you start writing your code.

• Do you want to modify the spreadsheet that's accepting form input, or produce a separate sheet that has the modified data? If you want to have a record of what was actually input by a user, you'd best leave the original data alone. If you're using a second sheet for the massaged output, the presence of multiple tabs might be confusing to your users, unless you take steps to hide it.

• Do you want to do the modifications as forms come in, or (in bulk) at some point afterwards? If you already have collected data, you'll have to have the bulk processing, and that will involve looping and having to handle insertions of new rows in the middle of things. To handle forms as they come in, you'll need to set up a function that is triggered by form submissions, and only extend the table further down... but you've got more learning to do - see Container-Specific Triggers, Understanding Triggers and Understanding Events for background info.

• Will you primarily use Spreadsheet service functions, or javascript Arrays? This choice is often about speed - the more you can do in javascript, the faster your script will be, but switching between the two can be confusing at first.

Here's an example function to do bulk processing. It reads all existing data into an array, goes through that and copies all rows into a new array, expanding multiple names into multiple rows. When done, the existing sheet data is overwritten. (Note - not debugged or tested.)

function bulkProcess() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataIn = ss.getDataRange().getValues();
  var dataOut = [];

  for (var row in dataIn) {   // Could use:  for (var row = 0; row < dataIn.length; row++)
    var names = dataIn[row][1].split(','); // array of names in second column
    var rowOut = dataIn[row];
    for (var i in names) {
      rowOut[1] = names[i];  // overwrite with single name
      dataOut.push(rowOut);  // then copy to dataOut array
    }
  }
  // Write the updated array back to spreadsheet, overwriting existing values.
  ss.getRange(1,1,dataOut.length,dataOut[0].length).setValues(dataOut);
}