Converting this Formula into Google Sheet Script but unable to do this

Question

I have been using below formula and trying to convert it into Google Sheet App Script. Because I am using number of such conditions with that formula to populate a single result.

And this formula is becoming bigger and bigger day by day. So looking forward to your response if someone can replace it with a script so i will try to replace other conditions as well.

I have tried to replace it but it was not working even a single line.

Your help will be highly appreciated for this issue.

    =IFERROR(IF(AND($B5<>"",$H5<>""), 
    IF(AND($H5<>"",$I5<>""), 

    IF(OR(AND(DATEDIF($J5,$H5,"D")<1095,$AE5="")),"***MADE ( " &TEXT($H5,"MM-DD-YYYY")&")- " & 
    
IF(AND(DATEDIF($J5,$H5,"D")<1095,$AE5=""), " MIS? "&",",""),"***MADE ( " &TEXT($H5,"MM-DD-YYYY")&")- " &" COMPLETE -2021 ")& 

    IF(OR(AND(DATEDIF($J5,$H5,"D")<1095,$AE5<>""))," DONE=> "& 

    IF(AND(DATEDIF($J5,$H5,"D")<1095,$AE5<>""),"MIS "&TEXT($AE5," MM.DD.YYYY "&", "),""),"")&"***","*******"),"*******"),"ERROR")

Here is the attached Sheet where formula works and give the result. https://docs.google.com/spreadsheets/d/1v3CP7D0V13Tr3ykwPQNkpYVuEB6uNrQjqtwq2bx20-8/edit?usp=sharing

question from:https://stackoverflow.com/questions/65831805/converting-this-formula-into-google-sheet-script-but-unable-to-do-this

Answer 1

You're making a pretty tedious request for someone to undergo. However, since you say you haven't gotten a single line to work, I'll provide one example below. Hopefully that gets you started, but you probably should consider taking a course or following some videos before asking for more help. A couple I would recommend are:

• Ben Collins Courses
• Learn Google Sheets Youtube Channel

Both are run by good guys.

Spreadsheet formula:

IF(AND($H5<>"",$I5<>""),

Would be scripted as...

  var ss =SpreadsheetApp.getActiveSheet();
  if(ss.getRange("i5").getValue()!='' &&
    ss.getRange("h5").getValue()!=''){
      //code for when true

  }