Imagine you have a Google Sheet with tabular sales data, such as this:
| A | B | C | D | E | |
| 1 | Month | North | South | East | West |
| 2 | 22-01 | 120 | 140 | 160 | 180 |
| 3 | 22-01 | 220 | 240 | 260 | 280 |
| 4 | 22-01 | 320 | 340 | 360 | 380 |
The table contains sales data from four regions, represented in columns, and monthly sales volumes represented in rows. Let's also say that you are developing a Google Apps Script that needs to do a few things, including summing up the sales in the East region.
How would you go about coding that?
Well, the easiest thing to do is to pull the values out of the East column one by one and add them together. Something like this would do the job:
function calcSales() {
// Get the current spreadsheet
const ss = SpreadsheetApp.getActive();
// Get the active sheet
const sh = ss.getActiveSheet();
// Get the values from the three cells
const east1 = sh.getRange('D2').getValue();
const east2 = sh.getRange('D3').getValue();
const east3 = sh.getRange('D4').getValue();
// Sump up the values
const total = east1 + east2 + east3;
// Log the total
console.log(total);
}While the code above gets the job done, we can easily see that it isn't really scalable: what if new monthly sales rows are added to the sheet and we need to include them in our total? The script in its current form will not account for these rows.
Luckily, we can improve on things by using higher-order functions. Think of these functions as utility functions that were added to the Javascript language. Instead of you having to write a bunch of code, you can use these functions to speed things up and make your code more readable.
We'll look at three higher-order functions: filter, map, and reduce. Our code starts as follows:
function calcSales() {
// Get the current spreadsheet
const ss = SpreadsheetApp.getActive();
// Get the active sheet
const sh = ss.getActiveSheet();
// Get the values from the entire East column
let east = sh.getRange('D2:D').getValues();
console.log(east);
}When we run the script, we see in the console a nested array where each array member is a sub-array containing the values from the column: both our number values and the empty cells below them.
We can get rid of the empty cells by using the filter method. The method takes in a functions, which it uses to evaluate each member of the array. If the result of the function is TRUE then the member will be kept; otherwise, the member will be discarded. Filter returns a new array.
function calcSales() {
// Get the current spreadsheet
const ss = SpreadsheetApp.getActive();
// Get the active sheet
const sh = ss.getActiveSheet();
// Get the values from the entire East column
let east = sh.getRange('D2:D').getValues();
east = east.filter(row => row[0] > 0);
console.log(east);
}I'm using the arrow function form to make things shorter. Inside the filter function we check whether the first value in the sub-arrays is bigger than 0. If not then we discard the value.
If you run the code with the new line included, then the console will log only the rows that contain our values. We're making progress!
The data is still showing up as a nested array, which make things a bit tricky. Let's flatten the nested array to a simple array using the map method. This method takes in an array, and applies a user-supplied function on every member of the array, returning a new array. Our function will simply extract the first value of the sub-array and return an array of values:
function calcSales() {
// Get the current spreadsheet
const ss = SpreadsheetApp.getActive();
// Get the active sheet
const sh = ss.getActiveSheet();
// Get the values from the entire East column
let east = sh.getRange('D2:D').getValues();
east = east.filter(row => row[0] > 0);
east = east.map(row => row[0]);
console.log(east);
}Logging the updated function results in a single array of the values. Now we can use the reduce method to total them. This method takes in two inputs: the accumulator and the current value. The method applies a function on each member of the array to reduce it to the accumulator. In our case, the accumulator is simply incremented with the value of the current value:
function calcSales() {
// Get the current spreadsheet
const ss = SpreadsheetApp.getActive();
// Get the active sheet
const sh = ss.getActiveSheet();
// Get the values from the entire East column
let east = sh.getRange('D2:D').getValues();
east = east.filter(row => row[0] > 0);
east = east.map(row => row[0]);
const total = east.reduce((t, c) => t+=c, 0);
console.log(total);
}The zero at the end of the reduce method initializes the accumulator to zero.
Ok, so we used the three higher-order functions to generate the total for the East column. What if we need our script to calculate a grand total for all four regions? Our modified script would look like this:
function calcSales() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Regions');
// Get a nested array of data from all rows for the four regions
let sales = sh.getRange('B2:E').getValues();
// Remove rows with empty cells
sales = sales.filter((row) => row[0] > 0);
// Use the map method to return a new array.
// The method takes in the sub-array of the four regions
// and reduces it to a total for the row
sales = sales.map((row) => row.reduce((t, c) => t + c, 0));
// Now we have a simple array that we can reduce to a grand total, as we saw earlier.
const total = sales.reduce((t, c) => (t += c), 0);
console.log(total);
}In the code above, we implement a reduce function inside a map function. The map function uses reduce to calculate a sum of every row. Then we implement another reduce to get the total of all rows.
I'm hoping that this gives you an idea of how to implement higher-order functions with Apps Script.
Please drop me a line if you have any questions.