How to download single sheet as PDF to my local device directly (not export to Google Drive)?

Question

I've come across a few scripts to use with Google Sheets that will let me export a single sheet to a file on my Google Drive. However, instead of sending it there, I want it to download to my computer directly.

I'm looking to replace this...

DriveApp.createFile()

with something else that will send the file, with a customized name, as a file to download in my browser.

Answer 1

• You want to download a specific sheet in the active Spreadsheet as a PDF file.

If my understanding is correct, how about this sample script? This sample script supposes the following points.

1. Script is the container-bound script of Spreadsheet.
2. Sheet you want to download is in the active Spreadsheet.
3. When the script is run, a dialog is opened. When the button is clicked, the active sheet is downloaded as a PDF file to the local PC.
   • In this script, the PDF file is downloaded by Javascript. So I used a dialog to execute Javascript.

Sample script:

When you use this script, please copy and paste this script to the script editor. Script is the container-bound script of Spreadsheet. When you run downloadSheetAsPDF(), a dialog is opened on the Spreadsheet. Please check it. When you click the button, the PDF file is downloaded.

function downloadSheetAsPDF() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetId = ss.getActiveSheet().getSheetId();
  var url = "https://docs.google.com/a/mydomain.org/spreadsheets/d/" + ss.getId() + "/export?exportFormat=pdf&gid=" + sheetId + "&access_token=" + ScriptApp.getOAuthToken();
  var str = '<input type="button" value="Download" onClick="location.href=\'' + url + '\'" >';
  var html = HtmlService.createHtmlOutput(str);
  SpreadsheetApp.getUi().showModalDialog(html, "sample");
}

Note:

• This is a simple sample script. So please modify this for your situation.
• If you want to download the specific sheet name, please modify to var sheetId = ss.getSheetByName("sheetName").getSheetId();.

References:

• Class HtmlService
• Class Ui

If this was not the result you want, I apologize.

Edit:

• You want to use the specific filename of PDF file, when the file is downloaded.
• You want to automatically download when the script is run.

If my understanding is correct, how about this sample script? The flow of this sample script is as follows. I think that there might be several answers for your situation. So please think of this as just one of several answers.

1. PDF file is created as a temporal file.
2. Create the URL for downloading.
3. Open a dialog box and the PDF file is automatically downloaded by running Javascript.
4. Remove the temporary file.
5. Close the dialog box.

Sample script:

function downloadSheetAsPDF2() {
  var filename = "sampleFilename.pdf"; // Please set the filename here.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetId = ss.getActiveSheet().getSheetId();

  // Creat PDF file as a temporary file and create URL for downloading.
  var url = "https://docs.google.com/a/mydomain.org/spreadsheets/d/" + ss.getId() + "/export?exportFormat=pdf&gid=" + sheetId + "&access_token=" + ScriptApp.getOAuthToken();
  var blob = UrlFetchApp.fetch(url).getBlob().setName(filename);
  var file = DriveApp.createFile(blob);
  var dlUrl = "https://drive.google.com/uc?export=download&id=" + file.getId();

  // Open a dialog and run Javascript for downloading the file.
  var str = '<script>window.location.href="' + dlUrl + '"</script>';
  var html = HtmlService.createHtmlOutput(str);
  SpreadsheetApp.getUi().showModalDialog(html, "sample");
  file.setTrashed(true);

  // This is used for closing the dialog.
  Utilities.sleep(3000);
  var closeHtml = HtmlService.createHtmlOutput("<script>google.script.host.close()</script>");
  SpreadsheetApp.getUi().showModalDialog(closeHtml, "sample");
}

Answer 2

Alternatively, You can use the anchor tag to download to local drive with a custom name:

Flow:

• Create custom download url for pdf export from using spreadsheet id
• UrlFetchApp to fetch the pdf
• Serve pdf as Data URI using anchor tag
• Use anchor tag's download attribute to provide the custom name for the download

Snippet:

function downloadPdfToDesktop() {
  var ss = SpreadsheetApp.getActive(),
    id = ss.getId(),
    sht = ss.getActiveSheet(),
    shtId = sht.getSheetId(),
    url =
      'https://docs.google.com/spreadsheets/d/' +
      id +
      '/export' +
      '?format=pdf&gid=' +
      shtId;
  var val = 'PDFNAME';//custom pdf name here 
  val += '.pdf';
  //can't download with a different filename directly from server
  //download and remove content-disposition header and serve as a dataURI
  //Use anchor tag's download attribute to provide a custom filename
  var res = UrlFetchApp.fetch(url, {
    headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
  });
  SpreadsheetApp.getUi().showModelessDialog(
    HtmlService.createHtmlOutput(
      '<a target ="_blank" download="' +
        val +
        '" href = "data:application/pdf;base64,' +
        Utilities.base64Encode(res.getContent()) +
        '">Click here</a> to download, if download did not start automatically' +
        '<script> \
        var a = document.querySelector("a"); \
        a.addEventListener("click",()=>{setTimeout(google.script.host.close,10)}); \
        a.click(); \
        </script>'
    ).setHeight(50),
    'Downloading PDF..'
  );
}