Google app scripts: email a spreadsheet as excel

Question

How do you make an app script which attaches a spreadsheet as an excel file and emails it to a certain email address?

There are some older posts on Stackoverflow on how to do this however they seem to be outdated now and do not seem to work.

Thank you.

Answer 1

Here's an up-to-date and working version. One prerequisite for this Google Apps script to work is that the Drive API v2 Advanced Google Service must be enabled. Enable it in your Google Apps script via Resources -> Advanced Google Services... -> Drive API v2 -> on. Then, that window will tell you that you must also enabled this service in the Google Developers Console. Follow the link and enable the service there too! When you're 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 to set the url to download from. Doing it through the file.exportLinks collection is not working anymore. Thanks to @tera for pointing that out in his answer.

Answer 2

It looks like @Christiaan Westerbeek's answer is spot on but its been a year now since his post and I think there needs to be a bit of a modification in the script he has given above.

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

There is something wrong with this line of code, maybe that exportLinks has now depreciated. When I executed his code it gave an error to the following effect:

TypeError: Cannot 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 the "download as xlsx" URL that can be used to directly download the spreadsheet as an xlsx file that 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>>> where <<>> should be replaced by the ID of your file.

Check here to easily understand how to extract the ID from the URL of your google sheet.