title: Populating a Google From DropDown with Values from a Google Sheet
body: <p>I looked through all of the questions, and I can't quite figure out what I am doing wrong. Here is the background:</p>
  
  <p>I created a spreadsheet that has multiple tabs. Each tab corresponds to types of events (e.g parades, festivals, sports). Each of these tabs is used to assign specific job numbers to a specific event. Parade job numbers are in the 2000 range, festivals 3000 range, sports 4000 range. </p>
  
  <p>I then have a different tab which defines an array that collects all of the job numbers and events, and sorts them alphabetically. This tab is called "Active_Job_Numbers".</p>
  
  <p>My goal is to create several forms that call to this spreadsheet for the values on the "Active_Job_Numbers" tab. I sort of have this working, but not completely. Each form has a purpose such as submitting a request to accounting to process a credit card transaction; another form to submit a request to cut a check.</p>
  
  <p>All of these requests have to be tied to an ever changing list of job numbers. When I update the spreadsheet, the dropdown in the spreadsheet will populate based on the  "Active_Job_Numbers" data.</p>
  
  <p>The code that I am using does work, BUT if I update a job number or event name on the spreadsheet, the form still sees the old value. I have triggers set up for onSubmit and onOpen and still, the only way I can get the dropdown in the form to update is if I change one of the "var" labels. Then save it. Then reopen the form. This is a showstopper.</p>
  
  <p>Here is the code I am using: </p>
  
  <pre><code>function updateForm(){
    // call your form and connect to the drop-down item
    var creditCardRequestGoogleForm = FormApp.openById("1LqtPLGEhocGdkg75mkdldfplxSxWJmYZCp3u1bMwVQQ");
  
    var jobNumberDropDown = creditCardRequestGoogleForm.getItemById("1632374512").asListItem();
  
  
  
  
  // identify the sheet where the data resides needed to populate the drop-down
    var ss = SpreadsheetApp.openById("17fgel9Jfl4Aske85mfkwsphBQpZgtGhR8Q");
  SpreadsheetApp.setActiveSpreadsheet(ss);
    var activeJobNumbers = ss.getSheetByName('Active_Job_Numbers');
  
    // grab the values in the first column of the sheet - use 2 to skip header row 
    var jobNumValues = activeJobNumbers.getRange(2, 1, activeJobNumbers.getMaxRows() - 1).getValues();
  
    var jobNums = [];
  
    // convert the array ignoring empty cells
    for(var i = 0; i &lt; jobNumValues.length; i++)    
      if(jobNumValues[i][0] != "")
        jobNums[i] = jobNumValues[i][0];
  
    // populate the drop-down with the array data
    jobNumberDropDown.setChoiceValues(jobNums);
  
  }
  </code></pre>
  
  <p>A separate but related problem...there are some forms I have that have two different drop-down elements that call to the same spreadsheet but to different tabs. I am not sure how to group this script so I have to separated out like so:</p>
  
  <pre><code>function updateForm(){
    // call your form and connect to the drop-down item
    var ccrForm = FormApp.openById("1r5n6Sr7fhtGCqht_QHy9qWR1v2ESabxdE-bMxjR-M");
  
    var jobNumberDropDown = ccrForm.getItemById("1678955392").asListItem();
  
  
  
  
  // identify the sheet where the data resides needed to populate the drop-down
    var ss = SpreadsheetApp.openById("17eGAlcnBB7ejdfqp3HBQBK9wJAKWYhBQpZgtGhR8Q");
  SpreadsheetApp.setActiveSpreadsheet(ss);
    var currentJobNumbers = ss.getSheetByName('Active Job Numbers');
  
    // grab the values in the first column of the sheet - use 2 to skip header row 
    var jobNumValues = currentJobNumbers.getRange(2, 1, currentJobNumbers.getMaxRows() - 1).getValues();
  
    var jobNums = [];
  
    // convert the array ignoring empty cells
    for(var i = 0; i &lt; jobNumValues.length; i++)    
      if(jobNumValues[i][0] != "")
        jobNums[i] = jobNumValues[i][0];
  
    // populate the drop-down with the array data
    jobNumberDropDown.setChoiceValues(jobNums);
  
  }
  
  function updateForm(){
    // This call your form and connect to the drop-down item
    var form = FormApp.openById("1r5n6Sr7fhtGCqht_QHy9qWR1v2ESabxdE-bMxjR-M");
  
    var poNumberDropDown = form.getItemById("57865789991").asListItem();
  
  
  
  
  // This is the second dropdown which calls the same spreadsheet but different tab NEED HELP COMBINING THESE TWO MORE ELEGANTLY
    var ss = SpreadsheetApp.openById("17eGAlcnBB7ejdfqp3HBQBK9wJAKWYhBQpZgtGhR8Q");
  SpreadsheetApp.setActiveSpreadsheet(ss);
    var currentPONumbers = ss.getSheetByName('PO_Number');
  
    // grab the values in the first column of the sheet - use 2 to skip header row 
    var poNumValues = currentPONumbers.getRange(2, 1, currentPONumbers.getMaxRows() - 1).getValues();
  
    var poNums = [];
  
    // convert the array ignoring empty cells
    for(var i = 0; i &lt; poNumValues.length; i++)    
      if(poNumValues[i][0] != "")
        poNums[i] = poNumValues[i][0];
  
    // populate the drop-down with the array data
    poNumberDropDown.setChoiceValues(poNums);
  
  }
  </code></pre>
  
  <p>Any insight would be much appreciated. </p>