Google Sheets – Fix ‘TypeError: can’t call method getSheetName of undefined’ in Google Apps Script

google sheetsgoogle-apps-script

This code fails with:

TypeError: can't call method "getSheetName" of undefined. (line 7)

Code:

function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();
    var sheetnames = [];

    for(var i=0; i < sheets.length+1; i++)
        sheetnames.push(sheets[i].getSheetName());

    return ss.toast(sheetnames, "Greetings!", 300);
}

when this works:

function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();
    var sheetnames = [];

    for(var i=0; i < sheets.length+1; i++)
        sheetnames.push(sheets[0].getSheetName());

    //Returns: Nimikirjasto,Nimikirjasto,Nimikirjasto,Nimikirjasto,Nimikirjasto
    // Text is too long to fit to the popup.
    return ss.toast(sheetnames, "Greetings!", 300);
}

Best Answer

Use the following code.

Code

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shs = ss.getSheets(), num = shs.length;
  var sheetnames = [];

  for(var i=0; i<num; i++) {
    sheetnames.push(shs[i].getSheetName());
  }

  return ss.toast(sheetnames, "Greetings!", 300);
}

Explained

The sheets are zero based (as arrays are). So, adding an extra sheet in the iteration will result in an error (because of an unknown sheet index).

Remark

Your second code will always work, because the first entry (zero) will always be found. Try this, if you want to stack the sheet names:

sheetnames.push(shs[i].getSheetName() + '\n');

Reference

Related Topic