CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
user2240778
user2240778

Reputation: 309

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

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)

Upvotes: 0

Views: 1040

Answers (2)

JPV
JPV

Reputation: 27262

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)
}

Upvotes: 0

Casper
Casper

Reputation: 1435

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)
}

Upvotes: 1

Related Questions