-1

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")")
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Mounir Youssef
  • 13
  • 2

1 Answers1

1

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

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Hey so I tried the suggestions you mentioned but they aren't working. I just get this "Error : TypeError: "= COUNTIF(" is not a function" so im not sure what im doing wrong. Ill copy that last line of code again. return currentcell.setFormula("= COUNTIF("(targetsheet + '!' + startrange + ':' + endrange + ',">0")')) – Mounir Youssef Mar 08 '21 at 07:35
  • You are using double quotes instead of simple and there are extra parenthesis. Try this (already in the answer) -> `return currentcell.setFormula('=COUNTIF(' + targetsheet + '!' + startrange + ':' + endrange + ',">0")')` – Rubén Mar 08 '21 at 08:37
  • Oh sorry my bad. When I copied that correctly though, its gives me a Formula Parse Error. Also the way its setup will make so that the COUNTIF formula takes place instead of my custom formula, so it basically will only do it once, then if I change the range variables the cell won't update, am I right? How can I get it to update? – Mounir Youssef Mar 08 '21 at 10:45