Google-Spreadsheet Script: Send Email with attached XLSX version of Spreadsheet

Question

for backup purpose, I'd like to use the below script to convert a Google-Spreadsheet to XLSX and send it to an email address of choice. This script has worked a few weeks ago for some spreadsheets I use. From some other spreadsheets though, I receive the email with a "broken" attachement. Currently, I can not get it to work again, and I suspect a compatibility problem with the code.

Can someone please help me?

    function getGoogleSpreadsheetAsExcel(){
      try {
        var ss = SpreadsheetApp.getActive();
        var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getId() + "&exportFormat=xlsx";
        var params = {
          method      : "get",
          headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
          muteHttpExceptions: true
        };

        var blob = UrlFetchApp.fetch(url, params).getBlob();
        blob.setName(ss.getName() + ".xlsx");
        MailApp.sendEmail("amit@labnol.org", "Google Sheet to Excel", "The XLSX file is attached", {attachments: [blob]});
      } catch (f) {
      Logger.log(f.toString());
      }
    }

A sample of a "broken" attachement is attached as a printscreen. It still comes in as XLSX, but it's HTML code that says the Document doesn't exist. The link generate by the script is correct though. And when done manually(copy/paste the generated link into browser), it works just fine.

Broken attachement:

Answer 1

I've run into some problems on @roman77 code, as some lines there seem to be deprecated.

With a few modifications it is working now.

function send_report_email(spreadsheetId) {
  var spreadsheet   = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = spreadsheet.getId(); 
  var file          = DriveApp.getFileById(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 = (spreadsheet.getName()) + '.xlsx';
 var blobs   = [response.getBlob().setName(fileName)];

 var receipient = "xxxx"
 var subject = "xxxx"
 var emailbody = "first line \n\nsecond line \n\nCheers!,"

 MailApp.sendEmail(receipient, subject, emailbody, {attachments: blobs});
  }

Answer 2

I used the below script instead. Remember to enable Google Drive API 2 (Ressources\Advanced Google Services)

function myFunction(config) {

 var spreadsheet   = SpreadsheetApp.getActiveSpreadsheet();
 var spreadsheetId = spreadsheet.getId()
 var file          = Drive.Files.get(spreadsheetId);
 var url           = file.exportLinks[MimeType.MICROSOFT_EXCEL];
 var token         = ScriptApp.getOAuthToken();
 var response      = UrlFetchApp.fetch(url, {
   headers: {
     'Authorization': 'Bearer ' +  token
   }
 });

 var fileName = (spreadsheet.getName()) + '.xlsx';
 var blobs   = [response.getBlob().setName(fileName)];

 var receipient = "xxx"
 var subject = "xxx"
 var emailbody = "xxx"

 MailApp.sendEmail(receipient, subject, emailbody, {attachments: blobs});
}