Skip to content

How do I change my formula syntax to use variables

I’m trying to use set formula but it keeps saying I’m having an issue with my syntax but I’m not exactly sure how to fix it. I’m trying to use declared variables as the inputs to the formula.
The bold line below is where I’m having the problem.

function LEARNBASICS() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var currentsheet = ss.getActiveSheet();

var targetsheet = currentsheet.getRange(17,17).getValue();

var startrange = currentsheet.getRange(18,17).getValue();

var endrange = currentsheet.getRange(19,17).getValue();

var currentcell = currentsheet.getCurrentCell();

return currentcell.setFormula("=COUNTIF('targetsheet'!'startrange':'endrange',">0")")
}

Answer

Use the concatenate operator + and the single and double quotes or template literals (template strings) properly.

Let say that you decided to use simple quotes. Since your formula requires the use of double quotes per Google Sheets formula syntax, you might use single quotes on each string literal. To do this, replace

return currentcell.setFormula("=COUNTIF('targetsheet'!'startrange':'endrange',">0")")

by

return currentcell.setFormula('=COUNTIF(' + targetsheet + '!' + startrange + ':' + endrange + ',">0")')

By the other hand if you decide to use template literals, the replace the referred code line by

return currentcell.setFormula(`=COUNTIF(${targetsheet}!${startrange}:${endrange},">0")`)

Related