How to get the list of all sheet in a dropdown list in a sidebar

Question

I'm working in an sidebar menu in an google sheet, to set some variables that i need in my script.

My script is already working. It is easy, on form submit, it will create an event in my calendar.

However, in order to simplify the installation of my script, I would like to show a dropdown list in my sidebar with the names of all the sheet of my worksheet.

I see various post, that show how to do a dropdown list like this :

But this is not my problem, my problem is how to get the list of the sheet and put it in a dropdown list.

Thanks for your help,

function onOpen() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var entries = [{name: "Sync Calendar Now", functionName: "syncActive"}];

  entries.push(null); // adding line separator
  entries.push({name: "Settings", functionName: "paramID"});


  spreadsheet.addMenu(sheetName, entries);

};


function paramID() {
  //TODO : Mettre ID calendrier ici :)

  var html = HtmlService.createHtmlOutputFromFile('SideBar')
      .setTitle('My custom sidebar')
      .setWidth(300);
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .showSidebar(html);
}

There is how i get the list names of the sheets

function getListNames(){

  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i=0 ; i<sheets.length ; i++) out.push( sheets[i].getName() )
  Logger.log(out);
  return out  
}

For exemple, I have in my worksheet : Sheet 1 , Sheet 2 , Sheet 3

When my sidebar open, i want to display a dropdown list with : Sheet 1, Sheet 2 and Sheet 3

Edit : Next I want to return the value selected.

Do you want to just show a list with your sheets or do you want to do something with them onSelect? — ziganotschka, Aug 29 '19 at 13:09
Sorry for avoid this. Yes i want to return the value selected ! :) — Jordan Matas, Aug 29 '19 at 13:11

score 2 · Accepted Answer · answered Aug 29 '19 at 14:09

You can use scriptlets

This allows you to use Apps Script functions within the html code:

  <body>
    <select name="Available sheets" onchange="myJsFunction()")>
    <? var sheets=spreadsheet.getSheets(); ?>
    <? for(var i=0;i<sheets.length;i++) { ?>
      <option value=<?=sheets[i].getName()?>> <?= sheets[i].getName()?></option>
    <? } ?>
    </select>
  <script>
   function myJsFunction(){
     var name=document.getElementsByName("Available sheets")[0].value;
     google.script.run.goToSheet(name);
    };
  </script>
  </body>

google.script.run allows you to pass the selected sheet to an Apps Script function.

In the .gs file you then need to define what you want to do within the Apps Script function based on the selected sheet, example:

function goToSheet(selectedSheetName){
  Logger.log('The selected sheet is: '+ selectedSheetName);
  }

Also, you need to change your line var html = HtmlService.createHtmlOutputFromFile('SideBar')

to var html = HtmlService.createTemplateFromFile('SideBar').evaluate()

to allow the script to evaluate the content of the scriptlets.

Thanks a lot ! I don't know that was possible with scriptlets, this is very usefull ! — Jordan Matas, Aug 29 '19 at 14:42

How to get the list of all sheet in a dropdown list in a sidebar

1 Answers1

Linked