How to Populate Google Sheet Cell with Selected Value from HTML UI?

Question

I have created an HTML UI from a Google Sheet file I am working with. The UI pulls a list of vendors from my Vendor Database tab, allowing a user to select which vendor they'd like to place an order with from the HTML UI. Upon click of the Save button in the HTML UI, though, I'd like the file to populate cell B12 of the POTemplate tab with the user's selection, but am unsure how to do this. Right now, I have taken the following steps to make this happen, but with limited success:

APPS SCRIPT

This populates the HTML drop down list with vendor names from our Vendor Database tab in the active file:

function getVendors() {
  var active = SpreadsheetApp.getActive();
  var sheet = active.getSheetByName("Vendor Database");
  var lastRow = sheet.getLastRow();
  var myRange = sheet.getRange("A2:A" + lastRow); 
  var data    = myRange.getValues();
  var optionsHTML = "";
  for (var i = 0; i < data.length; i+=1) {
    optionsHTML += '<option>' + data[i][0] + '</option>';
  };
  return optionsHTML;

This attempts to grab the vendor selected in the HTML UI and populate the preferred cell in our POTemplate tab, B12:

  function save(formObj) {
    var vendor = formObj.vendor;
    var app = SpreadsheetApp;
    var orderSheet = app.getActiveSpreadsheet().getSheetByName("POTemplate");
     orderSheet.getRange(B12).setValue(vendor);
}

HTML Code

<html>
<head>
  </head>
  <body>
    <form>
    <div>
      <select id="vendor">
        <?!= getVendors(); ?>
      </select>    
      <select>
        <?!= getATTN(); ?>
      </select>
    </div>
  </form>
    <input type="button" value="Save PO" class="button button2" 
onClick="google.script.run.save(this.parentNode)" />
  </body>
</html>

You say "limited success" - what is working, and what is not? Be clear and specific. Are there error messages in Apps Script? In the HTML browser console? — tehhowch
Thanks for looking into this, Tehhowch. The breakdown is occurring when the chosen vendor is selected by the user in the HTML UI and the 'onClick' function is called to execute. My guess is the error is in the save() function language as nothing happens when I ask the code to populate cell 'B12' in the POTemplate tab of my file with the selected option from the HTML UI. Thanks for looking at this! — Eric K.
Sorry... no error messages. Just not populating cell B12, as directed. — Eric K.
Have you verified that your functions are all being called, by logging to the browser console in HTML and to Stackdriver in your apps script code? Have you tried using a local function to call the .run method, rather than directly calling it via oncl onclick? — tehhowch
When I run the save() function by itself after subsituting in a hard-coded sample vendor name, B12 populates. The breakdown, when I run the code as a whole, looks to be that apps script is not pulling the HTML UI selection before working to set the value of B12. — Eric K.

Cooper Cooper · Accepted Answer · 2018-04-02T17:53:41

You can also do it like this:

code.gs:

function getSelectOptions() {
  var data = SpreadsheetApp.getActive().getSheetByName('Vender Database').getDataRange().getValues();
  var options = [];
  //the loop skips the first line
  for (var i = 1; i < data.length; i++) {
    options.push(data[i][0]);
  }
  return options;
}
//for the webapp
function doGet()
{
  var output=HtmlService.createHtmlOutputFromFile('test');
  return output.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
//for a dialog
function showDialog(){
  var ui=HtmlService.createHtmlOutputFromFile('test');
  SpreadsheetApp.getUi().showModelessDialog(ui, 'Get Options');
}

test.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
  <script>
    $(function() {
        google.script.run
          .withSuccessHandler(updateSelect)
          .getSelectOptions();//this updates the select options everytime you load the page.
      });

     function updateSelect(vA){
      var select = document.getElementById("sel1");
      select.options.length = 0; 
      for(var i=0;i<vA.length;i++)
      {
        select.options[i] = new Option(vA[i],vA[i]);
      }
    }  
  </script>
  </head>
  <body>
  <select id="sel1"></select>
  </body>
  </html>

Saving the Selected Value in a Sheet

additional gs:

function savSelected(selected){
  SpreadsheetApp.getActive().getSheetByName("POTemplate").getRange('B12').setValue(selected);
}

additional script function:

function savSelect(){
      var selected=$('#sel1').val();
      google.script.run.savSelected(selected);
    }

additional html:

<select id="sel1" onChange="savSelect();"></select>

How to Populate Google Sheet Cell with Selected Value from HTML UI?

2 Answers