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:

http://igoogledrive.blogspot.in/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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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,

Kishan.

iGoogleDrive