List all the names in a spreadsheet and skip the first few

Question

I'm using the following code to list all the names of sheets in a particular sheet.

function listSheetNames() {

    var names = SpreadsheetApp.getActive().getSheets()
        .map(function(s) {
            return [s.getName()];
        })

    //Sheet index where list needs to go
    SpreadsheetApp.getActive().getSheets()[1]
        .getRange(1, 1, names.length, names[0].length).setValues(names)
}

However I want to be able to skip the first few names so they dont get listed. How can this be done.

There is another article which shows this, but the code in that doesn't auto update, or is there a way of combining the two, without using the in cell formula from the one below.

And is there a way of inserting a link to the sheet when it lists.

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 = 3 ; i < sheets.length+1 ; i++ ) out[i-2] = [sheets[i-1].getName()];
  return out
}
catch( err ) {
  return "#ERROR!" 
}
}

UPDATE

So I managed to club the two together as follows

function listSheetNames() {

    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    var sheets = SpreadsheetApp.getActive().getSheets()
       .map(function(s) {
       return [s.getName()];
         }) 

    var out = new Array( ss.length+1 ) ;

  //out[0] = [ "Name" , "gid" ];
  for (var i = 3 ; i < ss.length+1 ; i++ ) out[i-2] = [ss[i-1].getName()];     

  SpreadsheetApp.getActive().getSheets()[1]
  .getRange(1, 1, sheets.length, sheets[0].length).setValues([out])

}

Now I get an error, If I make .setValues([out])

incorrect range height, was 1 but should be 22 (line 46)

If I leave is as .setValues(out)

Then I get the error

Cannot convert Array to Object[][]. (line 46)

Check your range selection. I think you need to use `.getRange(1, 1, out.length, out[0].length)` since you need to select a range that is as big as the `out` array. — Casper, Apr 15 '17 at 10:26
Tried that I get the error "TypeError: Cannot read property "length" from undefined. (line 46)" — user2240778, Apr 15 '17 at 10:41
Oh sorry, that was assuming `[0]` would have a value assigned and that's not the case. Try this instead: `.getRange(1, 1, out.length, out[1].length)` (since `out[1]` should contain a value). You could also just use this (since your array always has 1 column): `.getRange(1, 1, out.length, 1)` — Casper, Apr 15 '17 at 10:56
Tried both, get the error "Incorrect range height, was 1 but should be 23 (line 46)" — user2240778, Apr 15 '17 at 11:46

score 1 · Accepted Answer · answered Apr 15 '17 at 14:11

1

This should do the trick. I've added a link to the sheet in another column. It puts the output in a tab called 'Output tab'.

function listSheetNames() {
  var sheets = SpreadsheetApp.getActive().getSheets()
       .map(function(s) {
       return s.getName();
         }) 
  var array = []
  for (var i = 3; i < sheets.length; i++) {
    array.push([sheets[i], '=hyperlink("'+SpreadsheetApp.getActive().getUrl()+'#gid='+SpreadsheetApp.getActive().getSheetByName(sheets[i]).getSheetId()+'"; "Linkname")']);
  }
  SpreadsheetApp.getActive().getSheetByName('Output tab').getRange(1,1,array.length,array[0].length).setValues(array)
}

answered Apr 15 '17 at 14:11

Casper

1,435
10
22

Anyway to link the listed item instead of a separate column with a linkname? – user2240778 Apr 15 '17 at 20:10
Figured it out for (var i = 2; i < sheets.length; i++) { array.push([ '=hyperlink("'+SpreadsheetApp.getActive().getUrl()+'#gid='+SpreadsheetApp.getActive().getSheetByName(sheets[i]).getSheetId()+'"; "'+sheets[i]+'")']); Thats a ton Casper, appreciate it! – user2240778 Apr 16 '17 at 08:11

score 0 · Answer 2 · answered Apr 24 '17 at 12:22

I believe it should be possible to do everything in the first loop (map)

function listSheetNames() {
var ss = SpreadsheetApp.getActive();
var array = ss.getSheets()
    .map(function (s) {
        return [s.getName(), '=hyperlink("' + ss.getUrl() + '#gid=' + ss.getSheetByName(s.getName())
            .getSheetId() + '"; "Linkname")'];
    }).slice(2) //first two elements removed
ss.getSheetByName('Output tab')
    .getRange(1, 1, array.length, array[0].length)
    .setValues(array)
}

List all the names in a spreadsheet and skip the first few

2 Answers2