Friday, September 20, 2013

Google Spreadsheet Script to Send Email only Active Sheet as PDF Attachment

Question:

( by Brian Galloway )


I use google docs to share with all of my employees our day to day schedule but I also like to email myself the pdf of the particular day.  The only thing when I send myself the pdf, it sends me a pdf with every sheet.  I am trying to figure a way to just send myself the sheet of the particular day. Does anyone have any ideas how to accomplish this?
Cheers, Brian


Solution:

Have a look at the following code:

///////////////////////////////////////

// This script has been developed by Kishan

// For more visit: iGoogleDrive.blogspot.com

function onOpen(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  menuEntries.push({name: "Send ActiveSheet as PDF", functionName: "SendSheetAsPDF"});
  spreadsheet.addMenu("Send Email", menuEntries);
};

function SendSheetAsPDF() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssID = ss.getId(); 
  var sheet = ss.getActiveSheet();
  var email = "kishan.pionero@gmail.com";
  var subject = "SUBJECT HERE..!!";
  var body = "Body of email here..!!";
  var getSheetId = sheet.getSheetId().toString();
  var sheetName = sheet.getName();
  
  var oauthConfig = UrlFetchApp.addOAuthService("google");
  oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
  oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=https://spreadsheets.google.com/feeds/");
  oauthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
  oauthConfig.setConsumerKey("anonymous");
  oauthConfig.setConsumerSecret("anonymous");
  var requestData = { "method": "GET","oAuthServiceName": "google","oAuthUseToken": "always" };
  
  var url = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="+ssID+"&gid="+getSheetId+"&portrait=true"+"&exportFormat=pdf";
  var result = UrlFetchApp.fetch(url , requestData);
  var contents = result.getContent();
  
  MailApp.sendEmail(email,subject,body,{attachments:[{fileName:sheetName+".pdf",content:contents,mimeType:"application//pdf"}]});
};

///////////////////////////////////////

put the above code in your script editor, save it 
and then refresh your spreadsheet. You'll be able to see the custom menu "Send Email" under which you'll have sub menu "Send ActiveSheet as PDF".

Have a look at the following animated screenshot:


So now, when you click on this sub menu, you will get an email on the email Id provided with the active sheet as attachment in PDF format.

In above code change the email Id "kishan.pionero@gmail.com" to your email Id.


And If you are not much familiar with scripts then check out the following link:http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

8 comments:

  1. This is a very cool way of using spreadsheets and sending out PDF's . I would like to adjust this bit of code .
    var url = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="+ssID+"&gid="+getSheetId+"&portrait=true"+"&exportFormat=pdf";
    var result = UrlFetchApp.fetch(url , requestData);
    var contents = result.getContent();

    In a way that it will send out only a certain range from a spreadsheet, let say A1:G66 or a named range

    ReplyDelete
    Replies
    1. Hi Joris,

      Not sure whether this can be achieved directly.
      But what we can do is, write a code to create a new sheet and then copy that range in this new sheet and then send that new sheet as .pdf attachment and then delete that newly created sheet...
      all this can be done in a script!

      Delete
  2. Hi Kishan,
    Sounds good, I've a finetuned spreadsheet that I print out partially to PDF manually all the time.
    Do you think the script would keep all my formatting and logo into place.
    Maybe it is better to make a new sheet completely linked to the cells in the range where I need the PDF from and let the unchanged script run on this?

    ReplyDelete
  3. Joris,
    Did you fine tune your spreadsheet using the routines in the article?
    I need a routine to export a range from a spreadsheet as a PDF.... copying to another will not work for me!
    Any help would be gratefully received!
    regards
    Allan

    ReplyDelete
  4. Hi Allan,
    No, my script stops where I'm selecting the range that has to be printed will be selected, from there on it is handwork for me :-(...
    The only thing that will work as a script for creating a PDF from a selection is replacing fields in a doc with the cells you want and then print this . You can look for the "employee of the week award" in the script gallery, you can use a bunch of code snippets from there.
    Joris

    ReplyDelete
  5. This would be really helpful for me. I tried installing it. The menu item appears as promised but when I click on it nothing happens. Does this code still work?
    As far as I can tell I have not been upgraded to New Google Sheets yet. I am not a programmer, I'm afraid.

    ReplyDelete
  6. My problem with this is if it hasn't been run before, and I trigger it with a custom menu option, it just hangs. It has to be run from the script editor the first time or it won't work. Is there another way around that? For what I am doing that won't work.

    ReplyDelete
  7. I can't get this to run.
    Request failed for returned code 302. Truncated server response:

    ReplyDelete