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

by

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

More Related Contents:

  1. Is there a way to evaluate a formula that is stored in a cell?
  2. Refresh data retrieved by a custom function in Google Sheet
  3. How do you create a “reverse pivot” in Google Sheets?
  4. How to protect the Apps Script code in a Google spreadsheet?
  5. Create Google Calendar Events from Spreadsheet but prevent duplicates
  6. ultimate short custom number formatting – K, M, B, T, etc., Q, D, Googol
  7. Google Apps Script: How to set “Use column A as labels” in chart embedded in spreadsheet?
  8. Retrieving Google Sheets cell border style programmatically
  9. Determining the last row in a single column
  10. Cannot find method getRange(number,number)?
  11. How do I add formulas to Google Sheets using Google Apps Script?
  12. You do not have permission to call openById
  13. Deleting rows in google sheets using Google Apps Script
  14. Google Apps Script, copy one spreadsheet to another spreadsheet with formatting
  15. Delete a row in Google Spreadsheets if value of cell in said row is 0 or blank
  16. Single Google Form for multiple Sheets [duplicate]
  17. Adding Buttons To Google Sheets and Set value to Cells on clicking
  18. Custom function throws a “You do not have the permission required to setValue” error
  19. Margins parameters for Google spreadsheet export as PDF
  20. Google Apps Script: Dynamically creating spreadsheet menu items
  21. Script to convert .XLSX to Google Sheet and move converted file
  22. Hidden field in a Google Form
  23. onEdit() doesn’t catch all changes
  24. Apply multiple font colors to the text in a single Google Sheets cell
  25. Adding a property to an Html template gives error “Object does not allow properties to be added or changed”
  26. TypeError: Cannot read property ‘getRange’ of null (line 6, file “Code”) in google sheet script [duplicate]
  27. Google spreadsheet “Cannot call method “getRange” of null”
  28. .setFormula() and received error missing ) argument list
  29. How can I merge multiple tabs in a Google Spreadsheet using Google App Script?
  30. Passing cell references to spreadsheet functions

Leave a Comment