Data Shaping in Google Spreadsheet – Generating New Rows from Multiple Values in a Single Column

Today Tony Hirst posted Data Shaping in Google Refine – Generating New Rows from Multiple Values in a Single Column. Coincidently I faced a similar problem today working with a Google Spreadsheet so thought I’d share my solution.
Here’s a Google Spreadsheet where I’ve been capturing some MOOC infrastructure (contract me if you’d like to contribute/edit). For the technology column I’ve just been dumping in comma separated terms.

Source spreadsheet

Having entered some data I started thinking about how it could be presented. One idea was to use the Smile timeline widget, which I’ve experimented with before here. To get the data in the right shape I need to split the technologies to have one per row. To do this I wrote the custom function below in Google Apps Script which splits the technology into one per row (I’m undecided now if I’ll still use this for a timeline – it’s another case of it’s taken longer to write this up than actually do it)

Reshaped spreadsheet

// splitColumnAndRepeatRows iterates row by row in the input range and returns an array spliting
// by a comma separated column
// Arguments:
//   - anArray: values[][]
//   - splitColumnIndex: specifies the column number where the cell values need to be comma split
//                       0 indexed
// Returns an [][] Array of values.
function splitColumnAndRepeatRows(anArray, splitColumnIndex) {
  var output = [];
  for (i in anArray){ // for each row
    var splitArray = anArray[i][splitColumnIndex].split(","); // split values in specified column
    for (j in splitArray){ // for each split cell value
      var row = anArray[i].slice(0); // take a copy of source row
      row[splitColumnIndex] = alltrim(splitArray[j]); // replace comma separated value with current split value
      output.push(row); // push new row to output
    }
  }
  return output;
}
function alltrim(str) {
  return str.replace(/^\s+|\s+$/g, '');
}

Update: added an alltrim function to remove whitespaces

chevron_left
chevron_right

Join the conversation

comment 6 comments
  • Sam

    Martin, this is great, and something I would love to incorporate into an e-health project I’m working on at the moment. For my dissertation this summer, I’m creating a website to study how people with Coeliac disease use the web and smartphones to organise their lives, and at the moment am pooling data from various APIs to create a locations database that I am pulling into the prototype website and android app.
    I would like to do something similar to what you are doing, and separate the data in the address column (‘D’) into individual columns, so that the street address, city, postcode and country are each in their own columns (see below – can I use your script to do this?)
    A sample of my spreadsheet:
    https://docs.google.com/spreadsheet/ccc?key=0Al135Uzr-xDZdDNoZlNQSTROWDJ5NDVyRjBqZWVlTGc
    The prototype Android app (sorry, only London at the moment – if I can crack the above, i’ll be loading lots more towns and cities): https://play.google.com/store/apps/details?id=com.shoutem.n690303
    Website: http://www.givemeglutenfree.com

    • Martin Hawksey

      Sure re-use away 🙂 I’ve tried a couple of times to add it to the Script Gallery so that anyone can freely use it but keep getting a server error
      Martin

  • Dan

    This script looks like it will do exactly what I’m looking for for a project I’m working on. However, I’m a newb to scripts and I’m not exactly sure how to take the script you have above and point it to the data on my sheet and then how to point it to my destination. I know that is very basic, I’m just not able to do it. If there is anyway you could share the script with me that is functioning on the spreadsheet, I can figure out how to change locations and destinations on my sheet I believe and be good to go. Would you be willing to do that?
    Thanks!
    Dan

Comments are closed.

css.php