Modifying a function to download a sheet

Question

I'm looking to modify this function so I don't actually need to click a Download Now link. I'd just like the script to download the sheet automatically. Appreciate any help. Thank you.

    function copySheet() {

      var ss = SpreadsheetApp.getActiveSpreadsheet();
    var first = ss.getSheetByName("Road MT");
      ss.rename(first.getRange(2, 14).getValue());
      // this would change the name to whatever is in Row 2, col 14

     var myValue = 
    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("N2")
    .getValue();
      var copiedSpreadSheet = 
    SpreadsheetApp.getActiveSpreadsheet().copy(myValue);

      var ssID = SpreadsheetApp.getActive().getId();
      var URL = 'https://docs.google.com/spreadsheets/d/'+ssID+'/export? 
   format=xlsx';

      // Display a modal dialog box with download link.
      var htmlOutput = HtmlService
              .createHtmlOutput('<a href="'+URL+'">Download Now</a>')
              .setSandboxMode(HtmlService.SandboxMode.IFRAME)
              .setWidth(300)
              .setHeight(40);
      SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Download XLS');

    }

Possible duplicate of [How to download single sheet as PDF (not export to Google Drive)](https://stackoverflow.com/questions/56215898/how-to-download-single-sheet-as-pdf-not-export-to-google-drive) — TheMaster, Jul 22 '19 at 21:21

score 2 · Answer 1 · answered Jul 22 '19 at 23:50

You can do that, but you need to invoke the download from doGet or doPost like this:

function doGet(fileId) {
  var URL = 'https://docs.google.com/spreadsheets/d/'+fileId+'/export?format=xlsx';  
  var fileName = 'download.xlsx';
  var blob = DriveApp.getFileById(fileId).getBlob();
  return ContentService.createTextOutput(blob).downloadAsFile(fileName);
}

Modifying a function to download a sheet

1 Answers1