Why isn't my setFormula not accepting multiple IF formulas?-CodePudding

I can't write the correct formula with multiple IF function in AppScript for Google Sheets

function myFynction() {
      var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      ss.getRange("J2").setFormula("=
      IF ('Another Sheet'!$E2<>"";'Another Sheet'!$E2; 
      IF ('Another Sheet'!$G2<>"";'Another Sheet'!$G2; 
      IF ('Another Sheet'!$I2<>"";'Another Sheet'!$I2; 
      IF ('Another Sheet'!$K2<>"";'Another Sheet'!$K2;""))))");
      
      var lr = ss.getLastRow();
      var fillDownRange = ss.getRange (2, 6, lr-1);
      ss.getRange ("J2").copyTo(fillDownRange);

CodePudding user response：

In your script, the double quotes are used in the formula while the formula is enclosed by the double quotes. And, the line break is used. I think that these are the reason for your issue. If you want to put your formula by reflecting the line break, how about enclosing the formula with the backtick (`) characters as follows?

Pattern 1:

In this pattern, your showing formula is used.

function myFynction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange("J2").setFormula(`=
IF ('Another Sheet'!$E2<>"";'Another Sheet'!$E2; 
IF ('Another Sheet'!$G2<>"";'Another Sheet'!$G2; 
IF ('Another Sheet'!$I2<>"";'Another Sheet'!$I2; 
IF ('Another Sheet'!$K2<>"";'Another Sheet'!$K2;""))))`);
  var lr = ss.getLastRow();
  var fillDownRange = ss.getRange(2, 6, lr - 1);
  ss.getRange("J2").copyTo(fillDownRange);
}

Pattern 2:

In this pattern, the line break of \n is used for your showing formula.

function myFynction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange("J2").setFormula(`=\nIF ('Another Sheet'!$E2<>"";'Another Sheet'!$E2; \nIF ('Another Sheet'!$G2<>"";'Another Sheet'!$G2; \nIF ('Another Sheet'!$I2<>"";'Another Sheet'!$I2; \nIF ('Another Sheet'!$K2<>"";'Another Sheet'!$K2;""))))`);
  var lr = ss.getLastRow();
  var fillDownRange = ss.getRange(2, 6, lr - 1);
  ss.getRange("J2").copyTo(fillDownRange);
}