How to select a specific google sheet and attach as a PDF for sending email using MailApp

Question

I am trying to select a specific sheet from the spreadsheet and attach it as a PDF to the email before sending it to the recipient.

Now, the code below works, but its a workaround using for loop and if-else to eliminate all other unwanted sheets in the from the report. This code also closes my active sheets and reopens

My main trouble is getting the specific sheet in the attachment so that I do not have to use a for loop

Any help is greatly appreciated! :)

function sendReport() {

    var sheetName="Report";
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();

    for (var i = 0; i < sheets.length; i++) 
    if (sheets[i].getSheetName() !== sheetName) {
      sheets[i].showSheet();
    }

  var message = {
    to: "example@gmail.com",
    subject: "ICT Online Helpdesk Report",
    body: "Hi team,\n\nPlease find the monthly report attached.\n\nThank you,\nBob",
    name: "ICT Helpdesk", 
    attachments: [ ss.getAs(MimeType.PDF).setName("Monthly Ticket report")],
  }
 
   MailApp.sendEmail(message);

   for (var i = 15; i < sheets.length; i++) {
    sheets[i].showSheet()
  }

}

Does this answer your question? [Google apps script getAs('application/pdf') layout](https://stackoverflow.com/questions/45209619/google-apps-script-getasapplication-pdf-layout) — Kos, Jun 15 '21 at 13:09

Yuri Khristich · Accepted Answer · 2021-06-15T23:14:58.493

I don't understand why do you say: "the code below works". It sends all the sheets in PDF.

If you need to send just one sheet, you have to copy the sheet in a new Spreadsheet and send the new Spreadsheet.

Probably you need something like this:

function sendReport() {

  var ss = SpreadsheetApp.getActiveSpreadsheet(); // current spreadsheet
  var sheet = ss.getSheetByName('Report'); // get the sheet 'Report'

  var new_ss = SpreadsheetApp.create('Monthly Ticket report'); // create a new spreadsheet
  sheet.copyTo(new_ss); // copy the 'Report' to the new spreadsheet
  new_ss.deleteSheet(new_ss.getSheets()[0]); // remove the empty first sheet

  var message = {
    to: "example@mail.com",
    subject: "ICT Online Helpdesk Report",
    body: "Hi team,\n\nPlease find the monthly report attached.\n\nThank you,\nBob",
    name: "ICT Helpdesk", 
    attachments: [ new_ss.getAs(MimeType.PDF)] // send the new spreadsheet as PDF
  }
  MailApp.sendEmail(message);

  DriveApp.getFileById(new_ss.getId()).setTrashed(true); // delete the new spreadsheet

}

Thanks Yuri! This code works well for me. However, I have charts and cell reference in the "Report" sheet and that is not being copied into the new sheet :( — NOOR UL KARIM, Jun 17 '21 at 01:55
This is an important details. It needs another way to handle charts and cell references. I just added another answer. It would be better if you update your question as well. — Yuri Khristich, Jun 17 '21 at 06:27

score 0 · Answer 2 · answered Jun 17 '21 at 06:25

If you have charts and cell references on the copied sheet you need another algorithm before you send it.

You have to copy the spreadsheet a whole (not just one sheet), convert all references on the sheet 'Report' into a plain text and remove the rest of sheets.

Here is the code:

function sendReport() {

  var ss = SpreadsheetApp.getActiveSpreadsheet(); // current spreadsheet
  
  var file = DriveApp.getFileById(ss.getId()).makeCopy(); // copy of current spreadsheet
  file.setName('Monthly Ticket report'); // rename the copy
  var new_ss = SpreadsheetApp.openById(file.getId()); // open the copy

  // convert all references into a plain text
  var range = new_ss.getSheetByName('Report').getDataRange();
  range.setValues(range.getDisplayValues());
  
  // get all sheets but 'Report'
  var sheets_to_remove = new_ss.getSheets().filter(x => x.getName() != 'Report');
  sheets_to_remove.forEach(x => new_ss.deleteSheet(x)); // remove them
  
  var message = {
    to: "example@gmail.com",
    subject: "ICT Online Helpdesk Report",
    body: "Hi team,\n\nPlease find the monthly report attached.\n\nThank you,\nBob",
    name: "ICT Helpdesk", 
    attachments: [ new_ss.getAs(MimeType.PDF)] // send the new spreadsheet as PDF
  }
  MailApp.sendEmail(message);

  DriveApp.getFileById(new_ss.getId()).setTrashed(true); // delete the new spreadsheet

}

Error Exception: You cannot delete a sheet with a linked form. Please unlink the form first. The first sheet named "responses" in the original spreadsheet is connected to a google form. But, I wonder It should work regardless because this code you wrote only deletes sheet from the new copy of the spreadsheet — NOOR UL KARIM, Jun 20 '21 at 03:20
I don't know. If the error happens, it's probably need to unlink the form from the copied sheet/spreadsheet. I'm sure it can be done automatically via script. But I just have never done that. You can ask the new question (how to unlink a form and a sheet...) and get a proper answer. — Yuri Khristich, Jun 20 '21 at 06:50

How to select a specific google sheet and attach as a PDF for sending email using MailApp

2 Answers2

Linked