title: Google Sheets Sidebar User Input Drop-down Menu w/ Values from Spreadsheet Range
body: <p>I have read a bunch of forum posts about using drop-down menus in Google Sheets, but I'm really not familiar enough with scripting to modify those solutions to meet my needs. I would really appreciate any help I can get with this!</p>
  
  <p>This sidebar adds a new entry to the end of my Job Log sheet. It currently works with text inputs, but I want some of the fields to be drop-down menus with options from various dynamic ranges in the spreadsheet.</p>
  
  <p><a href="https://i.stack.imgur.com/Xq1x8.png" rel="nofollow noreferrer">Screenshot</a><br>
  <a href="https://docs.google.com/spreadsheets/d/10pv5KvQYloxmx_7wQxlySTQslCQBFTcWwAsvkLHop7Y/edit?usp=sharing" rel="nofollow noreferrer">Sandbox Copy of Spreadsheet</a></p>
  
  <ol>
  <li>Drop-down options will populate when sidebar is opened with showNewJobSidebar()</li>
  <li>Inputs for Turnaround, Category, Upcharge, Discount, Client, and Type will be drop-down menus</li>
  </ol>
  
  <p>Here are the dynamic data ranges to use:  </p>
  
  <ul>
  <li>Turnaround = Rates!A2:A</li>
  <li>Category = Rates!B2:B</li>
  <li>Upcharge = Upcharges!A2:A</li>
  <li>Discount = Discounts!A2:A</li>
  <li>Client = Roster!A2:A</li>
  <li>Type = Data!A2:A</li>
  </ul>
  
  <p>Applicable parts of Code.gs:</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>function showNewJobSidebar() {
    var html = HtmlService
        .createHtmlOutputFromFile('NewJobSidebar')
        .setTitle('New Job Sidebar')
        .setWidth(200);
    SpreadsheetApp.getUi()
        .showSidebar(html);
  }
  
  function applyNew(turnaround, category, upcharge, discount,
  datein, timein, datedue, timedue, client, type, description, pages) {
    var ss = SpreadsheetApp.getActive()
    var sheet = ss.getSheetByName("Job Log");
  
    //Select the column we will check for the first blank cell
    var columnToCheck = sheet.getRange("B:B").getValues();
    
    // Get the last row based on the data range of a single column.
    var lastRow = getLastRowSpecial(columnToCheck);
    
    var selectedRow = lastRow + 1;
  
    //Apply turnaround to Column B = Column 2
    sheet.getRange(selectedRow, 2).setValue(turnaround);
    //Apply category to Column C = Column 3
    sheet.getRange(selectedRow, 3).setValue(category);
    //Apply upcharge to Column D = Column 4
    sheet.getRange(selectedRow, 4).setValue(upcharge);
    //Apply discount to Column E = Column 5
    sheet.getRange(selectedRow, 5).setValue(discount);
    //Apply datein to Column F = Column 6
    sheet.getRange(selectedRow, 6).setValue(datein);
    //Apply timein to Column G = Column 7
    sheet.getRange(selectedRow, 7).setValue(timein);
    //Apply datedue to Column H = Column 8
    sheet.getRange(selectedRow, 8).setValue(datedue);
    //Apply timedue to Column I = Column 9
    sheet.getRange(selectedRow, 9).setValue(timedue);
    //Apply client to Column L = Column 12
    sheet.getRange(selectedRow, 12).setValue(client);
    //Apply type to Column M = Column 13
    sheet.getRange(selectedRow, 13).setValue(type);
    //Apply description to Column N = Column 14
    sheet.getRange(selectedRow, 14).setValue(description);
    //Apply pages to Column O = Column 15
    sheet.getRange(selectedRow, 15).setValue(pages);
    
  };
  
  function getLastRowSpecial(range){
    var rowNum = 0;
    var blank = false;
    for(var row = 0; row &lt; range.length; row++){
  
      if(range[row][0] === "" &amp;&amp; !blank){
        rowNum = row;
        blank = true;
      }else if(range[row][0] !== ""){
        blank = false;
      };
    };
    return rowNum;
  };</code></pre>
  </div>
  </div>
  </p>
  
  <p>NewJobSidebar.html:</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>&lt;body bgcolor="#039EB2"&gt;
    &lt;font face="arial" color="white"&gt;
      &lt;b&gt;Enter new job details.&lt;/b&gt;&lt;br&gt;&lt;br&gt;
      
      &lt;!-- Create input fields to accept values from the user --&gt;
      Turnaround:&lt;br&gt;
      &lt;input type="text" id="Turnaround"&gt;&lt;br&gt;&lt;br&gt;
      
      Category:&lt;br&gt;
      &lt;input type="text" id="Category"&gt;&lt;br&gt;&lt;br&gt;
      
      Upcharge:&lt;br&gt;
      &lt;input type="text" id="Upcharge"&gt;&lt;br&gt;&lt;br&gt;
      
      Discount:&lt;br&gt;
      &lt;input type="text" id="Discount"&gt;&lt;br&gt;&lt;br&gt;
      
      Date In:&lt;br&gt;
      &lt;input type="text" id="DateIn"&gt;&lt;br&gt;&lt;br&gt;
      
      Time In:&lt;br&gt;
      &lt;input type="text" id="TimeIn"&gt;&lt;br&gt;&lt;br&gt;
      
      Date Due:&lt;br&gt;
      &lt;input type="text" id="DateDue"&gt;&lt;br&gt;&lt;br&gt;
      
      Time Due:&lt;br&gt;
      &lt;input type="text" id="TimeDue"&gt;&lt;br&gt;&lt;br&gt;
      
      Client:&lt;br&gt;
      &lt;input type="text" id="Client"&gt;&lt;br&gt;&lt;br&gt;
      
      Type:&lt;br&gt;
      &lt;input type="text" id="Type"&gt;&lt;br&gt;&lt;br&gt;
      
      Description:&lt;br&gt;
      &lt;input type="text" id="Description"&gt;&lt;br&gt;&lt;br&gt;
      
      Pages:&lt;br&gt;
      &lt;input type="text" id="Pages"&gt;&lt;br&gt;&lt;br&gt;
      
      &lt;!-- Create a button to update values --&gt;
      &lt;button onclick='saveNew()'&gt;Save New Job&lt;/button&gt;&lt;br&gt;&lt;br&gt;&lt;br&gt;
      
  &lt;script&gt;
  
    function saveNew(){
     //Get the value of the input fields 
     var turnaround = document.getElementById("Turnaround").value
     var category = document.getElementById("Category").value
     var upcharge = document.getElementById("Upcharge").value
     var discount = document.getElementById("Discount").value
     var datein = document.getElementById("DateIn").value
     var timein = document.getElementById("TimeIn").value
     var datedue = document.getElementById("DateDue").value
     var timedue = document.getElementById("TimeDue").value
     var client = document.getElementById("Client").value
     var type = document.getElementById("Type").value
     var description = document.getElementById("Description").value
     var pages = document.getElementById("Pages").value
     
     //Log input values in the web browser console
     console.log(turnaround, category, upcharge, discount,
     datein, timein, datedue, timedue, client, type, description, pages)
  
     //Send values as arugments to the server side function
     google.script.run.applyNew(turnaround, category, upcharge, discount,
     datein, timein, datedue, timedue, client, type, description, pages)
    }
    
  &lt;/script&gt;
      
    &lt;/font&gt;
    &lt;/body&gt;</code></pre>
  </div>
  </div>
  </p>