Convert all sheets to PDF with Google Apps Script

Question

I'm trying to convert a Google spreadsheet with multiple sheets to a PDF file. The script below works, but it only creates a PDF with the last page of the spreadsheet.

function savePDFs() {
    SpreadsheetApp.flush();

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();
    var url = ss.getUrl();

    //remove the trailing 'edit' from the url
    url = url.replace(/edit$/,'');

    //additional parameters for exporting the sheet as a pdf
    var url_ext = 'export?exportFormat=pdf&format=pdf' + //export as pdf

    //below parameters are optional...
    '&size=letter' + //paper size
    '&portrait=false' + //orientation, false for landscape
    '&fitw=true' + //fit to width, false for actual size
    '&sheetnames=false&printtitle=false&pagenumbers=false' + //hide optional
    '&gridlines=false' + //false = hide gridlines
    '&fzr=false' + //do not repeat row headers (frozen rows) on each page
    '&gid='; //leave ID empty for now, this will be populated in the FOR loop

    var token = ScriptApp.getOAuthToken();

    //make an empty array to hold your fetched blobs
    var blobs = [];

    //.fetch is called for each sheet, the response is stored in var blobs[]
    for(var i = 0; i < sheets.length; i++) {
        var sheetname = sheets[i].getName();

        //if the sheet is one that you don't want to process,
        //continue' tells the for loop to skip this iteration of the loop
        if(sheetname == "Team Member Numbers")
            continue;

        //grab the blob for the sheet
        var response = UrlFetchApp.fetch(url + url_ext + sheets[i].getSheetId(), {
            headers: {
                'Authorization': 'Bearer ' +  token
            }
        });

        //convert the response to a blob and store in our array
        blobs.push(response.getBlob().setName(sheets[i].getName() + '.pdf'));
        var array_blob = response.getBlob().setName(sheets[i].getName() + '.pdf');
    }

    //from here you should be able to use and manipulate the blob to send and
    //email or create a file per usual.

    // send email
    var subject = "Enter Subject"
    var message = "See attached PDF"
    MailApp.sendEmail("email addy here", subject, message,{attachments:[array_blob]});
}

possible duplicate of Export (or print) with a google script new version of google spreadsheets to pdf file, using pdf options — Nathan Hughes
@NathanHughes - this question is about all sheets in a spreadsheet, while previous was about a single sheet. Subtle difference, but not duplicate, imo. Further, since the time of the accepted answer on other question, the OAuth story became much simpler - not that it changes the question, but people are less likely to adapt code from closed questions, so we might do a disservice by closing this one. (Regardless, I tacked the answer on both to make it easy for others to find.) — Mogsdad
@Mogsdad: thanks for the clarification, I retracted my close vote. — Nathan Hughes
NOTE: This doesn't work if the sheet is hidden. Use activate() to unhide a sheet. — Andrew Roberts

Gilbert W Gilbert W · Accepted Answer · 2016-02-24T19:54:39

I've tweaked @Mogsdad code slightly to print the entire spreadsheet as one PDF. The key is tweaking the export parameter. Basically replace

'&gid=' + sheet.getSheetId()   //the sheet's Id

with

(optSheetId ? ('&gid=' + sheet.getSheetId()) : ('&id=' + ss.getId()))  // Print either the entire Spreadsheet or the specified sheet if optSheetId is provided

So the code above minus the looping looks like:

function savePDFs( optSSId, optSheetId ) {

  // If a sheet ID was provided, open that sheet, otherwise assume script is
  // sheet-bound, and open the active spreadsheet.
  var ss = (optSSId) ? SpreadsheetApp.openById(optSSId) : SpreadsheetApp.getActiveSpreadsheet();

  // Get folder containing spreadsheet, for later export
  var parents = DriveApp.getFileById(ss.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  }
  else {
    folder = DriveApp.getRootFolder();
  }

  //additional parameters for exporting the sheet as a pdf
  var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf

      // Print either the entire Spreadsheet or the specified sheet if optSheetId is provided
      + (optSheetId ? ('&gid=' + sheet.getSheetId()) : ('&id=' + ss.getId()))

      // following parameters are optional...
      + '&size=letter'      // paper size
      + '&portrait=true'    // orientation, false for landscape
      + '&fitw=true'        // fit to width, false for actual size
      + '&sheetnames=false&printtitle=false&pagenumbers=false'  //hide optional headers and footers
      + '&gridlines=false'  // hide gridlines
      + '&fzr=false';       // do not repeat row headers (frozen rows) on each page

  var options = {
    headers: {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken()
    }
  }
  var response = UrlFetchApp.fetch("https://docs.google.com/spreadsheets/" + url_ext, options);
  var blob = response.getBlob().setName(ss.getName() + '.pdf');

  //from here you should be able to use and manipulate the blob to send and email or create a file per usual.
  //In this example, I save the pdf to drive
  folder.createFile(blob);

}

Btw, thank you -- I've been looking for a solution for this for a long time!

Convert all sheets to PDF with Google Apps Script

4 Answers