I've been working on an answer to StackOverflow question Datepicker: Disabling dates in the data. I've successfully developed a small webapp that excludes specific dates from a jQuery Datepicker using the beforeShowDay option and an array of hardcoded dates.
Problem
At present, the array of excluded dates is hard coded, but these dates should be generated dynamically. Although I have a function in code.gs getuserdates() which will return the "userdates" array ["12/13/2019", "12/14/2019", "12/16/2019", "12/17/2019", "12/24/2019"], I haven't found a single reference on the web to explain how to pass the array values dynamically to the webapp.
The answer by @Tanaike on Disable dates in the datepicker based on values from the Google Sheet using Google Apps Script seems relevant to this problem, but I've failed to adapt any successful code that includes the array. I think part of the problem here is that Tanaike's answer was 100% Javascript whereas this scenario requires both Javascript and jQuery.
I tried scriptlets (not expecting them to work, but you never know. They all generated an error Uncaught SyntaxError: Unexpected token '<'
var userdates = <? getuserdates(); ?>var userdates = <?= getuserdates(); ?>var userdates = <?!= getuserdates(); ?>
Goal
To update dynamically the values of the variable array.
Code
The following code works flawlessly; the only problem is that the array values are hard coded.
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]+ '"');
}
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;
}
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>
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>
var userdates = ["12/13/2019", "12/14/2019", "12/16/2019", "12/17/2019", "12/24/2019"];
$(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"];
}
}
});
});
</script>
