时间戳

首页 > 解决方案 > 将值的 Google Sheet 列表添加到具体化自动完成功能

javascript - 将值的 Google Sheet 列表添加到具体化自动完成功能

问题描述

我正在尝试创建一个简单的网络应用程序，将国家添加到谷歌表格中。并使用物化自动完成来帮助用户（它只是自动完成国家，没有附加图像）。我看到了几个物化自动完成的例子，但总是带有预定义的自动完成列表。这是我使用的html：

<html>
<head>
    <!--Import Google Icon Font-->
    <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
    <!--Import materialize.css-->
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">

    <!--Let browser know website is optimized for mobile-->
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
</head>
<body>
    <div class="row">
        <div class="col s12">
            <div class="row">
                <div class="input-field col s12">
                    <i class="material-icons prefix">work_outline</i>
                    <input id="autocomplete-input" type="text" class="autocomplete">
                    <label for="autocomplete-input">Country</label>
                </div>
            </div>
        </div>
    </div>

    <div class="input-field col s12">
        <button class="btn waves-effect waves-light amber accent-4" id="add_btn">Add country
            <i class="material-icons right">send</i>
        </button>
    </div>
    <!-- Compiled and minified JavaScript -->
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>
    <script>
        google.script.run.withSuccessHandler(tags).getCountry();
        function tags(data) {
          var availableTags = data;
          $('input.autocomplete').autocomplete({
              data: availableTags
            });
        };
        document.getElementById("add_btn").addEventListener("click", doStuff);
        function doStuff() {
            var ucountry = document.getElementById("autocomplete-input").value;
            google.script.run.userClicked(ucountry);
            document.getElementById("autocomplete-input").value = "";
        };
    </script>
</body>
</html>

这是我在谷歌脚本中的代码，函数 getCountry 有效，并返回一个国家列表。但是我没有成功地将它们添加到物化自动完成功能中。

function doGet() {
    var template = HtmlService.createTemplateFromFile("page");
    var html = template.evaluate();
    return html;
}
function userClicked(country){
    var url = "https://docs.google.com/spreadsheets/d/1_GtmVrdD1Es6zQZna_Mv-Rc3JkIu66-q_knQ8aqcUIc/edit#gid=0";
    var ss = SpreadsheetApp.openByUrl(url);
    var ws = ss.getSheetByName("Data");
    ws.appendRow([country]);
}
function getCountry(){
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  var list = ws.getRange(1,1,ws.getRange("A1").getDataRegion().getLastRow(),1).getValues(); // contains countries
  list = list.map(function(r){return r[0]; });
  Logger.log(list);
  var data = "{";
  for (var i = 0; i < list.length; i++) {
    data = data + "'" + list[i] + "': null,";    
  }
  data = data.slice(0, -1) + "}";
  Logger.log(data);
  return data;
}

这是https://materializecss.com/autocomplete.html上的信息

  // Or with jQuery

  $(document).ready(function(){
    $('input.autocomplete').autocomplete({
      data: {
        "Apple": null,
        "Microsoft": null,
        "Google": 'https://placehold.it/250x250'
      },
    });
  });

标签： javascriptgoogle-apps-scriptgoogle-sheetsautocompletematerialize

解决方案

回答：

您可以使用网络轮询以设定的时间间隔更新您的页面，以便它始终从工作表中检索更新的数据。

代码：

在这里捎带答案，编辑您的脚本以包括：

function polling(){
  setInterval(update, 500);
}
function update(){
  google.script.run.withSuccessHandler(tags).getCountry();        
}

并确保polling()在加载时运行该函数：

<body onload="polling()">
  <!-- your body goes here -->
</body>

整页.html ：

<html>
<head>
    <meta charset="utf-8">
    <!--Import Google Icon Font-->
    <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
    <!--Import materialize.css-->
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">

    <!--Let browser know website is optimized for mobile-->
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
     
    <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <link rel="stylesheet" href="/resources/demos/style.css">

    <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
</head>

<body onload="polling()">
    <div class="row">
        <div class="col s12">
            <div class="row">
                <div class="input-field col s12">
                    <i class="material-icons prefix">work_outline</i>
                    <input id="autocomplete-input" type="text" class="autocomplete" placeholder="Country">
                </div>
            </div>
        </div>
    </div>

    <div class="input-field col s12">
        <button class="btn waves-effect waves-light amber accent-4" id="add_btn">Add country
            <i class="material-icons right">send</i>
        </button>
    </div>
    <script>
        function polling(){
          setInterval(update, 500);
        }
        function update(){
          google.script.run.withSuccessHandler(tags).getCountry();        
        }
        
        google.script.run.withSuccessHandler(tags).getCountry();
        function tags(list) {
          console.log(list);
          var availableTags = list;
          $("#autocomplete-input").autocomplete({
              source: availableTags
            });
        };
        document.getElementById("add_btn").addEventListener("click", doStuff);

        function doStuff() {
          var ucountry = document.getElementById("autocomplete-input").value;
          google.script.run.userClicked(ucountry);
          document.getElementById("autocomplete-input").value = "";
        };
    </script>
</body>
</html>

并保持您的code.gs文件不变。

参考：

推荐阅读