Google应用程序脚本-导出活动工作表没有工作表名称

我使用下面的脚本将工作表导出为Excel文件，但输出文件名始终是文档名称+工作表名称(" export - Report.xlsx")。如何将其修改为仅使用工作表名称作为导出文件("Report.xlsx")的文件名?

function ExportSheet()
{
var SheetApp = SpreadsheetApp.getActive(); 
SheetApp.rename("Exported");

ShtURL = SheetApp.getUrl();
ShtID = SheetApp.getId();
ShtGID = SheetApp.getSheetId();
var url = ShtURL.toString().replace("/edit", "/export?format=xlsx&gid=" + ShtGID);

var html = HtmlService.createHtmlOutput('<html><script>'
+'window.close = function(){window.setTimeout(function(){google.script.host.close()},9)};'
+'var a = document.createElement("a"); a.href="'+url+'"; a.target="_blank";'
+'if(document.createEvent){'
+'  var event=document.createEvent("MouseEvents");'
+'  if(navigator.userAgent.toLowerCase().indexOf("firefox")>-1){window.document.body.append(a)}'                          
+'  event.initEvent("click",true,true); a.dispatchEvent(event);'
+'}else{ a.click() }'
+'close();'
+'</script>'
// Offer URL as clickable link in case above code fails.
+'<body style="word-break:break-word;font-family:sans-serif;">Failed to open automatically. <a href="'+url+'" target="_blank" onclick="window.close()">Click here to proceed</a>.</body>'
+'<script>google.script.host.setHeight(40);google.script.host.setWidth(410)</script>'
+'</html>')
.setWidth( 90 ).setHeight( 1 );
SpreadsheetApp.getUi().showModalDialog( html, "Opening ..." );
SheetApp.rename("Template");
}

不幸的是，在您的情况下，使用ShtURL.toString().replace("/edit", "/export?format=xlsx&gid=" + ShtGID)的URL，不能直接更改文件名。那么，在这种情况下，下面的修改如何?

修改脚本:

function ExportSheet() {
var SheetApp = SpreadsheetApp.getActive();
SheetApp.rename("Exported");
ShtURL = SheetApp.getUrl();
ShtID = SheetApp.getId();
ShtGID = SheetApp.getSheetId();
var url = ShtURL.toString().replace("/edit", "/export?format=xlsx&gid=" + ShtGID);
// --- I modified below script.
var blob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();
var file = DriveApp.createFile(blob.setName(SheetApp.getSheets()[0].getSheetName()));
url = "https://drive.google.com/uc?export=download&id=" + file.getId();
// ---
var html = HtmlService.createHtmlOutput('<html><script>'
+ 'window.close = function(){window.setTimeout(function(){google.script.host.close()},9)};'
+ 'var a = document.createElement("a"); a.href="' + url + '"; a.target="_blank";'
+ 'if(document.createEvent){'
+ '  var event=document.createEvent("MouseEvents");'
+ '  if(navigator.userAgent.toLowerCase().indexOf("firefox")>-1){window.document.body.append(a)}'
+ '  event.initEvent("click",true,true); a.dispatchEvent(event);'
+ '}else{ a.click() }'
+ 'close();'
+ '</script>'
// Offer URL as clickable link in case above code fails.
+ '<body style="word-break:break-word;font-family:sans-serif;">Failed to open automatically. <a href="' + url + '" target="_blank" onclick="window.close()">Click here to proceed</a>.</body>'
+ '<script>google.script.host.setHeight(40);google.script.host.setWidth(410)</script>'
+ '</html>')
.setWidth(90).setHeight(1);
SpreadsheetApp.getUi().showModalDialog(html, "Opening ...");
SheetApp.rename("Template");
file.setTrashed(true); // Added
}

在此修改中，将转换后的XLSX创建为临时文件。在这里，文件名被更改。并且，使用创建的文件的URL下载该文件。时间文件被删除

注意:

从您的脚本中，我认为您可能想要使用第一个选项卡的工作表名称。但是，如果您想给出具体的文件名，请修改blob.setName(SheetApp.getSheets()[0].getSheetName())的SheetApp.getSheets()[0].getSheetName()。

修改脚本:

注意:

相关内容

最新更新

热门标签：