Create an array with dates and add days between first and last dates except Saturday and Sunday

Question

I know there are some questions that looks similar to this, but I'm scratching my head with the specific necessity I have.

I have one GoogleSheet Column with automatic imported dates in each row like this (European dates): 22/04/2020, 23/04/2020, 24/04/2020, 28/04/2020. (one row is comma-separated for the example). On another column I have the day of the week with an arrayformula function:

PROPER(TEXT(B5:B;"DDDD"))

My function on Google App Script is creating an array with all the dates associated with the name of the day

let numberOfDates = currentSheet.getRange(5,1).getValue(); // I have the number of dates written on the Sheet, I just get the value
    let arrayDates = [];
    for (let i=0;i<numberOfDates;i++){
    arrayDates.push([currentSheet.getRange(5+i,2).getValue(),currentSheet.getRange(5+i,3).getValue()]);  
              }

If I do Logger.log on the array, this is the type of value it will return

[[Wed Apr 22 00:00:00 GMT+02:00 2020, Wednesday], [Thu Apr 23 00:00:00 GMT+02:00 2020, Thursday], [Fri Apr 24 00:00:00 GMT+02:00 2020, Friday], [Tue Apr 28 00:00:00 GMT+02:00 2020, Tuesday]]

Now I also need to have Monday 27/04/2020 in this Array because I'm going to create a planning. The thing is I don't want to confuse what is added from what is the source so in the end, all added dates I will put them in red for example.

Sometimes, the set of dates at the source will be over a month and I will want to put in the mix all the dates in-between except Saturday and Sunday.

I guess it is complex, but maybe you could hit me with a solution on how to create this Array ?

Thank you very much !

Answer 1

Generates a date array between a start date and and end date and excludes Saturdays and Sundays

function dateDayArray(start,end) {
  var start=start||new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate());  
  var end=end||new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate() + Math.floor(Math.random()*100));//Just a random date for testing
  const endv=end.valueOf();
  if(isDate(start) && isDate(end)) {
    var dtA=[];
    let n=0;
    do{
      var cur=new Date(start.getFullYear(),start.getMonth(),start.getDate()+n++);
      if(cur.getDay()>0 && cur.getDay()<6) {
        dtA.push(Utilities.formatDate(cur, Session.getScriptTimeZone(), "MMM dd, yyyy, E"));
      }
    }while(cur.valueOf()<endv);

  }else{
    console.log("Invalid Inputs");
  }
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(JSON.stringify(dtA)), "Display Results");
}

function isDate(date){
  return(Object.prototype.toString.call(date) === '[object Date]');
}

JavaScript Date Class

Answer 2

Considering that you the first and the last dates (they are currentSheet.getRange(5, 2).getValue() and currentSheet.getRange(5 + numberOfDates - 1, 2).getValue() respectively), you could iterate through all the days in between and, for each day, check if it's Saturday or Sunday: if that's not the case, push the date to arrayDates.

Add days method:

To achieve that, you should first have a method to add a day to a date. For that, you can define the following method to the Date prototype, as shown in this thread:

Date.prototype.addDays = function(days) {
    var date = new Date(this.valueOf());
    date.setDate(date.getDate() + days);
    return date;
}

Iterating through Dates:

Then, you can iterate between first and last date with a while loop (for each iteration, compare current date to last date). And for each iteration, use getDay() to check if it's weekday or not:

const days = ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"];
const numberOfDates = currentSheet.getRange(5,1).getValue();
let arrayDates = [];
let currentDate = currentSheet.getRange(5, 2).getValue();
const lastDate = currentSheet.getRange(5 + numberOfDates - 1, 2).getValue();
while (currentDate <= lastDate) {
  if (currentDate.getDay() > 0 && currentDate.getDay() < 6) {
    arrayDates.push([currentDate, days[currentDate.getDay()]]);
  }
  currentDate = currentDate.addDays(1);
}
return arrayDates;