Google脚本-将工作表转换为XLSX [英] Google Script - convert sheet to XLSX

问题描述

我有这段代码可以搜索驱动器文件夹中的新文档，并通过电子邮件发送文件

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]"} );
  }

在发送之前，我需要将工作表文件转换为XLSX格式. 有人可以帮我吗?

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

谢谢

推荐答案

您可以按照以下教程，介绍如何将当前的Google Spreadsheet转换为Excel XLSX格式，然后使用getGoogleSpreadsheetAsExcel()方法将文件作为附件通过电子邮件发送给指定的用户.

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脚本通过电子邮件发送给Google电子表格excel版本
• 在Google Apps脚本中将.xls转换为google电子表格

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

这篇关于Google脚本-将工作表转换为XLSX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！