How to use a formula written as a string in another cell [evaluate for Google Spreadsheet]

Question

I read several old posts about Google Spreadsheet missing the evaluate function. There is any solution in 2016?

The easiest example.

• 'A1' contains the following string: UNIQUE(C1:C5)
• 'B1' I want to evaluate in it the unique formula written in 'A1'.

I've tried concatenating in this way: 'B1' containing ="="&A1 but the outcome is the string =UNIQUE(C1:C5). I've also tried the indirect formula.

Any suggestion to break last hopes, please?

Additional note

The aim is to write formulas in a spreadsheet and use these formulas by several other spreadsheets. Therefore, any change has to be done in one place.

Answer 1

Short answer

Use a script that includes something like var formula = origin.getValue() to get the string and something like destination.setFormula(formula) to return the formula.

Explanation

As was already mentioned by the OP, Google Sheets doesn't have a EVALUATE() built-in function. A custom function can't be used because custom functions can only return one or multiple values but can't modify other cell properties.

A script triggered by a custom menu, events or from the Google Apps Script editor could be used to update the formulas of the specified cells.

Since the formulas will be kept as strings, it could be more easy to keep them in the script rather than in the spreadsheet itself.

Example

The following is a very simple script that adds the specified formula to the active range.

function addFormula() {
  var formula = '=UNIQUE(C1:C5)';
  var range = SpreadsheetApp.getActiveRange();
  range.setFormula(formula);
}