How Do I Add A New Value To A Google Sheet From A Text Field In A Web App And Then Automatically Update The Associated Dropdown?

WARNING: I'm not a programmer by trade. Ok. Got the disclaimer out of the way. So this might not be the best way to do this but here is the scenario. I have a dropdown that gets po

Solution 1:

You can specify a client side callback function if you use google.script.run withSuccessHandler(callback) where your callback could update the list only and not the whole site.

Example:

google.script.run.withSuccessHandler(updateDropdownWidget).updateDropdownList(text_from_input)

Where updateDrownList(text_from_input) is a function in your Apps Script that adds text to the sheet using SpreadsheetApp for example, and returns the "text" to the callback function: updateDropdownWidget(text) which adds a new list item to the HTML drop-down list in your front end.

index.html:

<form><labelfor="newOption">New option for the dropdown:</label><inputtype="text"id="nopt"name="newOption"><inputtype="button"value="Submit"onclick="google.script.run.withSuccessHandler(updateDropdownWidget)
.updateDropdownList(document.getElementById('nopt').value)"></form><labelfor="cars">Choose a car:</label><selectname="cars"id="cars">
<?!= values; ?>
</select><script>functionupdateDropdownWidget(text){
    var option = document.createElement('option');
    option.value = text;
    option.text = text;
    document.getElementById('cars').add(option);
  }
</script>

Code.gs:

functiondoGet(e){
  var ss = SpreadsheetApp.getActiveSheet();
  var lastRow = ss.getDataRange().getLastRow();
  var values = ss.getRange(1,1,lastRow,1).getValues();
  var valuesArray = [];
  for (var i = 0; i < values.length; i++){
    valuesArray.push('<option value="'+values[i]+'">' +values[i]+ '</option>');
  }
  var tmp = HtmlService.createTemplateFromFile("index");
  tmp.values = valuesArray;
  return tmp.evaluate();
}

functionupdateDropdownList(text_from_input){
  // Log the user input to the consoleconsole.log(text_from_input);
  // Write it to the sheet below the rest of the optionsvar sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getDataRange().getLastRow();
  sheet.getRange(lastRow+1,1).setValue(text_from_input);
  // Return the value to the callbackreturn text_from_input;
}

Solution 2:

Here's an example:

In my Stack Over Flow spreadsheet I four buttons which can be used to run any function in 3 script files and every time I load the sidebar it reads the functions in those script files and returns them to each of the select boxes next to each button so that I test functions that I write for SO with a single click and I can select any function for any button. Here's the Javascript:

 $(function(){//JQuery readystate function
    google.script.run
    .withSuccessHandler(function(vA){
      let idA=["func1","func2","func3","func4"];
      idA.forEach(function(id){
        updateSelect(vA,id);
      });
    })
    .getProjectFunctionNames();
  })

Here is GS:

functiongetProjectFunctionNames() {
  const vfilesA=["ag1","ag2","ag3"];
  const scriptId="script id";
  const url = "https://script.googleapis.com/v1/projects/" + scriptId + "/content?fields=files(functionSet%2Cname)";
  const options = {"method":"get","headers": {"Authorization": "Bearer " +  ScriptApp.getOAuthToken()}};
  const res = UrlFetchApp.fetch(url, options);
  let html=res.getContentText();
  //SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), "Project Functions");let data=JSON.parse(res.getContentText());
  let funcList=[];
  let files=data.files;
  files.forEach(function(Obj){
    if(vfilesA.indexOf(Obj.name)!=-1) {
      if(Obj.functionSet.values) {
        Obj.functionSet.values.forEach(function(fObj){
          funcList.push(fObj.name);
        });
      }
    }
  });
  //SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(funcList.join(', ')), "Project Functions");return funcList;//returns to withSuccessHandler
}

Image:

Animation: