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?

Question

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.

doGet function

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.productList = productListArray;
 
  return tmp.evaluate();
}

Add to Google Sheet

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

Add record

function addRecord(){
       
      var tagInfo = {};
         
      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);
}

How dropdowns are populated in the HTML.

<div class="input-field col s3">
    <select id="product" onchange="buildURL()">
        <option disabled selected value="">Choose a product</option>
            
        <?!= productList; ?>

     </select>

     <label>Product</label>
    
</div>

Need to see anything else? I think it might be relatively easy to add the new value to the column but the tricky part seems to be the update of only that one dropdown and not the entire app. To me it seems like I want to trigger the doGet() function again but only for that specific dropdown. Thoughts?

UPDATE: current code to add new value to dropdown

function addProduct() {
    let newProd = document.getElementById("newProduct").value;
    google.script.run.withSuccessHandler(updateProductDropdown).addNewProduct(newProd);
      
    document.getElementById("newProduct").value = "";
}
   
function updateProductDropdown(newProd){
       var newOption = document.createElement('option');
       newOption.value = newProd;
       newOption.text = newProd;
       document.getElementById('product').add(newOption);
}

UPDATE2: App Scripts function to add new value to column in spreadsheet

function addNewProduct(newProd){
  var columnLetterToGet, columnNumberToGet, direction, lastRow, lastRowInThisColWithData, rng, rowToSet, startOfSearch, valuesToSet;
  var ss = SpreadsheetApp.openById(ssId);
  var ws = ss.getSheetByName("List Source - External");
  
  lastRow = ws.getLastRow();
  //Logger.log('lastRow: ' + lastRow)
  
  columnNumberToGet = 9;//Edit this and enter the column number
  columnLetterToGet = "I";//Edit this and enter the column letter to get
  
  startOfSearch = columnLetterToGet + (lastRow).toString();//Edit and replace with column letter to get
  //Logger.log('startOfSearch: ' + startOfSearch)
  
  rng = ws.getRange(startOfSearch);
  
  direction = rng.getNextDataCell(SpreadsheetApp.Direction.UP);//This starts
  //the search at the bottom of the sheet and goes up until it finds the
  //first cell with a value in it
  
  //Logger.log('Last Cell: ' + direction.getA1Notation())
  
  lastRowInThisColWithData = direction.getRow();
  //Logger.log('lastRowInThisColWithData: ' + lastRowInThisColWithData)
  
  rowToSet = lastRowInThisColWithData + 1;
  valuesToSet = [newProd];
  
  ws.getRange(rowToSet, 9).setValues([valuesToSet]);
  
  return newProd;
  
}

SOLUTION to Update Materialize Dropdown

function updateProductDropdown(newProd){
   newProdOption = document.getElementById('product');
   newProdOption.innerHTML += '<option>' + newProd + '</option>';
   var elems = document.querySelectorAll('select'); 
   var instances = M.FormSelect.init(elems);
}

Ok. Can you expand on that? Sounds like that would be triggered from the UI side like the 'addRecord' function I use but on the Google Scripts side something needs to happen to add the new value to the Google Sheets column and then refresh the one dropdown correct? — DK_Connection, Jun 11 '20 at 14:44
Go to documentation Guides section client to server communication read it until you understand it — Cooper, Jun 11 '20 at 14:48
This one? https://developers.google.com/apps-script/guides/html/communication I guess this one works too. https://developers.google.com/apps-script/guides/html/reference/run#code.gs_2 — DK_Connection, Jun 11 '20 at 17:27

Aerials · Accepted Answer · 2020-06-17T08:28:31.297

0

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>
  <label for="newOption">New option for the dropdown:</label>
  <input type="text" id="nopt" name="newOption">
  <input type="button" value="Submit" 
onclick="google.script.run.withSuccessHandler(updateDropdownWidget)
.updateDropdownList(document.getElementById('nopt').value)">
</form>
<label for="cars">Choose a car:</label>
<select name="cars" id="cars">
<?!= values; ?>
</select>
<script>
  function updateDropdownWidget(text){
    var option = document.createElement('option');
    option.value = text;
    option.text = text;
    document.getElementById('cars').add(option);
  }
</script>

Code.gs:

function doGet(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();
}

function updateDropdownList(text_from_input){
  // Log the user input to the console
  console.log(text_from_input);
  // Write it to the sheet below the rest of the options
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getDataRange().getLastRow();
  sheet.getRange(lastRow+1,1).setValue(text_from_input);
  // Return the value to the callback
  return text_from_input;
}

edited Jun 17 '20 at 08:28

answered Jun 11 '20 at 15:20

Aerials

4,231
1
16
20

I'm not positive but wouldn't the l dropdown need to be updated on the server side since it is using Google Script to access and manipulate the Google Sheet? – DK_Connection Jun 11 '20 at 21:16
I added some more explanation to my answer. – Aerials Jun 12 '20 at 07:24
Ok. So I'm going to add an event listener to listen for the button click. When that happens the function, let's call it `addProduct`, is triggered. `addProduct` grabs the value in the new product field and passes it to `updateDrownList(text)`. `updateDrownList(text)` adds new product value to the Google Sheet. If that is successful then `updateDropdownWidget()` is executed. Now, the initial dropdown fill happens on the Apps Script side. How would I update it from the front end? – DK_Connection Jun 12 '20 at 12:53
I added some code to my original question showing how the dropdowns are built. They use `` not `

DK_Connection

Jun 12 '20 at 15:16

Ok, instead of an "li" element create an "option" element and (add, addChild, append or appendChild)-it to the containing element of ID "source" – Aerials Jun 13 '20 at 18:36

Thanks. I'm still a little stuck. Partly because I am trying to figure out how to add the single value to the end of a specific column (question posted here: https://stackoverflow.com/questions/62393563/how-do-i-add-a-new-value-to-the-end-of-a-column-in-google-sheets-with-apps-scrip). I'm not sure I understand how to pass `text_from_sheet` back to the `updateDropdownWidget(text_from_sheet)` – DK_Connection Jun 15 '20 at 19:57

to obtain text_from_sheet I would use `SpreadsheetApp` it has methods like [`getLastRow()`](https://developers.google.com/apps-script/reference/spreadsheet/sheet#getlastrow) that can be useful – Aerials Jun 16 '20 at 08:18

Hmm. So I'm not quite grasping this. In my Apps script I have `updateDrownList()` where I am passing the new value from my text field on button click. Once that executes the new value is added to my spreadsheet in the proper column and `updateDropdownWidget` executes on the JavaScript side or am I misunderstanding this? When that JS executes it is adding that same value to the dropdown. Where does SpreadsheetApp come in since the `updateDropdownWidget` executes on the JS side? – DK_Connection Jun 16 '20 at 14:18

You have to pass a return value to the callback function, that's why I say "text_from_sheet" it's up to you if you want to grab the value from the sheet, or pass the one from user input directly. I have updated my answer. Hope it is clear now. Remember to accept and/or upvote if it answers your question – Aerials Jun 17 '20 at 08:30

Can't seem to get that to work. I returned the value of newProd at the end of the `addProduct` function that puts the new value in the spreadsheet but the `updateProductDropdown` function doesn't seem to grab it. I'm probably making some newb mistake. I added the functions I am using on the JS side to the original question – DK_Connection Jun 17 '20 at 16:02

Not sure if it matters but my JS is not inline but in separate JS file. – DK_Connection Jun 17 '20 at 19:52

`addNewProduct()` from your .gs source is the function that should return text to `updateProductDropdown` that is in your client side. I don't see your `addNewProduct()` function – Aerials Jun 18 '20 at 07:17

I added the GS function that adds the new value to the Google Sheet column to my original question. – DK_Connection Jun 18 '20 at 13:07

Your problem is in `updateProductDropdown` check your – Aerials Jun 18 '20 at 13:41

Sorry. Forgot to update my original question with what the `updateProductDropdown` function now looks like. I've updated it. – DK_Connection Jun 18 '20 at 13:47

I don't see why it shouldn't work. Try debuggin using the console. write the instructions of `updateProductDropdown` manually until you understand where your issue is – Aerials Jun 18 '20 at 13:53

Not sure if I am using console right but I see this in the readout `Uncaught ReferenceError: options is not defined at HTMLDocument. (userCodeAppPanel:10)` – DK_Connection Jun 18 '20 at 15:10

A little more info. I am using Materialize CSS for the styling of the form. Not sure if that is relevant but when I do a search for the above error it returns a similar error with Materialize. – DK_Connection Jun 18 '20 at 15:14

Ok. Materialize is probably the issue. Here is what another person shared with me. If you need to change the select options after it's been initialized, I'm just guessing that you'd need to destroy the instance, append your original select with your new option, then run step 2 again. https://codepen.io/doughballs/pen/LYGxEwb actually we just need to add the option to the original select and then re-init. Bingo. `$('#my-select').append(''); $('select').formSelect();` How would you do that with JS and my current implementation? – DK_Connection Jun 18 '20 at 17:20

Still struggling with this. It's probably dead simple. I know that `newProd` is being returned to the `updateProductDropdown`. I just can't get the dropdown to update. A I mentioned earlier this should be just a re-instantiation of the select. I imagine I c an do that in the `updateDropdown` function but haven't been able to get it to work. Here is the Codepen again with an example of re-instatiation. https://codepen.io/doughballs/pen/LYGxEwb – DK_Connection Jun 22 '20 at 22:20

Took forever to figure it out. Turns out it was simply a matter of adding the new option and re-initializing the Materialize select. I've added the code to the original question. – DK_Connection Jun 24 '20 at 13:33

Cooper · Answer 2 · 2020-06-11T18:00:11.283

0

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:

function getProjectFunctionNames() {
  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:

edited Jun 11 '20 at 18:00

answered Jun 11 '20 at 17:49

Cooper

59,616
6
23
54

Ok. So let's say what I need to add is a new product. I'm going to use `addEventListener` to the button like `document.getElementById("btn").addEventListener("click", addProduct);` The addProduct function will trigger a gs script . Something like `google.script.run.addProduct(newProduct);` The script will then add the new product to the end of the appropriate column in the Google Sheet and if that is successful it will update the product dropdown. Sound about right? – DK_Connection Jun 11 '20 at 18:55
google.script.run is designed to call functions on the server. addEventlistener is designed to bind click events to functions on the client. – Cooper Jun 11 '20 at 19:06
Okay I just read your question again. You're not a programmer. So I guess I just mention that the client is the enviroment that the browser supports on your computer the server is where google apps script runs. With google apps script you can use some javascript both client side and server but not all. Things have any to do with html events are totally server side. – Cooper Jun 11 '20 at 19:11
Thanks but I'm not sure if I am on the right track. I used the event listener to listen for the click. When that happens addProduct is triggered. addProduct grabs the value in the new product field and passes it to a function in the Google Apps Script (.gs). There the new product value is added to the Google Sheet. if that is successful then the drop-down gets refreshed. At least that is how my mind envisions it. Similar to how the userClicked() and addRecord() function in my question work with the exception of performing an action on the app after the data is pushed to the Google Sheet. – DK_Connection Jun 11 '20 at 19:19
Pretty crazy how long it tool but I finally it figured out. I've added the code to my original question. – DK_Connection Jun 24 '20 at 13:30

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?

2 Answers2

Example:

index.html:

Code.gs:

Linked