How to Insert Value into Another Cell Entirely-CodePudding

We want to insert the formula =IF(E2="","","No") into column F of the attached Sheet if column E is blank. However, this is a bit tricky for a few reasons. Column F already has this formula. We have a separate apps script that basically overwrites this and inserts "Yes" when an email is sent. On "No" the email is sent, so "Yes" prevents the email sending again.

The apps script for trying to reset "Yes" back to the formula =IF(E2="","","No") is below. The issue is that if you have "Yes" in column F, even when column E has values (not "" as the code says), it somehow converts the "Yes" into "No". The issue seems to be that the code overwrites all of column F with the function, rather than the cells that just have column E blank. We are not experts at this so your patience and guidance is appreciated!

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

  var values = ss.getRange("E2:E").getValues();
  
  for(var i = 0; i<values.length; i  ){
        if (values[i] == ""){
         
          var rangeEToModify = ss.getRange("E2:E");
          rangeEToModify.setValue('=IFERROR(INDEX(StudentMatches!B:B,Match(A2,StudentMatches!D:D,0)),"")'); 
          var rangeFToModify = ss.getRange("F2:F");
          rangeFToModify.setValue('=IF(E2="","","No")'); 
        }
      }}

Link to sheet

CodePudding user response：

Insert formula into a column of cells

function updateEformula() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Active Student List");
  const rg = sh.getRange(2, 5, sh.getLastRow() - 1);
  const vs = rg.getValues();
  vs.forEach((r,i) => {
    if(r[0] == "") {
      sh.getRange(i 2,5).setFormula(formula for row i   2,column 5);
      sh.getRange(i 2,6).setFormula(formule for row i 2,column 6)
    }
  });
}

CodePudding user response：

@Cooper provided most of the answer above. However, we adjusted the last 2 lines so that the formulas adjust for each row in the sheet. This required the use of template literals. I borrowed the example of usage of template literals here.

function updateEFformulas() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Active Student List");
  const rg = sh.getRange(2, 5, sh.getLastRow() - 1);
  const vs = rg.getValues();
  vs.forEach((r,i) => {
    if(r[0] == "") {
      sh.getRange(i 2,5).setValue('=IFERROR(INDEX(StudentMatches!B:B,Match'   '(A'   (i 2)   ',StudentMatches!D:D,0)),"")');
      sh.getRange(i 2,6).setValue('=IF'   '(E'   (i 2)   '="","","No")')
    }
  });
}