Export Single Sheet to PDF in Apps Script
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);
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()
}
}
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() || '[email protected]';
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;
};