javascript - 为什么当用户提交输入时我的 html 选择选项不更新？

问题描述

我正在使用应用程序脚本构建一个调度助手，该助手从谷歌表格中提取数据并更新。本质上，当用户想要预约时，会打开一个 html 侧边栏。当侧栏打开时，下拉菜单会显示正确的选项（基于谷歌表中的值的可用约会）但是，一旦做出选择并单击添加约会按钮，我希望下拉菜单更新其选项，如果约会空位是不再可用（如果用户想在每次会议中添加多个约会，理想情况下不必关闭侧边栏来进行多个约会）。感谢您的任何帮助，您可以提供。

我试过的：

将 loadAppt() 函数添加到按钮的 onclick。
将最初填充下拉列表的相同代码添加到单击 addCustomerAppt 按钮时运行的函数。
单击 addCustomerAppt 按钮时关闭侧边栏
单击 addCustomerAppt 按钮时刷新侧边栏

我的代码：

function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .createMenu('Customer')
      .addItem('Add', 'addCustomer')
      .addToUi();
}

//function that displays a user input form. Used to enter customer information
function addCustomer() {
  var active = SpreadsheetApp.getActiveSpreadsheet();
  var ss = active.getSheetByName('Customer Data');
  if (ss) {
    var html = HtmlService.createHtmlOutputFromFile('testground').setTitle('Add Customer');//change to user
    SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
    .showSidebar(html);
  }
  else {
    ss = active.insertSheet();
    ss.setName('Customer Data');
    var html = HtmlService.createHtmlOutputFromFile('testground').setTitle('Add Customer');
    SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
    .showSidebar(html);
    var header = ['Date Time', 'First Name', 'Last Name', 'Email', 'Phone', 'Student'];
    var range = ss.getRange("A1:F1");
    range.setValues([header]).setFontWeight('bold').setBackground('cyan').setBorder(true, true, true, true, true, true);
  }
  
}


//function to calculate available appointments by date and time. 
//adds user friendly formatting depending on availability of appointment.
function calcAvailable() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange("E2").setFormula("=C2 - D2");

  //get the last row to dynamically input the formula above
  var lastRow = ss.getLastRow();
  //why do I have to subtract one from last row? I don't seem to understand getLastRow()
  var fillDownRange = ss.getRange(2, 5, lastRow - 1);
  ss.getRange("E2").copyTo(fillDownRange);

  //set rules for background colors
  var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberGreaterThan(0)
    .setBackground("#90ee90")
    .setRanges([fillDownRange])
    .build();
  var rule2 = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberLessThanOrEqualTo(0)
    .setBackground("#ffcccb")
    .setRanges([fillDownRange])
    .build();
var rules = ss.getConditionalFormatRules();
rules.push(rule);
rules.push(rule2);
ss.setConditionalFormatRules(rules);
}

**function newAppointment(dateTime, first, last, email, phone) {
  var values = [dateTime, first, last, email, phone];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Customer Data');
  sheet.appendRow(values);

  //this section updates the available column after the apoinment is made
  var apptSheet = ss.getSheetByName('Appointment View');
  var lastRow = apptSheet.getLastRow();
  var dataArray = apptSheet.getRange(2, 1, lastRow - 1, 2).getDisplayValues();
  //must use split here as the date and time are passed as a single string;
  var dtgArray = dateTime.split(',');
  
  dataArray.forEach(function(item, index) {
      
      if(item[0] == dtgArray[0] && item[1] == dtgArray[1]) {
      var updateCol = apptSheet.getRange(index + 2, 4, 1, 1);
      var currentAppt = updateCol.getValue();
      updateCol.setValue(currentAppt += 1); 
      }
  });

}**

function getList() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var apptSheet = ss.getSheetByName('Appointment View');
  var lastRow = apptSheet.getLastRow();
  //apps script cannot return any date object. Must use getDisplayValues() below and post process.
  //var dataArray = apptSheet.getRange(2, 1, lastRow - 1, 2).getDisplayValues();
  var dataArray = apptSheet.getRange(2, 1, lastRow - 1, 5).getDisplayValues();
  var filterArr = dataArray.filter(apptBlock => apptBlock[4] > 0);
  var listArr = [];
  filterArr.forEach(function (block) {
    listArr.push([block[0], block[1]]);

  })
  
  return listArr;
}

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    
    <script>
      **function addCustomerAppt() {
        //Add customer info from the form
        var dateTime = document.getElementById('dateTime').value;
        //var time = document.getElementById('time').value;
        var firstName = document.getElementById('firstName').value;
        var lastName = document.getElementById('lastName').value;
        var email = document.getElementById('email').value;
        var phone = document.getElementById('phone').value;
        //pass the above variables to the app script function
        google.script.run.newAppointment(dateTime, firstName, lastName, email, phone);
        
      }**


      //script to load appt into the dropdown menu. Limits choices to what is in appointment spreadsheet
      **function loadAppt() {
        google.script.run.withSuccessHandler(function(ar) 
        {
        var apptSelect = document.getElementById("dateTime");
        var option = document.createElement("option");
        option.value = "";
        option.text = "";
        apptSelect.appendChild(option); 

        ar.forEach(function(item, index){
          var option = document.createElement("option");
          option.value = [item[0], item[1]];
          option.text = [item[0], item[1]];
          apptSelect.appendChild(option);
        });
        }).getList();
      };**
    </script>
  </head>
  <body>
    <form>
      <p>
        **<label for="dateTime">Enter Appointment Date:&nbsp;&nbsp;</label>
        <select id="dateTime" name="dateTime"></select>
      </p>
      <script>loadAppt();</script>**
      <p>
        <label for="firstName">Enter First Name:&nbsp;&nbsp;</label>
        <input type="text" id="firstName" name="firstName">
      </p>
      <p>
        <label for="lastName">Enter Last Name:&nbsp;&nbsp;</label>
        <input type="text" id="lastName" name="lastName">
      </p>
      <p>
        <label for="email">Enter Customer Email:&nbsp;&nbsp;</label>
        <input type="email" id="email" name="email">
      </p>
      <p>
        <label for="phone">Enter Customer Phone:&nbsp;&nbsp;</label>
        <input type="text" id="phone" name="phone">
      </p>
      **<p>
        <label for="addCustomerAppt"></label>
        <input type="button" value="Add Customer" onclick="addCustomerAppt()">
      </p>**
      
  </body>

标签： javascripthtmlgoogle-apps-scriptgoogle-sheets

javascript - 为什么当用户提交输入时我的 html 选择选项不更新？

问题描述

解决方案

推荐阅读