javascript - Populate jQuery autocomplete list using value array from Google Spreadsheet

Question

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.

Answer 1

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 );
}