Sending scheduled emails from Google Sheets using a script

Google Sheets is an amazing tool where you can automate a lot of things using Google Scripts.

We currently have over thirty files within Google Sheets that contains automated scripts running everywhere from once every five minutes to once every four hours.

It is very easy to automate these tasks and I’ll write up another post about it, but below is a very simple source code where you can get Google Sheets to send out emails at regular intervals.

It is very ideal where you have data coming into Google Sheets at regular intervals (using scripts of course) and then you could perhaps send out the data once everyday to your team.

function exportAsExcel() {
 var email = 'joe@bloggs.com';
 var subject = "This is a schedule email";
 var body = "This is the body of the email";
 var url = 'https://docs.google.com/spreadsheets/export?id=[GOOGLE SHEETS FILE ID]&amp;exportFormat=xlsx'
 var token = ScriptApp.getOAuthToken();
 var response = UrlFetchApp.fetch(url, {
 headers: {
 'Authorization': token
 }
 });
 var contents = response.getContent();
 MailApp.sendEmail(email,subject ,body, {attachments:[{fileName:"test.xlsx", content:contents, mimeType:"application//xlsx"}]});
 }

function exportAsExcel() {

var email = '[email protected]';

var subject = "This is a schedule email";

var body = "This is the body of the email";

var url = 'https://docs.google.com/spreadsheets/export?id=[GOOGLE SHEETS FILE ID]&exportFormat=xlsx'

var token = ScriptApp.getOAuthToken();

var response = UrlFetchApp.fetch(url, {

headers: {

'Authorization': token

}

});

var contents = response.getContent();

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

}

One final note: if you are having trouble receiving the file, have a check in your spam folder or with your IT team. I had to get our IT team to clear the email as it was deemed to be malicious.

the world of analytics. the easy way.

a blog by tony lu

Leave a Reply Cancel reply