How to call a Google Sheet formula from within a custom function?

3
votes

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?

1
google-apps-scriptgoogle-sheetsgoogle-sheets-formulacustom-function
Does this answer your question? Is there a way to evaluate a formula that is stored in a cell? – TheMaster
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
Specifically, emphasis on the "old post" in this answer. In Short, No You can't do that. The least roundabout way would be this. – TheMaster
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
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

1 Answers

4
votes

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

Status: Won't Fix (Infeasible) by [email protected]

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.