Send mail with attached PDF to recipient after confirmation

Question

I have to edit a Google Spreadsheet file daily. When I'm finished, I would like to send a message to people, notifying them that I'm done. Attached to that notification mail, I want to send them one specific sheet (called Report), as a PDF.

I found this option which sends email (and is working fine):

function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;  // First row of data to process
var numRows = 2;   // Number of rows to process
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 1, numRows, 2)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
var emailAddress = row[0];  // First column
var message = row[1];       // Second column
var subject = "Sending emails from a Spreadsheet";
MailApp.sendEmail(emailAddress, subject, message);
}
}

Is there a way to add the specific sheet as a PDF?

A secondary question: How can I create some sort of button in the spreadsheet ("Send now"), to let me easily send this email, and so I don't have to open the script editor every time?

Answer 1

Menu-driven on-demand operation

Within the Google Sheets user interface, a menu item would be a natural way to set this up to be run on-demand.¹ A good resource for learning how to do this yourself is Google's Quickstart: Macros, Menus, and Custom Functions.

From that tutorial, here's the code that would add a "Send Report" menu item to your spreadsheet, which would call a sendReport_() function when selected:

/**
 * A special function that runs when the spreadsheet is open, used to add a
 * custom menu to the spreadsheet.
 */
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Send Report', functionName: 'sendReport_'}
  ];
  spreadsheet.addMenu('Custom', menuItems);
}

sendReport_() function

Let's assume we have a getPdfBlob() function, that will return a blob suitable for attaching to an email. With that taken care of, here is all sendReport_() needs to do:

// From https://stackoverflow.com/a/37149036/1677912
function sendReport_() {

  // Configuration parameters; customize as you wish
  var sheetName = "Report";
  var subject = "Email subject line";
  var recipients = "user1@example.com, user2@example.com";
  var htmlMessage = "Greetings,<br/><br/>"
                  + "Please find today's report attached as a PDF.<br/><br/>"
                  + "Cheers,<br/><br/>Paranoia";

  // Get the IDs of the spreadsheet & sheet-of-interest
  var ss = SpreadsheetApp.getActive();
  var sheetId = ss.getSheetByName(sheetName).getSheetId();

  // Retrieve the PDF blob
  var pdfBlobArray = getPdfBlobs(ss.getId(),sheetId);

  // Send the email + attachment
  MailApp.sendEmail(recipients,subject,"Report attached.", {
    htmlBody: htmlMessage,
    attachments: pdfBlobArray
  });
}

getPdfBlobs() utility function

A utility to generate a PDF of a spreadsheet appears in Convert all sheets to PDF with Google Apps Script. That can be adapted to return a blob containing the PDF of the single sheet you're after.

You must enable the Advanced Drive Service through "Resources > Advanced Drive Services...", and the developer console. (See this for more info.)

Note: There is some grotty customization of the PDF output supported by editing URL parameters that are embedded inside this function.

/**
 * Get one or all sheets in a spreadsheet as PDF file blobs.
 *
 * From: https://stackoverflow.com/a/37149036/1677912
 * Adapted from https://stackoverflow.com/a/30492812/1677912
 *
 * @param {String}  optSSId       (optional) ID of spreadsheet to export.
 *                                If not provided, script assumes it is
 *                                sheet-bound and opens the active spreadsheet.
 * @param {String}  optSheetId    (optional) ID of single sheet to export.
 *                                If not provided, all sheets will export.
 */
function getPdfBlobs( optSSId, optSheetId ) {

  // If a sheet ID was provided, open that sheet, otherwise assume script is
  // sheet-bound, and open the active spreadsheet.
  var ss = (optSSId) ? SpreadsheetApp.openById(optSSId) : SpreadsheetApp.getActiveSpreadsheet();

  // Get URL of spreadsheet, and remove the trailing 'edit'
  var url = ss.getUrl().replace(/edit$/,'');

  // Get array of all sheets in spreadsheet
  var sheets = ss.getSheets();

  // Loop through all sheets, generating PDF blobs.
  var blobArray = [];
  for (var i=0; i<sheets.length; i++) {
    var sheet = sheets[i];

    // If provided a optSheetId, only save it.
    if (optSheetId && optSheetId !== sheet.getSheetId()) continue; 

    //additional parameters for exporting the sheet as a pdf
    var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
        + '&gid=' + sheet.getSheetId()   //the sheet's Id
        // following parameters are optional...
        + '&size=letter'      // paper size
        + '&portrait=true'    // orientation, false for landscape
        + '&fitw=true'        // fit to width, false for actual size
        + '&sheetnames=false&printtitle=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 + url_ext, options);

    var blob = response.getBlob().setName(ss.getName() + ' - ' + sheet.getName() + '.pdf');

    // Add blob to our array
    blobArray.push(blob);
  }

  // Return array of PDF blobs
  return blobArray;
}

/**
 * Dummy function for API authorization only.
 * From: https://stackoverflow.com/a/37172203/1677912
 */
function forAuth_() {
  DriveApp.getFileById("Just for authorization"); // https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579#c36
}

---

¹This could be further extended to be an add-on, so that the script would not need to be attached to a specific spreadsheet.