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"

Then you will see following screen:

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

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".

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".

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:

To get the domain name from the email address:

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:

You will see next:

Just type in "moment js" and click  "OK".

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

Next click on save:

Next click on the "Code.gs" tab:

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:

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





Post Comment