google.script.run在自定义菜单中的用法 [英] Usage of google.script.run in custom menues

问题描述

我试图在Google表格中实现自定义HTML菜单。下拉列表通过函数 getValuesForRngName（） 填充来自命名范围的值。在列表中选择值后，脚本应该再次从表单中检索范围，确定价格并将价格填充到文本框servicePrice中。然而，问题在于这行代码 values = google.script.run.getValuesForRngName（'ServiceList'）; 没有检索到任何内容。

您可以建议如何使它工作吗？

HTML代码如下：

 < div> <形式> <表> < TR> < td>选择服务：< / td> < td>< select name =servicesMDid =servicesMDonChange =retrieveServicePrice（this）;>< / select>< / td> < / TR> < TR> < TD><峰; br /><峰; br /> < script type =text / javascript> function onSuccess（values）{var option，dropDown; for（i = 1; i< values.length; i ++）{dropDown = document.getElementById（servicesMD）; option = document.createElement（option）; dropDown.options.add（可选）; option.value = values [i] [0]; // service ID option.text = values [i] [1]; // service name}} function populate（）{google.script.run.withSuccessHandler（onSuccess）.getValuesForRngName（'ServiceList'）; }< / script>服务价格：< input id =servicePricename =servicePricetype =text/>< br />< br /> < / TR> < /表> < /形式>< / DIV><脚本> window.addEventListener（'load'，populate（））;函数retrieveServicePrice（element）{var selectionIndex; var值; values = google.script.run.getValuesForRngName（'ServiceList'）; selectionIndex = document.forms [0] .action = element.options [element.selectedIndex] .getAttribute（value）; document.getElementById（servicePrice）。value = values [selectionIndex] [3]; //应检索服务的价格}< / script>  

GAS代码如下：

function onOpen（）{SpreadsheetApp.getUi //或DocumentApp或FormApp。 .createMenu（'Custom Menu'）.addItem（'Show custom menu'，'openServiceSalesForm'）.addToUi（）;}函数openServiceSalesForm（）{var html = HtmlService.createHtmlOutputFromFile（'Page'）.setWidth（600）.setHeight （400）; SpreadsheetApp.getUi（）。showModalDialog（html，'Service sales form'）;} // RangeListfunction getValuesForRngName（rngName）{var rngValues = SpreadsheetApp.getActiveSpreadsheet（）。getRangeByName（rngName）.getValues（）; return rngValues;}

假设，ServiceList范围内的列按以下顺序 Service ID |服务名称| Price

 < script> 
 window.addEventListener（'load'，populate（））; 
 var serviceID; 
 
函数retrieveServicePrice（element）{
 serviceID = element; 
 google.script.run.withSuccessHandler（injectValuesIntoHTML）.getValuesForRngName（'ServiceList'）; 
} 
 window.injectValuesIntoHTML = function（values）{
 for（var i = 0; i< values.length; i ++）{
 if（serviceID == values [ i] [0]）{
 document.getElementById（servicePrice）。value = values [i] [2]; //应检索服务的价格
} 
} 
} 
< / script> 
  

HTML：

 < select name =servicesMDid =servicesMDonChange =retrieveServicePrice（this.value）;> 
  

I'm trying to implement custom HTML-menu in Google Sheets. There is drop-down list populated with values from named range via function getValuesForRngName(). Upon selection of value in list, the script should retrieve the range from the sheet again, determine the price and populate the price into a text box servicePrice. However, the problem is that this line of code "values = google.script.run.getValuesForRngName('ServiceList');" is not retrieving anything.

Could you please suggest how to make it work?

HTML code is provided below:

<div>
  <form>
    <table>
      <tr>
        <td>Select Service:</td>
        <td><select name="servicesMD" id="servicesMD" onChange="retrieveServicePrice(this);"></select></td>
      </tr>
      <tr>
        <td><br/><br/>

          <script type="text/javascript">
            function onSuccess(values) {
              var option, dropDown;
              for (i = 1; i < values.length; i++) {
                dropDown = document.getElementById("servicesMD");
                option = document.createElement("option");
                dropDown.options.add(option);
                option.value = values[i][0]; // service ID
                option.text = values[i][1]; // service Name 
              }
            }

            function populate() {
              google.script.run.withSuccessHandler(onSuccess).getValuesForRngName('ServiceList');
            }
          </script>
          Service Price: <input id="servicePrice" name="servicePrice" type="text" /><br/><br/>
      </tr>
    </table>
  </form>
</div>

<script>
  window.addEventListener('load', populate());

  function retrieveServicePrice(element) {
    var selectionIndex;
    var values;
    values = google.script.run.getValuesForRngName('ServiceList');
    selectionIndex = document.forms[0].action = element.options[element.selectedIndex].getAttribute("value");
    document.getElementById("servicePrice").value = values[selectionIndex][3]; // should retrieve price of service
  }
</script>

GAS code is below:

function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .createMenu('Custom Menu')
      .addItem('Show custom menu', 'openServiceSalesForm')
      .addToUi();
}
function openServiceSalesForm() {
  var html = HtmlService.createHtmlOutputFromFile('Page')
      .setWidth(600)
      .setHeight(400);
      SpreadsheetApp.getUi().showModalDialog(html, 'Service sales form');
} 

// RangeList
function getValuesForRngName(rngName) {
  var rngValues = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(rngName).getValues();
  return rngValues;
}

解决方案

Assuming that, the columns in the range ServiceList are in the following order Service ID | Service Name | Price

<script>
    window.addEventListener('load', populate());
    var serviceID;

    function retrieveServicePrice(element) {
        serviceID = element;
        google.script.run.withSuccessHandler(injectValuesIntoHTML).getValuesForRngName('ServiceList');
    }
    window.injectValuesIntoHTML = function(values) {
        for (var i = 0; i < values.length; i++) {
            if (serviceID == values[i][0]) {
                document.getElementById("servicePrice").value = values[i][2]; // should retrieve price of service
            }
        }
    }
</script>

HTML:

<select name="servicesMD" id="servicesMD" onChange="retrieveServicePrice(this.value);">

这篇关于google.script.run在自定义菜单中的用法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！