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];

  else
    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));
          
  else
    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));

/*
Results:
1
[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)
*/

1 comment:

  1. It is very helpful and informative blog post. I would like to thankful to you providing such a information I have also have website providing very good information
    internship for web development | internship in electrical engineering | mini project topics for it 3rd year | online internship with certificate | final year project for cse

    ReplyDelete