Triggering function refresh when using ArrayFormula

Question

I am hitting an issue where I need to ensure Google sheets triggers a cell refresh when data changes. For a variety of reasons, the way my sheet is setup, is that I have an ArrayFormula calculation/login in the header of the cell, and that will essentially calculate all the rows beneath it. For simplicity, my equations are ArrayFormula and passing a value such as 'C2:C', which is ultimately saying 'please give me the value of column C for the given row. So I may have something like ArrayFormula(C2:C + 1) - which is really saying 'give me column C of my current row and add one.

Now, the PROBLEM is that I am building a custom function, lets call it Foo, and I want to also pass it 'columnC of the given row' - so lets say now my ArrayFormula looks like this: ArrayFormula(Foo(C2:C)).

The issue is that, I believe, because of the way google sheets tries to smartly cache function calls, it doesnt recognize when a value changes and to recalculate the call. Google sheets sees that 'C2:C' has not changed at all, so it wont refresh it, even if I did actually change the value of column C of that given row.

Anyone have any suggestions on how to better implement this or work around this?

I created a good test case of this with the following google sheet: https://docs.google.com/spreadsheets/d/1fCiRSiuII-2vOWJD9LWWkh9qjK0P_dZskofwBYxiZUI/edit?usp=sharing

If you notice, I have two columns, Total Sft and 'Total sqft New'. Both of these should generate the same value. In the first one, I did a manually multiply - eg A2:A*B2:B - which generates the expected result. In the second column, rather than a manual multiple equation, I created a test function called testFunctionMultiply which does the same thing - multiplies the values.

As you can see the columns show different values, when I would have expected them to match. Thoughts?

RemcoE33 · Accepted Answer · 2021-06-22 19:24:50Z

1

The arrayformula does not work on every function. Like the native DGET function for instance. If you modify the function to accept an array then it works as expected. Here is a copy from your sheet.

The formula now is:

={"Total Sqft New";ARRAYFORMULA(IF(ISBLANK(Sheet1!A2:A),,testFunctionMultiply(A2:A,B2:B)))}

The script:

function testFunctionMultiply(param1, param2) {
  const arg1 = param1.flat()
  const arg2 = param2.flat()
  const output = []

  for (i = 0; i < arg1.length; i++){
    output.push([Number(arg1[i])*Number(arg2[i])])
  }

  return output
}

TIP:

Don't use "" as null value. Google apps script will pick this up in .getLastRow() method for example.

//Wrong:
=IF(A2 = "","","Not blank"

//Good:
=IF(A2 = "",,"Not blank:

answered Jun 22, 2021 at 19:24

RemcoE33

1,6111 gold badge7 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jawhar_m Over a year ago

Thank you! That helps me better understand what I'm missing. But one critical problem is with the above, it results in a recalculation on each new row entry, no? Right now I'm running into timeout issues. Any clever solutions to work around that so it only calculates for the new entries?

RemcoE33 Over a year ago

That is the principle of the arrayformula. Or modify the script that not returns into a custom function but paste the values in the sheet itself. Then you could rerun this script via a menu/shortcut/button/timer trigger. Then you (or the trigger) runs when you are done adding rows.

jawhar_m Over a year ago

hi Remco - sorry I didnt understand your comment. Can you please re-explain? Since my sheet has thousands of rows, and truthfully I only use arrayformula because of a different integration which needs it so it triggers for a new entry - so usually the calculation truly is a one-time thing and no ned to recalculate for everyone. Need architectural guidance as to how to keep this performant. Thank you.

Collectives™ on Stack Overflow

Triggering function refresh when using ArrayFormula

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related