CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formulagoogle-sheets-queryimportrange
Gastón BA
Gastón BA

Reputation: 53

Convert a string to a formula in Google Spreadsheet

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.

Upvotes: 5

Views: 10450

Answers (2)

Wicket
Wicket

Reputation: 38200

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

Related

Upvotes: 1

player0
player0

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

Upvotes: 1

Related Questions