Hi,
Today I will show you the script that can make copying formulas down work, when it does not work with arrayformula.
But first, let’s have some introductions about arrayformula, for those who has not used this before in Google Sheets, some equivalents in Glide and some good practices while using it. I hope it can help you in building your Glide apps as well as applying it in your normal day job.
1/ Definition
Arrayformula is defined as “a range, mathematical expression using one cell range or multiple ranges of the same size, or a function that returns a result greater than one cell.”
Before, you would have to copy down formulas when you want it to work with the whole column. This is not the case now with arrayformula available.
2/ Example usage
Example 1: Put in cell C1: ARRAYFORMULA(A1:A&" "&B1:B), it will concatenate all values from each row of A & B to the corresponding row in C.
This is equivalent to a Template column in Glide with settings: “A B”, pointing A to column A & B to column B.
Example 2: Let’s say you want to calculate the total value of each order, with two input columns: quantity & selling price. The third column can be called ‘order value’
The formula to put in cell C2 of the order value column would look like this:
This is equivalent to a Math column in Glide, with the formula being Quantity * Selling price.
3/ Good practices
3.1. Care about the empty inputs
In the case just above, when I load the data into my Glide test app, it tells me that I have exceeded the 500 free rows for the Free plan. Why did that happen?
That’s because the arrayformula automatically fills down value, in this case zeros, to rows where we don’t have input value.
To take care of that, and this would be applicable for all cases, we can use something like:
=ARRAYFORMULA(IF(A2:A<>"",A2:A*B2:B,""))
This will tell the formula to only make a calculation where the A column is not null, otherwise return a null value.
3.2. Keep the arrayformula in the headers
Instead of putting the arrayformula in row 2 where the data starts, you can put it in the header and never have to worry about accidentally delete that row ever again.
This formula below would create an array, populating the first cell with the header name, and the arrayformula starts from below that.
={"Order value";ARRAYFORMULA(IF(A2:A<>"",A2:A*B2:B,""))}
3.3. Only calculates and show values when the row has data
For the case above, if you notice it, I have wrapped a condition that only when A2:A has data will the calculation A2:A*B2:B be processed. This is to avoid sending null or 0 values to the rows without data and potentially make you hit your Glide rows limit.
It can go like this:
=ARRAYFORMULA(IF(A2:A<>"",A2:A*B2:B,""))
or like this:
=ARRAYFORMULA(IF(LEN(A2:A),A2:A*B2:B,""))
which takes into consideration the length of the string in column A. Only when it returns a number higher than 0 will the calculation be triggered.
3.4. Avoid the #ERROR or #VALUE with VLOOKUP, FIND and many other formulas
You may have known that VLOOKUP, FIND and many other formulas will return an error, whether it be #ERROR or #VALUE if they can’t find a match with the conditions provided. In an ARRAYFORMULA context, this can break your formula and resulting in problems with your array calculation.
To avoid this, we can use the IFERROR formula, which has the syntax: IFERROR(calculation, value if calculation returns error), which makes the VLOOKUP or FIND return a designated value if it can not find a match.
It can go like this:
=ARRAYFORMULA(IF(A2:A<>"",IFERROR(VLOOKUP(A2:A,range to lookup, column to lookup),""),""))
In this case, if the VLOOKUP can not find a match, it will return a null value. You can change it to 0 or whatever you want depends on your case.
4/ When arrayformula doesn't work, what do we do?
It has been documented that arrayformula does not work with some specific formulas, INDIRECT being one of them, and just returns the first row result.
Furthermore, the custom functions you write with Google Scripts won’t work with arrayformula as well, such as the one I wrote to compute distance & travel time I have written about here.
In cases like this, Scripts is the alternative to come to. Navigate to Tools > Script Editor in your sheets to write and save scripts.
One Script that I have been using is:
function fillFormula() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Hotels distance');
var lr = sheet.getLastRow();
var fillDownRange = sheet.getRange(lr, 4, 1);
sheet.getRange("D"+(lr-1)).copyTo(fillDownRange);
}
This works in the following steps:
- The bot goes to the sheet you want to copy the formula down, specified here is the ‘Hotel distance’ sheet.
- Then it determines what is the last row with content in the getLastRow() function.
- It uses that last row number to get the right fill down range, in this case is the cell in the last row, at column 4 (because I want to fill down column D here), and fill only 1 cell. That’s the meaning of lr, 4, 1.
- Then it grabs the cell just above it, which has already been populated with a formula (the getRange(“D”+(lr-1)) part), and copy to the determined fill down range.
Here’s how it works in real time when I input new addresses to calculate distance (I hid the emails and addresses above):
Another alternative of this is:
function fillFormula() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Hotels distance');
var vals = sheet.getRange("D2:D").getValues();
var lr = vals.filter(String).length;
sheet.getRange("D"+(lr)).autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);}
It grabs the range of values from D2 to the last row of column D, then use a filter to get just non-blank values. Last row number here is the length of that string, which can be inferred as the last row with formula (different from the method used above).
That value is then used to grab the last cell with formula in the range, and an autoFillToNeighbour method is called to fill the next row with according formula.
5/ Setting the triggers
After we’re done with the scripts, navigate to Edit > Current Project’s Triggers.
Click “Add Trigger” and then set the right function to run, and the event type being “On change” for it to work with Glide edits.
Hopefully this tutorial helps. Feel free to comment here or message me if you need help implementing it.
