How do I make formula auto populate in gsheets with google app script

Question

I've got the script below that works for other less complex formula.

function onEdit (e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Statements to Import");

  var range = e.range;//The range of cells edited

  var columnOfCellEdited = range.getColumn();//Get column number
  //Logger.log(columnOfCellEdited)

  if (columnOfCellEdited === 1) {

ss.getRange("D2").setFormula("");
var lr2 = ss.getLastRow();
var fillDownRange2 = ss.getRange(2, 4, lr2-1);
ss.getRange("D2").copyTo(fillDownRange2);

when I enter the formaula I want to populate I get the error

Syntax error: SyntaxError: Invalid or unexpected token line: 11 file: Auto fill formula on main sheet.gs

The formula I want to enter is

=IF(AND(A2="",B2="", C2=""), "",  
IF(OR(E2="*Missing From Logic*", G2="Missing From Logic*", S2="Missing From Logic*"), "Create Path Logic",
IF(A2="", "Date Received",
IF(C2="", "Source ID",
IF(I2="", "Owner",
IF(K2="", "Location",
IF(FIND("Z:\My Drive\Library\",K2)<>1, "Description",
IF(I2="", "Currency",
IF(I2="", "Value (USD Approx)",
IF(I2="", "Owner",
IF(O2="", "Q",
IF(P2="", "Y",
IF(AND(Q2="", index('Path Logic'!$F:$F, MATCH($C2, 'Path Logic'!$A:$A, 0), 0)="Y"), "Usage Start",
IF(AND(R2="", index('Path Logic'!$F:$F, MATCH($C2, 'Path Logic'!$A:$A, 0), 0)="Y"), "Usage End",
IF(T2="", "Agreement Type",
IF(T2="", "Service Type",
IF(W2="", "Intended Payee",
IF(W2="", "Direct Collect",
IF(LEFT(F2,1)<>1, "Y", "TBC")))))))))))))))
))
))

When input to the script, I escape the double quotes and existing backslash. I'm assuming the issue is somewhere within escaping a character somewhere. Any help would be greatly appreciated!

Current script is

function onEdit (e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Statements to Import");

  var range = e.range;//The range of cells edited

  var columnOfCellEdited = range.getColumn();//Get column number
  //Logger.log(columnOfCellEdited)

  if (columnOfCellEdited === 1) {

ss.getRange("D2").setFormula("=IF(AND(A2=\"\",B2=\"\", C2=\"\"), \"\",  
IF(OR(E2=\"*Missing From Logic*\", G2=\"Missing From Logic*\", S2=\"Missing From Logic*\"), \"Create Path Logic\",
IF(A2=\"\", \"Date Received\",
IF(C2=\"\", \"Source ID\",
IF(I2=\"\", \"Owner\",
IF(K2=\"\", \"Location\",
IF(FIND(\"Z:\\My Drive\\Library\\\",K2)<>1, \"Description\",
IF(I2=\"\", \"Currency\",
IF(I2=\"\", \"Value (USD Approx)\",
IF(I2=\"\", \"Owner\",
IF(O2=\"\", \"Q\",
IF(P2=\"\", \"Y\",
IF(AND(Q2=\"\", index('Path Logic'!$F:$F, MATCH($C2, 'Path Logic'!$A:$A, 0), 0)=\"Y\"), \"Usage Start\",
IF(AND(R2=\"\", index('Path Logic'!$F:$F, MATCH($C2, 'Path Logic'!$A:$A, 0), 0)=\"Y\"), \"Usage End\",
IF(T2=\"\", \"Agreement Type\",
IF(T2=\"\", \"Service Type\",
IF(W2=\"\", \"Intended Payee\",
IF(W2=\"\", \"Direct Collect\",
IF(LEFT(F2,1)<>1, \"Y\", \"TBC\")))))))))))))))
))
))");
var lr2 = ss.getLastRow();
var fillDownRange2 = ss.getRange(2, 4, lr2-1);
ss.getRange("D2").copyTo(fillDownRange2);

Answer 1

Your javascript strings must be terminated before the next newline character. You can use Line Continuation Character (\ - back slash) to continue your string on the next line.

Sample Code:

function onEdit (e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Statements to Import");

  var range = e.range;//The range of cells edited

  var columnOfCellEdited = range.getColumn();//Get column number
  //Logger.log(columnOfCellEdited)

  if (columnOfCellEdited === 1) {

    ss.getRange("D2").setFormula("=IF(AND(A2=\"\",B2=\"\", C2=\"\"), \"\",\n \
    IF(OR(E2=\"*Missing From Logic*\", G2=\"Missing From Logic*\", S2=\"Missing From Logic*\"), \"Create Path Logic\",\n \
    IF(A2=\"\", \"Date Received\",\n \
    IF(C2=\"\", \"Source ID\",\n \
    IF(I2=\"\", \"Owner\",\n \
    IF(K2=\"\", \"Location\",\n \
    IF(FIND(\"Z:\\My Drive\\Library\\\",K2)<>1, \"Description\",\n \
    IF(I2=\"\", \"Currency\",\n \
    IF(I2=\"\", \"Value (USD Approx)\",\n \
    IF(I2=\"\", \"Owner\",\n \
    IF(O2=\"\", \"Q\",\n \
    IF(P2=\"\", \"Y\",\n \
    IF(AND(Q2=\"\", index('Path Logic'!$F:$F, MATCH($C2, 'Path Logic'!$A:$A, 0), 0)=\"Y\"), \"Usage Start\",\n \
    IF(AND(R2=\"\", index('Path Logic'!$F:$F, MATCH($C2, 'Path Logic'!$A:$A, 0), 0)=\"Y\"), \"Usage End\",\n \
    IF(T2=\"\", \"Agreement Type\",\n \
    IF(T2=\"\", \"Service Type\",\n \
    IF(W2=\"\", \"Intended Payee\",\n \
    IF(W2=\"\", \"Direct Collect\",\n \
    IF(LEFT(F2,1)<>1, \"Y\", \"TBC\"))))))))))))))) \n \
    ))\n \
    ))");
    var lr2 = ss.getLastRow();
    var fillDownRange2 = ss.getRange(2, 4, lr2-1);
    ss.getRange("D2").copyTo(fillDownRange2);
  }

}

• in this example, I appended \n \ at the end of each line to include a newline in the string and to continue the string on the next line

Output: