Disable dates in the datepicker based on values from the Google Sheet using Google Apps Script

Question

From the html I made with date picker, if a date was selected and submitted, it's output will be saved in the Google Sheet.

Here is the sample output:

here is the html code:

 <div class="row">
 <div class="input-field col s4">
         <input id="subDate" type="text" class="datepicker">
         <label for="subDate">Select Date</label>
 </div> 

and here is the datePicker sample:

As you have noticed there are some disabled dates in the calendar. It is due to the option in the following java script:

<script>

 document.addEventListener('DOMContentLoaded', function() {
   var timeSelect = document.querySelectorAll('select');
   M.FormSelect.init(timeSelect);

   google.script.run.withSuccessHandler(populateDates).revealDates();                            

  });

   function populateDates(disabledDays){
   var disabledDays = [new Date("2019, 12, 25").valueOf(), new Date("2019, 7, 18").valueOf()];
   var dateSelect = document.getElementById('subDate');
   M.Datepicker.init(dateSelect, {
                        minDate: new Date ("2019, 5, 10"), 
                        maxDate: new Date ("2019, 8, 21"), 
                        disableWeekends: true,
                        disableDayFn: function(day){
                           return disabledDays.indexOf(day.valueOf()) > -1;
                        }
                        });

   }
</script>

I wanted to disable the repeating dates in the google sheet if it reaches 5 times in the column. In the example output above, you will notice:

August 20, 2019
July 26, 2019
July 19, 2019

Exist 5 times in the column. Now, to get only the values which exist 5 times, I used the code which I got from @Christopher Bradley

Google Apps Script:

function revealDates(){

  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Test_Data");
  var dateRg = ws.getRange(1, 9, ws.getLastRow(), 1).getValues();

  var CheckLimitReached = function (T)
  {
   var records= {};
   T.forEach(function (x) { records[x] = (records[x] || 0) + 1; });
   var limit_reached = Object.keys(records).filter(function (R) {
   return records[R] >= 5;});
   return limit_reached;
  };

 var dateDisable = CheckLimitReached(dateRg);
 Logger.log(dateDisable);
 return dateDisable;
 }

the log of this code is:

I want to disable the dates of the following log/ result. And to disable it, I think I need to place it in the disabledDays array in the javascript. I used

google.script.run.withSuccessHandler(populateDates).revealDates();

But still I can't disable the dates. I thought it should be in the format of

new Date("2019, 12, 25").valueOf()

and @Rubén gave this code:

for(var i = 0; i < dateDisable.length; i++){
  var testDate = Utilities.formatDate(dateDisable[i], "GMT+8","yyyy, MM, dd");
  Logger.log(testDate);
}

since it resulted in an error I tried to make this:

var testDate = Utilities.formatDate(new Date(dateDisable[i]), "GMT+8","yyyy, MM, dd");

and logging it the result is:

Still, I can't disable the date in the datepicker.

Answer 1

• You want to disable the dates of weekends and the dates retrieved from the values of Spreadsheet for the datepicker.
  • In your sample Spreadsheet, you want to disable August 20, 2019, July 26, 2019, July 19, 2019 and the weekends.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

Modified script:

Please modify the function of populateDates() of HTML & Javascript side as follows.

function populateDates(disabledDays){
  var dateSelect = document.getElementById('subDate');
  M.Datepicker.init(dateSelect, {
    minDate: new Date ("2019, 5, 10"), 
    maxDate: new Date ("2019, 8, 21"), 
    disableWeekends: true,
    disableDayFn: function(day){ // Modified
      return disabledDays.some(e => {
        var obj = new Date(e);
        return obj.getFullYear() == day.getFullYear() && obj.getMonth() == day.getMonth() && obj.getDate() == day.getDate();
      });
    }
  });
}

Note:

• In this case, the values of disabledDays from revealDates() of Google Apps Script are the string values. So the string values are converted to the date object at the script of disabledDays = disabledDays.map(e => new Date(e)).
• In this modification, I didn't modify Google Apps Script.

Reference:

• some()