Google App Script - Export Active Sheet Without Sheet Name-CodePudding

I am using the script below to export a sheet as Excel file but the output file name is always Document Name Sheet Name ("Exported - Report.xlsx"). How can I modify this to only use the sheet name as file name of the exported file ("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");
}

CodePudding user response：

In your situation, unfortunately, using the URL of ShtURL.toString().replace("/edit", "/export?format=xlsx&gid=" ShtGID), the filename cannot be directly changed. So, in this case, how about the following modification?

Modified script:

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
}

In this modification, the converted XLSX is created as a temporal file. Here, the filename is changed. And, the file is downloaded using the URL of the created file. The temporal file is removed.

Note:

From your script, I thought that you might have wanted to use the sheet name of the 1st tab. But, if you want to give the specific filename, please modify SheetApp.getSheets()[0].getSheetName() of blob.setName(SheetApp.getSheets()[0].getSheetName()).