How to get a value from a Listbox in HTML Service

user_4368112 0
Viewed 185

I am having a problem with HTML Service in Google Apps Script. I am trying to get a value from a listbox to pull and populate the rest of a form. This worked great when using uiApp but since Google has decided to shut-down uiApp next month, I have no choice but to migrate all my apps to HTML Service.

I have found some bites on using scriptlets here on stackoverflow but I keep getting Errors.

HTML

<form>

<table>
<tbody>
<tr>
  <td><label>Select Clinic:</label></td>
  <td><select name="selectClinic"id="selectClinic"onchange="doThisOnChange(this.value)"> 
  </select>

  <script>
    doThisOnChange = function(value)
    {
    alert( "Do something with the value: " + value );
    <?!=
    var ClinicID =  value;
    var SS = SpreadsheetApp.openById("MyID");
    var sheet= SS.getSheetByName('Data');
    var getClinicName = sheet.getRange("D"+ClinicID+":D"+ClinicID);
    window.document.getElementById("clinicName").value = getClinicName ; 
    ?>          
    }
    </script>

    <tr>
    <td><label>Clinic Name:</label></td>
    <td><input type="text" name="clinicName" id="clinicName" value=""> 
  </td>
</tr>

It appears that the script is not able to get the value passed when a scriptlet is used. Getting the error

"ReferenceError: "value" is not defined. (line 5, file "Code")"

I was finally able to get the form to populate the fields by using the window.document.getElementById method, but it appears that the script stops when a scriptlet is not used.

Any help would be appreciated.

google-apps-script listbox web-applications
1 Answers

Problem

You are mixing up server-side component that runs only once on template evaluation (scriplets) and client-side script. Thus, value used as an doThisOnChange argument can't be accessed in the scriplet.

Solution

If you need to call server-side component more than once, you should use the google.script.run API instead as this is the only way of communicating with server-side functions.

Sample

So, if I understood your goal correctly, you should decouple your code like this (please, note that it is important for the server-side function to explicitly return the desired output + you set a withSuccessHandler() to perform client-side actions on server-side function successful run result):

Server-side

function getClinicName(ClinicID) {
  var OPCDSS = SpreadsheetApp.openById("MyID"); //I assume SS === OPCDSS?;
  var sheet = OPCDSS.getSheetByName('Data');
  var range = sheet.getRange("D"+ClinicID+":D"+ClinicID);
  var name  = range.getValue(); //this part was originally missing;
  return name;    
}

Client-side

  <script>
    /**
     * Asynchronously calls server-side function;
     * @param {String} value clinic select value; 
     */
    function doThisOnChange (value) {
      alert( "Do something with the value: " + value );
      google.script.run.withSuccessHandler(fillName).getClinicName(value);
    }

    /**
     * Performs client-side actions on server-side function return;
     * @param {*} serverOutput value returned from server-side;
     */
    function fillName (serverOutput) {
      var clinicNameInput = document.getElementById('clinicName');
          clinicNameInput.value = serverOutput;
    }
</script>

Multiple server-side fields

As discussed in comments, added an example of working with multi-column and / or row Range instances. If you call a getValues() method on a Range, the resulting Array of values to iterate on will be structured as:

[ //pseudo-code;
  row1 [ col1 , ..., colN ] ,
  ...,
  rowN [ col1, ..., colN ] 
]

Please, note that working on 2D Array instances is just a best practice since getValue() / getValues() methods are computationally heavy. To sum up, instead of directly returning the name of the clinic, you would create an Object instance containing all clinic properties (that can be later referenced in client-side callback function by key) or you can simply pass return the Array and work on it client-side:

function getData(ClinicID) {
  var ss     = SpreadsheetApp.openById("MyID");
  var sheet  = ss.getSheetByName('Data');
  var range  = sheet.getRange(yourRangeboundaries); 
  var values = range.getValues(); //gets 2D Array of values;

  var output = {}; //initiate output Object;

  output.name = values[0][0]; //assuming name is in 1st row 1st column;
  output.phone = values[0][4]; //assuming phone is in 1st row 5th column;
  //etc;

  return output;
}

Useful notes

Client-to-server communication with google.script.run is asynchronous in nature, which means that you don't have to wait until your server-side function finishes to perform other tasks, but at the same time you can only access server-side results in scope of a callback function.

Useful links

  1. Templated HTML guide;
  2. Client-to-server communication guide;
  3. google.script.run API reference;
  4. getValue() method reference;
user_114076950
Related