Tuesday, September 21, 2010

Array Function to Recode Data in Google Apps Scripts

Google Docs IconI went on my honeymoon with my beautiful wife last week and the week before. Having a little time off of work gave me the opportunity to get some work done :). I've been wanting for a while to develop a survey to find out what makes a good programmer.

I've been working with Google Docs and Google Forms to see what they're capable of. This spreadsheet posed a few difficulties. The primary problem was that I had a set of text values which needed to be recoded to numerical values from another range.

I wrote this array function for Google Apps Scripts in Google Spreadsheets to recode values based on an array of values.

Here's an example spreadsheet demonstrating the Array Data Block Recode Function.

Here's what the function looks like with a few tests:
function recode(data, values, valueColumnIndex)
  var  valueHash = {};
  // if the values are in an array, make a hash table
  if (values.constructor == Array)
    for (var i = 0; i < values.length; i++)
      valueHash[values[i][0]] = values[i][valueColumnIndex];

    valueHash = values;
  var ret = [];
  // if the data are in an array, recursively recode them
  if (data.constructor == Array)
    for (var i = 0; i < data.length; i++)
      ret.push(recode(data[i], valueHash, valueColumnIndex));
    ret = valueHash[data] != undefined ? valueHash[data] : data;

  return ret;

var values = [['a', '1', 'I'], ['b', '2', 'II']];

print(recode('a', values, 1));
print(recode(['a', 'b', 'c'], values, 1));
print(recode([['a', 'b'], ['b', 'c']], values, 1));
print(recode(['a', ['a', 'b'], [['a', 'b', 'c']]], values, 2));

[1, 2, 'c']
[[1, 2], [2, 'c']]
['I', ['I', 'II'], [['I', 'II', 'c']]]

Google Apps Syntax:
=Recode(A1:B3, D1:F2, 1)
=Recode(A1:B3, D1:F2, 2)

No comments:

Post a Comment