CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-apispreadsheet
NOOR UL KARIM
NOOR UL KARIM

Reputation: 3

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

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: "[email protected]",
    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()
  }

}

Upvotes: 0

Views: 207

Answers (2)

Yuri Khristich
Yuri Khristich

Reputation: 14537

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: "[email protected]",
    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

}

Upvotes: 0

Yuri Khristich
Yuri Khristich

Reputation: 14537

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: "[email protected]",
    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

}

Upvotes: 1

Related Questions