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