Export Single sheet and save as sheet name

Question

I have a workbook with multiple sheets in it. I have set it up so that on the main page you can click export next to the sheet name and it exports that sheet to xls. Is there a way to export the sheet and save it as that sheet name rather than the workbook name?

function getSheetUrl() {
  var SS = SpreadsheetApp.getActiveSpreadsheet();
  var ss = SS.getActiveSheet();
  var url = 'https://docs.google.com/a/d/spreadsheets/d/Sheet ID/';
  url += 'export?format=xlsx&gid=';
  url += ss.getSheetId(); 
  return url;
}

In A49 I have:

https://docs.google.com/a/d.net/spreadsheets/d/SHEET ID/export?format=xlsx&gid=

And then in D:D I have the sheet ID's

And this is what generates the URL

=HYPERLINK(CONCATENATE($A$49,D32),"Export")

Answer 1

I don't thing that can be done by using the URL since you have to capture the new document and change the name.

What I was able to do is to create a script function that will create a new file with the name of the sheet but it will store it in your Drive with an specified Folder ID, then it will take the id for the new file and it will create a url that will be added to the A1:A1 cell for you to download the new document with the sheet name. After 1 minute the file will be sent to trash.

function getSheetUrl() {
        var SS = SpreadsheetApp.getActiveSpreadsheet();
        var ss = SS.getActiveSheet();
        var url = 'https://docs.google.com/spreadsheets/d/SpreadsheetId/';
        url += 'export?format=xlsx&gid=';
        url += ss.getSheetId();
        var params = {
            method: "GET",
            headers: {
                "authorization": "Bearer " + ScriptApp.getOAuthToken()
            }
        };
        var response = UrlFetchApp.fetch(url, params).getBlob().setName(ss.getSheetName()).copyBlob();
        var dir = DriveApp.getFolderById("FolderId");
        var file = dir.createFile(response);
        var id = file.getId();
        ss.getRange("A1:A1").setValue("https://docs.google.com/uc?id=" + id + "&export=download");

  Utilities.sleep(60000);

        DriveApp.getFileById(id).setTrashed(true);
}

I am not a master in Apps Script but is the way I was able to make it work. I hope this helps, if you don't want the file to be sent to trash just delete the 2 lines from utilities and the setTrashed one. You can keep the documents to delete them later also from Trash to avoid using your Drive Space, I was not able to find a method that deletes the file permanently just the removeFile() but this just remove the file from the Drive and apparently it will still use Drive space.

Greetings.