How do I export to pdf then email for only one tab of my google sheets file?

Question

I'm trying to export to pdf and email one of the tabs in a Google Sheets report using apps script. I found some very useful code in another Stackoverflow post. However, the pdf export is including every tab in my workbook instead of just the one tab I'd like to send.

The code itself calls out "sending sheet 0" in lines 3 & 4 but I can't figure out how to change the syntax to send only one tab.

I've included the code from the other post below for convenience. Can you please help me figure out how to only send the 2nd tab?

function sendSheetToPdfwithA1MailAdress(){ // this is the function to call
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheets()[0]; // it will send sheet 0 wich is the first sheet in the spreadsheet.
  // if you change the number, change it also in the parameters below
  var shName = sh.getName()
  sendSpreadsheetToPdf(1, shName, ss.getSheetByName("Back End").getRange('C13').getValue(),"MacroTickets Ticketing Report ", "This is it !");
}
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
      + '&portrait=true'    // orientation, false for landscape
      + '&fitw=true'        // fit to width, false for actual size
      + '&sheetnames=true&printtitle=false&pagenumbers=true'  //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 +")", 
      "html content only", 
      mailOptions);

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

Tanaike Tanaike · Accepted Answer · 2019-12-24T05:18:29

You want to export 2nd tab in the active Spreadsheet as a PDF file.
You want to achieving this by modifying the script in your question.

If my understanding is correct, how about this modification?

Issue:

In your script, when sendSpreadsheetToPdf(0, shName, sh.getRange('A1').getValue(),"test email with the adress in cell A1 ", "This is it !"); is run, sheetId of var sheetId = sheetNumber ? spreadsheet.getSheets()[sheetNumber].getSheetId() : null; is null. By this, all sheets are exported with (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId)).

When you want to export only 2nd tab, please modify as follows.

Modified script:

sendSpreadsheetToPdf(0, shName, sh.getRange('A1').getValue(),"test email with the adress in cell A1 ", "This is it !");

sendSpreadsheetToPdf(1, shName, sh.getRange('A1').getValue(),"test email with the adress in cell A1 ", "This is it !");

Note:

For example, when you want to export only 1st tab, please modify above as follows.

sendSpreadsheetToPdf("0", shName, sh.getRange('A1').getValue(),"test email with the adress in cell A1 ", "This is it !");

If I misunderstood your question and this was not the result you want, I apologize.

How do I export to pdf then email for only one tab of my google sheets file?

1 Answers

Issue:

Modified script:

Note: