object - I am having trouble with an if function, I am trying to build a small module and a comparion operator is not functioning -
i trying build small module can automate gantt chart. color cells within date range , color weekends differently. however, specific section giving me trouble.
if (celldate>=schstartdate && celldate<=schenddate){ if(celldate.getday()>=1 && celldate.getday()<=5){ sheet.getrange(j,k).setbackground("#9193ff"); } else sheet.getrange(j,k).setbackground("#f2f4d9"); if (celldate==schstartdate && sheet.getrange(2,k).getvalue() == startshift){sheet.getrange(j,k).setbackground("red");} while first sections works perfectly, second section "==" operator not giving desired result. in the section above "equal to" condition works absolutely fine , colors cell expected same "equal to" condition doesnt seem work in
if (celldate==schstartdate && sheet.getrange(2,k).getvalue() == startshift) {sheet.getrange(j,k).setbackground("red");} the entire code below
function plannedganttchart(){ var ui = spreadsheetapp.getui(); var ss = spreadsheetapp.getactivespreadsheet() ss.setactivesheet(ss.getsheetbyname("search")); var sheet = spreadsheetapp.getactivesheet(); var startrow = 2; var numrows = spreadsheetapp.getactivespreadsheet().getlastrow(); var startcolumn=1; var numcolumns = spreadsheetapp.getactivespreadsheet().getlastcolumn(); sheet.getrange("n3:bw65").setbackground("white"); //recolors entire sheet (i=1;i<=numcolumns-124;i++){ if(sheet.getrange(2,i).getvalue()=="planned start date"){ var startdatecolumn=i; } //assigns column containing planned start date if(sheet.getrange(2,i).getvalue()=="days"){ var shiftcolumn=i; } //assigns column containing shifts if(sheet.getrange(2,i).getvalue()=="planned end date"){ var enddatecolumn=i; } //assigns column containing planned end date } // section allocates planned start date day , shift (j=3; j<=numrows; j++) { var plannedstartdate = new date(sheet.getrange(j,startdatecolumn).getvalue()); var startdate = plannedstartdate.getdate(); var starthour = plannedstartdate.gethours(); if (starthour>=6 && starthour<16) var startshift="am" //allocates shift based on hour else if(starthour>=16 && starthour <=23) var startshift="pm"; // allocates pm shift based on hour else if(starthour>=0 && starthour <=2){ startdate=startdate-1; // if dates falls after midnight docks start date 1 , assigns pm shift var startshift="pm"; } // section allocates planned end date day , shift var plannedenddate=new date(sheet.getrange(j,enddatecolumn).getvalue()); var enddate = plannedenddate.getdate(); var endhour = plannedenddate.gethours(); if (endhour>=6 && endhour<16){ var endshift="am"; //allocates shift based on hour } else if (endhour>=16 && endhour<=23){ var endshift = "pm"; // allocates pm shift based on hour } else if (endhour>=0 && endhour<=2){ enddate = enddate-1; //dates falls after midnight docks start date 1 , assigns pm shift endshift = "pm"; } if(math.round(sheet.getrange(j,shiftcolumn).getvalue()*2)<(sheet.getrange(j,shiftcolumn).getvalue()*2)) // calculates number of required shifts var shifts= math.round(sheet.getrange(j,shiftcolumn).getvalue()*2)+1; else var shifts =math.round(sheet.getrange(j,shiftcolumn).getvalue()*2); var schstartdate= new date(plannedstartdate.getfullyear(),plannedstartdate.getmonth(),startdate); var schenddate= new date(plannedenddate.getfullyear(),plannedenddate.getmonth(),enddate); var shiftcount=0; //initializes counter of shift // potential error area, section, (k=14;k<=75;k++){ //k=14 reflect column number of grid var tempdate1=new date(plannedstartdate.getfullyear(),plannedstartdate.getmonth(),sheet.getrange(1,k).getvalue()); var tempdate2=new date(plannedstartdate.getfullyear(),plannedstartdate.getmonth()+1,sheet.getrange(1,k).getvalue()); if (tempdate1 >= schstartdate && tempdate1 <= schenddate) celldate = tempdate1; else celldate = tempdate2; if (celldate>=schstartdate && celldate<=schenddate){ if(celldate.getday()>=1 && celldate.getday()<=5){ sheet.getrange(j,k).setbackground("#9193ff"); } else sheet.getrange(j,k).setbackground("#f2f4d9"); if (celldate==schstartdate && sheet.getrange(2,k).getvalue() == startshift){sheet.getrange(j,k).setbackground("red");} } /*if(sheet.getrange(1,k).getvalue() == startdate && sheet.getrange(2,k).getvalue() == startshift){ sheet.getrange(j,k).setbackground("#9193ff"); //shades background color blue of starting shift shiftcount++; //increases counter of shifts allocated sheet.getrange(j,k).setvalue(shiftcount); } else { if(shiftcount>0 && (sheet.getrange(1,k).getvalue()<=enddate|| shiftcount<=shifts)){ if(celldate.getday()>=1 && celldate.getday()<=5){ sheet.getrange(j,k).setbackground("#9193ff"); //sequentially runs across cells changing them planned color shiftcount++; sheet.getrange(j,k).setvalue(shiftcount); } //increases counter of shifts allocated else{ sheet.getrange(j,k).setbackground("#f2f4d9"); sheet.getrange(j,k).setvalue(shiftcount);} //colours weekends separately falls shift if(shiftcount>0 && sheet.getrange(1,k).getvalue()==enddate && sheet.getrange(2,k).getvalue()=="pm" && endshift=="am"){ sheet.getrange(j,k).setbackground("white"); sheet.getrange(j,k).setvalue(shiftcount); shiftcount--; } }*/ } // k loop close bracket //sheet.getrange(j,13).setvalue(shiftcount); // assigns number of shifts iterated } // j loop close bracket } //function close bracket
Comments
Post a Comment