Google脚本 - 将表转换为XLSX

Question

我有此代码可以在驱动器文件夹中搜索新文档，然后通过电子邮件发送文件，

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

var email = "[email protected]";      
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: "[email protected]"} );
  }

我需要在发送之前将表文件转换为XLSX格式。 有人可以帮我吗？

谢谢

Answer 1

您可以关注此 tutorial要使用 getGooglesPreadSheetSexcel（）方法，将XLSX格式进行Excel XLSX格式，然后将文件作为附件发送给指定用户的附件。

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

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

以下是一些类似的线程，可能会有所帮助：

• 在Google Apps脚本中