Sunday, August 19, 2012

Send a Google Spreadsheet sheet to an email address daily by setting a trigger

Send a Google Spreadsheet sheet to an email address weekly or daily or hourly or minutely by setting a timer trigger.

You must have been wondering about whether "Is there a way to send a Google Spreadsheet sheet to an email address daily and that too by a script" If yes then how?

Then you would be very happy if you will have a look at the following script which will help you in achieving it.

So, here is the script:

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

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Send Email", functionName: "sendEmail"}];
  ss.addMenu("Scripts", menuEntries);
};


function sendEmail() {
  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();
  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName();
  //var email = Session.getUser().getEmail();
  var email = Session.getEffectiveUser();
  var subject = "this is my subject";
  var body = "this is my body :)";
  
  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=0&portrait=true" +"&exportFormat=xls";
    
  var result = UrlFetchApp.fetch(url , requestData);  
  var contents = result.getContent();
  MailApp.sendEmail(email,subject ,body, {attachments:[{fileName:sheetName+".xls", content:contents, mimeType:"application//xls"}]});
};

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

You have to copy paste this script in the "script editor", which you will find at the menu bar of the spreadsheet.

I have also coded the "open function" such that after you execute this script and then reopen your spreadsheet you will view "Scripts" menu at the menu bar. Have a look at the screenshot below. 



In this "Scripts" you will have the entry "Send Email", clicking on it will send you the copy of that spreadsheet.

And now to set the trigger you have to go to "Script editor" and there you have to click on the trigger icon below the "Publish" tool bar command. Have a look at the screenshot below:



Then click on "Add a new trigger", then in Run select "sendEmail" and in Events click on "Time-driven" and then select at whatever frequency you want to execute this trigger.

I hope this script would have solved your problems of manually emailing the same spreadsheet again and again.


And If you are not much familiar with scripts then check out the following link:

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,

35 comments:

  1. Absolutely brilliant - seems like this should be native functionality; thanks so much for the time to write this.

    R

    ReplyDelete
    Replies
    1. Most welcome.
      I will also try to code a script which will save the spreadsheet directly to the Google Drive instead of sending it to Gmail.

      Delete
  2. This example works very well. Is there a way to send the spreadsheet in the body of an email instead of a xls document?

    ReplyDelete
    Replies
    1. Sorry for late reply..
      You can send the link of the spreadsheet in an email...
      Hope that helps you.

      Delete
    2. did you ever find an easy way to send the spreadsheet data in the body of an email and schedule it?

      Delete
  3. I have the same question as Chris Ward. It would more practical if one could just open the email and see all the data from your sheet on a table format on the body of the email instead of having to open a file attachment. Any ideas?

    ReplyDelete
    Replies
    1. Hi Chris & Wilson,
      Sorry for late reply..
      You can send the link of the spreadsheet in an email...
      Hope that helps you.

      Delete
    2. did you ever find a way to send the data in the body of the email?

      Delete
  4. i am getting this error

    Request failed for returned code 404. Server response:

    ReplyDelete
    Replies
    1. Hi gigi,

      I don't know what's going wrong for you. If you wish then share your spreadsheet with me, let me see if I can help you on this..

      Delete
    2. Hi, gigi joseph. Perhaps, you don't have true a sheet id in url request.
      See here "&gid=0&portrait=true"
      You must change gid=0 for your sheet's gid. Or remove its all "&portrait=true"

      Delete
  5. I was able to get it to work just fine (emails myself) but how would I change the email so it sends to someone else?

    ReplyDelete
    Replies
    1. In the script there is a line:
      var email = Session.getEffectiveUser();

      change it to:
      var email = "youremail@example.com";

      Delete
  6. this is awesome. thanks so much. now to figure out how to send in "layout" instead of "portrait" . it even works in pdf

    ReplyDelete
  7. Sorry if this is late, but how can I change this to send an email only when someone submits from the form? I'm hoping for it to send only the newest filled form's info. Thank you!

    ReplyDelete
  8. HI, I tried your code above but I keep getting error when I try to Run the script:

    Request failed for returned code 302. Truncated server response
    ....
    The document has moved <A... (use muteHttpExceptions option to examine full response) (line 28, file "Code")")

    Can you please help?

    ReplyDelete
    Replies
    1. This happens to me as well just as soon as I upgraded to the "New Google Sheets" -Can anyone chime in to fix the code?

      Delete
  9. I was able to figure it out...sort of. This code needs your entire URL of the spreadsheet: var url = "https://docs.google.com/spreadsheets/d/XXXXX#gid=XXXXX". Once I did this, it worked. Even though triggers are not supported, they work too! The spreadsheet gets emailed as normal however when you try and open the attachment a pop-up states the file is corrupt; if you keep pressing ok you eventually are greeted with your spreadsheet at the bottom of some garbled text. I found that if you replace ".xls" in the last line with ".doc" here: {attachments:[{fileName:sheetName+".doc", It emails the attachment as a Word Document that renders better than .xls when the trigger emails you. Still not as pretty as in the beginning (before the new Google Sheets) but is working. This was all done by trail and error. Maybe someone that has more knowledge than I can chime in on a better method? Ideally, what I would like to have is a screenshot PDF sent but that is way over my head :-/

    ReplyDelete
  10. Hi there, it seems with the new Google sheets this code is not working any more. I suspect the URLs need to be changed in some way. Can anybody help?

    ReplyDelete
  11. use this
    var url = "https://docs.google.com/spreadsheets/d/"
    + ssID + "/export?format=xlsx&id="+ssID;

    ReplyDelete
  12. Thanks to Kishan and other members who had extended support.
    I had been surfing number of websites to sort this solution, finally got one working.

    I request kishan to update as recommended by some members who have given their valuble inputs & identified the issues.

    Thanks to all

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. This comment has been removed by the author.

    ReplyDelete
  15. Can anybody help to replace this section with an OAuth2 for Apps Script. OAuth 1.0 support was deprecated in 2012 and is scheduled to be shut down on April 20, 2015.

    https://developers.google.com/apps-script/migration/oauth-config

    Thanks for any help,
    ~Erik
    _______________________________________________________________
    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");
    _______________________________________________________________

    ReplyDelete
    Replies
    1. yes change it to this:

      function onlyToAddTheDriveScope(){
      DriveApp.getRootFolder()
      }

      function onOpen() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var menuEntries = [ {name: "Send Email", functionName: "sendEmail"}];
      ss.addMenu("Scripts", menuEntries);
      };


      function sendEmail() {

      var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();

      var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName();

      //var email = Session.getUser().getEmail();
      var email = "XXXXXX@XXXXX.com";

      var subject = "HKSE Stock Prices";

      var body = "";

      var requestData = {"method": "GET",
      "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}
      };




      var url = "https://docs.google.com/spreadsheets/d/"
      + ssID + "/export?format=xlsx&id="+ssID;



      var result = UrlFetchApp.fetch(url , requestData);

      var contents = result.getContent();

      MailApp.sendEmail(email,subject ,body, {attachments:[{fileName:sheetName+".xls", content:contents, mimeType:"application//xls"}]});

      };

      Delete
    2. Works perfectly again, thank you very much!

      Delete
    3. Hi, Need to send this more than 1 email, but in above script only one email id can be add, requesting you to pls help me on this

      Delete
    4. MailApp.sendEmail(email_ID1,subject ,body, {attachments:[{fileName:sheetName+".xls", content:contents, mimeType:"application//xls"}]});

      MailApp.sendEmail(email_ID2,subject ,body, {attachments:[{fileName:sheetName+".xls", content:contents, mimeType:"application//xls"}]});

      where ...

      var email_ID1 = someID1@some.com
      var email_ID2 = someID2@some.com

      Delete
    5. I have one master sheet with multiple sub-sheets in deferent vendor names I need to send this to particular vendor with the attachment how do i send? Can you pls help me on this

      Delete
  16. A good friend helped me set this up so that I can send something automatically each week to several people other than myself. Select "Run" then "send" to send the google sheet (or doc, etc.) to several emails. After the "send" function is selected, a box pops up in the sheet and asks who the sheet should be sent to. Further, this script sends my google sheet (or doc etc.) as a PDF.

    // START
    function onOpen() {
    var ui = SpreadsheetApp.getUi();
    ui.createMenu( "Sender-Thingy" )
    .addItem( "Send", "send" )
    .addToUi();
    };

    function send() {
    var ui = SpreadsheetApp.getUi();
    var ss = SpreadsheetApp.getActive();
    var subject = "EA Matrix Update";
    var body = "Here is the most recent EA matrix update, this update is sent automatically every Monday by Google script.";

    var response = ui.prompt("Who should we send to? (list multiple email addresses, separated by commas (,) )", ui.ButtonSet.OK_CANCEL)
    if( response.getSelectedButton() == ui.Button.OK ) {
    MailApp.sendEmail( response.getResponseText(), subject, body,
    {
    attachments: [
    {
    fileName: ss.getName()+".pdf",
    content: ss.getAs("application/pdf").getBytes(),
    mimeType: "application/pdf"
    }
    ]
    }
    );
    }
    };

    // END

    ReplyDelete
  17. How do I export all the sheets and not just the first one?

    ReplyDelete
  18. Here is an add-on which now does the job.
    Anyone can send a spreadsheet as an attachment instantly and can also schedule recurring emails. The attachments can be PDF, XLS, XLSX, CSV etc.

    https://chrome.google.com/webstore/detail/spreadsheet-mailer/nfefgbkeihioeamkeoeecjdaepfnoole

    ReplyDelete
  19. I tried to make it but is showing Error in Line 16. Please give me the solution

    ReplyDelete
  20. Please note - i've removed my e-mail ID intentionally

    ReplyDelete
  21. what changes do I need to make to the script if value of a cell reaches a certain number to send me an email?

    ReplyDelete