Google Script to email PDF from specific google sheet sends all sheets on some sheets, not others

Question

I have a Google spreadsheet with multiple tabs. I have a button on each sheet that calls the functions to create and email a pdf of the active sheet. When the sheets (tabs) are in either the first or second position, the pdf that generates is all the sheets instead of just the active sheet. How can I adjust so it only sends the active sheet each time? It appears to me that the issue is with using the Index number and the getSheets() function. Here is the code:

function sendSheetToPdf(){ // this is the function to call
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet(); // it will send the active sheet.
  // if you change the number, change it also in the parameters below
  var shName = sh.getName()
  var shNum = (ss.getActiveSheet().getIndex() - 1)
  sendSpreadsheetToPdf(shNum, sh.getRange('I5').getValue(), sh.getRange('F12').getValue(),sh.getRange('I3').getValue(), sh.getRange('B6').getValue());
}
function sendSpreadsheetToPdf(sheetNumber, pdfName, email,subject, htmlbody) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = spreadsheet.getId()  
  var sheetId = sheetNumber ? spreadsheet.getSheets()[sheetNumber].getSheetId() : null;  
  var url_base = spreadsheet.getUrl().replace(/edit$/,'');

  var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf

      + (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId)) 
      // following parameters are optional...
      + '&size=A4'      // paper size
      + '&printtitle=false'
      + '&scale=4'
      + '&portrait=false'    // orientation, false for landscape
      + '&sheetnames=false'
      + '&pagenumbers=false'  //hide optional headers and footers
      + '&gridlines=false'  // hide gridlines
      + '&fzr=false';       // do not repeat row headers (frozen rows) on each page

  var options = {
    headers: {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
    }
  }

  var response = UrlFetchApp.fetch(url_base + url_ext, options);
  var blob = response.getBlob().setName(pdfName + '.pdf');
  if (email) {
    var mailOptions = {
      attachments:blob, htmlBody:htmlbody
    }
MailApp.sendEmail(
      email, 
      subject+" (" + pdfName +")", 
      htmlbody, 
      mailOptions);

MailApp.sendEmail(
      Session.getActiveUser().getEmail(), 
      "FRWD "+subject+" (" + pdfName +")", 
      htmlbody, 
      mailOptions);
  }
}

Tanaike Tanaike · Accepted Answer · 2018-10-25T00:03:48

Reason of issue:

The reason of your issue is the following 2 lines.

var sheetId = sheetNumber ? spreadsheet.getSheets()[sheetNumber].getSheetId() : null;
+ (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId))

By above 2 lines, the following issues occur.

When sheetNumber is 0, sheetId becomes null.
When sheetId is 0 and null, '&id=' + spreadsheetId is used.
When there is the sheet with gid=0 at the index 1 (2nd page), when the active sheet is the index 0 (1st page) or index 1 (2nd page), '&id=' + spreadsheetId is used.

In the ternary operator and if, 0 and null is judged as false. By above issues, a PDF file with all sheets is returned when the active sheet is 1st and 2nd page.

Modification points:

I think that ss.getActiveSheet().getIndex() always has the number of index.

So how about these modifications?

From:

var sheetId = sheetNumber ? spreadsheet.getSheets()[sheetNumber].getSheetId() : null;

and

+ (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId))

To:

var sheetId = spreadsheet.getSheets()[sheetNumber].getSheetId();

and

+ sheetId

Note:

If you want to check the variable, how about modifying as follows?

To:

var sheetId = !isNaN(sheetNumber) && sheetNumber >= 0 && sheetNumber < spreadsheet.getSheets().length ? spreadsheet.getSheets()[sheetNumber].getSheetId() : null;

and

+ (sheetId != null ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId))

If this was not what you want, I'm sorry.