使用Google Apps脚本根据Google表格中的值禁用日期选择器中的日期 [英] Disable dates in the datepicker based on values from the Google Sheet using Google Apps Script

问题描述

从我使用日期选择器制作的html中，如果选择并提交了日期，则其输出将保存在Google表格中.

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.

以下是示例输出:

这是html代码:

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

这是datePicker示例:

and here is the datePicker sample:

您已经注意到日历中有一些禁用日期.这是由于以下Java脚本中的选项所致:

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>

如果该列中的重复日期达到5次，我想禁用Google工作表中的重复日期.在上面的示例输出中，您将注意到:

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

在该列中存在5次.现在，为了只获取存在5次的值，我使用了从@Christopher Bradley获得的代码

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脚本:

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;
 }

此代码的日志为:

我想禁用以下日志/结果的日期.并禁用它，我想我需要将其放在javascript的disabledDays数组中.我用

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()

和@Rubén给出了以下代码:

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.

推荐答案

• 您要禁用周末日期以及从日期选择器的Spreadsheet值中检索的日期.
  • 在示例电子表格中，您要禁用August 20, 2019，July 26, 2019，July 19, 2019和周末.
  • 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.

    请修改HTML&的populateDates()的功能. JavaScript方面如下.

    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();
          });
        }
      });
    }
    

    注意:

    • 在这种情况下，Google Apps脚本的revealDates()中的disabledDays的值是字符串值.因此，字符串值将在disabledDays = disabledDays.map(e => new Date(e))脚本中转换为date对象.
    • 在此修改中，我没有修改Google Apps脚本.

    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.
    • some()

    这篇关于使用Google Apps脚本根据Google表格中的值禁用日期选择器中的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！