CODEWITHSUNDEEP
htmlgoogle-apps-scriptgoogle-sheets
Adamsonss1112
Adamsonss1112

Reputation: 1

A drop-down list of cells located in the Google Sheets

Hi I am trying to get a dropdown list using HTML and Google Script. The options should be taken from the Google Spreadsheet. My code causes errors: "There was an error! ReferenceError: document is not defined." What should I improve? How to do it? Sorry for the mess in the code. I'm not a programmer.

code.gs

  function getDropDownContent() {
  var sheet = SpreadsheetApp.openById('XYZ').getSheetByName('MAG');
  var lastRow = sheet.getLastRow();
  var myRange = sheet.getRange("J2:J" + lastRow);
  var options = new Array()
  var select = document.getElementById("NUMREJ");
  var data = myRange.getValues();

  for(var i = 0; i < lastRow; i++) {
    options.push(data[i]);
  }

  return options;
};

HTML CODE

     <html>
          <head>
            <base target="_top">
<script>
         function addList() {
            console.log('addList ran!');
        
            google.script.run
              .withFailureHandler(onFailure)
              .withSuccessHandler(injectMyContent)
              .getDropDownContent();
          };
        
          window.injectMyContent = function(argReturnedData) {
            for(var i = 0; i < argReturnedData.length; i++) {
              var opt = argReturnedData[i];
              var el = document.createElement("option");
              var el = document
              el.text = opt;
              el.value = opt;
              select.appendChild(el);
            };
          };
        
          window.onFailure = function(err) {
            alert('There was an error! ' + err.message);
          };
        
        
        
             </script>
          </head>
          <body onload = "addList()">
         <select id="NUMREJ">
    < option > Choose a option < /option>
                    </select>
        </body>
        </html>

Upvotes: 0

Views: 47

Answers (1)

Cooper
Cooper

Reputation: 64100

You getElementById() is clientside only:

You don't have access to the server side dom in the script editor.

function getDropDownContent() {
  var sheet = SpreadsheetApp.openById('XYZ').getSheetByName('MAG');// server side only except in scriptlets
  var lastRow = sheet.getLastRow();
  var myRange = sheet.getRange("J2:J" + lastRow);
  var options = new Array()
  var select = document.getElementById("NUMREJ");//client side code only
  var data = myRange.getValues();

  for(var i = 0; i < lastRow; i++) {
    options.push(data[i]);
  }

  return options;
};

You could accomplish this with scriptlets in templated html.

Upvotes: 1

Related Questions