CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Abhay
Abhay

Reputation: 845

Get column headers in a sidebar dropdown based on selected sheet

I'm creating a sidebar in Google Sheets where users can first select a sheet from a dropdown and then select a column from that sheet based on the column headers.

    <!-- Select a data sheet to classify -->
    Select sheet to classify: <br>
    <select id="dataSheetDropdown">
    <? var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets() ?>
    <? for(var i=0;i<sheets.length;i++) { ?>          
          <? sheet = sheets[i] ?>
          <? if(sheet.getName().indexOf("Lookup") === -1){?>
          <option> <?= sheet.getName()?> </option>
          <? } ?>
    <? } ?>
    </select>
    <button onclick="selectLookup()">Select</button>

    <br>
    <br>
    
    <!-- Select the lookup column -->
    Select column to lookup data:
    <select id="columnDropdown">
    <? var sheetName = document.getElementsById("dataSheetDropdown")[0].value ?>
    <? var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName) ?>    
    <option> <? sheet.getDataRange().getValues()[0] ?> </option>
    </select>

However, I can't figure out how to get the sheet to read the name of the sheet from the first dropdown.

Upvotes: 1

Views: 167

Answers (1)

ziganotschka
ziganotschka

Reputation: 26836

That's not quite how scriptlets work:

scriptlet code executes before the page is served, it can only run once per page

So once your page is loaded, you won't be able to dynamically evaluate a call to Apps Script by changing the dropdown options.

What you can instead is to use google.script.run to call an Apps Script function on button click.

For this

  • call an Apps Script function within the Javascript funciton selectLookup()
  • pass it the sheet name as parameter
  • retrieve the data in Code.gs
  • return the result back to clientside and evaluate inside withSuccessHandler

Sample:

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
     <!-- Select a data sheet to classify -->
    Select sheet to classify: <br>
    <select id="dataSheetDropdown">
    <? var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets() ?>
    <? for(var i=0;i<sheets.length;i++) { ?>          
          <? sheet = sheets[i] ?>
          <? if(sheet.getName().indexOf("Lookup") === -1){?>
          <option> <?= sheet.getName()?> </option>
          <? } ?>
    <? } ?>
    </select>
    <button onclick="selectLookup()">Select</button>
    <br>
    <br>   
    <!-- Select the lookup column -->
    Select column to lookup data:
    <select id="columnDropdown">  
    </select>
    <script>
    function selectLookup(){        
      var sheetName = document.getElementById("dataSheetDropdown").value;
      google.script.run.withSuccessHandler(populateOption).getData(sheetName);
    }
    function populateOption(data){
      for (var i = 0; i < data.length; i++){
        document.getElementById("columnDropdown").innerHTML +="<option>"+ data[i] + "</option>";
        }
      }
    </script>
  </body>
</html>

Code.gs

function doGet() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); 
  return HtmlService.createTemplateFromFile("index").evaluate();
}

function getData(sheetName){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  return  sheet.getDataRange().getValues()[0] ;
}

Note:

It's getElementById, not getElementsById

Upvotes: 2

Related Questions