Export Large Google Sheet to PDF

google sheetsgoogle-apps-script

I have a google sheet having 110,000 rows and 10 columns.

If I try to export using the normal File > Download > As PDF, it takes forever to make the Save button work (The ~4800 pages of the PDF keeps rendering and on a premature save, nothing happens) I have done this on Chrome, Firefox and Edge browser, just to see if changing browser would make a difference.

Then I tried using app-script, but the ones available on the WebApp StackExchange did not work.

I know the solution will be with an app script code. What is the best solution for me to able to export the 1 million cell sized sheet to a PDF easily.

EDIT:

As per the comments below I did execute the 8 line code in the newer app script console of Google,

function saveAsPDF() {
const folderName = `Cubing`;
const fileNamePrefix = `filename`;

DriveApp.getFoldersByName(folderName)
.next()
.createFile(SpreadsheetApp.getActiveSpreadsheet()
              .getBlob()
              .getAs(`application/pdf`)
              .setName(`${fileNamePrefix} - ${Utilities.formatDate(new 
 Date(), `GMT-8`, `yyyy-MM-dd`)}`));  
 }

But I got a message which is not related to the code.

The execution log said,

Exception: We're sorry, no servers are currently available. Please wait a bit and try again.
saveAsPDF   @ Code.gs:7

Best Answer

There is a ` pair-matching problem:

1
|
v
`${fileNamePrefix} - ${Utilities.formatDate(new 
 Date(), `GMT-8`, `yyyy-MM-dd`)}`
         ^     ^  ^          ^  ^
         |     |  |          |  |
         1     2  2          3  3

While there are multiple ways to fix your script, what about this (use the + to concatenate values and regular strings instead of template literals):

fileNamePrefix + ' - ' + Utilities.formatDate(new Date(), 'GMT-8', 'yyyy-MM-dd')

Resources

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals

Related Solutions

Google-sheets – onEdit() function to trigger script exporting sheet to PDF. Workaround to trigger from mobile sheet app

The simple onEdit trigger cannot do things like DriveApp.getFolderById because it runs without authorization.

You can create an installable trigger that will also run on every edit, but with the same privileges as the user creating it (i.e., you).

Google Sheets – Use Cell Value in PDF File Name When Exporting

I used the script below, located in https://stackoverflow.com/q/56215898/7108653, like suggested for @Tanaike, and adjusted with just a few things (lines 3, 4 and 5).

function downloadSheetAsPDF2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetId = ss.getSheetByName("RECEITA PARA IMPRESSÃO").getSheetId();
  var valor = SpreadsheetApp.getActiveSheet().getRange('M3').getValue();  
  var filename = valor;


  // 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 + "&portrait=false&scale=4&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");
}

Related Topic