Convert a string to a formula in Google Spreadsheet

Question

I´d been more than 10 hours reading and trying different options with no success.

I have this string (this is actually a string that is generated by other formulas)

QUERY({IMPORTRANGE(A1;$D$1);IMPORTRANGE(A2;$D$1);IMPORTRANGE(A3;$D$1);IMPORTRANGE(A4;$D$1)};"select Col13, sum(Col1), sum(Col2), sum(Col3), sum(Col4), sum(Col5), sum(Col6), sum(Col7), sum(Col8), sum(Col9), sum(Col10), sum(Col11), sum(Col12) group by Col13";0)

And I want it to be read as a formula. So for example I try this function:

function doConvert(formula) {
  // Strip leading "=" if there
  if (formula.charAt(0) === '=') formula = formula.substring(1);
  return eval(formula);
}

But I get:

Error / SyntaxError: Falta ":" detrás del ID de propiedad.

(In English would be: ":" missing after property ID.

Any other solution would be great.

Please share a copy of your spreadsheet and include an example of a successful outcome. — Tedinoz, Jan 12 '20 at 08:15

score 1 · Accepted Answer · answered Jan 12 '20 at 15:55

1

add = to your generated string and try like this:

function onEdit() { 
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet_Name_Here');  
var src = sheet.getRange("A1");              // The cell which holds the formula
var str = src.getValue(); 
var cell = sheet.getRange("C5");             // The cell where you want the results to be in
cell.setFormula(str);              
}

answered Jan 12 '20 at 15:55

player0

124,011
12
67
124

1

Hi, thanks! This worked fine! I´d just edited line 2 with: "var sheet = SpreadsheetApp.getActiveSheet();" so I don´t have to refer to Sheet Name. – Gastón BA Jan 12 '20 at 20:06

Rubén · Answer 2 · 2020-01-12T20:14:18.587

1

Google Sheets formulas can't be evaluated on the server/client code, only on the Google Sheets UI.

If you are looking that your string be passed as a formula to a cell, then use the setFormula(formula) method from the Class Range

NOTE: there is no need to preppend the equal sign to the formula.

Example

Assuming that the formula string is in A1 and that you want to put is as formula in B1

function setFormula(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var source= sheet.getRange('A1');
  var formula = source.getValue();
  var target = sheet.getRange('B1');
  target.setFormula(formula);
}

answered Jan 12 '20 at 19:05

Rubén

34,714
9
70
166

Thanks! This worked fine! I use this formula but I change the first line with _function onEdit() {_ because everything was working using the play button on the script editor, but I didn´t know how to activate it from the spreadsheet. How was I suppose to make this run automatically? – Gastón BA Jan 12 '20 at 20:10
@GastónBA To run a function from the spreadsheet you could add an image an assign the function to it so each time the image is clicked the function will be ran. Also you could add a custom menu. Using simple on edit trigger should work, but perhaps you should made other changes too, like using the edit event object to get the calculated formula string when one of the referenced cells be edited. . If you need further help, post a new question. – Rubén Jan 12 '20 at 20:19

Convert a string to a formula in Google Spreadsheet

2 Answers2

Linked