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

score 1 · Accepted Answer · answered Sep 03 '20 at 19:10

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

answered Sep 03 '20 at 19:10

TheMaster

45,448
6
62
85

score 0 · Answer 2 · answered Sep 03 '20 at 19:05

Google Sheets can't evaluate "formulas" (text values) that include Google Sheets built-in functions.

One option is to use Google Apps Script to write the text as a formula by using setFormula/setFormulas¹.

Regarding

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

It's not working because setFormula requires a string but you the formul declaration is assigning an Array and the member of this Array is not being built using the correct formula syntax.

There is a comma after the = (remove it)
There are extra commas and "
Instead of using the built-in function SUBSTITUTE use a JavaScript method like String.prototype.replace:

var E1 = "2mm*300*300";
var formula = "=Round("+E1.replace('mm','')+"*7.85/1000000,1))"
console.info(formula)

NOTES:

It might also be used setValue/setValues but you will be relying on the Google Sheet data type automatic detection and some times this doesn't work well)

Evaluate/Calculate a Concatenate statement using google Script

2 Answers2