当前位置： 文江博客 话题详情

如何排除使用Google Apps脚本部署的表格中达到容量的选项

发布于 2025-01-29 10:00:24 字数 1915 浏览 5 评论 0 原文

我使用具有容量的选项的Google Apps脚本创建了一个Web表单。

在下面的示例中，有一个问题要求游客选择芝士蛋糕或巧克力蛋糕。假设我只有两块芝士蛋糕和三块巧克力蛋糕，如果两个访客已经​​选择了芝士蛋糕，我想从表格中删除芝士蛋糕的选项，并使该选项不可见且不可选拔仅巧克力蛋糕。

然后，我应该如何实现此类选择使用Google Apps脚本具有容量的选项？

请注意，但我想创建一个自定义的Web表单，这次我不为此使用Google表单。

编辑

以下链接将显示该程序如何在电子表格上保存数据：

index.htex.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>

  <body>
    <form class="" action="<?!= getScriptUrl(); ?>" method="post">
      <div>
        <h1 id="Question">
          Choose either cheesecake or chocolate cake.
        </h1>

          <select id="" name="cake" class="form-control">
            <option value="cheesecake">cheesecake</option>
            <option value="chocolate_cake">chocolate_cake</option>
          </select>
      </div>

      <div class="form-submit">
        <input type="submit" name="" value="Submit">
      </div>
    </form>
  </body>
</html>

code.gs

function doGet(){

 return HtmlService.createTemplateFromFile("index").evaluate();

}

function getScriptUrl() {
  var url = ScriptApp.getService().getUrl();
  Logger.log(url);
  return url;
}

function doPost(e){

  var sh = SpreadsheetApp.openById("11nE1yL24HamfbAbeRjoQV6SE0ecq6rCx1WlbQZ8N8R0").getSheets()[0];

  sh.appendRow([String(e.parameters.cake)]);

  return HtmlService.createHtmlOutput('<b>Thank you!</b>');
}
原文

I created a web form using Google Apps Script that has options with capacity.

In the following example, there is a question to ask visitors to choose cheesecake or chocolate cake. Suppose that I have only two pieces of cheesecake and three pieces of chocolate cake, and if two visitors has already choose the cheesecake, I want to remove the option of cheesecake from the form and make that option invisible and thus unselectable, showing the option of chocolate cake only.

Then, how should I implement such a select question whose options have capacity using Google Apps Script?

Note but I want to create a custom web form, and that this time I do NOT use Google Forms for that purpose.

EDIT

The following link will show how this programme saves data on a spreadsheet: https://docs.google.com/spreadsheets/d/11nE1yL24HamfbAbeRjoQV6SE0ecq6rCx1WlbQZ8N8R0/edit?usp=sharing

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>

  <body>
    <form class="" action="<?!= getScriptUrl(); ?>" method="post">
      <div>
        <h1 id="Question">
          Choose either cheesecake or chocolate cake.
        </h1>

          <select id="" name="cake" class="form-control">
            <option value="cheesecake">cheesecake</option>
            <option value="chocolate_cake">chocolate_cake</option>
          </select>
      </div>

      <div class="form-submit">
        <input type="submit" name="" value="Submit">
      </div>
    </form>
  </body>
</html>

code.gs

function doGet(){

 return HtmlService.createTemplateFromFile("index").evaluate();

}

function getScriptUrl() {
  var url = ScriptApp.getService().getUrl();
  Logger.log(url);
  return url;
}

function doPost(e){

  var sh = SpreadsheetApp.openById("11nE1yL24HamfbAbeRjoQV6SE0ecq6rCx1WlbQZ8N8R0").getSheets()[0];

  sh.appendRow([String(e.parameters.cake)]);

  return HtmlService.createHtmlOutput('<b>Thank you!</b>');
}

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

韬韬不绝 2025-02-05 10:00:24

如果要根据数量删除选项，则应将每个蛋糕的数量保存到纸上。

在这里，我修改了您的代码并添加了一些功能：

code.gs：

function doGet(){
  return HtmlService.createTemplateFromFile("index").evaluate();
}

function getScriptUrl() {
  var url = ScriptApp.getService().getUrl();
  Logger.log(url);
  return url;
}

function doPost(e){
  var ss = SpreadsheetApp.openById("insert sheet id here");
  var sh1 = ss.getSheets()[0];
  sh1.appendRow([String(e.parameters.cake)]);

  //update Inventory
  var sh = ss.getSheetByName("Inventory");
  var row = sh.createTextFinder(e.parameters.cake).findNext().getRow();
  var range = sh.getRange(row, 2);
  var data = range.getValue();
  range.setValue(parseInt(data - 1))

  return HtmlService.createHtmlOutput('<b>Thank you!</b>'); 
  
}

function getAvailableFlavors(){
  var ss = SpreadsheetApp.openById("insert sheet id here");
  var sh = ss.getSheetByName("Inventory");
  var data =  sh.getRange(2, 1, 2, 2).getValues();
  var filtered = data.filter(arr =>  arr[1] > 0 || arr[1] != ''); //remove flavor to array if quantity is 0 or empty
  return filtered;
}

index.html：

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>

  <body onload="addOptions()">   <!--Execute addOptions function immediately after a page has been loaded-->
    <form class="" action="<?!= getScriptUrl(); ?>" method="post">
      <div>
        <h1 id="Question">
          Choose either cheesecake or chocolate cake.
        </h1>
          <select id="dropdownList" name="cake" class="form-control"> 
          </select>
      </div>

      <div class="form-submit">
        <input type="submit" name="" value="Submit">
      </div>
    </form>
  </body>
  <script>
    function addOptions() {
      /*This will call server-side Apps Script function getAvailableFlavors and if it is successful, 
      it will pass the return value to function addListValues which will add options to the drop down menu*/
      google.script.run
        .withFailureHandler(onFailure)
        .withSuccessHandler(addListValues)
        .getAvailableFlavors();
    }

    function addListValues(values) { 
      //Add options to drop down menu using the values of parameter 'values'.     
      for (var i = 0; i < values.length; i++) {
        var option = document.createElement("option");
        option.text = values[i][0];
        option.value = values[i][0];
        var select = document.getElementById("dropdownList");
        select.appendChild(option);
      }
    }

    function onFailure(err) {
      alert('Error: ' + err.message);
    }
  </script>
</html>

在这里，我创建了一个名为“ 库存”的表格：

< a href =“ https://i.sstatic.net/ra7y1.png” rel =“ nofollow noreferrer”>

每个人每次订购蛋糕时，数量会减少1。

< img src =“ https://i.sstatic.net/6t4fw.gif” alt =“在此处输入图像说明”>

当蛋糕数量到达0时，脚本将从下拉列表中删除芝士蛋糕：

注意：您可以复制并粘贴上面的代码，但您需要一个名为库存的表格，其中包括上面的示例中的内容和格式相同。

参考：

If you want to remove an option based on quantity, then you should save the quantity of each cake to a Sheet.

Here I modified your code and added some features:

Code.gs:

function doGet(){
  return HtmlService.createTemplateFromFile("index").evaluate();
}

function getScriptUrl() {
  var url = ScriptApp.getService().getUrl();
  Logger.log(url);
  return url;
}

function doPost(e){
  var ss = SpreadsheetApp.openById("insert sheet id here");
  var sh1 = ss.getSheets()[0];
  sh1.appendRow([String(e.parameters.cake)]);

  //update Inventory
  var sh = ss.getSheetByName("Inventory");
  var row = sh.createTextFinder(e.parameters.cake).findNext().getRow();
  var range = sh.getRange(row, 2);
  var data = range.getValue();
  range.setValue(parseInt(data - 1))

  return HtmlService.createHtmlOutput('<b>Thank you!</b>'); 
  
}

function getAvailableFlavors(){
  var ss = SpreadsheetApp.openById("insert sheet id here");
  var sh = ss.getSheetByName("Inventory");
  var data =  sh.getRange(2, 1, 2, 2).getValues();
  var filtered = data.filter(arr =>  arr[1] > 0 || arr[1] != ''); //remove flavor to array if quantity is 0 or empty
  return filtered;
}

index.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>

  <body onload="addOptions()">   <!--Execute addOptions function immediately after a page has been loaded-->
    <form class="" action="<?!= getScriptUrl(); ?>" method="post">
      <div>
        <h1 id="Question">
          Choose either cheesecake or chocolate cake.
        </h1>
          <select id="dropdownList" name="cake" class="form-control"> 
          </select>
      </div>

      <div class="form-submit">
        <input type="submit" name="" value="Submit">
      </div>
    </form>
  </body>
  <script>
    function addOptions() {
      /*This will call server-side Apps Script function getAvailableFlavors and if it is successful, 
      it will pass the return value to function addListValues which will add options to the drop down menu*/
      google.script.run
        .withFailureHandler(onFailure)
        .withSuccessHandler(addListValues)
        .getAvailableFlavors();
    }

    function addListValues(values) { 
      //Add options to drop down menu using the values of parameter 'values'.     
      for (var i = 0; i < values.length; i++) {
        var option = document.createElement("option");
        option.text = values[i][0];
        option.value = values[i][0];
        var select = document.getElementById("dropdownList");
        select.appendChild(option);
      }
    }

    function onFailure(err) {
      alert('Error: ' + err.message);
    }
  </script>
</html>

Here I created a sheet named "Inventory":

enter image description here

and each time a person orders a cake, the quantity is reduced by 1.

enter image description here

When the quantity of cake reaches 0, the script will remove the cheesecake from the drop down list:

enter image description here

Note: You can copy and paste the code above but you need a Sheet named Inventory with the same content and format from my example above.

References:

回复 原文
未央 2025-02-05 10:00:24

我相信您当前的情况和目标如下。

  • 您的HTML包含在Google Apps脚本项目中。
  • 您需要从电子表格中检索值，并在下拉列表中显示它们。
  • 例如，有两块芝士蛋糕。当用户选择芝士蛋糕并提交时，您需要从电子表格中的芝士蛋糕中降低 1 。
  • 当芝士蛋糕的数量为 0 时，您不想选择它。

在这种情况下，以下示例脚本怎么样？

样本电子表格

为了测试，使用此脚本中的 ，使用以下电子表格。该表是电子表格的第一张选项卡。

示例脚本，

请复制并将以下脚本粘贴到脚本编辑器。而且，请反映Web应用程序的最新脚本。

Google Apps脚本方面： code.gs 

var spreadsheetId = "###"; // Please set your Spreadsheet ID.

function updateValue(e) {
  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheets()[0];
  var range = sheet.getRange("A2:B" + sheet.getLastRow());
  var values = range.getValues().map(([a, b]) => a == e ? [a, --b] : [a, b]);
  range.setValues(values);
  return values.map(([a, b]) => `<option value="${a}" ${b == 0 ? "disabled" : ""}>${a}</option>`).join("");
}

const doGet = () => {
  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheets()[0];
  var [, ...values] = sheet.getDataRange().getValues();
  var v = values.map(([a, b]) => `<option value="${a}" ${b == 0 ? "disabled" : ""}>${a}</option>`).join("");
  var html = HtmlService.createTemplateFromFile("index.html");
  html.values = v || "";
  return html.evaluate();
}

html＆amp; JavaScript方面： index.html 

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
</head>

<body>
  <form id="form">
    <div>
      <h1 id="Question">
        Choose either cheesecake or chocolate cake.
      </h1>

      <select id="sampleSelect" name="cake" class="form-control">
        <?!= values ?>
      </select>
    </div>

    <div class="form-submit">
      <input type="submit" value="Submit" onclick="saveValue(this);return false;">
    </div>
  </form>
  <script>
    function saveValue(e) {
      const v = document.getElementById("form").cake.value;
      google.script.run.withSuccessHandler(updated => {
        document.getElementById("sampleSelect").innerHTML = updated;
      }).updateValue(v);
    }
  </script>
</body>

</html>

测试

请访问在修改后的脚本中反映的已部署的Web应用程序。这样，您可以看到下拉列表。

在此样品中，芝士蛋糕有2个。选择芝士蛋糕并单击“提交”按钮时，电子表格上的芝士蛋糕数量从2到1。选择芝士蛋糕并再次单击按钮时，您可以确认该选项。不能选择芝士蛋糕。

我认为这可能是您的预期结果。

注意

  • 修改Google Apps脚本时，请将部署修改为新版本。这样，修改后的脚本反映在Web应用程序中。请小心。

  • 请 重新启动Web应用程序而无需更改新IDE的Web应用程序的URL 。

  • 此示例脚本是一个简单的脚本。因此，请为您的实际情况进行修改。

参考

I believe your current situation and goal are as follows.

  • Your HTML is included in the Google Apps Script project.
  • You want to retrieve the values from the Spreadsheet and show them in the dropdown list.
  • For example, there are two pieces of cheesecake. When an user selects a cheesecake and submit it, you want to reduce 1 from cheesecake in the Spreadsheet.
  • When the number of cheesecake is 0, you don't want to select it.

In this case, how about the following sample script?

Sample spreadsheet

In this script, in order to test, the following Spreadsheet is used. This sheet is the 1st tab of the Spreadsheet.

enter image description here

Sample script

Please copy and paste the following scripts to the script editor. And, please reflect the latest script to the Web Apps.

Google Apps Script side: Code.gs

var spreadsheetId = "###"; // Please set your Spreadsheet ID.

function updateValue(e) {
  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheets()[0];
  var range = sheet.getRange("A2:B" + sheet.getLastRow());
  var values = range.getValues().map(([a, b]) => a == e ? [a, --b] : [a, b]);
  range.setValues(values);
  return values.map(([a, b]) => `<option value="${a}" ${b == 0 ? "disabled" : ""}>${a}</option>`).join("");
}

const doGet = () => {
  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheets()[0];
  var [, ...values] = sheet.getDataRange().getValues();
  var v = values.map(([a, b]) => `<option value="${a}" ${b == 0 ? "disabled" : ""}>${a}</option>`).join("");
  var html = HtmlService.createTemplateFromFile("index.html");
  html.values = v || "";
  return html.evaluate();
}

HTML & Javascript side: index.html

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
</head>

<body>
  <form id="form">
    <div>
      <h1 id="Question">
        Choose either cheesecake or chocolate cake.
      </h1>

      <select id="sampleSelect" name="cake" class="form-control">
        <?!= values ?>
      </select>
    </div>

    <div class="form-submit">
      <input type="submit" value="Submit" onclick="saveValue(this);return false;">
    </div>
  </form>
  <script>
    function saveValue(e) {
      const v = document.getElementById("form").cake.value;
      google.script.run.withSuccessHandler(updated => {
        document.getElementById("sampleSelect").innerHTML = updated;
      }).updateValue(v);
    }
  </script>
</body>

</html>

Testing

Please access the deployed Web Apps reflected in the modified script. By this, you can see the dropdown list.

In this sample, cheesecake has 2. When you select a cheesecake and click the "Submit" button, the number of cheesecakes on the Spreadsheet becomes from 2 to 1. When you select cheesecake and click the button again, you can confirm that the option of cheesecake cannot be selected.

I thought that this might be your expected result.

Note

  • When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.

  • You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".

  • This sample script is a simple script. So, please modify this for your actual situation.

Reference

回复 原文
~没有更多了~

关于作者

[浮城]

暂无简介

文章
评论
26 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

櫻之舞

文章 0 评论 0

弥枳

文章 0 评论 0

m2429

文章 0 评论 0

寻找一个思念的角度

文章 0 评论 0

野却迷人

文章 0 评论 0

我怀念的。

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文