How do I add a new value to a Google Sheet from a text field in a Web App and then automatically update the dropdown?

[Dan Konig]

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 populated via a Google Sheet. The user chooses a selection from the list but this dropdown does not have all of the possible values it could have. There will likely be a time when the user needs a new value added. While I could manually update the spreadsheet as new values are requested that introduces an element of human availability to get this done and I'm not always available.

What I would prefer is a self-serve model. I want to supply the user with a text field where they can enter the new value and submit it to the Google Sheet. Then I would like the dropdown to be updated with the new value for the user to choose. 

Now, I realize that I could just submit the value in the new field to the Google Sheet but that will require building a condition to see whether it is the dropdown or text field that has a value in it. I'd also need some type of error handling in case both the dropdown and text field have values. That seems like a bigger headache to program then my ask. 

I'm not sure what code you would need to see to help make this work but here is what I think might help.

function doGet(e){
 
   
  var ss = SpreadsheetApp.openById(ssId)
  var ws = ss.getSheetByName("External");
  var range = ws.getRange("A2:D2");
  var valuesArray = [];
  for (var i = 1; i <= range.getLastColumn(); i++){
    var lastRowInColumn = range.getCell(1, i).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
    var list = ws.getRange(2,i,lastRowInColumn-1,1).getValues();
    valuesArray.push(list);
  }
 
   
  var userEmail = Session.getActiveUser().getEmail();
  var sourceListArray = valuesArray[2].map(function(r){ return '<option>' + r[0] + '</option>'; }).join('');
  var productListArray = valuesArray[3].map(function(r){ return '<option>' + r[0] + '</option>'; }).join('');
 
  var tmp = HtmlService.createTemplateFromFile("config");
  tmp.email = userEmail;
  tmp.sourceList = sourceListArray;
  tmp.productList = productListArray;
 
  return tmp.evaluate();
}

function userClicked(tagInfo){
 
  var ss = SpreadsheetApp.openById(ssId)
  var ws = ss.getSheetByName("Data");
 
  ws.appendRow([tagInfo.email, tagInfo.source, tagInfo.product, new Date()]);
 
}

    function addRecord(){
       
          var tagInfo = {};
         
          tagInfo.email = document.getElementById("email").value;
          tagInfo.source = document.getElementById("source").value;
          tagInfo.product  = document.getElementById("product").value;
         
          google.script.run.userClicked(tagInfo);
         
         
          var myApp = document.getElementById("source");
          myApp.selectedIndex = 0;
          M.FormSelect.init(myApp);
         
          var myApp = document.getElementById("brand");
          myApp.selectedIndex = 0;
          M.FormSelect.init(myApp);

          var myApp = document.getElementById("product");
          myApp.selectedIndex = 0;
          M.FormSelect.init(myApp);
     }

Need to see anything else?

[Kim Nilsson]

Not reading any of the code... just thinking out loud.

You should be able to have a separate "insert new value here" function, in a separate area of the page.

And when you submit a new value I'm pretty sure that the entire web app is reloaded, and thereby the new value should be available in your drop-down.

No?

[Dan Konig]

I wouldn't want to reload the entire app. That would reset all of the fields the user already selected.