Datepicker: Disabling dates in the data

Question

I have a datepicker which enables 3weeks from now(start) up to the rolling 12weeks(end). I would like to add a condition that if the date on that span was already in the data(Spreadsheet) it will also be disabled. See script below:

 <script>

   var data = SpreadsheetApp
        .openById('Spreadsheet ID')
        .getSheetByName("VL Request")
        .getDataRange()
        .getValues();  
   var datefiled = [0];
   var userName = Session.getEffectiveUser().getUsername();


      $( function() {
      //enable next week onwards 
    var date = new Date();
    var weekday = date.getDay();
    var daysTillWeekOver = 21 - weekday;
    var enddayofweek = daysTillWeekOver + 5;
        $("#datepicker").datepicker({
      minDate:daysTillWeekOver,
      maxDate: "3M" + enddayofweek
      });
      } );
</script>

I want to add a condition that if the user filed for that date(datefiled) it will be disabled on the datepicker for that user so the user will avoid filing a duplicate on that date.

Answer 1

You want to limit a JQuery datepicker to exclude dates previously chosen by the user. This means that the dates to be excluded may vary on each use the script .

The topic How To Disable Specific Days and Dates Using BeforeShowDay In jQueryUiDatepicker? (props to @Amal) show that the datepicker script requires a simple array of dates. However I found that every example showing the use of BeforeShowDay assumes that the excluded dates are hard-coded into the script. This is unacceptable in your scenario because of the variability of the user and consequently the dates previously selected by the user.

The following is a complete webapp that takes the spreadsheet data and searches for instances of the username, obtains the relevant VL dates, pushes the dates to an array read by the webapp, and then picked up by the Datepicker and reflected in the browser.

---

Example of excluded dates

Props to Tanaike and ZektorH for their advice for devising the method to pass the array of dates to be available to the webapp javascript.

Link to the webapp

Link to the Spreadsheet

Note: the script assumes that the user name is "user1". But there is a commented line (var userName = Session.getEffectiveUser().getUsername()) that can be used to return the actual user and select dates based on that user.

The key things to note are:

• the basic code for DataPicker exclusion (in JavaScript.html) is that proposed by Amal
• this is wrapped in a withSuccessHandler that calls function getuserdates() (in code.gs) which returns the dates previously selected by the user.

Comments on specific processes

• getuserdates()
• var datasheetData = datasheetRange.getDisplayValues();: all the data is obtained at the outset:
• datasheetRange.sort(6); (Column F on a non-zero-based basis): the data is sorted by date to enable the output to be in date order:
• var datanames = datasheetData.map(function(e){return e[2];});': the list of user names is extracted by using the Javascript [map](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/map) method .e[2]` will access Column C, or column 2 on a zero-based basis)
• var userdates = [];: a temporary array is setup to capture the previous dates
• userdates.push('"' + datasheetData[i][5]+ '"');: the script loops thought the user names, matches on the name of the user running the script, and extracts the relevant VL date(s) to the temporary array.
• datasheetRange.sort(1);: the spreadsheet data is resorted by Timestamp.
• if (userdates.length !=0){: the script tests whether or not any previous dates were captured.
• userdates look like this: ["11/11/2019", "11/15/2019", "11/16/2019", "11/17/2019", "11/24/2019"]

DatePicker

• minDate: start date is 3 weeks from today: "+3W"
• maxDate: end date is 12 weeks from today: "+12W"
• beforeShowDay: this is the datepicker parameter for exclusion of specific dates. The values userdates are returned from getuserdates()
• $thisdate defines a date format of m/d/y, and then
• if ($.inArray($thisDate, userdates) == -1) {: uses the JQuery inArray method to test for the array date equal to the calendar date; if there is no match, it returns -1. So, when a match is made, the function returns "false" = 0 and the date is "greyed out/blurred" in the calendar.

---

code.gs

function doGet(request) {
  return HtmlService.createTemplateFromFile('Page')
      .evaluate();
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

function getuserdates() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "VL Request";
  var datasheet = ss.getSheetByName(sheetname);

  // assume user name
  //var userName = Session.getEffectiveUser().getUsername()
  var username = "user1";

  // set variables
  var datafirstrow = 2;
  var dataLR = datasheet.getLastRow();
  var dataLC = datasheet.getLastColumn();
  var datasheetRange = datasheet.getRange(datafirstrow,1, dataLR-datafirstrow+1, dataLC);
  //Logger.log(datasheetRange.getA1Notation());

  // sort the data by date
  datasheetRange.sort(6); // sort by Column F - VL date
  var datasheetData = datasheetRange.getDisplayValues();
  //Logger.log(datasheetData);

  //   get the user names as an array
  var datanames = datasheetData.map(function(e){return e[2];});//[[e],[e],[e]]=>[e,e,e]
  //Logger.log(datanames); // DEBUG
  //Logger.log(datanames.length) // DEBUG

  // create an array to hold any dates
  var userdates = [];

  //  loop through the user names; test for equivalence to "username", and save VF date to an array
  for (var i=0;i<datanames.length;i++){
    //Logger.log("dataname = "+datanames[i])
    if (datanames[i] === username){
      // Logger.log("DEBUG: i= "+i+", user name = "+datanames[i]+", VL date = "+datasheetData[i][5]);
      //userdates.push('"' + datasheetData[i][5]+ '"');
      userdates.push(datasheetData[i][5]);
    }
    else{
      // Logger.log("DEBUG: i= "+i+" - no match");
    }
  }
  // resort the data by Timestamp
   datasheetRange.sort(1); // sort by Column A

  if (userdates.length !=0){
  //Logger.log("There are "+userdates.length+" previous dates for this user.");//DEBUG
  }
  else{
  //Logger.log("There no previous dates for this user");//DEBUG
  }

  //Logger.log(userdates);
  return userdates;
}

---

JavaScript.html

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script src="//ajax.googleapis.com/ajax/libs/jqueryui/1.9.1/jquery-ui.min.js"></script>
<script>
// Tanaike - Pattern2
// https://stackoverflow.com/a/58811576/1330560
google.script.run.withSuccessHandler(userdates => {
//console.log(userdates);
  $(function() {
    $('#datepicker').datepicker({
      minDate: "+3W", 
      maxDate: "+12W",
      beforeShowDay: function (date) {
        $thisDate = (date.getMonth() + 1) + "/" + date.getDate() + "/" + date.getFullYear();
        if ($.inArray($thisDate, userdates) == -1) {
          return [true, ""];
        } else {
          return [false, "", "Unavailable"];
        }
      }
    });
  });
}).getuserdates();
</script>

---

Page.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link rel="stylesheet" href="//ajax.googleapis.com/ajax/libs/jqueryui/1.9.1/themes/cupertino/jquery-ui.css">
    <?!= include('Stylesheet'); ?>
  </head>
  <body>
  <div class="demo" >
    <h1>jQuery datepicker</h1>
     <p>click here : <input type="text" name="date" id="datepicker" /></p>
  </div>
  <?!= include('JavaScript'); ?>
</body>
</html>

---

Stylesheet.html

<style>
.demo { margin: 30px ; color : #AAA ; font-family : arial sans-serif ;font-size : 10pt } 
p { color : red ; font-size : 14pt } 
</style>