What is a custom function, what it isn’t and limitations

A custom function is the same as an Excel formula, i.e. =left(), =mid(), etc..  except that you’re going to be making your own.

Custom functions cannot:

  • Take longer than 30 seconds to execute
  • Change the value of other cells
  • Manipulate the UI
  • Cannot refresh on their own

Custom functions should:

Best Practices

  • Stick to all caps to follow the common naming convention of existing formulas, ex: MID, LEN, IF
  • When you’re writing private functions (functions that aren’t going to be available to the front end), add an underscore before the function name (ex: _sayHello()).  This just helps other people know that these functions aren’t meant to be used in the sheet – it’s just a JavaScript convention and not a hard rule.
  • Use batch operations when possible, try to avoid changing values of an array of cells within a loop as an example.  Try to write out all the values once your calculations are done.

Understanding data types from input within a spreadsheet

In a simple custom function that takes in the value of a cell, we’re generally going to be receiving a string or a number. Take the following for example:

//cell A1 contains the word "hello"
//we'll pass in "hello" to the custom function =SayHello(a1)
//"hello" is now equal to dataFromCellA1


function SayHello(dataFromCellA1) {
Logger.log(typeof dataFromCellA1) // Logs "string"
}

Easy.  What happens when we want to use this custom function with multiple cells (arrays)? We’re going to get an object, in fact, this is a 2d array.

//cell A1 contains the word "hello" and cell A2 contains the word "world"
//we'll pass in "hello" and "world" to the custom function =SayHello(a1:a2)
//"hello" and "world" is now equal to dataFromCellA1andA2
 
 
function SayHello(dataFromCellA1andA2) {
Logger.log(typeof dataFromCellA1andA2) // Logs "object"
Logger.log(Array.isArray(dataFromCellA1andA2)) // Logs "[[hello], [world]]"
Logger.log(dataFromCellA1andA2) // Logs "TRUE"
}

As long as you understand that a range of cells is going to produce a 2d array, you can then accommodate by adjusting your code to loop through the arrays.

Creating the autocomplete / instructions for the user (JSdoc)

Google Sheets allows you to use certain portions of JSdoc to create the autocomplete for the formula within the spreadsheet. There are a few things that don’t seem to work (example: optional arguments). But, for the most part you’ll be able to annotate your custom functions and have a handy help menu appear in the spreadsheet.

Take the following code as an example:

 
/**
* Returns a concatenated string
*
* @param {"hello"} string1 first word 
* @param {"world"} string2 second word
* @return returns a concatenated string from 
* @customfunction
*/

function CONCATSTRING(string1,string2) {
 return string1 + string2 // returns "helloworld" 
}

In the spreadsheet, the formula will now be added into autocomplete and also displays a great help menu:

Feel free to make a copy of the spreadsheet with the code above to see what’s going on and try it for yourself.

Go ahead, write your first script!

Head to a Google Spreadsheet and try writing your first script by going to the Tools > Script Editor menu.

Save it, give it the necessary permissions, and try running it from the sheet.