Question

0

171

Visualizações

Google App Script - Export Active Sheet Without Sheet Name

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

about 4 years ago · Juan Pablo Isaza

1 Respostas

Responde à pergunta

0

Responde à pergunta

Encontrar trabalhos remotos

Accepted Answer · 2022-06-28T08:07:12.965Z

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()).

0

171

Google App Script - Export Active Sheet Without Sheet Name

1 Respostas

0

Modified script:

Note:

Encontrar trabalhos remotos

Andres GPT