Automatically send email with PDF of sheets weekly

[kevi...@worldvision.ca]

Is it possible to automatically send weekly emails to a list of recipients of PDFs of each sheet in Google Spreadsheet? I've looked around but haven't found any ways to do this, only found ways to send the actual email, but not with the sheets.

[Marc-Olivier Meunier]

I don't think this has to do with the add-on, it's a google spreadsheet issue. You should be able to do that either with some other add-ons or with script editor.

https://gist.github.com/ixhd/3660885

[Alex Wyatt]

You can do it with a script, triggered for whatever time you want. Here's what I use, you'll want to change where it looks for emails (for me it's the "Recipients" page, A2:A), as well as the name of the sheet with the data and the Mileage-related stuff on the bottom. If you want to do multiple sheets, it may be easiest to use a separate script for each sheet you want copied...until someone who knows Google Script better than I do replies. There's some function in here that specifies a particular sheet of the spreadsheet, so it might be easy to get all of them with just a few key deletions here

I got this from here, or some place like it (honestly can't remember exactly where, sorry), so that page might have more helpful info

/* Send Spreadsheet in an email as PDF. You need to set to trigger automatically on the first of each month. */

function emailSpreadsheetAsPDF() {

      

  // Get the currently active spreadsheet URL (link)

  // Or use SpreadsheetApp.openByUrl("<<SPREADSHEET URL>>");

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  

  // Sets the active sheet to Mileage

  ss.setActiveSheet(ss.getSheetByName("Mileage"));

  

  // Gather emails from the list of recipients

  var recipientsSheet = ss.getSheetByName("Recipients");

  var recipients = recipientsSheet.getRange("A2:A");

  var emails = recipients.getValues();

  

  // Email Body can  be HTML too with your logo image - see ctrlq.org/html-mail

  var body = null; 

  var nomiles = "No mileage to report this month"

  

  // Gather the Sheet ID from the name

  var ID = ss.getSheetId();

  

  // Subject of email message

  // The date time string can be formatted in your timezone using Utilities.formatDate method

  var date = Utilities.formatDate(new Date(), "CDT", "yyyy-MM-dd");

  var subject = ss.getSheetName() + " - " + date ;

  

  var url = ss.getUrl();

  url = url.replace(/edit$/,'');

  

  /* Specify PDF export parameters`

  // From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579

    exportFormat = pdf / csv / xls / xlsx

    gridlines = true / false

    printtitle = true (1) / false (0)

    size = legal / letter/ A4

    fzr (repeat frozen rows) = true / false

    portrait = true (1) / false (0)

    fitw (fit to page width) = true (1) / false (0)

    add gid if to export a particular sheet - 0, 1, 2,..

  */

 

  var url_ext = 'export?exportFormat=pdf&format=pdf'   // export as pdf

                + '&size=letter'                       // paper size

                + '&portrait=true'                     // orientation, false for landscape

                + '&fitw=true'                         // fit to width, false for actual size

                + '&sheetnames=false&printtitle=false' // hide optional headers and footers

                + '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines

                + '&fzr=false'                         // do not repeat row headers (frozen rows) on each page

                + '&gid='+ID;                          // the sheet's Id

  

  var token = ScriptApp.getOAuthToken();

  Logger.log("Token: " +token);

  var sheets = ss.getSheets();

  

    // Convert individual worksheets to PDF

    var response = UrlFetchApp.fetch(url + url_ext, {

       headers: {

        'Authorization': 'Bearer ' +  token //Bug was here needed a space after Bearer 

       }

    });

  Logger.log("Response code: " +response.getResponseCode());

  

    //convert the response to a blob and store in our array

    var pdf = response.getBlob().setName(ss.getSheetName() + " - " + date + '.pdf')

   

  // Define the scope

  Logger.log("Storage Space used: " + DriveApp.getStorageUsed());

   

  // If allowed to send emails, and if there is mileage data to report, send the email with the PDF attachment

  if (MailApp.getRemainingDailyQuota() > 0 && ss.getSheetByName("Mileage").getRange("E3").getValue() > 0) {

    GmailApp.sendEmail(emails, subject, body, {attachments:[pdf]});  

    }

  else {

    GmailApp.sendEmail(emails, subject, nomiles);

    }

}