CODEWITHSUNDEEP
javascripthtmlgoogle-apps-scriptgoogle-sheets
A. Prats
A. Prats

Reputation: 59

getValues from Google Sheet to html <select>

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:

sidebar

Upvotes: 2

Views: 930

Answers (1)

Tanaike
Tanaike

Reputation: 201523

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:

Upvotes: 1

Related Questions