10

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..)

Rubén
  • 34,714
  • 9
  • 70
  • 166
Max L.
  • 9,774
  • 15
  • 56
  • 86
  • Possible duplicate of [Is there a way to evaluate a formula that is stored in a cell?](http://stackoverflow.com/questions/16303680/is-there-a-way-to-evaluate-a-formula-that-is-stored-in-a-cell) – Rubén Nov 22 '16 at 04:28

6 Answers6

6

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

eddyparkinson
  • 3,680
  • 4
  • 26
  • 52
5

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("");
David
  • 295
  • 3
  • 6
3

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!

user2988332
  • 115
  • 1
  • 9
  • 1
    Actually, I was too quick to conclude... but the above doesn't evaluate the function and set its value, but rather sets the formula itself... not what I was looking for :( – haridsv Feb 15 '16 at 17:21
  • This is weird because I currently use it without any problem. What does the cell show for you? – user2988332 Feb 16 '16 at 18:04
  • The cell shows the value, but if you edit it you see the formula, so it behaves exactly like a regular formula cell. I worked around by copying the cells to themselves (source and destination same range) using Range.copyTo with {contentsOnly:true} option. I found the solution in one of the other SO threads, not sure which so don't have a link handy now. – haridsv Feb 17 '16 at 03:57
1

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.

Kusha Tavakoli
  • 181
  • 8
  • The SIN function is just an example, I want to be able to have all the spreadsheet functions at my disposal within custom functions. (query, pmt, nper, etc...) – Max L. Nov 07 '14 at 18:48
1

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);
  }
}
Fabian K
  • 59
  • 1
  • 7
0

I don't know if it's possible with high-level functions. However, it's possible with some common and easy-to-understand functions like (sum, subtract etc).

Following is the code I used to set values after the calculation is done in scripting 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;
}

Don't forget to add a trigger "onEdit" on Myfun1() to automatically update the return value.

Sowmay Jain
  • 375
  • 2
  • 4
  • 12