Click here to Skip to main content
15,666,844 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.


    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")

My sheet Link.
Sheet1 - Google Sheets[^]

What I have tried:

Could not find anything relevant which could convert his into Google Sheet App Script.

function myFunction() {

 var sheet = SpreadsheetApp.getActive().getSheetByName('DATA')
 var ss = sheet.getRange('AO3')

   if(ss.getRange("i5").getValue()!='' && ss.getRange("h5").getValue()!='')

ss = "Done";
Updated 28-Jan-21 5:43am

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900