I have an onEdit-script which calculates the value of a cell based on the content of a row of other cells. I need this script to be in an onEdit-trigger rather than a regular cell-function because I don't always want the calculation to be redone when the value in one of the targeted cells is changed, but instead it checks for certain conditions and recalculates only when those are met.
A small problem I'm having with this is that one of the cells that the onEdit-script reads data from contains a function rather than a simple number. This causes a problem because sometimes when I enter data in a cell, it will trigger both this cell-function and the onEdit-script. And most of the time the oEdit-function runs before the cell-function finishes so the onEdit-function just picks up "Thinking" from this cell and thus it returns NaN.
So I guess a convenient solution to this would have been to make the onEdit-function wait for the "targeted" cells to finish their calculation but I don't think there is a way to do this?
Of course I could move the cell-function(which basically is a SUM-function with some added functionality) to the onEdit-script, which would solve the issue.
But to me it doesn't seem so nice having all interactivity in the onEdit-trigger. Or am I just being silly?
Or is there another approach I could take somehow?
I think the best solution would be to replace your custom formula (that "thinks") with a regular spreadsheet formula if possible. And it seems to be your case, since it's just a SUM function with "added functionality". Probably a SUMIF will suffice.
If your function is indeed complicated and can't be written as regular formula (which I really doubt), the best solution would be indeed to move the calculation to the onEdit-trigger.
Regardless of your problem ,I always advise to not use custom formulas, they are really problematic. e.g. haven't you ran into the caching issue?