Turning an =IFS statement into a script in Google Sheets

Question

In a current Google Sheets book (on the main tab, named "Master") this formula currently sits in Column E, starting in Row 3, going down to the end of the sheet.

=IFS(V3="","",AND(V3<>"", AJ3=""),"Needs Invoice"
,AND(AJ3<>"", AM3=""),"Payment Due",AI3<>AN3,"Partial Payment Due",AI3=AN3,"Paid")

For clarity:

If "V" is blank, "E" is also blank
If "V" is NOT blank and AJ is blank, "E" is "Needs Invoice"
If AJ is NOT blank and AM is blank, "E" is "Payment Due"
If AI and AN both have values and are NOT equal, "E" is "Partial Payment Due"
If AI and AN both have values and are equal, "E" is "Paid"

This works for what I need it to do, but I'd much rather have this as a script to fill column E and run on edit of columns V, AI, AJ, AM or AN (the columns used in the above formula).

I have no idea how to go about getting this started, as it is way above my skill level. Any help would be great!

Cooper Cooper · Accepted Answer · 2019-02-26T21:29:25

If "V" is blank, "E" is also blank
If "V" is NOT blank and AJ is blank, "E" is "Needs Invoice"
If AJ is NOT blank and AM is blank, "E" is "Payment Due"
If AI and AN both have values and are NOT equal, "E" is "Partial Payment Due"
If AI and AN both have values and are equal, "E" is "Paid"

function ifToScript() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Master');
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  for(var i=0;i<vA.length;i++) {
    if(!vA[i][21]) {
      vA[i][4]='';
    }
    if(vA[i][21] && !vA[i][35]) {
      vA[i][4]="Needs Invoice";
    }
    if(vA[i][35] && !vA[i][38]) {
      vA[i][4]="Payment Due";
    }
    if(vA[i][34] && vA[i][39] && vA[i][34]!=vA[i][39]) {
      vA[i][4]="Partial Payment Due";
    }
    if(vA[i][34] && vA[i][39] && vA[i][34]==vA[i][39]) {
      vA[i][4]="Paid";
    }  
  }
  rg.setValues(vA);
}

Try the below as an onEdit(). Note: vA.length will always be 1.

function onEdit(e) {
  var ss=e.source;
  var rg=e.range;
  var sh=e.range.getSheet();
  var col=e.range.columnStart;
  var row=e.range.rowStart;
  var colA=[22,35,36,39,40]
  if(sh.getName()!='Master'){return;}
  if(colA.indexOf(col)>-1 && sh.getName()=='Master'){
    var rg=sh.getRange(e.range.rowStart,1,1,40);
    var vA=rg.getValues();
    for(var i=0;i<vA.length;i++) {
      if(!vA[i][21]) {
        vA[i][4]='';
      }
      if(vA[i][21] && !vA[i][35]) {
        vA[i][4]="Needs Invoice";
      }
      if(vA[i][35] && !vA[i][38]) {
        vA[i][4]="Payment Due";
      }
      if(vA[i][34] && vA[i][39] && vA[i][34]!=vA[i][39]) {
        vA[i][4]="Partial Payment Due";
      }
      if(vA[i][34] && vA[i][39] && vA[i][34]==vA[i][39]) {
        vA[i][4]="Paid";
      }  
    }
  rg.setValues(vA);
  }
}

Turning an =IFS statement into a script in Google Sheets

1 Answers