使用 Google 电子表格中的值数组填充 jQuery 自动完成列表 [英] Populate jQuery autocomplete list using value array from Google Spreadsheet

问题描述

我正在尝试使用以下 此处 HTML 服务文本框中的自动完成功能.

我能够成功地这样做，但我想更改可用的标签.标签应该来自电子表格.我已经尝试了以下..任何帮助.

任何帮助我如何修改代码以正确链接可用标签.

解决方案

将 UI HTML 与将填充您的列表的 Apps 脚本代码分开是个好主意.首先，这里是如何分离这些位，从 Apps 脚本函数返回 availableTags 列表.(改编自之前对类似问题的回答.)

作为网络应用发布，这将生成一个带有自动完成功能的输入框，其中可用标签已从应用脚本功能提供.我们在页面加载时调用该函数，如下所示:

 google.script.run.withSuccessHandler(buildTagList).getAvailableTags();

代码.gs

function doGet() {var 模板 = HtmlService.createTemplateFromFile('自动完成');var htmlOutput = template.evaluate().setSandboxMode(HtmlService.SandboxMode.NATIVE).setTitle('jQuery UI 自动完成 - 默认功能');返回 html 输出；}函数 getAvailableTags() {//在生产代码中，通过//读取电子表格.var availableTags = ["动作脚本","AppleScript","阿普",基本的"，C"，"C++","Clojure","COBOL","冷聚变","二郎","Fortran",格调"，"哈斯克尔","爪哇","JavaScript","Lisp","珀尔","PHP",Python"，红宝石"，"斯卡拉",方案"];返回(可用标签)；}

自动完成.html

<link rel="stylesheet" href="http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css"/><script src="http://code.jquery.com/jquery-1.9.1.js"></script><script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script><div class="ui-widget"><label for="tags">标签:</label><input id="tags"/>

<脚本>//此函数中的这段代码在页面加载时运行.$(函数(){google.script.run.withSuccessHandler(buildTagList).getAvailableTags();});函数 buildTagList(availableTags) {$( "#tags" ).autocomplete({来源:availableTags});}

getAvailableTags()

使用上述框架，您可以继续填充电子表格中的 availableTags 列表，替换我们开始使用的固定数组.像这样:

function getAvailableTags() {var ss = SpreadsheetApp.openById("0Avt7ejriwlxudGZfV2xJUGJZLXktm2RhQU1uRUgtaXc");var s = ss.getSheetByName("数据库");var 数据 = s.getDataRange().getValues();无功标头 = 1;//在顶部跳过的标题行数var tagColumn = 2;//列#(基于0)包含标签var availableTags = [];for (var row=headers; row < data.length; row++) {availableTags.push(data[row][tagColumn]);}返回(可用标签)；}

I am trying to use the following here autocomplete feature in an HTML service textbox.

I am successfully able to do so, but I want to change the available tags. The tags should be from a spreadsheet. I have tried the following..any help please.

<script>
var ss = SpreadsheetApp.openById("0Avt7ejriwlxudGZfV2xJUGJZLXktm2RhQU1uRUgtaXc");
   var s = ss.getSheetByName("Database");
   var lastrow = s.getLastRow();
   var list = s.getRange(2,3, lastrow).getValues();
   for( var i = 0; i < list.length; i++)  {

  }

    $(function() {
    var availableTags = [list[i][0]];
    $( "#tags" ).autocomplete({
    source: availableTags
    });
    });
</script>

Any help how can I modify the code to link the availableTags properly.

解决方案

It's a Good Idea to separate the UI HTML from the Apps Script code that will populate your list. So first, here's how to separate those bits, with the availableTags list getting returned from an Apps Script function. (Adapted from a previous answer to a similar question.)

Published as a web app, this will yield an input box with the autocomplete function attached, where the available tags have been provided from an apps script function. We call that function when the page loads, like this:

      google.script.run.withSuccessHandler(buildTagList)
                       .getAvailableTags();

Code.gs

function doGet() {
  var template = HtmlService
                 .createTemplateFromFile('Autocomplete');

  var htmlOutput = template.evaluate()
                   .setSandboxMode(HtmlService.SandboxMode.NATIVE)
                   .setTitle('jQuery UI Autocomplete - Default functionality');

  return htmlOutput;
}

function getAvailableTags() {
  // In production code, get an array of options by
  // reading a spreadsheet.
  var availableTags = [
    "ActionScript",
    "AppleScript",
    "Asp",
    "BASIC",
    "C",
    "C++",
    "Clojure",
    "COBOL",
    "ColdFusion",
    "Erlang",
    "Fortran",
    "Groovy",
    "Haskell",
    "Java",
    "JavaScript",
    "Lisp",
    "Perl",
    "PHP",
    "Python",
    "Ruby",
    "Scala",
    "Scheme"];

  return( availableTags );
}

Autocomplete.html

<link rel="stylesheet" href="http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css" />
<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
<script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>

<div class="ui-widget">
  <label for="tags">Tags: </label>
  <input id="tags" />
</div>

<script>
// This code in this function runs when the page is loaded.
$(function() {
  google.script.run.withSuccessHandler(buildTagList)
      .getAvailableTags();
});

function buildTagList(availableTags) {
  $( "#tags" ).autocomplete({
    source: availableTags
  });
}
</script>

getAvailableTags()

With the framework working as above, you can move on to populating the availableTags list from your spreadsheet, replacing the fixed array we've started with. Something like this:

function getAvailableTags() {

  var ss = SpreadsheetApp.openById("0Avt7ejriwlxudGZfV2xJUGJZLXktm2RhQU1uRUgtaXc");
  var s = ss.getSheetByName("Database");
  var data = s.getDataRange().getValues();
  var headers = 1; // number of header rows to skip at top
  var tagColumn = 2; // column # (0-based) containing tag

  var availableTags = [];
  for (var row=headers; row < data.length; row++) {
    availableTags.push(data[row][tagColumn]);
  }

  return( availableTags );
}

这篇关于使用 Google 电子表格中的值数组填充 jQuery 自动完成列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！