Search code examples
google-apps-scriptgoogle-sheetsweb-applications

Update web app drop down lists daily with new items

I’ve got a functioning google webapp that draws data from a Google spreadsheet. I’d like to use the same webapp link over the course of a week, but have the drop down lists update daily with new items. Currently the drop down lists are created in my .gs file when the web app is first deployed. Is it possible to either update these lists on the fly as new data comes available or is there a way to re-deploy the webapp say overnight to update the lists that way?

So far have searched for 'updating html lists in google webapp on the fly' and similar.

Below have included the .gs and html so you can see how the list is sourced and how it is used in HTML.

var url = "https://docs.google.com/spreadsheets/d/idcode/edit#gid=0";


  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Draws");
  var data = ws.getRange(1, 1, ws.getLastRow(), 8).getValues();

function doGet(e){
  
  var m_list =ws.getRange(1, 1, ws.getRange("A1").getDataRegion().getLastRow()).getValues();
  var m_htmlListArray = m_list.map(function(r){ return '<option>' + r[0] + '</option>'; }).join('');

  var f_list =ws.getRange(1, 5, ws.getRange("E1").getDataRegion().getLastRow()).getValues();
  var f_htmlListArray = f_list.map(function(r){ return '<option>' + r[0] + '</option>'; }).join('');


  var tmp = HtmlService.createTemplateFromFile("page");
  tmp.title = "Title";
  tmp.mlist = m_htmlListArray;
  tmp.flist = f_htmlListArray;
  var html = tmp.evaluate().setTitle('Team Selection WebApp');
  var htmlOutput = HtmlService.createHtmlOutput(html); 
  htmlOutput.addMetaTag('viewport', 'width=device-width, initial-scale=1');  
  return htmlOutput

<select class="form-select form-select-sm" id="m_p1" onchange="getDrawNumOne()">
              <option selected>Men's Player 1</option>
              <?!= mlist; ?>
            </select>
Solution

  • Since the values for the dropdown comes from the spreadsheet, you might make use of setTimeout on the client-side to implement a polling solution.

    Related

    JavaScript

    Google Apps Script && Client-Side JavaScript