Google Apps Script

Custom Functions in Spreadsheets

A custom function is a function that is called directly from a cell in a Spreadsheet using the syntax =myFunctionName().

  1. Writing a Custom Function
  2. Using a Custom Function in a Spreadsheet
  3. Permissions and Custom Functions

Writing a Custom Function

Custom functions generally follow this format:

function customFunctionName(arg1, arg2, ..., argN) {
  // The code for your custom function goes here
  ...
  // The value you return here will be displayed in the cell in the Spreadsheet
  // in which the custom function is entered
  return someValue;
}

Here is a step-by-step example of how to write a custom function that converts a number from inches to millimeters.

  1. Since custom functions are only applicable to scripts that are bound to a Spreadsheet, you must first open or create a Spreadsheet from Google Drive, and then open the Script Editor from Tools > Script Editor.
  2. Copy and paste the following code into your script, and then click the Save icon and give your script a name.
function in2mm(inNum) {
  // Function to convert from INCHES to MILLIMETERS
  var outNum = 0;     // this will hold the answer
  var factor = 25.4;  // multiply input by this factor to get output
  if (typeof inNum != "number") {  // check to make sure input is a number
    throw "input must be a number";  // throw an exception with the error message
  }
  outNum = inNum * factor;  // calculate the answer
  return outNum;  // return the answer to the cell which has the formula
}

Now that you've created a custom function, let's analyze line-by-line what the script is doing.

function in2mm(inNum) {
  // code
}
This is how you create a JavaScript function, which will be a custom Spreadsheet function named in2mm. Notice that when you are creating a function which is intended to be called as a custom Spreadsheet formula, you tell the function to accept some input value. The variable named inNum is doing that above. This function will accept one input value which will be referenced through the variable called inNum. When creating scripts to be used as custom Spreadsheet functions, keep in mind that these are not intended to be run independently, but instead through some formula entered into a Spreadsheet cell.

The code presented for this function requires nothing beyond basic JavaScript — no special Spreadsheet objects or methods from the Google Apps Script are used.

  var outNum = 0;
The code above creates a variable called outNum (initially set to 0) to hold the final answer which will be returned to the spreadsheet cell.

  var factor = 25.4;
The code above sets the conversion factor of inches to millimeters into a variable called factor.

  if (typeof inNum != "number") {  // check to make sure input is a number
    throw "input must be a number";  // throw an exception with the error message
  }
The code above checks to be sure that the input value to this function is a number. If it is not a number, an exception is thrown with a string value that contains the error message. Google Apps Script handles this exception by displaying #ERROR! in the Spreadsheet cell and by displaying a comment in the cell with the error message. When the return statement is reached, execution ends and a value is returned to the Spreadsheet cell.

  outNum = inNum * factor;
The code above does the math to convert inches to millimeters by multiplying the input value by the conversion factor. The result value is stored in the outNum variable declared earlier.

  return outNum;
This is how the function returns the result to the Spreadsheet cell which contains the =in2mm() formula.

Using a Custom Function in a Spreadsheet

Using a custom function in a Spreadsheet is very similar to using built-in Spreadsheet formulas and functions.

To try this out, you can use the in2mm function you created above. In your Spreadsheet, type =in2mm(10) in cell A1 and then press Enter. The cell will display Thinking... momentarily, and then the result of 254 will appear in the cell.

If you instead get the error message "#NAME?" in the cell then the spreadsheet cannot find the function. First, ensure that you have spelled the name of the function correctly. It may take a moment for the spreadsheet to rescan the script and find the new function. You can manually start the scan by going to Tools > Script Manager... and clicking the Reload button.

You can also reference the values of other cells in the inputs to your custom functions. For example, enter the number 1 in cell A2. Then, in cell B2, enter =in2mm(A2). Cell B2 will display Thinking... momentarily, and then the result of 25.4 will appear in cell B2.

Custom functions return values, but they cannot set values outside the cells they are in. In most circumstances, a custom function in cell A1 cannot modify cell A5. However, if a custom function returns a double array, the results overflow the cell containing the function and fill the cells below and to the right of the cell containing the custom function. You can test this with a custom function containing return [[1,2],[3,4]];.

Permissions and Custom Functions

Custom functions can read data from the current Spreadsheet and can call anonymous services such as SOAP, UrlFetch and Google Translate. An anonymous service is a service that does not require user credentials to run.

However, custom functions have some restrictions that other functions do not have. For example, custom functions run silently and are not permitted to access user-specific services. A user-specific service is a service that is tied to a particular user, such as Google Calendar. User-specific services may access private data, such as Google Calendar, or may act as the user, performing tasks such as sending email.

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.