How to evaluate spreadsheet formula in user function?

Question

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

How can I evaluate spreadsheet functions in a user-defined function, something like "eval", which will work as follows:

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

Is it possible?

Please note that I do not care about the SIN function in particular, I want to access the full arsenal of spreadsheet functions ( PMT , QUERY , NPER , etc.).

Answer 1

Application Spreadsheet Functions - Script

Impossible - this has been asked many times. Suggest you check the google-apps-script problem list to see if something has changed. But the last thing I checked has no way to do this, 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", since it has java script versions of spreadsheet formulas. https://github.com/audreyt/ethercalc

Answer 2

I got this job. Using the setpoint will do the trick. So something like this:

 function MyFun1(){ SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('A1').setValue(Myfun2()) } function MyFun2(){ return "=SIN(45)+123" } 

Hope this helps!

Answer 3

I think you need to divide this problem into two different problems.

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

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

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

Let me know if this helps.

Answer 4

I do not know if this is possible using high-level functions. However, this is possible with the help of some simple and understandable functions, such as (sum, subtraction, etc.).

Below is the code that I used to set the values ​​after the calculation was done in the script itself.

 function MyFun1() { SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('A1').setValue(MyFun2()); } function MyFun2() { var one = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dashboard"); var two = one.getRange('A2').getValue(); var three = one.getRange('A3').getValue(); return two*three; } 

Remember to add the "onEdit" trigger to Myfun1 () to automatically update the return value.