3

Is it possible to call any of the Google Sheets formulas from within your own Google Apps script custom function? If so, how?

It obviously doesn't work to merely use the formula name like you would inside the sheet. Like this, which is what I was hoping would work:

function myCustomFunction() {
  return TDIST(1,30,1); // calling =myCustomFunction() does not work due to TDIST giving a ReferenceError, even though =TDIST(1,30,1) works inside the spreadsheet
}

But since Google's built-in formulas probably rely on some JS implemented functions, it would be strange if there wasn't an API so that we could reuse those functions easily. I've looked at the Google Sheets API reference, but it seems made for calling your custom functions from external services. It seems you may call Google's own formulas through such a POST request. But that seems terribly cumbersome, when you ought to be able to refer to them directly from within your custom script which is tied to the specific Google Sheet environment anyway. If not, then what is the least roundabout way of using those inbuilt formula functions from within a custom function?

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Magne
  • 16,401
  • 10
  • 68
  • 88
  • Does this answer your question? [Is there a way to evaluate a formula that is stored in a cell?](https://stackoverflow.com/questions/16303680/is-there-a-way-to-evaluate-a-formula-that-is-stored-in-a-cell) – TheMaster Apr 23 '20 at 15:30
  • Hm.. no, not really. In my particular custom function I don't want to interface directly with the spreadsheet, but simply use the built-in functions directly, inputing parameters from the script itself, not from the spreadsheet. – Magne Apr 23 '20 at 16:03
  • 1
    Specifically, emphasis on the "old post" in [this answer](https://stackoverflow.com/a/16309091/). In Short, No You can't do that. The least roundabout way would be [this](https://stackoverflow.com/a/34659559/). – TheMaster Apr 23 '20 at 16:08
  • *but simply use the built-in functions directly, inputing parameters from the script itself,*. Mentioning `myCustomFunction` as if it's a custom function called from spreadsheet gives a different view. Regardless, the answer is the same. Not possible. – TheMaster Apr 23 '20 at 16:11
  • `myCustomFunction` is typically called from the spreadsheet, yes. I don't need to _evaluate_ an expression (like `=1+2`), and using a generic `eval()` would be way too broad for what I'm thinking. I'm sure they haven't implemented that because of security concerns. I was hoping the functions the default formulas use were exposed in some sort of API.. Thanks for your input, tho. – Magne Apr 23 '20 at 16:49
  • The underlying assumption here is of course that Google have implemented the default formulas (like `SUM`, `TDIST` etc.) by using (fairly regular) javascript functions. But there might be some black magic going on in the rendering of Google Sheets which I don't yet realise... Maybe because of the presumably async reactive programming used to implement the spreadsheet (regular formulas do seem to load instantaneous while custom functions do take some time to load). – Magne Apr 23 '20 at 16:53

1 Answers1

5

This has been a feature request since 2011 and has been declined by Google citing inter team issues.

Status: Won't Fix (Infeasible) by ev....@google.com

We have requested this of the Spreadsheet team, and are marking as "Won't Fix" for now.

Apparently, The spreadsheet team hasn't responded yet. A related product forum post states that Google has no intention of integrating spreadsheet functions with scripts.

Community
  • 1
  • 1
TheMaster
  • 45,448
  • 6
  • 62
  • 85