Google Script - convert sheet to XLSX

Question

I have this code that search new doc in drive folder, and send files via email

var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var sheet = ss.getActiveSheet();

var email = "[e-mail]";      
var timezone = ss.getSpreadsheetTimeZone();

var today     = new Date();
var oneDayAgo = new Date(today.getTime() - 1 * 24 * 60 * 60 * 1000);  
var startTime = oneDayAgo.toISOString();

var search = '(trashed = false or trashed = false) and (modifiedDate > "' +    startTime + '")';
var folder1 = DriveApp.getFoldersByName('SaveToPDF').next();
var files1  = folder1.searchFiles(search);

var row = "", count=0;

while( files1.hasNext() ) {

var file1 = files1.next();

var fileName = file1.getName();
var fileURL  = file1.getUrl();
var lastUpdated =  Utilities.formatDate(file1.getLastUpdated(), timezone, "yyyy-MM-dd HH:mm");
var dateCreated =  Utilities.formatDate(file1.getDateCreated(), timezone, "yyyy-MM-dd HH:mm")

row += "<li>" + lastUpdated + " <a href='" + fileURL + "'>" + fileName + "</a></li>";

sheet.appendRow([dateCreated, lastUpdated, fileName, fileURL]);

count++;
}
if (row !== "") {
row = "<p>" + count + " file(s) have changed in your Google Drive in the past 24 hours. Here's the list:</p><ol>" + row + "</ol>";
row +=  "<br><small>To stop these notifications, please <a href='" + ss.getUrl() + "'>click here</a> and choose <em>Uninstall</em> from the Drive Activity menu.<br/></small>";
MailApp.sendEmail(email, "Google Drive - File Activity Report", "", {htmlBody: row, cc: "[e-mail]"} );
  }

I need to convert sheet files to XLSX format before send. Can someone help me?

Thanks

Answer 1

You can follow this tutorial on how to convert the current Google Spreadsheet to Excel XLSX format and then emails the file as an attachment to the specified user using getGoogleSpreadsheetAsExcel() method.

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("[e-mail]", "Google Sheet to Excel", "The XLSX file is attached", {attachments: [blob]});

  } catch (f) {
    Logger.log(f.toString());
  }
}

Here are some similar threads which might help:

• Google apps script to email google spreadsheet excel version
• Converting .xls to google spreadsheet in google apps script