I’m sure that at some point in your life you had to use spreadsheet software like Microsoft Excel or Google Sheets.
They are relatively simple and straight forward to be used but also very powerful and easily extendable.
In today’s episode of Code Bite, we will look into Google Sheets and its ability to add code and custom functions so we can extend it.
A function is a piece of code that manipulates data from the spreadsheet to calculate a new value automatically for us. A very common example of such a function is SUM, that calculates the sum of a column or group of cells.
All of the spreadsheet software supports a lot of such functions that are pre-built into them but they also support the ability to extend them and write our own.
To write a custom function in Google Sheets we use a feature of it called Apps Script which is a rapid application development platform where we can write code in JavaScript directly in the browser that will then be executed in our spreadsheet.
To start writing we can go to Tools > Script editor in the top menu and that will bring up the online code editor.
In it, upon first open, we will have one file called Code.gs along with a blank starting function, named myFunction.
As a starting example, we will rename this function to DOUBLE and add an input parameter in its declaration. Inside the body of the function, we need to return a value and for this example, we will just multiply the input value with 2.
/**
Doubles the input value
@customfunction
*/
function DOUBLE(input) {
return input * 2;
}
We can now save the script and if we go back to the spreadsheet and add some data to it, we can now reference this function in any cell and send in the data cell reference as the input for the value.
When executing this function, Google Sheets will shortly display a Loading message in the cell, but it will then display the returned value from the function.
These functions can do whatever we want but there are some limitations that we need to follow like:
- Names must be unique and different than the ones used by the built-in functions
- The name must not end with an _, and
- Function names are typically written with uppercase letters, although this is not required.
Each function can return a single value as in our example but it can also return an array of values. This array will then be expanded into adjacent cells as long as they are empty. If they are not an error will be shown.
The function that we wrote is usable but for anyone else that might come to edit the document it will be unknown and the user will need to know it exists to use it. We can fix this by adding the function to the autocomplete list, the same as all of the built-in functions are.
To do this, we need to add a JsDoc @customfunction tag in front of the function as a comment where in this comment we can write a short explanation of what our function does.
Now with the comment added, when we start to write the function name, the function will be offered by the autocomplete, along with the function description.
The great power that these functions have, comes from the ability to call and interact with other tools and services from Google like Translate, Maps, connect to an external database, work with XML and others. By far, the most powerful feature for me is the ability to make an external HTTP request to any API or webpage and get data from it by using the UrlFetch service.
To demonstrate this, I’ll paste in a function that will convert US dollars to Swiss franc but it will not assume the currency rate but instead, it will retrieve it from an external API.
/**
* Converts US dollars to SWISS francs.
*
* @param {number} dollars The total number of dollars.
* @return {number} swissFrancs The provided value in Swiss francs.
* @customfunction
*/
function USDTOCHF(dollars){
// Gets a cache that is common to all users of the script.
var cache = CacheService.getScriptCache();
// Accesses the memory location (rates.CHF) of the script cache.
var rate = cache.get('rates.CHF');
// If a cache miss occurs, the program fetches the current
// CHF rate from an API and stores that rate in the cache
// for later convenience.
if (!rate) {
var response =
UrlFetchApp.fetch('https://api.exchangeratesapi.io/latest?base=USD');
var result = JSON.parse(response.getContentText());
rate = result.rates.CHF;
cache.put('rates.CHF', rate);
}
// Converts dollars to CHF according to the latest rate.
var swissFrancs = dollars * rate;
// Returns the CHF value.
return 'CHF ' + swissFrancs.toFixed(2);
}
The function also uses the built-in cache service where it will not call the API for all of the calculations but it will call it once for the first calculation and then it will store that value in the cache.
Every other calculation will then be made with the cached value so the performance of them will be greatly improved and we won’t hit the server that often as rates do not change that quickly.
Since the API returns JSON, once we get the response from the server, we need to parse the JSON into an object and we can then get the rate, multiply it with the input value and return the new, calculated value to the cell.
If you liked the video, then be sure to hit the like button, subscribe to the channel if you haven’t already and I’ll see you all in the next one.
