How to evaluate a spreadsheet formula within a custom function?

google sheets custom function
google sheets run script on click
google sheets custom formula
google sheets evaluate string as formula
spreadsheet functions
google sheet date functions
spreadsheet evaluate
google sheet evaluate cell

In a spreadsheet I can enter =SIN(45)+123 in a cell, and it will be evaluated.

How can I evaluate spreadsheet functions within a custom function, something like an "eval" function that would work like this :

function myFunc() {
  return Sheet.eval("=SIN(45)+123")
}

is it possible ?

Note that I don't care about the SIN function in particular, what I want is to have access to the complete arsenal of spreadsheet functions (PMT, QUERY, NPER, etc..)


Spreadsheet functions from Apps-Script

Not possible - This has been asked many times. Suggest you check the google-apps-script issue list to see if anything has changed. But last I checked, there is no way to do it, and they have no plan to add it. https://code.google.com/p/google-apps-script-issues/issues/list

Ethercalc - java script spreadsheet formulas

If you need to, you can always copy the code from "ethercalc" as it has a java script versions of the spreadsheet formulas. https://github.com/audreyt/ethercalc

Tip: Create a Custom Formula Function in Google Spreadsheets , Google Spreadsheets offer plenty of built-in functions like SUM and the basics, starting with a very simple function that doubles a value. Yes, there is a limit of 255 chars per string evaluated, if your formula text exceeds this limit, the Evaluate function will fail. You can use it anywhere you like, but I’ve never seen this function in a conditional formatting formula, you can build very complex formulas without the need of Evaluate.


I got this working. Using set value will do the trick. Thus something like this:

function MyFun1(){
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('A1').setValue(Myfun2())
}

function MyFun2(){
   return "=SIN(45)+123"
}

Hope this helps!

Custom Functions in Google Sheets | Apps Script, Google Sheets offers hundreds of built-in functions like AVERAGE , SUM , and Here's a simple custom function, named DOUBLE , which multiplies an input value by 2: Create or open a spreadsheet in Google Sheets. To create a custom DISCOUNT function in this workbook, follow these steps: Press Alt+F11 to open the Visual Basic Editor (on the Mac, press FN+ALT+F11), and then click Insert > Module. A new module window appears on the right-hand side of the Visual Basic Editor. Copy and paste the following code to the new module.


I know this is an old question, but it might help someone. just assign the formula to a range, then grab the value.

//asign a formula to the range
var range = sheet.getRange("A1").setFormula("=SUM(D1:D100)");
//get the result
var result  = range.getValue();
//clean up
range.setFormula("");     

Excel Factor 12 Secret EVALUATE Function • My Online Training Hub, This Excel Factor tutorial was sent in by Bryon Smedley of Bristol, Tennessee. The EVALUATE function allows for the evaluation of a text equation as an algebraic Excel Factor 20 Custom Number Format Disguise  In a spreadsheet I can enter =SIN(45)+123 in a cell, and it will be evaluated. How can I evaluate spreadsheet functions within a custom function, something like an "eval" function that would work


I think you need to divide this issue up into two different concerns.

Do you want to grab data that is already on the spreadsheet, perform a calculation, and then print a result, or do you want to use the sin() function on calculations in code unrelated to the data in the spreadsheet?

If you are trying to do the latter, you should be able to reference spreadsheet functions by using Math.sin() in your Google Apps Script. For more information on using the sin() function in JavaScript, check this post out: http://www.w3schools.com/jsref/jsref_sin.asp

If you are trying to do the former, then what you should do is use a readRows() function (more information available here: http://gassnippets.blogspot.com/2012/11/create-your-first-google-apps-script.html) to load your spreadsheet data into a variable (or variables) in memory, perform your calculations, and print the final result out to the spreadsheet using a similar function.

Let me know if this helps.

Custom function may not calculate correct value, Custom function may not calculate expected value in Excel. 03/30/2020 Type the following formula in cell A10, and then press ENTER: excel The second type of function is what I refer to as a Procedure Function, which is a common programming structure used by those developing macros. A procedure function is used to perform some operation and then return a value to whatever programming code called that function. The third type of function is known as a User-Defined Function (UDF). It is this third type of function that makes Excel extremely powerful as it allows you to access the power of macros directly from within your worksheets.


I came across this question in an attempt to find a way to evaluate part of a function like it is possible in Excel.

Here is my dirty workaround - instead of outputting the result in an msgbox, you could simply store the value or displayvalue of the activecell in a variable and use it to your liking.

Notice however, that the function will temporarily overwrite whatever you have in your currently selected cell and it will need to recalculate the sheet before the result is available. Hence it's not a viable solution if you need to evaluate multiple cell values.

function evalPart() {
  var ui = SpreadsheetApp.getUi();
  myPart = Browser.inputBox("Enter formula part:", ui.ButtonSet.OK_CANCEL);
  if (myPart != "cancel") {
    myActiveCell = SpreadsheetApp.getActiveSpreadsheet().getActiveCell();
    myBackup = myActiveCell.getFormula();
    myActiveCell.setFormula(myPart);
    Browser.msgBox("Result of \\n \\n" + myPart + " \\n \\n " + myActiveCell.getDisplayValue());
    myActiveCell.setFormula(myBackup);
  }
}

Create custom functions in Excel, Although Excel includes a multitude of built-in worksheet functions, chances are it doesn't have a function for every type of calculation you perform. Custom  Google Sheets supports cell formulas typically found in most desktop spreadsheet packages. Functions can be used to create formulas that manipulate data and calculate strings and numbers. Here's a list of all the functions available in each category.


How to Create Custom User Defined Functions in Excel, Cannot place a value in a cell other than the cell (or range) containing the formula. In other words, UDF's are meant to be used as "formulas", not  The FILTER formula within Google Sheets helps us filter and return the rows in a range that meet a set of specified criteria. Read more: https://blog.sheetgo Skip navigation


Add formulas & functions - Computer, Open a spreadsheet. Type an equal sign (=) in a cell and type in the function you want to use. Note: You may see suggested formulas and ranges based on your data. A function help box will be To calculate the absolute value of this sum, you need to nest the sum formula within absolute value formula. To calculate both  If you are going to be using the result of these functions frequently throughout your spreadsheet, avoid the temptation of nesting these functions within other functions to get your desired result. Instead, simply type the volatile function into a single cell on your spreadsheet and reference that cell from within other functions.


Google Sheets: Working with Functions, Excel includes many common functions that can be used to quickly find the sum, average, count, maximum value, and minimum value for a range of cells. In  Introduction - Change Another Cell with a VBA UDF Despite what others say, it is possible to change another cell with a VBA user-defined function. Microsoft even says it’s impossible for a custom function to change another cell’s value, but I’m going to show you a couple UDFs that prove all these people wrong.