Why can't you use setValue in a custom functio

问题:

Closed. This question is opinion-based. It is not currently accepting answers.

---

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 4 years ago.

I'm really just curious, but does anyone know why you can't use setValue to write in different cell in a custom function?

The readme explains that you can't do this, but doesn't give a reason on why: link

Custom functions return values, but they cannot set values outside the cells they are in. In most circumstances, a custom function in cell A1 cannot modify cell A5. However, if a custom function returns a double array, the results overflow the cell containing the function and fill the cells below and to the right of the cell containing the custom function. You can test this with a custom function containing return [[1,2],[3,4]];.

Anyone knows if there is a good reason for this?

回答1:

I think it is quite logical. When you're calling a custom function in a cell, say A1, you are expecting the function to do the calculation (or whatever) and place the result in A1. If you want to see the result in B1, you'd write the same formula in B1.

As the doc explains, there might be cases where you want the function to return more than one value in which case, returning a 2D array will populate the cells adjacent to the one where the formula was called.

In any case, can you give the use case you have where you want to populate a different cell using a custom function. You can always call a script in other fashions (not as a formula) and have it modify any cell you want.