How To Create Custom Functions in Google Spreadsheets

Tutorial Category:

Share This:

Google Spreadsheets already provide a wide variety of functions that can be used to manipulate data, calculate numbers, etc. What if you have your own functions that you would like to use as a formula within a Google Spreadsheet? With the power of Google Apps Script, it can be done! Let’s learn how.

Let’s say we have a list of YouTube video ids. We want to be able to create clickable links to their respective videos on YouTube, but we don’t want to use the standard HYPERLINK function. Here’s an example:

Google Apps Script Custom Functions

STEP ONE

Navigate to Tools > Script Editor. Google will ask you what type of project you want to create the script for. Choose Blank Project. Give your project a name.

STEP TWO

You’ll see a function was already created for you called myFunction(). Delete the whole function and replace it with:

function VID(id) {
 var error;
 if (!id) {
  error = "Please provide a video id.";
 } 
 if (!error) {
  return "http://www.youtube.com/watch?v="+id;
 } else {
  return error;
 }
}

Let’s explain what’s going on in that code.

  1. The id of the video is passed in as the variable called id.
  2. If there is no id provided, define error message text as a variable called error.
  3. If there is no error, then return the YouTube video url.
  4. If there is an error, then return the error message.

STEP THREE

Now that we have the custom function created, how do we use it?

  1. Switch back to your spreadsheet and in cell A2, enter any YouTube video id.
  2. In cell B2, enter =VID(A2).

The result should be a clickable link to the YouTube video. Now let’s test the error message.

  1. In cell A3, leave the cell blank.
  2. In cell B3, enter =VID(B3).

The result should be the error message. Wasn’t that easy?! Oh, and you can also use lowercase for function names. So =vid(A2) will also work. Go have fun making  your own custom functions!

Share This:

Related Tutorials