Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts

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.

image

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:

image

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):

ezgif-1-395ef22c8140

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.

image

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.

image

Hopefully this tutorial helps. Feel free to comment here or message me if you need help implementing it.

48 Likes

This post was inspired by:

  • A discussion with @nathanaelb when he solved a problem by the sequence function.
  • A time when I helped @Robert_Petitto when his arrayformula just works with the first row and it populates the other rows with the same information.

1 Like

I’m learning a lot from you @ThinhDinh , thanks for sharing knowledge.
Conosco el idioma pero hay referencias técnicas que aveces se me dificulta entender. Pero sigo avanzando pronto subiré mi primer template! Cheers and thanks again!

2 Likes

Hi Erni, we’re looking forward to your first template and if you have any problems, feel free to have a post asking about it, I’ll try to help!

Since my school days, I have always believed in a knowledge sharing culture. I always try to share my knowledge with my friends and my colleagues, believing that by helping them reach a higher level, the standard of the company/community will also be raised, hence getting more quality discussions and more ideas to work with, which benefits all parties!

9 Likes

Thanks a lot, I’ll take your word on that. I’m still working around Oder retrieving but my next stop will be adding the distance feature you explained the other day!

Cyu around and awesome way to explain and link the posts to the original threads to get the big picture!

1 Like

When you come to distance part, send me a personal message so I can help setting it up!

1 Like

Thanks for the post. I will be referencing this for sure in the future. It might be worth it in the “Best practices” section to include iferror() wraps for vlookup and arrayformula(if(len(x),y,z) to avoid errors in the spreadsheet.

1 Like

Thanks Robert, I will update this today!

1 Like

Added the good practices of using LEN conditions to only provide value for rows with data, and IFERROR to avoid errors in lookup, find, etc.

This changes so many useless things I used to do :joy: :rofl:
Thank you!!!

1 Like

Hi @ThinhDinh,
thank you so much for sharing this tutorial.
I’m trying with the good practices but i encountered an issue.

In column ‘U’ I have numeric values fetched from an API through ImportJSON function. For some reason Google Sheet doesn’t seem to recognize this data as numbers, so I made this trick to create an auxiliary column ‘Y’ with an ARRAYFORMULA that simply adds a zero to the values in ‘U’ column.

This trick works fine with a simple ARRAYFORMULA I put on Y2 cell:
=ARRAYFORMULA(IF(U2:U="","",U2:U+0))

Now, following the tutorial, I’m trying to do something more robust putting on Y1 this formula:

={"Aqius Value";ARRAYFORMULA(IF(AND(H2:H="",U2:U=""),"",IF(AND(H2:H<>"",U2:U<>""),U2:U+0,IF(AND(H2:H<>"",U2:U=""),"No data"))))}

And this is what I get:

What I’m doing wrong? Any idea?

Thank you

Do you mind sharing a copy of the data to ariesarsenal@gmail.com so I can try directly on it? Thank you. Just that sheet will be enough.

Edit: Found the problem. Don’t use AND in ARRAYFORMULA because it won’t work.

Right formula for your case:

={"Aqius Value";ARRAYFORMULA(IF((H2:H="")*(U2:U="")=1,"",IF((H2:H<>"")*(U2:U<>"")=1,VALUE(U2:U),IF((H2:H<>"")*(U2:U="")=1,"No data",""))))}

You can use the +0 method, or the VALUE one.

2 Likes

Hi @ThinhDinh very instrumental, thanks!

I’m trying to put into practice your lesson and have it worked with “Counta”; unfortunately it counts results for all rows, instead of row-by-row
=arrayformula(if(isblank(C2:C)=TRUE;"";COUNTA(G2:J)))

If you have 2 mn to give me a clue about the mistake I have done, would be so kind of you (as always!).

(sheet “Planning”, column L) https://docs.google.com/spreadsheets/d/19BpuR1Atevp0zoYZGsmgwkb4btCrgIjW79lgx0BDng8/edit?usp=sharing

Many thanks in advance!

If I’m understanding it right, you want to have a sum of H to K for every row?

Yes exactly…

The right formula would be

=arrayformula(if(isblank(C2:C);"";H2:H+I2:I+J2:J+K2:K))

You can’t make that work horizontally, make it vertically.

1 Like

The “+” between each of course!
Thanks it works well
Cheers

Sorry @ThinhDinh, in fact I answered too quickly to your original question: the objective is not a sum per line, but to count the non-empty cells per line.

When I use the formula below, while it’s a ‘counta’, it makes a sum of the non-empty cells of all lines included in the arrayformula…
=arrayformula(if(isblank(D2:D);"";COUNTA(H2:H;I2:I;J2:J;K2:K)))

Can you try:

=arrayformula(if(isblank(D2:D);"";(H2:H<>"")+(I2:I<>"")+(J2:J<>"")+(K2:K<>"")))
3 Likes

It works now! Thanks very much

1 Like