CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
jawhar_m
jawhar_m

Reputation: 145

Triggering function refresh when using ArrayFormula

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?

Upvotes: 0

Views: 440

Answers (1)

RemcoE33
RemcoE33

Reputation: 1610

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:

Upvotes: 1

Related Questions