Custom spreadsheet functions

We have seen how to use built-in spreadsheet functions using an equals sign before the function name; custom functions work exactly the same way.

If we type in cell A1 the following: =myAgeInHours(), we will almost instantaneously get the value returned by the function, which will be something like 489,584, and it will change every hour.

This is all very simple and quite attractive at first glance, but we'll see in the next chapter that it is not always the best way to use Google Apps Script as there are a few annoying behaviors in this workflow.

If we want to preview the result without using the spreadsheet interface, the best way to do it is using the built-in Logger or the JavaScript keyword throw.

The Logger result is available from the View Logs menu item and shows every occurrence of every Logger.log call that we insert in the script. In our simple example, we could write it as follows:

function myAgeInHours(){
  var myBirthDate = new Date('1958/02/19 02:00:00').getTime();
  Logger.log(myBirthDate);
  myBirthDate = parseInt(myBirthDate/3600000, 10);
  Logger.log(myBirthDate);
  var today = parseInt(new Date().getTime()/3600000, 10);
  Logger.log(today);
  Logger.log(today-myBirthDate);
  return today-myBirthDate;
}

The preceding code will return the following result in the Logger view, which is interesting but not very easy to read because it shows only numbers and we need to concentrate on the code to determine what values are exactly shown:

We can easily make it more user friendly by simply adding a little information to our code. This can be achieved in two different ways: either by literally composing your result with strings and variables or using the format parameter in the Logger.log method and using the %s placeholder for variables (https://developers.google.com/apps-script/reference/base/logger#log(String,Object...)).

I'll use both methods in the following example:

function myAgeInHours(){
  var myBirthDate = new Date('1958/02/19 02:00:00').getTime();
  Logger.log("myBirthDate = "+myBirthDate);
  myBirthDate = parseInt(myBirthDate/3600000, 10);
  Logger.log("myBirthDate in hours (parseInt(myBirthDate/3600000, 10)) = "+myBirthDate);
  var today = parseInt(new Date().getTime()/3600000, 10);
  Logger.log("today in hours = %s",today);
  Logger.log("today-myBirthDate = %s",today-myBirthDate);
  return today-myBirthDate;
}

This previous code will return the following result:

That is far more readable, isn't it?

Another way to get a value from a script is using the throw command that literally throws a message over your browser page just like any script would do, but I personally don't like it much because it shows up the same way as an error does; it makes me feel like something bad just happened.

Finally, since we tried this code in a spreadsheet, we have two more options to show the result:

• Using the toast spreadsheet method that shows a small message at the right-bottom corner of the spreadsheet for a given number of seconds without stopping execution (https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#toast(String,String,Number))

• Using the so-called Browser service that works only in the context of a spreadsheet and actually shows a pop up on the spreadsheet page (https://developers.google.com/apps-script/reference/base/browser)

The following screenshot shows the output for the preceding example:

This last possibility is specific in that it pauses the execution of the script and waits for the user to execute some action. In the simplest case, it is just a click on the Ok button but we can also ask for some value using other methods from the Browser class, for example, we could use the following code:

var name = Browser.inputBox('Enter your name', Browser.Buttons.OK_CANCEL);

The preceding line of code will ask the user to enter a name that will be assigned to the variable name as illustrated in the following screenshot:

Now that we are hands on with all the tools available in the script editor and know how to set and show variable names, let us try some practical examples that can make our life easier.