我正在尝试使用谷歌应用程序脚本中的以下代码从google sheets制作zip文件:
var blobs = [];
function myFunction(){
var file = DriveApp.getFileById("fileId");
var blobObject = file.getBlob();
var blobObject1 = file.getBlob().setContentType("application/vnd.google-apps.spreadsheet").setName("blob1.xlsx");
var blobObject2 = file.getBlob().setContentType('application/vnd.ms-excel').setName("blob2.xlsx");
var blobObject3 = file.getBlob().setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet").setName("blob3.xlsx");
blobs.push(blobObject)
blobs.push(blobObject1)
blobs.push(blobObject2)
blobs.push(blobObject3)
Logger.log(file.getMimeType()); // output : application/vnd.google-apps.spreadsheet
Logger.log(blobObject.getContentType()) // output : application/pdf
Logger.log(blobObject1.getContentType()) // output : application/vnd.google-apps.spreadsheet
Logger.log(blobObject2.getContentType()) // output : application/vnd.ms-excel
Logger.log(blobObject3.getContentType()) // output : application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
var timestamp = new Date().toISOString().split("T")[0];
var zip = Utilities.zip(blobs, "FolderName"+timestamp+".zip");
var folder = DriveApp.getFolderById("FolderId");
folder.createFile(zip);
}
我可以得到4个文件的压缩文件。1个文件为.xlsx格式,3个为pdf格式。
但不幸的是,所有的.xlsx文件都被损坏了。
打开.xlsx时出现以下错误:
Excel cannot open the file because the file format or file extension is not valid.
Verify that the file has not been corrupted and that the file extension matches the format of the file.
请建议我如何才能达到理想的效果。
发布于 2019-03-15 04:18:56
昨天我刚开始玩这个的时候,突然失去了网络。所以这就是我最终要做的,它对我来说效果很好。谢谢你的代码,我将享受这一点,因为我决定学习更多关于Excel编程的知识。
function myFunction(){
var ss=SpreadsheetApp.openById('ssId');
var shts=ss.getSheets();
var blobs = [];
var url = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/export?format=xlsx&access_token=" + ScriptApp.getOAuthToken();
for(var i=0;i<shts.length;i++) {
var sh=ss.getSheetByName(Utilities.formatString('Sheet%s', i + 1));
sh.showSheet();
switch (i) {
case 0:
var jA=[1,2,3];
for(var j=0;j<jA.length;j++){shts[jA[j]].hideSheet();}//hide other sheets
shts[i].showSheet();
blobs.push(ss.getBlob().getAs('application/pdf'));
break;
case 1:
var jA=[0,2,3];
for(var j=0;j<jA.length;j++){shts[jA[j]].hideSheet();}//hide other sheets
blobs.push(UrlFetchApp.fetch(url).getBlob().setName("blob1.xlsx"));
break;
case 2:
var jA=[0,1,3];
for(var j=0;j<jA.length;j++){shts[jA[j]].hideSheet();}//hide other sheets
blobs.push(UrlFetchApp.fetch(url).getBlob().setName("blob2.xlsx"));
break;
case 3:
var jA=[0,1,2];
for(var j=0;j<jA.length;j++){shts[jA[j]].hideSheet();}
blobs.push(UrlFetchApp.fetch(url).getBlob().setName("blob3.xlsx"));
break;
}
}
var timestamp = new Date().toISOString().split("T")[0];
var zip = Utilities.zip(blobs, "FolderName"+timestamp+".zip");
var folder = DriveApp.getFolderById("folderId");
folder.createFile(zip);
}
https://stackoverflow.com/questions/55145461
复制相似问题