CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
user14398375
user14398375

Reputation: 175

Google Apps Script - can't set formula with concat

I made an application with google drive and sheets api the problem is that the sheet has some formulas that has conflicts with the created api so I was forced to erase the formulas and write a little app script to autofill the formulas. I was following this [tutorial][1]. But as the formula has strings to concat it throws a syntax error. My code is here:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange("I2").setFormula(CONCAT("problem string";"\n";C2;"\n";F2));

}

Do i need to declare the string as a variable?

EDIT: i am also having problems when using the \n new line character [1]: https://www.youtube.com/watch?v=cCBtsQGtzoQ

Upvotes: 0

Views: 1389

Answers (1)

Marios
Marios

Reputation: 27350

Issues:

  • setFormula accepts a string but you are passing CONCAT which is not defined nor it is a built in JavaScript method. To make it a string directly, you can use template literals.

  • also your formula is wrong because CONCAT can concatenate only 2 strings, not 5. You need to use CONCATENATE.

Solution:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange("I2").setFormula(`CONCATENATE("problem string";"\n";C2;"\n";F2)`);
}

Output:

output

Upvotes: 2

Related Questions