Google Apps Script创建Excel文件的工作表版本

Question

我正在努力自动化一个过程。目前，我正在将 Excel (.xlsx) 文件上传到云端硬盘中的特定文件夹。然后，我手动将它们转换为Google表格文件，并将其移动到单独的文件夹中。我想自动化这个过程。我知道我必须编写脚本来遍历 2 个文件夹以比较并查看哪些文件尚未转换，然后转换在两个位置都找不到的文件。但是，我正在努力寻找实现这一目标的最佳方法。

下面的代码可能在正确的轨道上，也可能不在正确的轨道上，我对此相当陌生，真的只是尝试将其拼凑在一起。 任何人的见解将不胜感激。

function Excel2Sheets() 
{
//Logs excel folder and string of files within
var excelfolder = DriveApp.getFolderById('1JbamZxNhAyZT3OifrIstZKyFF_d257mq');
var excelfiles = excelfolder.getFiles();
// Logs sheets folder and string of files within
var sheetfolder = DriveApp.getFolderById('1y10IwMobCdpQlYwWdveHLzxEz3Xml0Qt');
var ID = sheetfolder.getId();
var sheetfiles = sheetfolder.getFiles();
var MType = MimeType.GOOGLE_SHEETS;
while (excelfiles.hasNext()) {
var excelfile = excelfiles.next();
var excelname = excelfile.getName();
while (sheetfiles.hasNext()) {
var sheetfile = sheetfiles.next();
var sheetname = sheetfile.getName();
if(sheetname == excelname) {
break;
}
if(sheetfiles.hasNext(0)) {
var blob = excelfile.getBlob();
sheetfolder.createFile(excelname, blob, MType);
break;
}
}
}
}

我也玩过这个代码。 谢谢

function fileChecker()
{
try{
//Establishes Excel Source Folder
var excelfolder = DriveApp.getFolderById('1JbamZxNhAyZT3OifrIstZKyFF_d257mq');
//Establishes Sheet Target Folder
var sheetfolder = DriveApp.getFolderById('1y10IwMobCdpQlYwWdveHLzxEz3Xml0Qt');
//Establishes Return File Type
var MType = MimeType.GOOGLE_SHEETS;
//Gets all files in excel folder
var excelfiles = excelfolder.getFiles();
//loop through excel files
while(excelfiles.hasNext()){
//Establishes specific excel file  
var excelfile = excelfiles.next();
//Checks for file with same name in sheets folder
var sheetfiles = sheetfolder.getFilesByName(excelfile.getName());
//Logical Test for file match
if(sheetfiles.hasNext()){
//Gets File Name
var excelname = excelfile.getName();
//Creates File Blob
var blob = excelfile.getBlob();  
// Creates sheet file with given name and data of excel file
sheetfolder.createFile(excelname, blob, MType);
}
} 
}
catch(err){
Logger.log(err.lineNumber + ' - ' + err);
}
}

Answer 1

你的任何一个代码，如果它们到达createFile行，都应该抛出这个错误：

无效参数：文件内容类型

因为你正在通过一个Blob，而createFile(name, content, mimetype)期望一个String.

查看 DriveApp 的参考页面，无疑会注意到返回 Blob 的File#getAs(mimetype)方法和Folder#createFile(blob)方法，并尝试如下操作：

var gsBlob = excelfile.getAs(MimeType.GOOGLE_SHEETS);
gsfolder.createFile(gsBlob).setName(excelfile.getName());

但是，这也将返回一个错误：

从application/vnd.openxmlformats-officedocument.spreadsheetml.sheet转换 不支持 application/vnd.google-apps.spreadsheet。

查看getAs方法的文档表明，除非目标 mimetype 是MimeType.PDF，否则这通常是不受支持的操作。我的猜测是因为PDF转换非常简单 - 它的实现可能使用类似"打印"的功能 - 而电子表格格式转换需要仔细处理公式，图像，图表等。

根据过去使用Google云端硬盘的经验，一般用户知道可以使用云端硬盘执行Excel自动转换的能力 ->Google表格。但是，此功能仅在上传期间可用。从一个密切相关的问题中得出的结论是，我们必须使用Drive API"高级服务"，而不是更简单的本机DriveApp。启用高级服务，然后以下代码片段即可正常工作。请注意，高级云端硬盘服务将文件夹视为具有特定 mimetype 的文件(这就是没有特定于文件夹的方法的原因)，因此对于应用程序脚本环境中的用户来说，同时使用 DriveApp 和高级服务是最容易的。

// Convert the user's stored excel files to google spreadsheets based on the specified directories.
// There are quota limits on the maximum conversions per day: consumer @gmail = 250.
function convertExcelToGoogleSheets() 
{
var user = Session.getActiveUser(); // Used for ownership testing.
var origin = DriveApp.getFolderById("origin folder id");
var dest = DriveApp.getFolderById("destination folder id");
// Index the filenames of owned Google Sheets files as object keys (which are hashed).
// This avoids needing to search and do multiple string comparisons.
// It takes around 100-200 ms per iteration to advance the iterator, check if the file
// should be cached, and insert the key-value pair. Depending on the magnitude of
// the task, this may need to be done separately, and loaded from a storage device instead.
// Note that there are quota limits on queries per second - 1000 per 100 sec:
// If the sequence is too large and the loop too fast, Utilities.sleep() usage will be needed.
var gsi = dest.getFilesByType(MimeType.GOOGLE_SHEETS), gsNames = {};
while (gsi.hasNext())
{
var file = gsi.next();
if(file.getOwner().getEmail() == user.getEmail())
gsNames[file.getName()] = true;
}
// Find and convert any unconverted .xls, .xlsx files in the given directories.
var exceltypes = [MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY];
for(var mt = 0; mt < exceltypes.length; ++mt)
{
var efi = origin.getFilesByType(exceltypes[mt]);
while (efi.hasNext())
{
var file = efi.next();
// Perform conversions only for owned files that don't have owned gs equivalents.
// If an excel file does not have gs file with the same name, gsNames[ ... ] will be undefined, and !undefined -> true
// If an excel file does have a gs file with the same name, gsNames[ ... ] will be true, and !true -> false
if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName()])
{
Drive.Files.insert(
{title: file.getName(), parents: [{"id": dest.getId()}]},
file.getBlob(),
{convert: true}
);
// Do not convert any more spreadsheets with this same name.
gsNames[file.getName()] = true;
}
}
}
}

我上面的代码强制执行了一个合理的要求，即您关心的文件是您拥有的文件。如果不是这种情况，则建议删除电子邮件检查。只是要注意在给定的一天内转换太多电子表格。

如果在 Apps 脚本中使用高级服务，查看 Google 的 API 客户端库文档以了解关联的 API 通常会有所帮助，因为没有特定的 Apps Script 等效文档。我个人认为Python等价物最容易使用。

Answer 2

当从 Excel 文件的 Gmail 附件 blob 保存到没有高级服务的工作表时，这对我有用。

function saveExcel(blob, filename){
let resources = { title: filename };
let options = { convert: true };
let file = Drive.Files.insert(resources, blob, options);
let fileId = file.getId();
return fileId;
}

如果我正在从云端硬盘文件夹中读取文件，我会使用以下方法，例如：

function readExcelFiles() {
let files = convertFolder.getFiles();
let filesFound = 0;
let options = { convert: true };
while (files.hasNext()) {
let blob = file.getBlob();
let blobType = blob.getContentType();
let isXls = ((blobType.indexOf("spreadsheet") > -1) || (blobType.indexOf("ms-excel") > -1));
if (isXls) {
filesFound += 1;
let resources = { title: "tmp" + filesFound };
let file = Drive.Files.insert(resources, blob, options);
}
}
}

我发现很重要的一件事是使用唯一的文件名。作为转换后的文件保存到云端硬盘后，它们是谷歌表格，可以使用电子表格应用程序阅读。