Google script applications for email.

Question

I would like to write a script application to email the Excel version of my Google spreadsheet. I know that I can save the table as an Excel file. I am not sure if I can use the script to email the excel version as an attachment. How can I do that?

Answer 1

After replying to another last post ( Thomas van Latum ), I tried the proposed api document and got an interesting result ... here is the test code that I used and which works well, except that the file is in xlsx format and not in xls, but these days this is not necessarily a problem:

function googleOAuth_(name,scope) { var oAuthConfig = UrlFetchApp.addOAuthService(name); oAuthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope="+scope); oAuthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken"); oAuthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken"); oAuthConfig.setConsumerKey('anonymous'); oAuthConfig.setConsumerSecret('anonymous'); return {oAuthServiceName:name, oAuthUseToken:"always"}; } function test(){ var id = 'spreadsheet_ID' var url = 'https://docs.google.com/feeds/'; var doc = UrlFetchApp.fetch(url+'download/spreadsheets/Export?key='+id+'&exportFormat=xls', googleOAuth_('docs',url)).getBlob() DocsList.createFile(doc).rename('newfile.xls') } 

Note: if you did not rename it, its default name is Export.xlsx , it might be more useful to get its identifier so that you can use it later ... so the last line could be like this:

 var xlsfileID = DocsList.createFile(doc).getId() 

---

EDIT: to start the authorization process, try such a small function, run it from the script editor

 function autorise(){ // function to call to authorize googleOauth var id=SpreadsheetApp.getActiveSpreadsheet().getId(); var url = 'https://docs.google.com/feeds/'; var doc = UrlFetchApp.fetch(url+'download/documents/Export?exportFormat=html&format=html&id='+id, googleOAuth_('docs',url)).getContentText(); } 

Answer 2

The one that worked for me:

 var AUTH_TOKEN = "Enter your OAuth_Token"; ssID = SpreadsheetApp.getActiveSpreadsheet().getId(); var url = "http://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="+ ssID + "&exportFormat=xls"; //Add &gid=x at the end of above url if you only want a particular sheet var auth = "AuthSub token=\"" + AUTH_TOKEN + "\""; var res = UrlFetchApp.fetch(url, {headers: {Authorization: auth}}); var attachments = [{fileName:"<Filename>.xls", content: res.getContent(),mimeType:"application/vnd.ms-excel"}]; MailApp.sendEmail("<recipient email id>", "<email subject>", "<email body>", {attachments: attachments}); 

Get the OAuth documentation and the token from here https://developers.google.com/accounts/docs/OAuth2

Answer 3

The latest working version is given below. Based on this example, that is similar to the previous one, but uses a Google service account, which does not require a person to follow the link to receive a token. You must install the Oath library from Google, the instructions are pretty clear.

 var PRIVATE_KEY = 'xxx' var CLIENT_EMAIL = 'xxx'; var USER_EMAIL=Session.getActiveUser().getEmail() function getOathService() { return OAuth2.createService('GoogleDrive:' + USER_EMAIL) // Set the endpoint URL. .setTokenUrl('https://accounts.google.com/o/oauth2/token') // Set the private key and issuer. .setPrivateKey(PRIVATE_KEY) .setIssuer(CLIENT_EMAIL) // Set the name of the user to impersonate. This will only work for // Google Apps for Work/EDU accounts whose admin has setup domain-wide // delegation: // https://developers.google.com/identity/protocols/OAuth2ServiceAccount#delegatingauthority .setSubject(USER_EMAIL) // Set the property store where authorized tokens should be persisted. .setPropertyStore(PropertiesService.getScriptProperties()) // Set the scope. This must match one of the scopes configured during the // setup of domain-wide delegation. .setScope('https://www.googleapis.com/auth/drive'); } function sendEmail() { var oathService = getOathService(); var ssID = SpreadsheetApp.getActiveSpreadsheet().getId(); var file = Drive.Files.get(ssID ); var url = file.exportLinks[MimeType.MICROSOFT_EXCEL]; var file = UrlFetchApp.fetch(url, { headers: { Authorization: 'Bearer ' + oathService.getAccessToken() } }); var attachments = [{ fileName:'xxx.xls',//TODO DATE content: file.getBlob().getBytes(), mimeType:"application//xls", headers: { Authorization: 'Bearer ' + oathService.getAccessToken() } }]; MailApp.sendEmail(' [e-mail] ', 'xxx', 'Hi,\n\nPlease see the last data in attachment',{attachments:attachments}); } 

Answer 4

How I spent about four hours playing Rumpelstiltskin because none of the typically very old code snippets for old versions of tables and the old OAUTH can you find when googling "google docs script sends an excel application" or similar (i.e. you want to take an existing spreadsheet, convert it to Excel format and send it as an email attachment), indeed, I found a solution.

To create the actual content of the attachments, neither the expected res.getContent () nor the res.getBlob () nor the res.getBytes were executed. These hints are misleading!

The only thing that works for me is response.getBlob (). getContent ()!

All code:

 function sendCurrentDocAsEmail() { var driveService = getDriveService(); var ssID = SpreadsheetApp.getActiveSpreadsheet().getId(); var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName(); var email = " [e-mail] " var subject = "Here be Subjects"; var body = "Don't even think about learning how to code. It wasted time."; var file = Drive.Files.get(ssID ); var url = file.exportLinks[MimeType.MICROSOFT_EXCEL]; var response = UrlFetchApp.fetch(url, { headers: { Authorization: 'Bearer ' + driveService.getAccessToken() } }); var attachments = [{ fileName:sheetName+".xlsx", content: response.getBlob().getBytes(), // this single line has cost me hours! mimeType:"application//xls", headers: { Authorization: 'Bearer ' + driveService.getAccessToken() } }]; MailApp.sendEmail(email,subject ,body, {attachments:attachments}); } 

Where getDriveService () is a function of Google's “OAuth2 for Script Applications” readme at https://github.com/googlesamples/apps-script-oauth2

Answer 5

Use the following code snippet after changing it to suit your needs.

 var file = DocsList.getFileById(FILE_ID); var attachment = file.getAs('application/vnd.ms-excel'); MailApp.sendEmail(" [e-mail] ", "Subject", " Body" , {"fileName": "Your_file_name" , "mimeType" : "application/vnd.ms-excel" , "content":attachment.getBytes() } ); 

Please note that this code has not been tested, so feel free to fix an error or two that may appear