Update Sheets Rows with Formula-CodePudding

I am currently trying to make a function that allows a sheet to add the same formula to each row in column B upon insertion of new rows, so that only blank values will yield formula insertion. I based this on the answer given by Oriol Castander. I got his answer to work for one of my formulas, but not the ones for col B:

My current code for col B is:

function updateformula(){
  
 var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Active Student List") 

  var values = ss.getRange("B2:B").getValues();
  
  for(var i = 0; i<values.length; i  ){
        if (values[i] == ""){
         
          var rangeToModify = ss.getRange("B2:B");
          rangeToModify.setValue('=(IF(ISBLANK(A2)=TRUE," ", IF(ISNA(VLOOKUP(A2,'Student Matches'!D$2:D,1,FALSE)),"Yes","No")))'); 
        }
      }

Apps Script spits out error for the "rangetoModify" line with the code inside the .setValue(), likely because it looks like a mess. However, everything within the ' ' is the formula copied directly, which has worked in other codes and in Oriol Castander's solution. Any ideas how to make the last line work? It just seems to be a technical thing (not with Sheet).

CodePudding user response：

Description

The only way I was able to get your formula to work is to change the single quotes of the entire formula to double quotes. Then every occurance of double quote within the formula has to be escaped as shown. Then the single quotes on the sheet name will work. For ranges including sheet names with a space only single quotes will work in a formula.

Script

rangeToModify.setValue("=(IF(ISBLANK(A2)=TRUE,\" \", IF(ISNA(VLOOKUP(A2,'Student Matches'!D$2:D,1,FALSE)),\"Yes\",\"No\")))");