Skip to content
Advertisement

Google Webapp: How to dynamically pass array values to jquery script

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.

Link to Spreadsheet

Latest webapp url (updated)

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>

Advertisement

Answer

When getuserdates() in Google Apps Script side returns the value of ["12/13/2019", "12/14/2019", "12/16/2019", "12/17/2019", "12/24/2019"], userdates of var userdates = <?= getuserdates(); ?> is 12/13/2019,12/14/2019,12/16/2019,12/17/2019,12/24/2019 of the string type. I thought that by this, your script doesn’t work.

So, as one of several answers, how about this answer? Please modify JavaScript.html.

Pattern 1:

In this pattern, the scriptlets are used. I thought that this thread might be useful.

From:

var userdates = ["12/13/2019", "12/14/2019", "12/16/2019", "12/17/2019", "12/24/2019"];

To:

var userdates = [];
<? var data = getuserdates(); ?>
<? for (var i = 0; i < data.length; i++) { ?>
  userdates.push(<?= data[i] ?>);
<? } ?>
  • When the script is run, userdates is ["12/13/2019", "12/14/2019", "12/16/2019", "12/17/2019", "12/24/2019"].
  • As one more pattern using the scriptlets, for example, if you want to use var userdates = <?= getuserdates(); ?>, you can also modify var userdates = <?= getuserdates(); ?> to var userdates = <?= getuserdates() ?>.split(",");.

Pattern 2:

In this pattern, google.script.run is used.

From:

var userdates = ["12/13/2019", "12/14/2019", "12/16/2019", "12/17/2019", "12/24/2019"];

To:

google.script.run.withSuccessHandler(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();
  • When the script is run, userdates retrieved from getuserdates() is used as ["12/13/2019", "12/14/2019", "12/16/2019", "12/17/2019", "12/24/2019"].

Note:

  • In this case, it supposes that getuserdates() returns ["12/13/2019", "12/14/2019", "12/16/2019", "12/17/2019", "12/24/2019"].

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Edit 1:

About issue 1:

About the reason that the error of Uncaught SyntaxError: Unexpected token '<' occurs, the reason of this issue is <?!= include('JavaScript'); ?>. So please modify as follows.

From:

</div>
  <?!= include('JavaScript'); ?>
</body>

To:

</div>
<script>
var userdates = [];
<? var data = getuserdates(); ?>
<? for (var i = 0; i < data.length; i++) { ?>
  userdates.push(<?= data[i] ?>);
<? } ?>
</script>
  <?!= include('JavaScript'); ?>
</body>
  • In this case, please don’t add <script>###</script> to JavaScript of <?!= include('JavaScript'); ?>.
  • Unfortunately, it seems that the scriptlets cannot be used into the scriptlets.

About issue 2:

About the reason that [""12/11/2019"", ""12/15/2019"", ""12/16/2019"", ""12/17/2019"", ""12/24/2019""] is returned from getuserdates(), the reason of this issue is userdates.push('"' + datasheetData[i][5]+ '"');. So please modify as follows.

From:

userdates.push('"' + datasheetData[i][5]+ '"');

To:

userdates.push(datasheetData[i][5]);

Edit 2:

When the pattern 1 is used, the script is as follows. About getuserdates() of GAS side, please modify from userdates.push('"' + datasheetData[i][5]+ '"'); to userdates.push(datasheetData[i][5]);. And please modify HTML & Javascript side as follows.

HTML & Javascript: 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>
    <script>
    var userdates = [];
    <? var data = getuserdates(); ?>
    <? for (var i = 0; i < data.length; i++) { ?>
      userdates.push(<?= data[i] ?>);
    <? } ?>
    </script>
    <?!= include('JavaScript'); ?>
  </body>
</html>

HTML & Javascript: 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>
$(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>
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement