Creating a custom function in Google Apps Script

Learn the Basics of Google Apps Script - Part 3

All functions in Google Apps Script begins with the word “function”, to be followed by the name of the function which you can define.

The syntax of a function looks like this:

function function-name ( ){

  performs tasks or calculations
    
}

The bracket (or parenthesis) is used to pass parameters to the function. The codes in between the two curly brackets are the tasks or calculations which the function does.

Using the following simple function as an example, the TIMESTWO function accepts a numerical input and returns the result after multiplying the input by 2.

function TIMESTWO(input){ 

    return input * 2;

}        

Back to the USDTOSGD( ) function which we created in Part 2.

function USDTOSGD(dollars){
   var singDollars = dollars * 1.32;
   return singDollars; 
}

The name of our function is USDTOSGD. This function accepts a numeric input to perform the calculation on. You can choose whatever name for the parameter, which in this case we used ‘dollars’.

JavaScript variables or var are containers for storing data values.

In the function above, we declared a variable named singDollars to hold the new SGD value while the function performs a calculation of X1.32 to the USD value passed to it. Once the calculation is performed, the resulting 323.40 is stored in the var, singDollars.

It then returns the result stored in the var back to the instruction which invoked the function, which in this case, came from the Google Sheet.

Google Apps Script basically has two scopes for variables: global and local. Variables assigned outside of a function are called global variables, and variables assigned inside of a function are known as local variables.

Local variables are created inside a function and hence are available only to the function in which it is declared.

Variables defined inside a function cannot be accessed from anywhere outside the function. They can only be accessed from within the function when the function has started execution and is lost when the function terminates. Therefore, variables with the same name can be used in different functions.

var globalVariable = 100 // a Global var is defined outside a function

function ADDTWONUMBERS(){

    var localVariable1 = 20 // a Local var is defined inside a function

    return globalVariable + localVariable1

}

function MINUSTWONUMBERS(){

    var localVariable2 = 50 // a Local var is defined inside a function

    return globalVariable - localVariable2
}

Refer to the example above.

globalVariable is declared outside the two functions. Hence it can be accessed by both functions. localVariable 1 and 2 are declared within their own functions, hence accessible by their respective functions only.

In Google Apps Script, we use variables to hold values just like in algebra.

Variables play an important part in programming, especially if the function performs complex tasks and requires the use of variables to store values temporary for effective execution.

However, in our simple function example earlier, instead of using a variable to store the return value of a function like what we did previously, you can also achieve the same outcome by directly returning the result of the function without using a var as shown below.

function USDTOSGD(dollars){
    return singDollars = dollars * 1.32;
    // return singDollars;
}

Go ahead to modify your codes slightly. You should be getting the same result as before.

Note: The double slash indicates that line is a comment which will be ignored by Google Apps Script. You can also delete the third line (return singDollars;) totally.

function USDTOSGD(dollars){
      return singDollars = dollars * 1.32; 
}

Besides var, there are also let and const which have been added to the variable declaration vocabulary in Google Apps Script.

However, let’s stick with var for the moment. We shall have the opportunity to explore these other two declarations as we progress.