CODEWITHSUNDEEP
htmlgoogle-apps-scriptgoogle-sheets
William Larsen Bang
William Larsen Bang

Reputation: 197

Get an array of the sheets in a spreadsheet and list them?

I am trying to create a dynamically dependent dropdown, and for this I would like to have some layers of dependencies: first you choose between sheets, then between columns, and then between rows. I have a script now running in a Templated HTML file in Google Apps Script, which looks like this:

<?
var sheet   =  SpreadsheetApp.openById("1c7IwmyBrbNq5xwzo-7EyFewCx31WpfP4EzLpkHawffI").getSheetByName("test");
var lastRow = sheet.getLastRow();  

var categoryRange = sheet.getRange("C2:C"+lastRow); 
var category    = categoryRange.getValues();
?>

Category:  <select name="category">
<? for (var i = 0; i < category.length; ++i) { ?>
<option><?!= category[i] ?></option>
<? } ?>
</select>
<br/>

What I am trying to do now is to get an array or a list of some sort of all the sheets in a given spreadsheet, and then list them in a dropdown, with the names of the sheets.

From there I will have to find a way to make the dependencies, but then the selected sheet in the dropdown would set the sheet for the next dropdown or something?

Upvotes: 0

Views: 1070

Answers (2)

William Larsen Bang
William Larsen Bang

Reputation: 197

Okay with great help from the user Kriggs the solution to getting sheets pushing them to a list of some sorts is as follows:

Page.html

<p>List of things:</p>
<ul id="things">
<li>Loading...</li>
</ul>

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
</script>

<script>
// The code in this function runs when the page is loaded.
$(function() {
google.script.run.withSuccessHandler(showThings)
  .getSheetsNames();
});

function showThings(sheetsName) {
var list = $('#things');
list.empty();
for (var i = 0; i < sheetsName.length; i++) {
list.append('<li>' + sheetsName[i] + '</li>');
}
}
</script>

<?!= include('JavaScript'); ?>

JavaScript.html

 <script>
 window.addEventListener('load', function() {
 console.log('Page is loaded');
 });
 </script>

Code.gs

 function doGet(request) {
 return HtmlService.createTemplateFromFile('Page')
  .evaluate()
  .setSandboxMode(HtmlService.SandboxMode.IFRAME);
 }

 function include(filename) {
 return HtmlService.createHtmlOutputFromFile(filename)
  .getContent();
 }


 function getSheetsNames(){
 var ss   =  SpreadsheetApp.openById("Spreadsheet ID here");
 var sheetsName = [];
 var sheets = ss.getSheets();
 for( var i = 0; i < sheets.length; i++ ){
  sheetsName .push(sheets[i].getName() )
 };
 return sheetsName;
 }

All of the above together will generate Page.html where a list, of all the sheets in your spreadsheet, will be created. This was really useful for me, hope everyone can use this. I will continue to work out how to do the dependent dropwdown :)

Upvotes: 0

Kriggs
Kriggs

Reputation: 3778

To use in HTML the best practice is to load the page, run a function that return the info you need, and from that construct the HTML, here the sever side function:

function getSheetsNames(){
   var sheetsName = [];
   var sheets = ss.getSheets();
   for( var i = 0; i < sheets.length; i++ ){
      sheetsName .push(sheets[i].getName() )
   };
   return sheetsName;
}

If need help calling the server side function from HTML read best practices:

https://developers.google.com/apps-script/guides/html/best-practices#load_data_asynchronously_not_in_templates

Upvotes: 1

Related Questions