1

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 which 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);
  }
}
Jason Aller
  • 3,541
  • 28
  • 38
  • 38
econobro
  • 315
  • 3
  • 17

1 Answers1

1
  • 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:

From: 
sendSpreadsheetToPdf(0, shName, sh.getRange('A1').getValue(),"test email with the adress in cell A1 ", "This is it !");
To: 
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.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • No you're understanding correctly, and thanks for the reply. However, your modified script seems to work for every tab EXCEPT the one I need. It should be noted that my preferred tab started as the 2nd tab in the workbook but I moved it to the first tab after reading line 3 in the code. I have five tabs in the workbook and it works for each of the others. If I resequence my main sheet to align with one of the other successful exports, I get error "Invalid email: Home Team (line 37, file "Code")". Any ideas? – econobro Dec 24 '19 at 05:36
  • @econobro Thank you for replying. I apologize for the inconvenience. 1. I could understand that you want to export 2nd tab in the active Spreadsheet as a PDF file by modifying your script. Is my understanding correct? 2. I cannot understand about `It should be noted that my preferred tab started as the 2nd tab in the workbook but I moved it to the first tab after reading line 3 in the code.`. Can I ask you about it? 3. Can you provide a sample Spreadsheet and sample output you expect? By this, I would like to modify it. Of course, please remove your personal information. – Tanaike Dec 24 '19 at 05:48
  • Hi @tanaike, I was able to debug my email error. Your answer works. I had to decouple the destination email address from the export (line 8) before I could specify which tab to export. I moved the cell holding the email to C13 on another tab and modified line 6 as `sendSpreadsheetToPdf(1, shName, ss.getSheetByName("Back End").getRange('C13').getValue(),"MacroTickets Ticketing Report ", "This is it !");` Thanks again for the help!! I've updated my code in the initial question to reflect my debug. – econobro Dec 24 '19 at 06:10