- 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!).
- 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!
- When functions are attached to objects, they are referred to as methods.
- Used to add customised functionality to the Google spreadsheet application.
- 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:
- 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:
- 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():
- 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
- 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.
- 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.
- 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.
- 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.
- Here is a VBA version of the above function:
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.
- Paste code into the script editor
- From the spreadsheet, type “=concatRng(A1:A4, “,”, true)” where the indicated range contains:
- 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.
Here is a VBA version:
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.