Tuesday, January 4, 2011

User-Defined Functions

Note Added 05-FEB-2013: There is a full free chapter in PDF format on user-defined functions with lots more examples and discussion here @Leanpub


JavaScript Functions
  • All interaction with the Google App Scripting APIs requires them.
  • Very flexible and powerful.  Future postings should justify this statement.
  • First-class objects, they themselves have properties.
  • Create scope for variables and can create closures (v. powerful!).
  • Arguments can be of any type or number, JavaScript does not check whether the number of passed in arguments matches the parameter list.  
  • The length property of the arguments array-like object can be checked to ensure the correct number of arguments is passed in.  This technique is used in an example below.
  • VBA programmers, beware!  The parentheses are required even if the function takes no arguments.  Consider and compare the output displayed by a message box in the run_getDate() below.  The first instance displays a date string as expected but the second displays the function body, not its return value.  Functions, remember, are objects so the Browser.msgBox() call displays the string representation of the function object!

// Demonstrate the effect of ommitting the parentheses when invoking a
// function that has no parameters.
function run_getDate() {
 Browser.msgBox(getDate());
 Browser.msgBox(getDate); // Missing parentheses!
}


// Create a Date object and return the date as a string according to locale.
function getDate() {
 var today = new Date();
 
 return today.toLocaleDateString();
}
  • When functions are attached to objects, they are referred to as methods.


User-Defined Functions
  • Used to add customised functionality to the Google spreadsheet application.
  • Written in JavaScript and can be invoked from a spreadsheet cell in just the same manner as built-in functions using the =user-defined-function-name(arguments list) notation.
  • As with built-in functions, arguments can be passed as cell addresses or as literal values.
  • Cannot be used to display dialogs such as Browser.msgBox() or Brower.inputBox.
  • Cannot be used to set formats, e.g. the call “=changeMe(“A1”, “red”)” for the following function will fail:

// Given a cell address and a color, change the background colour of
// that cell on the active sheet.
// This function cannot be called as a user-defined function!!
function changeMe(cellAddress, bgColor) {
   var activeSpreadsheet = SpreadsheetApp.getActive();
   activeSpreadsheet.getRange(cellAddress).setBackgroundColor(bgColor);
}
  • The Google spreadsheet applications has a very large number of built-in functions, check to ensure a required function does not already exist before writing a customised version.
  • An oft-quoted and occasionally useful example that does some simple numeric manipulation and returns the result:

// Given a temperature in Celsius return its value in Fahrenheit
function celsius2Fahrenheit(celsius) {
   return celsius * 9 / 5 + 32;
}
  • Call this function from the spreadsheet by typing =celsius2Fahrenheit(<some numeric value>) or =celsius2Fahrenheit(<some cell address e.g. A1>) in a cell and get an answer, e.g. =celsius2Fahrenheit(37) returns 98.6 as expected.  


  • For completeness, here is the converse of the above:,fahrenheit2Celsius():

// Given a temperature in Fahrenheit, return its value in Celsius.
function fahrenheit2Celsius(fahrenheit) {
   return ( fahrenheit - 32 ) * 5/9;
}
  • Input can be generated by built-in functions and output can be processed by built-in functions.  For example, the relative standard deviation (RSD) is frequently cited in analytical chemistry measurements.  The function to calculate it is not provided by Google spreadsheets but the two values it needs, the standard deviation and the mean of a data set, are so it is easy to write ones own RSD function:


Sample Data For Testing RSD Function
measurements
19.81
18.29
21.47
22.54
20.17
20.1
17.61
20.91
21.62
19.17
// Given the mean and standard deviation,
// return the Relative Standard Deviation expressed as a percentage.
function RSD( stdev, mean ) {
   return 100 * (stdev/mean);
}
  • The RSD function can be called as “=ROUND(RSD(STDEV(A2:A11),AVERAGE(A2:A11)),2)
  • By nesting the calls to the built-in STDEV (standard deviation) and AVERAGE (mean) functions, their respective values are returned to the user-defined RSD function, which in turn passes its return value to the built-in ROUND() function to format the output to two decimal places.
  • String are equally amenable to user-defined function manipulations as numeric data.
  • Regular expressions are built in to JavaScript and are very a powerful tool for string manipulations.  More on these in a later entry!
  • Data in spreadsheets is often given as multi-value cells, e.g. “NA07346,NA11830,NA11832”.  This type of data, though frowned upon in relational databases, is frequently provided by reporting tools.  Here is a user-defined function that returns the number of single values, or elements, in a multi-valued input argument.

// Given a multi-valued input where values are separated by a delimiter, return the number // of elements in the multi-valued field.
function countElements(multiElementValue, delimiter) {
   return multiElementValue.split(delimiter).length;
}
  • It passes the delimiter string argument to  the split() method of the string object to generate an array of elements and then returns the length property of that array which is the number of elements in the array.
  • User-defined functions should be bug-tested.  Taking the example above, it gives erroneous counts for the following inputs:
    • Delimiter only: “,”, it reports 2
    • Multi-valued column with leading and/or trailing delimiter: “,NA22455,NA23144,”, it reports 4.
  • The problem with the above examples is that empty elements are counted.  A quick fix is to not count such empty elements.

// Given a multi-valued input where values are separated by a delimiter, return the number of elements
// in the multi-valued field.
// Check each element of the array returned by the split() method to ensure it contains a defined value.
function countElements(multiElementValue, delimiter) {
   var allElements = multiElementValue.split(delimiter);
   var validElementCount = 0;
   for ( var i = 0, allElementsLen = allElements.length; i < allElementsLen; i++ ) {
       if ( typeof(allElements[i]) !== 'undefined' ) {
           validElementCount++;
       }
   }
   
   return validElementCount;

}
Code Notes
  • The string split() method returns an array by splitting the string on the given delimiter.
  • Each element of the array is checked in a for loop by an if statement and only elements evaluating to true are counted.
  • The above version also correctly counts zeros ( 0 is considered false by JavaScript).
  • Here is a VBA version of the above function:

' User-Defined Function to count the number of elements in an input string
Function CountElements(cellValue As String, delimiter As String) As Integer
   Dim elements() As String: elements = Split(cellValue, delimiter)
   Dim validCount As Integer: validCount = 0
   Dim idx As Integer
   
   For idx = 0 To UBound(elements)
       If Len(elements(idx)) > 0 Then ' Only count elements that contain something
           validCount = validCount + 1
       End If
   Next idx
   
   CountElements = validCount
   
End Function

Example: Producing a Concatenated List From a Range Of Cells


Task: Write a user-defined function to concatenate a range of cells in a customised manner.  For concatenating a few cells, one can type, for example, =(A1 & “,” &  B1) but this is not feasible for for more than a few.  Provide the user with the option of surrounding each value in single quotes.  The motivation for this came from having to repeatedly check lists of strings or numbers against Oracle database tables. Of course one should use one should use a database API, such as ADO via VBA, for this but for situations where the potential range of databases, tables, and colums that need to be queried is large, then pasting a list into the IN() SQL operator of a WHERE clause in a TOAD editor window is a quick solution.


Input: Range of cells.


Output: Concatenated list of values separated by a user-defined delimiter with optional single quotes around each value.

JavaScript Code:
// Given a range of cells, return a single concatenated value with
// each value separated by the given delimiter
// with the option to enclose each element in single quotes.
function concatRng(rng, concatStr, addSingleQuotes) {
 var addSingleQuotesFunc = function(arrElement) { return "'" + arrElement + "'";}  
 
 if(addSingleQuotes) {
   var cellValues = rng.map(addSingleQuotesFunc); // Apply the function to
// each element of the array 'rng'
   return cellValues.join(concatStr);
 } else {
   return rng.join(concatStr);
 }
}
To Use

  1. Paste code into the script editor
  2. From the spreadsheet, type “=concatRng(A1:A4, “,”, true)” where the indicated range contains:

rat
cat
cow
dog
This gives the result 'rat','cat','cow','dog'.  A second example using a list of numbers where the single quotes are not want, =concatRng(A5:A7,",",false), produces the single value 1,2,3 where the cells of the given range contain these numbers


Code Notes
  • JavaScript automatically converts the given range address into an array of the values contained in the cells of that address.
  • String literal arguments such as delimiter above, must be passed in double quotes, using single quotes generates an error.  In JavaScript itself, strings can use either quote mark but only double quotes work for user-defined functions.
  • Using the length property of the special arguments object. it checks that at least three arguments were passed in, if there are less than three, throw an exception.  This is displayed on the spreadsheet as #ERROR! with the error message in the comment.
  • If the addSingleQuotes option is false, return a string where each element is separated by the delimiter argument using array object join() method.
  • If the addSingleQuotes option is true, apply the function named as addSingleQuotesFunc to each element of the array rng using its map() method to enclose each element in single quotes.  a string is then returned using the join() method of the resulting array.  This is a good demonstration of JavaScript functions as first class objects.


Here is a VBA version:
' Take a range of cells and concatenate the values into a single string, set the join char and an option to enclose each cell value in single quotes.
' Typical use is to generate a string of values for an IN operator of a WHERE clause in an SQL statement.
Function ConcatRng( rng As Range, concatStr As String, addSingleQuotes As Boolean ) As String
   Dim Cell As Range
   Dim arrCellValues() As String
   Dim idx As Long: idx = 0
  
  '  Loop through all the cells in the given range and add their values to an array
   For Each Cell In rng.Cells
       ReDim Preserve arrCellValues(idx)
       arrCellValues(idx) = Cell.Value
       idx = idx + 1
   Next Cell
   '  Enclose each array element in single quotes
   If addSingleQuotes Then
       For idx = 0 To UBound(arrCellValues)
           arrCellValues(idx) = "'" & arrCellValues(idx) & "'"
       Next idx
   End If
   
   ConcatRng = Join(arrCellValues, concatStr)
   
End Function

None of the functions shown so far manipulate any of the Google App Scripting APIs.  To create spreadsheet applications, it is necessary to use these APIs.  This will be the subject of the next and subsequent postings.

Note Added 2012-08-07: Added a blog entry entitled "User-Defined Functions Re-Visited: Use The Buit-Ins When They Are Provided" on this date that is relevant to this entry.

9 comments:

  1. wow, I never knew you could define custom functions using JS in Google spreadsheets... Of course, working for Resolver, I have to toe the corporate line and say python is the right answer ;-)

    always nice to meet a fellow spreadsheet enthusiast tho - look forward to future posts!
    Harry.

    ReplyDelete
  2. Hi Harry, thanks for your feedback. Python, nice one!

    Mick

    ReplyDelete
  3. do you have any fucntion where when it is called like

    =if(A2>"5",CallEmailFunction(),"Don't Call")

    so if CallEmailFunction is called it will send email to user with alert notification

    Thanks

    ReplyDelete
  4. I'll have a look in the next few days, I expect it's a feasible task so I'll give a try and post my findings.

    ReplyDelete
  5. I will be your slave jk..
    This is wht I'm starting off with but doesn't look good.

    function sendalert() {
    return MailApp.sendEmail("myEmailAddress@example.com","Subject", "Some Message Here");


    }

    ReplyDelete
    Replies
    1. I've posted a piece on email from Google spreadsheet, have a look.

      Delete
  6. Hi there,

    Great post!

    Just curious, does the function know which cell it's being called from? Is there a way to get this information?

    Cheers

    ReplyDelete
  7. Hi Webmaster, I think not. When you call a user-defined function from the spreadsheet, it is limited to inputs and outputs and I cannot see how the details of the cell from which it's called would be useful to it. However, if you invoke it from the script editor or using a button, then you can easily get details on what Excel calls the active cell. Here's a very simple function that displays the address of the active cell:
    function getActiveCellAddress() {
    Browser.msgBox( SpreadsheetApp.getActiveRange().getCell(1, 1).getA1Notation());
    }

    Not sure if that answers your question though.

    ReplyDelete
  8. CORRECTION to my own code, getting the active cell is much easier than the code in my previous comment:

    function test() {
    var activeCellAddress = SpreadsheetApp.getActiveSpreadsheet().getActiveCell().getA1Notation()
    Logger.log(activeCellAddress);
    }

    ReplyDelete