Creating Custom Spreadsheet Functions

Home >> Spreadsheet >> Creating Custom Spreadsheet Functions
This page is for developers and is not the information on how to create a macro using javascript. Last Updated (December 4th 2011)

They are very simple in reality. any function you write in javascript that follows the formatting can be used

function className_functionName(arguments){
  //function contents
  // return value
}

for example here is the math.pow() function

function math_pow (base,exponent) {
  var output = base;
  for (var i = 1; i < exponent; i ++) {
    output = output*base;
  }
  return output;
}

it follows the className_functionName style and thus if I call =math.pow(2,2) in the spreadsheet I would get a value of 4 returned. But now what about functions that dont have a set number of arguments. Well because java functions dont need set numbers of arguments you can code this the exact same way. Lets look at the sum() function

function default_sum() {
  var output = 0;
  for (var i =0; i < arguments.length; i++){
    output += arguments[i];
  }
  return output;
}

this function does not care about how many arguments it is given. It does a for loop to parse all of them and the returns the answer it is designed to, in this case it is the sum of all the arguments

Also notice how the className for sum() is default. this means that I don't have to call =default.sum() i can just call =sum() and it will add the default class for me.

Commenting and Documenting Custom Functions

I have recently been working on a python file that will read in the javascript file and format it by adding forgotten comments and turning it into a html file that can be browsed. The standard comment block looks like

/********************************* MATH - POW *********************************\
| Alternate Arguments:                                                         |
| Example: math.pow(2,3) returns 8                                             |
| Summary: This is the exponential function, you take the first number and you |
|   raise it to the exponent of the second number                              |
\******************************************************************************/

If the python file does not find a block comment before a function it will add one of this style.

/****************************** CLASS - FUNCTION ******************************\
| Alternate Arguments:
| Example:
| Summary:
|
\******************************************************************************/

If there is a comment above the function of this style then the auto-parser will read the contents and format the resulting html page properly.

Title of comments

The auto generator is not super smart and cannot predict what your function's full name is from its actual name. For example function math_min() is actually the function Minimum. This should be changed

Automatically Created Header
/********************************* MATH - MIN *********************************\

in your temp lated comment header, you should change it to its actual name to help users when they are searching for it.

Header with real Function name
/******************************* MATH - MINIMUM *******************************\

Arrays

[stub]
=min(B0:B10) this would pass every value of B0 through B10 into the function min()

if a function took in an array instead of a group of arguments and array could be created using brackets

=cross([B0:B10],[C0:C10])

this would pass in two arrays, B0 to B10 and C0 to C10.