Search code examples
htmlgoogle-apps-scriptgoogle-sheetsdropdown

How can I create a web app where I can read text from a sheet based on dropdown selections?

I am trying to create a role description generator which reads pre-written text from a Google Sheet and assembles it in blocks in a web app through selections (team, role, seniority level, etc.) in dropdown menus.

This is an example of what the data in the sheet looks like:

Team name Team description
A-team Description
B-team Description
... ...

So far, for the team selection, I have created the dropdown menu which reads the data from the sheet, and pulls the names of each team into a dropdown list. But my problem is loading the corresponding team description text into the HTML page. I just can't seem to get it to work.

When pressing the generate button, what should happen is that the description for A-team is loaded, but instead I get [object MouseEvent].

Any suggestions? Thanks in advance! :)

enter image description here

Here's my code:

Code.gs

var url = "*spreadsheet URL*";

function doGet(e) {
  
  return HtmlService.createTemplateFromFile('index')
  .evaluate()
  .setSandboxMode(HtmlService.SandboxMode.IFRAME);
  
}

//get the data for the dropdown list
function valuesForList(list) {
  
  //define the data
  var ss = SpreadsheetApp.openByUrl(url)
  var teamsSheet = ss.getSheetByName('Data');
  var lastRow = teamsSheet.getLastRow();
  var teamsRange = teamsSheet.getRange(1, 3, lastRow, 1);
  
  //create a named range
  ss.setNamedRange('teamsList', teamsRange);
  
  //get the values from the range
  var listValues = ss.getRangeByName(list).getValues();
  
  return listValues;
   
}

//the function to show the data on the index.html
function PostInfo (userInfo){
  
  //load the data
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Teams"); 
  var data = ws.getRange(2,1,ws2.getLastRow(),2).getValues();
  
  var teamList = data.map(function(r){ return r[0]});
  var teamDesc = data.map(function(r){ return r[1]});
  
  var position = teamList.indexOf(userInfo.teams);
  if(position > -1){
    
    return teamDesc[position]; 
    
  } else {
  
  return "Unavailable";
  }
  
}

index.html

<!DOCTYPE html>
<html>

  <head>
    <base target="_top">
    
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"> 
    
    <?!= HtmlService.createHtmlOutputFromFile('css').getContent(); ?>
  
    <script>
   
    function onListSuccess(list) {
    
      var listLength = list.length;
      
      for (i=0; i<listLength;i++) {
        var dropdown = document.getElementById("teams");
        
        var opt = document.createElement("option");
        
        dropdown.options.add(opt);
        
        opt.text = list[i][0];
        opt.value = list[i][0];
              
      }
       
    }    
       
    function onListSelect(teamDesc){
    
    var text = teamDesc.toString().split(",");
    
    document.getElementById('est').innerHTML = text;  
  }
  
    
  </script>
 
  </head>
  
  
  <body>
    <div id="main">
    <h1>Role Description Generator</h1>
    <p>
      <label for="teams">Team:</label>
    </p>
    <p>
      <select name="teams" id="teams" tabindex="2"></select>
    </p>
        <button id="btn">Generate</button>
    <div>   
       <label for="est">Team description:</label>
        <p id="est" name="est"></p>
    </div>
    </div>
  </body>
  
  
  <script>
 
  function populateList(){
  google.script.run.withSuccessHandler(onListSuccess).valuesForList('teamsList');
  }
   
   
  document.getElementById("teams").addEventListener("change", doStuff);
  document.getElementById("btn").addEventListener("click", onListSelect);
    
  function doStuff(){
  
  var userInfo = {};
  
  userInfo.teams = document.getElementById("teams").value;
  
  google.script.run.PostInfo(userInfo);
  
  }
  
  window.addEventListener('load', populateList);
  
  </script>

</html>
Solution

  • Modification points:

    • In your script, when the dropdown list is changed, doStuff() is run. But in this case, google.script.run.PostInfo(userInfo) runs only the function of PostInfo at Google Apps Script. By this, the returned value is not used.
    • And, when the button is clicked, onListSelect is run. But in this case, teamDesc of onListSelect(teamDesc) is the event object. By this, such value of [object MouseEvent] is shown. I thought that this might be the reason of your issue.
    • By the way, when I saw your Google Apps Script, I noticed that PostInfo has a modification point. When var data = ws.getRange(2,1,ws2.getLastRow(),2).getValues(); is run, I think that an error occurs. Because ws2 is not declared. In your case, is that ws? I thought that this might be due to your miscopy.

    When you want to show the value from PostInfo when the button is clicked, how about the following modification?

    Modified script:

    HTML&Javascript side:

    From: 
    document.getElementById("teams").addEventListener("change", doStuff);
document.getElementById("btn").addEventListener("click", onListSelect);
  
function doStuff(){

var userInfo = {};

userInfo.teams = document.getElementById("teams").value;

google.script.run.PostInfo(userInfo);

}
    To: 
    document.getElementById("btn").addEventListener("click", doStuff);

function doStuff(){
  var userInfo = {};
  userInfo.teams = document.getElementById("teams").value;
  google.script.run.withSuccessHandler(onListSelect).PostInfo(userInfo);
}

    Google Apps Script side:

    From: 
    var ws = ss.getSheetByName("Teams"); 
var data = ws.getRange(2,1,ws2.getLastRow(),2).getValues();
    To: 
    var ws = ss.getSheetByName("Teams"); 
var data = ws.getRange(2,1,ws.getLastRow(),2).getValues();

    Note:

    • In this modidication, it supposes that the Google Apps Script works fine and returns the correct values. Please be careful this.

    Reference: