Google Sheets - Method to Evaluate a Custom Formula with Variables

Question

I want to make a spreadsheet that can evaluate a formula that is built by the user.

If you look up about evaluating what's in a sheet cell, you can find this thread: https://stackoverflow.com/questions/16303680/is-there-a-way-to-evaluate-a-formula-that-is-stored-in-a-cell

which gives a function that will evaluate a math expression that gets typed into a single cell.

I want to be able to do this, but also include "variable names" in the cell content. The values for the variables would be stored in a range elsewhere in the spreadsheet. So for example:

Length / 0.8 * Width

where values for Length and Width will be defined in a range.

I have been able to get something that works if I only have 1 variable name, but handling multiple variable names is proving very difficult to me.

Will I have to resort to using the scripting engine to do this?

Answer 1

Yes, you will likely need to use Google Apps Script to handle a custom formula that includes variable names.

In Google Apps Script, you can use the eval() function to evaluate a formula that is stored as a string in a cell. To use variable names, you'll need to retrieve the values for those variables from a range in the sheet and substitute them into the formula before evaluating it.

Here's an example of how you can do this in Google Apps Script:

function evaluateFormula(formula) {
// Get the values of the variables from the sheet
var length = SpreadsheetApp.getActiveSheet().getRange("A1").getValue();
var width = SpreadsheetApp.getActiveSheet().getRange("B1").getValue();

// Substitute the values into the formula
var formulaWithValues = formula.replace("Length", length).replace("Width", width);

// Evaluate the formula
var result = eval(formulaWith

function evaluateFormula(formula) {
// Get the values of the variables from the sheet
var length = SpreadsheetApp.getActiveSheet().getRange("A1").getValue();
var width = SpreadsheetApp.getActiveSheet().getRange("B1").getValue();

// Substitute the values into the formula
var formulaWithValues = formula.replace("Length", length).replace("Width", width);

// Evaluate the formula
var result = eval(formulaWithValues);

// Return the result
return result;
}

You can call this function and pass in your formula string to get the result. Note that the eval() function can be dangerous as it can execute any JavaScript code, so be careful with the inputs you pass to it.