Export Single Sheet to PDF in Apps Script

Question

I have many sheets in a Spreadsheet. I have 2 sheets that I would like to export every time. Sheet One is a sheet with the information. Sheet Two is the sheet I need to give to customers and it takes references from Sheet One.

Currently, I create a new temporary spreadsheet, copy Sheet One to the new spreadsheet, then copy Sheet Two to the temporary spreadsheet. After, I convert the temporary spreadsheet to a pdf. Then I delete the temporary spreadsheet and save the pdf into a folder in Google Drive.

The PDF created contains 2 pages of both sheets. I only need Sheet Two. If I only transfer Sheet Two, the sheet is left with many #REF errors since Sheet One isn't there. Is there any way to export only Sheet Two without have the #REF errors?

Here is my code below:

//create a temporary spreadsheet, copy both files onto it
var newSpreadsheet = SpreadsheetApp.create(nameOfSheet);
var d = ss.getSheetByName('Data').copyTo(newSpreadsheet); //the sheet     with all the information
d.setName('Data');

sheetToCopy.copyTo(newSpreadsheet); //the sheet that uses references from the data sheet
newSpreadsheet.deleteSheet(newSpreadsheet.getSheetByName("Sheet1")); //delete the original sheet of the new spreadsheet
var blobOfNewSpreadsheet = newSpreadsheet.getBlob(); //create pdf
folder.createFile(blobOfNewSpreadsheet); //add to folder


//delete the temporary spreadsheet2
var deleteSpreadsheet = DriveApp.getFileById(newSpreadsheet.getId());
deleteSpreadsheet.setTrashed(true);

score 11 · Accepted Answer · edited Sep 26 '18 at 00:54

Hidden sheets are not included when a spreadsheet is exported via getBlob. So you can temporarily hide any unwanted sheets prior to exporting.

function export() {    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Unwanted Sheet');
  sheet.hideSheet();
  DriveApp.createFile(ss.getBlob());
  sheet.showSheet();
}

The above only hides one sheet, which is enough in the context of your question. Here is a version that hides everything but one.

function exportSheet(sheetName) {    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() !== sheetName) {
      sheets[i].hideSheet()
    }
  }
  DriveApp.createFile(ss.getBlob());
  for (var i = 0; i < sheets.length; i++) {
    sheets[i].showSheet()
  }
}

Great solution! The one I thought of was copying the display values of the sheet I wanted instead of the references for each cell. This solution is much faster. — Kevin Bai, Jul 13 '16 at 21:18
Please note that this saves the spreadsheet in portrait orientation. — user1251007, Jun 12 '21 at 20:52

score 6 · Answer 2 · answered Nov 05 '20 at 17:47

I had the same query and now I have tried many solutions but the following code is the best one as per my knowing. This script does not create a temporary sheet and is a bit faster than the old method.

Forgive me if I am wrong but just sharing it for everyone's benefit.

function generatePdf() {
  SpreadsheetApp.flush();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSpreadsheet = SpreadsheetApp.getActive(); // Get active spreadsheet.
  var sheets = sourceSpreadsheet.getSheets(); // Get active sheet.
  var sheetName = sourceSpreadsheet.getActiveSheet().getName();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var pdfName = sheetName + ".pdf"; // Set the output filename as SheetName.
  var parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents(); // Get folder containing spreadsheet to save pdf in.
  if (parents.hasNext()) {
    var folder = parents.next();
  } else {
    folder = DriveApp.getRootFolder();
  }
  var theBlob = createblobpdf(sheetName, pdfName);
  var newFile = folder.createFile(theBlob);
  var email = Session.getActiveUser().getEmail() || 'admin@gmail.com';
  var custemail = sourceSheet.getRange('A1').getValue();
  email = email + "," + custemail;
  // Subject of email message
  const subject = `Your subject Attachement: ${sheetName}`;
  // Email Body can  be HTML too with your image
  const body = "body";
  if (MailApp.getRemainingDailyQuota() > 0)
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments: [theBlob]
    });
  // delete pdf if already exists
  var files = folder.getFilesByName(pdfName);
  while (files.hasNext()) {
    files.next().setTrashed(true);
  }
  sourceSpreadsheet.toast("Emailed to " + email, "Success");

}

function createblobpdf(sheetName, pdfName) {
  var sourceSpreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var url = 'https://docs.google.com/spreadsheets/d/' + sourceSpreadsheet.getId() + '/export?exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
    +    '&size=A4' // paper size legal / letter / A4
    +    '&portrait=true' // orientation, false for landscape
    +    '&fitw=true' // fit to page width, false for actual size
    +    '&sheetnames=true&printtitle=false' // hide optional headers and footers
    +    '&pagenum=RIGHT&gridlines=false' // hide page numbers and gridlines
    +    '&fzr=false' // do not repeat row headers (frozen rows) on each page
    +    '&horizontal_alignment=CENTER' //LEFT/CENTER/RIGHT
    +    '&vertical_alignment=TOP' //TOP/MIDDLE/BOTTOM
    +    '&gid=' + sourceSheet.getSheetId(); // the sheet's Id
  var token = ScriptApp.getOAuthToken();
  // request export url
  var response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' + token
    }
  });
  var theBlob = response.getBlob().setName(pdfName);
  return theBlob;
};

Thank you for this solution. I've been pursuing similar, but have not been able to find documentation of all the query parameters. Can you please point me to it? Thanks! — Dave in Ohio, Feb 01 '23 at 23:17

score 3 · Answer 3 · edited Jan 03 '23 at 05:07

Top solution didn't work for me. Broken references, even with the "fix" in there.

The following did though.

It's a bit more elegant because it "hides" the sheets you don't need. Contrary to the top-rated solution which duplicates a spreadsheet, deleting sheets, and then downloading it.

function exportSheet() { 
  var sheetName = "YOUR SHEET NAME HERE";
  var ss = SpreadsheetApp.openById("YOUR SPREADSHEET ID HERE");
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() !== sheetName) {
      sheets[i].hideSheet()
    }
  }
  var newFile = DriveApp.createFile(ss.getBlob());
  for (var i = 0; i < sheets.length; i++) {
    sheets[i].showSheet()
  }
  newFile.moveTo(DriveApp.getFolderById("FolderID")); 
  //I just did this to move the file to somewhere else. 
}

Export Single Sheet to PDF in Apps Script

3 Answers3

Linked

Related