getValues from Google Sheet to html <select>-CodePudding

I'm rather new to Google Scripts and HTML and I've managed to put together something from what I could gathered on other posts, but I've hit a wall when trying to automatically populate an html dropdown menu from a Google Sheet workbook.

The aim is to pop up a sidebar on a Google Sheet that contains a dropdown menu based on data from that same Google Sheet.

Below the codes & additional context.

GOOGLE SCRIPT

function mediaplanAdminSidebar() {
  var widget = HtmlService.createHtmlOutputFromFile('mediaplan').setTitle('HELP');
  SpreadsheetApp.getUi().showSidebar(widget)
  SpreadsheetApp.newTextStyle().setBold(true)
}

HTML CODE

<html>
  <body>
  <style>span{font-size: 34px;font-weight: bold;text-decoration: normal;font-style: normal;font-family: sans-serif;}</style>
  <style>text{font-size: 24px;font-weight: normal;text-decoration: normal;font-style: normal;font-family: sans-serif;}</style>
  <style>p{font-size: 14px;font-weight: normal;text-decoration: normal;font-style: normal;font-family: sans-serif;}</style>
  <style>label{font-size: 14px;font-weight: normal;text-decoration: normal;font-style: normal;font-family: sans-serif;}</style>

  <br/><span>Paid Social</span>
  <br/><text>Media planner</text>
  <p>Through this menu you will be able to produce a slide deck containing basic media buying info as well as the forecast for a campaign of your choice.</p>
  
<label>Select your campaign:</label></br>

<div>
    <select>
      <option>
        <script>
          var sheet = SpreadsheetApp.openById("SPREADSHEETID").getSheetByName('[PAID] #1-Activity tracker');
          var myrange = sheet.getRange(7,6,1000);
          var campnames = myrange.getValues();
          campnames[1];
        </script>
      </option>
  </select>
</div>

  </body>
</html>

Further context:

This might look horribly wrong to most of you, but I'm struggling to grasp the basics of it, and I am failing to understand how to make these two pieces of code talk to each other.
I have tried a couple different ways without success. The above is the last I've got.
That given range (7,6,10000) in that particular sheet has data.
This is what it looks like (see below). Note the dropdown menu is empty.

CodePudding user response：

When I saw your script, it seems that you are trying to run Google Apps Script at the Javascript side. I think that this is the reason for your issue. And also, when campnames[1]; is output, the 2nd element of the array campnames is used. I thought that this might be your 2nd issue.

If you want to retrieve the values from Spreadsheet on the Google Apps Script side and show the values in the dropdown list of the HTML side, how about the following modification?

HTML side:

From:

<div>
    <select>
      <option>
        <script>
          var sheet = SpreadsheetApp.openById("SPREADSHEETID").getSheetByName('[PAID] #1-Activity tracker');
          var myrange = sheet.getRange(7,6,1000);
          var campnames = myrange.getValues();
          campnames[1];
        </script>
      </option>
  </select>
</div>

To:

<div><select><?!= data ?></select></div>

Google Apps Script side:

From:

function mediaplanAdminSidebar() {
  var widget = HtmlService.createHtmlOutputFromFile('mediaplan').setTitle('HELP');
  SpreadsheetApp.getUi().showSidebar(widget)
  SpreadsheetApp.newTextStyle().setBold(true)
}

To:

function mediaplanAdminSidebar() {
  var sheet = SpreadsheetApp.openById("SPREADSHEETID").getSheetByName('[PAID] #1-Activity tracker');
  var myrange = sheet.getRange(7, 6, 1000);
  var campnames = myrange.getValues();
  var widget = HtmlService.createTemplateFromFile('mediaplan');
  widget.data = campnames.reduce((s, [e]) => s  = `<option value="${e}">${e}<\/option>\n`, "");
  SpreadsheetApp.getUi().showSidebar(widget.evaluate().setTitle('HELP'));
}

Note:

About the maximum number of options, I thought that this thread might be useful. Ref

Reference:

HTML Service: Templated HTML