Is there a way to evaluate a formula that is stored in a cell?

Question

In a Google Docs spreadsheet, I'm looking for something like =EVAL(A1) where A1 is set to "=1+2".

I found out that in MS Excel there is an EVALUATE() function (which seems a bit tricky to use properly). But I could not find anything similar in Google Docs.

I also searched through the function list, but could not find anything helpful...

Solution 1

No, there's no equivalent to Excel's EVALUATE() in Google Sheets.

There's long history behind this one, see this old post for instance.

If you're just interested in simple math (as shown in your question), that can be done easily with a custom function.

function doMath( formula ) {
  // Strip leading "=" if there
  if (formula.charAt(0) === '=') formula = formula.substring(1);
  return eval(formula)
}

For example, with your A1, put =doMath(A1) in another cell, and it will be 3.

Solution 2

Short answer

As was mentioned previously, Google Sheets doesn't have a built-in EVALUATE function, but Google Sheets could be extended to add this function. Fortunately some SocialCalc files could be used to make this easier.

Script

On Google spreadsheet I'm sharing my progress. At this time I added the SocialCalc files that I think that are required and a couple of functions, and several test cases.

NOTES:

1. Google Sheets specific functions like FILTER, UNIQUE, among others are not available in SocialCalc as well as other functions like SIGN.
2. I think that the SocialCalc file should be replaced by those on https://github.com/marcelklehr/socialcalc as it looks to be updated recently. H/T to eddyparkinson (see https://stackoverflow.com/a/16329364/1595451)

Uses

The EVALUATE function on the linked file could be used as a custom function.

Example 1

A1: '=1+2 (please note the use of an apostrophe to make the formula be treated by Google Sheets as a string.

B1 formula:

=EVALUATE(A1)

B1 display value:

3

Example 2

To "EVALUATE" a formula like =VLOOKUP(2,A1:B3,2), at this time we need to use the "advanced" parameters. See the following example:

B1: '=VLOOKUP(2,A1:B3,2)

C1 formula:

=EVALUATE(B1,"data","A1:B3")

C1 display value:

B

Code.gs

/**
 *
 * Evaluates a string formula
 *
 * @param {"=1+1"}  formula   Formula string 
 * @param {"Tests"} sheetName Target sheet. 
 * @param {"A1"}    coord     Target cell. 
 * 
 * @customfunction
 *
 */
function EVALUATE(formula,sheetName,coord){
  // SocialCalc Sheet object
  var scSheet = new SocialCalc.Sheet();
  if(sheetName && coord){
    // Pass values from a Google sheet to a SocialCalc sheet
    GS_TO_SC(scSheet,coord,sheetName);
  }
  var parseinfo = SocialCalc.Formula.ParseFormulaIntoTokens(formula.substring(1));
  var value = SocialCalc.Formula.evaluate_parsed_formula(parseinfo,scSheet,1); // parse formula, allowing range return
  if(value.type != 'e'){
    return value.value;
  } else {
    return value.error;
  }
}
/**
 *
 * Pass the Google spreadsheet values of the specified range 
 * to a SocialCalc sheet
 *
 * See Cell Class on socialcalc-3 for details
 *
 */
function GS_TO_SC(scSheet,coord,sheetName){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  if(sheetName){
    var sheet = ss.getSheetByName(sheetName);
    var range = sheet.getRange(coord);
    } else {
      var range = ss.getRange(coord);
  }
  var rows = range.getNumRows();
  var columns = range.getNumColumns();
  var cell,A1Notation,dtype,value,vtype;
  // Double loop to pass cells in range to SocialCalc sheet
  for(var row = 1; row <= rows; row++){
    for(var column = 1; column <= columns; column++){
      cell = range.getCell(row,column);
      A1Notation = cell.getA1Notation();
      value = cell.getValue();
      if(cell.isBlank()){
        dtype = 'b';
        vtype = 'b';
      } else {
        switch(typeof value){
          case 'string':
            dtype = 't';
            vtype = 't';
            break;
          case 'date':
          case 'number':
            dtype = 'v'
            vtype = 'n';
            break;
        }
      }
      scSheet.cells[A1Notation] = {
        datavalue: value,
        datatype: dtype,
        valuetype: vtype
      }
    }
  }
}

formula1.gs

https://github.com/DanBricklin/socialcalc/blob/master/formula1.js

socialcalcconstants.gs

https://github.com/DanBricklin/socialcalc/blob/master/socialcalcconstants.js

socialcalc-3.gs

https://github.com/DanBricklin/socialcalc/blob/master/socialcalc-3.js

Solution 3

I know this an old post. I'm just wondering, why nobody suggested:

myCell.getValue();

This will give you the result of the formula in myCell (3 in your example).

If you want to write the result to the cell (instead of the formula), you could use:

function fixFormula(myCell) {
    myCell.setValue(myCell.getValue());
}

Solution 4

If you want to evaluate simple math(like A1: "(1+2)*9/3"), you can use query:

=query(,"Select "&A1&" label "&A1&" ''",0)

Solution 5

Copy and paste the formulas:

Maybe you can copy and paste the formulas you need from "jQuery.sheet". Moved to:

https://github.com/Spreadsheets/WickedGrid

Looks to be all "open source"

Wont fix the issue

Also: The issue "Enable scripts to use standard spreadsheet functions" is marked as "Wont fix", see https://code.google.com/p/google-apps-script-issues/issues/detail?id=26

Ethercalc there is a google like opensource spreadsheet called Ethercalc

GUI Code: https://github.com/audreyt/ethercalc

Formulas: https://github.com/marcelklehr/socialcalc

Demo - on sandstorm: https://apps.sandstorm.io/app/a0n6hwm32zjsrzes8gnjg734dh6jwt7x83xdgytspe761pe2asw0

View more solutions