title: Autocomplete in Google Apps Script with data from Google Sheets
body: <p>I am trying to use Google Apps Script to create a form, where the fields allow for autocompletion.  In other forms I’ve created, I’ve been able to pull an array of options from a google sheet, and use them to populate a drop down list, so I have to think it’s possible to do the same with an autocomplete process.</p>
  
  <p>I’ve blatantly copied <a href="https://www.w3schools.com/howto/howto_js_autocomplete.asp" rel="nofollow noreferrer">this example</a> from w3schools, and it works exactly as needed, as long as I declare the array within the javascript (as done in the example).  But what I haven’t been able to figure out is how to populate the array with options pulled from my google sheet.</p>
  
  <p>Here is where I started:</p>
  
  <pre><code>var PMOSupport;
  
  $(function() {
     google.script.run.withSuccessHandler(buildDropDowns)
     .getPMOSupport();
  });
  
  
  function buildDropDowns(data) {
    PMOSupport = data;
    console.log(PMOSupport);
  }
  
  
  function autocomplete(inp, arr) {
    console.log("ENTER AUTO");
    var currentFocus;
    inp.addEventListener("input", function(e) {
    // all of the remaining code is direct from the w3schools example
    // I'm cutting it from here for brevity, 
    // and because I know this works, when using the declared array below
    });
  }
  
  var countries = ["Afghanistan","Albania","Algeria","Andorra"];
  
  // this line works fine, when using the array declared above    
  // autocomplete(document.getElementById("myInput"), countries);  
  
  // this line does not work, when trying to use the array populated from the google sheet
  autocomplete(document.getElementById("myInput"), PMOSupport);  
  </code></pre>
  
  <p>When I run this, the page creates, and as soon as I type into the entry field, I get a message in the console: </p>
  
  <pre><code>`Uncaught TypeError: Cannot read property 'length' of undefined`
      at HTMLInputElement.&lt;anonymous&gt; (&lt;anonymous&gt;:32:28)
  </code></pre>
  
  <p>When I look into this, it’s saying that the ‘arr’ argument (PMOSupport) isn’t populated.  That’s why I added the 2 console.log lines, to see what order things are happening.  When I open the page, “ENTER AUTO” logs first, then the State Changes from Idle to Busy and Busy to Idle (while it calls getPMOSupport()), then the PMOSupport array logs in the console (also proving that I am in fact getting the correct data back from the sheet).  So clearly, it’s entering function autocomplete() before it calls the google.script.run.withSuccessHandler(buildDropDowns).getPMOSupport() portion, which is why the 'arr' argument is undefined.</p>
  
  <p>I’ve tried various ways of taking that out of the <code>$(function() … });</code> block, to try to get the PMOSupport array populated before the autocomplete() function runs.  Nothing I’ve done seems to be working.</p>
  
  <p>I’m sure this is something simple, and caused by bad habits I’ve picked up over time (I’m not a developer, I just cobble things together for my team).  But any help would be appreciated.</p>