Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

List names of sheets in Google Sheets and skip the first two

I found code to list the names of all the sheets in Google Sheets (from here):

function SheetNames() { // Usage as custom function: =SheetNames( GoogleClock() )
try {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets()
  var out = new Array( sheets.length+1 ) ;
  //out[0] = [ "Name" , "gid" ];
  for (var i = 1 ; i < sheets.length+1 ; i++ ) out[i] = [sheets[i-1].getName()];
  return out
}
catch( err ) {
  return "#ERROR!" 
}
}

My question is how can I modify the script to skip the first two sheet names and begin populating the list in the cell where the script is called?

I tried changing the var i = 1 to var i = 3 and that did skip the first two sheet names but it also created to blank cells. How do I skip the first two sheet names and not create any blank cells?

like image 318
Nathan Avatar asked May 13 '15 15:05

Nathan


People also ask

How do I change the order of sheets in Google Sheets?

On your computer, open a spreadsheet in Google Sheets. At the top, right-click the letter of the column you want to sort by. Click Sort sheet A to Z or Sort sheet Z to A.


1 Answers

You had the right idea, but your output array was placing the first item in position 3. Instead, do this:

for (var i = 3 ; i < sheets.length+1 ; i++ ) out[i-2] = [sheets[i-1].getName()];
like image 75
ScampMichael Avatar answered Sep 30 '22 13:09

ScampMichael