Evaluate/Calculate a Concatenate statement using google Script

Question

I have an input from the user in the format "12mm*300*300" in a single cell. I need to calculate the same using some formula or script but i am unable to get the desired result. The formula I used directly in Google Sheets was

=concatenate("=Round(",substitute(E1,"mm",""),"*7.85,1)")

but the same resulted in output of

=Round(12*300*300*7.85,1)

but did not calculate the same. Secondly, I tried the same using google script function setformula but that also resulted in error.

var formul =["=,concatenate(\"Round(\",substitute($B"+row+",\"mm\",\"\"),\"\*7.85/1000000,1)\"))"];
spreadsheet.getRange(range4).setFormula(formul);

Answer 1

Query can do simple math on strings like addition, subtraction, multiplication and division. Try

=ROUND(QUERY(,"SELECT "&CONCATENATE(SUBSTITUTE(E1,"mm",""),"*7.85")&" LABEL "&CONCATENATE(SUBSTITUTE(E1,"mm",""),"*7.85")&" ''",0))