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

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.

Koerlin

I also hope, the evaluate feature would be implemented! For your case, why INDIRECT didn't work? – Opheliaophelie

See this thread...#16304180 – Whatnot

Indirect is not the option I was looking for because I need to have a template spreadsheet with all formulas (mostly query) that several others spreadsheet will use. Very often, I need to modify the formula for all of them and that's why I wanted to share the same formula somewhere. – Koerlin

I've seen that post from 2013 before adding my one. I thought in 3 years they added some options. I've also read the most recent comment but at first glance, I didn't find it useful being the formula in a different spreadsheet but after Ruben shared the same idea, I thought that using and onOpen trigger in the script I can import the range of formulas using SpreadsheetApp.openById and then setting values where I need as suggested. – Koerlin

Regarding "that post" I recently posted an answer including a script that uses a the formula1.js from SocialCalc. At this time that doesn't work for functions like UNIQUE but it's a start. – Hoff

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

Hoff

I didn't think about this solution because I have formulas in another spreadsheet. (see my first comment above). I used a workaround but after your suggestion of using setFormula, I'll try to import formulas using SpreadsheetApp.openById in an onOpen trigger and the I'll use the setFormula for each cell I've to write in. I'll try and let you all know. Thanks for answering. – Koerlin

@soneangel: Besides using a spreadsheet as the formula library, you could use a Google Apps Script library or Google spreadsheet add-on :) – Hoff

Rubén, thanks for the suggestion but (how) can I use a query() in a script and then saving as a library? – Koerlin

@Koerlin I was talking about having using file in a Google Apps Script project to store the spreadsheet formulas as strings. Write something like var formula = '=QUERY(range_or_array,select_statement,headers)'; (place the proper parameters). See developers.google.com/apps-script/guide_libraries for further details. – Hoff

Can't use setters in google sheets from a google app script. developers.google.com/apps-script/guides/sheets/… – Handbook

@dsutherland I'm sorry, but I don't understand what you mean and how this comment is related to this Q/A. Please post a new question. – Hoff

In your answer, you show a function which uses the "setFormula" function. If you are using a Google App Script to perform this action for a Google Spreadsheet, you will not be able to use the setter "setFormula" because they are explicitly not allowed in a Google App Sheet. You will get an access is denied when the function runs. This is 100% relevant. – Handbook

@dsutherland My answer says that setFormula can't be used in custom functions, instead use a custom menu. – Hoff

Answer 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

Antrim