Google Application Scripts: Excel spreadsheet email

Question

How do you create a script application that attaches a spreadsheet as an excel file and sends it by email to a specific email address?

There are some old posts on Stackoverflow on how to do this, however they seem to be outdated and don't seem to work.

Thanks.

Answer 1

It seems that @Christiaan Westerbeek's answer is a slick, but a year has passed since its publication, and I think that in the script that he gave above, there should be a slight modification.

var url = file.exportLinks[MimeType.MICROSOFT_EXCEL]; 

Something is wrong with this line of code, it is possible that exportLinks now depreciating. When I executed his code, he gave an error for the following effect:

TypeError: Unable to read property " application/vnd.openxmlformats-officedocument.spreadsheetml.sheet " from undefined.

The workaround is as follows:

The URL in the above line of code is basically a “download as xlsx” URL, which you can use to directly download the spreadsheet as an xlsx file, which you get from File> Download as > Microsoft Excel (.xlsx)

This is the format:

https://docs.google.com/spreadsheets/d/<<<ID>>>/export?format=xlsx&id=<<<ID>>> << < https://docs.google.com/spreadsheets/d/<<<ID>>>/export?format=xlsx&id=<<<ID>>> << < https://docs.google.com/spreadsheets/d/<<<ID>>>/export?format=xlsx&id=<<<ID>>> where https://docs.google.com/spreadsheets/d/<<<ID>>>/export?format=xlsx&id=<<<ID>>> → should be replaced with the identifier of your file.

Check here to easily understand how to extract the identifier from the URL of your Google page.

Answer 2

Here is an updated and working version. One of the prerequisites for this Google Apps script to work is that Drive API v2 Advanced Google Service must be enabled. Include it in your Google Apps script through Resources → Advanced Google Services ... → API Driver v2 → on. This window will then tell you that you should also enable this service in the Google Developers Console. Follow the link and enable the service there too! When you are done, just use this script.

 /** * Thanks to a few answers that helped me build this script * Explaining the Advanced Drive Service must be enabled: http://stackoverflow.com/a/27281729/1385429 * Explaining how to convert to a blob: http://ctrlq.org/code/20009-convert-google-documents * Explaining how to convert to zip and to send the email: http://ctrlq.org/code/19869-email-google-spreadsheets-pdf * New way to set the url to download from by @tera */ function emailAsExcel(config) { if (!config || !config.to || !config.subject || !config.body) { throw new Error('Configure "to", "subject" and "body" in an object as the first parameter'); } var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var spreadsheetId = spreadsheet.getId() var file = Drive.Files.get(spreadsheetId); var url = 'https://docs.google.com/spreadsheets/d/'+spreadsheetId+'/export?format=xlsx'; var token = ScriptApp.getOAuthToken(); var response = UrlFetchApp.fetch(url, { headers: { 'Authorization': 'Bearer ' + token } }); var fileName = (config.fileName || spreadsheet.getName()) + '.xlsx'; var blobs = [response.getBlob().setName(fileName)]; if (config.zip) { blobs = [Utilities.zip(blobs).setName(fileName + '.zip')]; } GmailApp.sendEmail( config.to, config.subject, config.body, { attachments: blobs } ); } 

Update: I updated the way I set the download URL. Doing this through the file.exportLinks collection no longer works. Thanks to @tera for pointing this out in his answer.