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.

Solution 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);
}

Solution 2

I have a solution for my own use case. My investment broker exports data to its users in (badly-formatted) Excel. I do my own analysis in Google Sheets. I have found copy/pasting entire sheets of data to be accident-prone.

I have partially automated updating each tab of the records. In the sheet where I maintain all the records, the First tab is named "Summary"

1. Save the broker's .xlsx data to Google Sheets (File | Save as Google Sheets);
2. In the tab named Summary, enter into a cell, say "Summary!A1" the URL of this Google Sheet;
3. In cell A2 enter: =Char(34)&","&CHAR(34)&"Balances!A1:L5"&Char(34)&")"
4. In the next tab, enter in cell A1: ="IMPORTRANGE("&Char(34)&Summary!A1&Summary!A2 The leading double quote ensures that the entry is saved as a text string. Select and copy this text string
5. in cell A3, type an initial "=" + Paste Special.
6. This will produce an importrange of the desired text, starting at cell A3