CODEWITHSUNDEEP
htmlgoogle-apps-script
Taizooooon
Taizooooon

Reputation: 91

Populate HTML dropdown menu with html on render/load from column in google spreadsheet

I had followed liked process to get a drop down list be populated from google spreadsheet column in a side bar and it worked but by clicking a button. First I would like to have it be loaded when side bar loads instead of having it run after clicking a button. Sending I have on submit script that takes value from side bar and passes it to another function that updates it on sheet and clicking on drop down list button triggers that hence it does not show me drop down.

Can you please help me with figuring out on how to have the drop down list be loaded when side bar opens?

Please see below codes

code.gs

function openSidebar() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sn = ss.getSheetByName("Sheet1");
    var ri = sn.getRange('A1').getValue();
    var pval = sn.getRange(ri, 2).getValue();

    var html = HtmlService.createTemplateFromFile('Page');
    html.pageid = sn.getRange('A1').getValue();
    html.todaysdate = Utilities.formatDate(new Date(),"CST", "MM/dd");
    html.pastnote = pval;

    var rendered = html.evaluate().setSandboxMode(HtmlService.SandboxMode.IFRAME)
    .setTitle('Add Note / Interaction')
    .setWidth(100)
    .setHeight(200);

    SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
    .showSidebar(rendered);
}

function getValuesFromSpreadsheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  return sheet.getRange(1, 4, sheet.getLastRow(), 1).getValues(); // Retrieve values and send to Javascript
}

function processForm(formObject) {
  var ui = SpreadsheetApp.getUi();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sn = ss.getSheetByName("Sheet1");
  var pvalr = sn.getRange(formObject.rowID, 2);
  var pval = formObject.pNote;
  var nxtstpR = sn.getRange(formObject.rowID, 3);
  var nxtstp = formObject.NxtStp;
  var string = '';
  if (pval != ''){
    string = "\n"+pval;
  }
  var string = formObject.tdDate+"-"+formObject.initial+"\n"+formObject.note+string;
  pvalr.setValue(string);
}

Page.html

<!DOCTYPE html>
<script>
  function loadManufacturers() {
    google.script.run.withSuccessHandler(function(ar) {
      let select = document.createElement("select");
      select.id = "select1";
      select.setAttribute("onchange", "selected()");
      document.getElementById("NxtStp").appendChild(select);
      ar.forEach(function(e, i) {
        let option = document.createElement("option");
        option.value = i;
        option.text = e;
        document.getElementById("select1").appendChild(option);
      });
    }).getValuesFromSpreadsheet();
  };

  function selected() {
    const value = document.getElementById("select1").value;
    console.log(value);
  }
</script>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    Please fill in the form and hit submit<br><br>
    <form id="myForm" onsubmit="google.script.run.withSuccessHandler(google.script.host.close).processForm(this)">
      <input type="hidden" name="rowID" value=<?= pageid ?>>
      <input type="hidden" name="tdDate" value=<?= todaysdate ?>>
      <input type="hidden" name="pNote" value=<?= pastnote ?>>
      Your initial:<br>
      <input type="text" name="initial" required="required"><br><br>
      Interaction type:<br>
      <input type="radio" name="iType" value="In Office"> In Office<br>
      <input type="radio" name="iType" value="Skype"> Skype<br>
      <input type="radio" name="iType" value="Phone Call"> Phone Call<br>
      <input type="radio" name="iType" value="Email" required="required"> Email<br><br>
      Your note:
      <textarea name="note" cols="30" rows="10" required="required"></textarea><br><br>
      <div class="dropdown">
        <button onclick="loadManufacturers()" class="dropbtn">Update Next Step</button>
        <div id="NxtStp" class="dropdown-content"></div>
      </div>
      <br><br><br>
      <input type="submit" value="Submit">
    </form><br>
    <input type="button" value="Cancel" onclick="google.script.host.close()" />
  </body>
</html>

Thank you for the help in advance.

Taizooooon

Upvotes: 0

Views: 168

Answers (1)

Tanaike
Tanaike

Reputation: 201523

  • You want to load the drop down list when the sidebar is opened.

If my understanding is correct, how about this modification? Please think of this as just one of several possible answers.

In your current script, when the button of Update Next Step is clicked, the drop down list is loaded by loadManufacturers(). Using this, how about the following modification?

Modified script:

There are 2 modification points.

From: 
<script>
  function loadManufacturers() {
To: 
<script>
  loadManufacturers(); // Added

  function loadManufacturers() {
  • By this modification, loadManufacturers() is run when the sidebar is opened, and the drop down list is loaded.

And also please modify as follows.

From: 
select.id = "select1";
select.setAttribute("onchange", "selected()");
To: 
select.id = "select1";
select.name = "select1"; // Added
select.setAttribute("onchange", "selected()");
  • By this modification, when the button of Submit is clicked, the selected value can be retrieved with formObject.select1 at processForm(formObject).

Note:

  • And also, in this modification, although I'm not sure whether this is what you want because this is not shown in your question, how about removing <button onclick="loadManufacturers()" class="dropbtn">Update Next Step</button>?

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 1

Related Questions