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.
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)
// 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
Last updated by at .
Loading...
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
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
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
Hi Dan if you open https://docs.google.com/spreadsheet/ccc?key=0AqGkLMU9sHmLdFNhMmM5ZEJ1Qk90bFBicXpwbmF2MWc#gid=1 and File > Make a copy for a functioning copy
Of course… :) I missed that it’s controlled by a in-cell formula, I’ve got it working exactly what I need! Thanks so much!!