How Add the Link in the Dropdown options?

user_8470494 0
Viewed 53

Hi I'm working on script that I got in this forum.

Options are mentioned in the column A and Links are added in Column B, in the dropdown If I click the option 1 I want to open the respective link which is in Column B in another tab.

enter image description here

Code.gs

function doGet(e) {
  return HtmlService.createTemplateFromFile("Index.html")
    .evaluate()
    .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

function getSelectOptions()
{
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Options');
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var options=[];
  for(var i=0;i<vA.length;i++)
  {
    options.push(vA[i][0]);
  }
  return vA;
}

function showSidebar()
{
  var userInterface=HtmlService.createHtmlOutputFromFile('Index');
  SpreadsheetApp.getUi().showModelessDialog(userInterface, 'The Drop Down with No Options now has options.');
}

Index.html

<!DOCTYPE html>
<html>
  <head>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
  <script>
    $(function() {
        $('#txt1').val('');
        google.script.run
          .withSuccessHandler(updateSelect)
          .getSelectOptions();
      });
    function updateSelect(vA)
    {
      var select = document.getElementById("sel1");
      select.options.length = 0; 
      for(var i=0;i<vA.length;i++)
      {
        select.options[i] = new Option(vA[i],vA[i]);
      }
    }
    console.log("My code");
  </script>
  </head>
  <body>
   <select id="sel1" style="width:125px;height:35px;margin:10px 0 10px 0;">
   </select>
 </body>
</html>

I couldn't figure it out how to embed the link into option. should another function defined for individual column or can it be done in single function?

Note: I'm not a coder.

google-apps-script google-sheets drop-down-menu javascript
1 Answers

You can use the onChange event to open the link when a user chooses an option

For this:

  • Use the syntax new Option(text, value). To pass the option and the link as the text and the value, you need to access the respective nested array elements:
select.options[i] = new Option(vA[i][0],vA[i][1]);

Sample:

<!DOCTYPE html>
<html>
  <head>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
  <script>
    $(function() {
        $('#txt1').val('');
        google.script.run
          .withSuccessHandler(updateSelect)
          .getSelectOptions();
      });
    function updateSelect(vA)
    {
      var select = document.getElementById("sel1");
      select.options.length = 0; 
      for(var i=0;i<vA.length;i++)
      {
        select.options[i] = new Option(vA[i][0],vA[i][1]);
      }
    }
    console.log("My code");
    
    function myFunction(){
      var value = document.getElementById("sel1").value;
      window.open(value, '_blank');
    }
  </script>
  </head>
  <body>
   <select id="sel1" style="width:125px;height:35px;margin:10px 0 10px 0;" onchange="myFunction()"> 
   </select>
 </body>
</html>
user_115997890
Related