Easily Create Custom Functions For Google Sheets

Author : Code With Mark

If you have used Google Sheets for your projects, then you know it’s a lot better than regular old Microsoft Excel. One of the reasons Google Sheets out shines Excel is in it’s “Functions“. They definitely make your life a whole lot easier.

Even if one of your projects demands for some creative commuting, Google Sheets got your covered. It allows you to create your own custom functions.

Creating Custom Functions

Let’s create a simple google sheet custom function which will double it’s value.

Open up a google sheet and then go to: “Tools > Script editor

google-sheet-custom-functions-code-with-mark-2017-01-05-100

Then you will see following screen:

google-sheet-custom-functions-code-with-mark-2017-01-05-101

 

On top of the page, you will notice “Untitled project”. If you click on it, you will see a screen:

google-sheet-custom-functions-code-with-mark-2017-01-05-102

 

Type in there “My First Custom Function” and then click “Ok”.

Note: This section is known as “Google App Script” short for “GAS”.  GAS is based on “Plain Javascript”. Meaning anything you can do in “Javascript” you can do it in “Google App Script”. 

Next replace the myFunction text with the following:


/**
* Multiplies the input value by 2.
*
* @param {number} input The value to multiply.
* @return The input multiplied by 2.
* @customfunction
*/
function DOUBLE(input) 
{
 return input * 2;
}

After that click on “Save”.

google-sheet-custom-functions-code-with-mark-2017-01-05-103

 

 

Next go to your google sheet and click in a cell and then in the address bar type in the function name “DOUBLE(4)” and then result will be “8”.

google-sheet-custom-functions-code-with-mark-2017-01-05-106

 

Advance Google Sheet Custom Function

Let’s say if one of your projects requires to separate username and domain from user email address.

You can write the 2 custom functions:

 


function GetNameFromEmail(email)
{
  return email.replace(/@.*$/,"");
}

function GetDomainFromEmail(email)
{
  return email.substring(email.lastIndexOf("@")+1);
}

 

To extract the name out of email address:

google-sheet-custom-functions-code-with-mark-2017-01-05-107

 

To get the domain name from the email address:

google-sheet-custom-functions-code-with-mark-2017-01-05-108

 

Include External Javascript Libraries

With google app script,  you can also include external javascript libraries in your custom functions.

For example, if your project requires for you to find out the week ending date for any given date, you can easily do that with moment js library by including the library.

Next go to  GAS screen:

google-sheet-custom-functions-code-with-mark-2017-01-05-109

 

You will see next:

google-sheet-custom-functions-code-with-mark-2017-01-05-110

Just type in “momentjs” and click  “OK”.

 

Next delete the “myFunction” text and paste the moment js source code.

google-sheet-custom-functions-code-with-mark-2017-01-05-111

 

Next click on save:

google-sheet-custom-functions-code-with-mark-2017-01-05-112

Next click on the “Code.gs” tab:

 

google-sheet-custom-functions-code-with-mark-2017-01-05-113

 

Below is the custom function that include moment js functions to get the week ending date for any give date.


function MyWeekEndingDate(date) 
{
  return moment(date).endOf('week').format('MM/DD/YYYY');
}


Go to your google sheet and in a cell type in the function name and the date:

google-sheet-custom-functions-code-with-mark-2017-01-05-114

Now you should be able to create and use google custom functions easily.

Sharing is caring:            



Did you find this helpful?