Google Apps Script with multiple IF conditions

Question

I use this formula in Column G

=IF(ISBLANK(A3),"User missing",IFS(C3=E3,0,E3="","Empty Data",C3<>E3,"Wrong Data"))

Applying this formula for 30k data make my sheet slow. Need to convert to script.

As If and IFS is used in same formula, I am facing difficult to convert this to Script.

Condition:

If Column A is Empty then "User Missing"
If A have data, 
then condition is
If E is empty then "Empty Data"
If C and E is not equal then "Wrong Data"
If C and E is equal then 0

Marios · Accepted Answer · 2020-12-21 16:50:30Z

3

Given that you need the columns A,C,E I used the range A2:E and paste the resulting data in column F:

function myFunction(){
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sh = ss.getSheetByName("Sheet1");
    const vals = sh.getRange("A2:E"+sh.getLastRow()).getValues();
    const cvals = []; 
    vals.forEach(r=>{           
         cvals.push( [
                 r[0]==''?"User Missing":
                 r[4]==''?"Empty Data":
                 r[2]!=r[4]?"Wrong Data":0
         ])            
      });
    sh.getRange(2,6,cvals.length,1).setValues(cvals); // paste in column F
}

edited Dec 21, 2020 at 16:50

answered Dec 21, 2020 at 16:11

Marios

27.5k9 gold badges40 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Moses Over a year ago

Same question here, Instead C,D,E . If I use A,C,E How will be the vals?

Marios Over a year ago

please edit your question to update the conditions based on the new columns.

Marios Over a year ago

@Moses not sure what you edited. You want to change only the input columns? the conditions are based on columns C,D,E again? Please edit this part and change the column letters you want to use:

If Column D is Empty then "User Missing" If D have data,  then condition is If E is empty then "Empty Data" If C and E is not equal then "Wrong Data" If C and E is equal then 0

Marios Over a year ago

@Moses I updated the answer. let me know if it worked

Collectives™ on Stack Overflow

Google Apps Script with multiple IF conditions

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related