2

This seems like a trivial question. But I have spent some times, I still cannot find any clues from the Internet. I have been trying the work-arounds. They all are not nice. Returning null or undefined will just result in a blank cell. Of course return "#N/A" (as a string) is far far far from good.

For instance, we have a function in Google App Script.

function GIVE_ME_NA() {
    return NA();     // This won't work
}

And in cells A1 and A2

A1 =GIVE_ME_NA()
A2 =ISNA(A1)

The desired results showing in A1 and A2 are

    A       B
1  #N/A   TRUE

In the sheet formula, we can simply use =NA(). But in App Script, I cannot.

Rubén
  • 34,714
  • 9
  • 70
  • 166
midnite
  • 5,157
  • 7
  • 38
  • 52

2 Answers2

0

What if you 

return "=NA()"?

I'm unclear from the way that you are using this why you want a gas solution, because you are using it as an in-cell function anyway.

If you are doing a normal script, you can use something like this

ss.getRange(1,1).setFormula('NA()');
Rubén
  • 34,714
  • 9
  • 70
  • 166
J. G.
  • 1,922
  • 1
  • 11
  • 21
  • Yes. You are right. Using `setFormula('=NA()')` can achieve this. But in my case it is a bit difficult to change it to the `setFormula()` way. My function takes a Range (one cell, or an array, or a 2D array), check the values inside, and return an array of the same number of items. For example, it takes `A1:D4`. Check the values, some `TRUE` and some `FALSE`. Then return an 4x4 array. If `E1=myFunc(A1:D4)`, it fills `E1:H4`. If `D3` and `D4` are blanks, I want `H3` and `H4` get the values `#N/A`. – midnite May 08 '19 at 20:54
0

At this time it's not possible because the supported return values for custom functions are string, number, boolean and Date.

As you already noticed returning NA() doesn't work.

A custom function can't return a formula, user return '=NA()' is set in Google Sheets as a TEXT value until the user manually edit the cell value (or put the cell in edit mode then press Enter.

Related

Reference

Rubén
  • 34,714
  • 9
  • 70
  • 166