Generate PDF of only one sheet of my spreadsheet
I need a script that creates a PDF with only one sheet of my spreadsheet. I currently have a script that generates a PDF but does it with the entire file. I can not copy the values to another file, since the sheet I need to export is graphics with data extracted from another sheet. Could you help me? Thank you.
function myFunction() {
var archivo = SpreadsheetApp.getActive();
var hoja = archivo.getSheetByName("Graficos 2");
var id = archivo.getId();
var archivoId = DriveApp.getFileById(id);
var archivoBlob = archivoId.getBlob();
var carpetaId = archivoId.getParents();
var contenidoPDF = archivo.getAs(MimeType.PDF);
carpetaId.next().createFile(contenidoPDF);
}
Solution 1:
This question has been addressed more or less here.
In short, you can modify export parameters in Url queryString to get a specific sheet. And then convert the blob to a pdf.
Here is the code for the same, base code was obtained from here
function emailSpreadsheetAsPDF() {
// Send the PDF of the spreadsheet to this email address
var email = "Your Email Id";
// Get the currently active spreadsheet URL (link)
// Or use SpreadsheetApp.openByUrl("<<SPREADSHEET URL>>");
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("<Your Sheet name>")
// Subject of email message
var subject = "PDF generated from sheet " + sheet.getName();
// Email Body can be HTML too with your logo image - see ctrlq.org/html-mail
var body = "Install the <a href='http://www.labnol.org/email-sheet'>Email Spreadsheet add-on</a> for one-click conversion.";
// Base URL
var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());
/* Specify PDF export parameters
From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
*/
var url_ext = 'exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
+ '&size=letter' // paper size legal / letter / A4
+ '&portrait=false' // orientation, false for landscape
+ '&fitw=true&source=labnol' // fit to page width, false for actual size
+ '&sheetnames=false&printtitle=false' // hide optional headers and footers
+ '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
+ '&fzr=false' // do not repeat row headers (frozen rows) on each page
+ '&gid='; // the sheet's Id
var token = ScriptApp.getOAuthToken();
//make an empty array to hold your fetched blobs
var blobs;
// Convert your specific sheet to blob
var response = UrlFetchApp.fetch(url + url_ext + sheet.getSheetId(), {
headers: {
'Authorization': 'Bearer ' + token
}
});
//convert the response to a blob and store in our array
blobs = response.getBlob().setName(sheet.getName() + '.pdf');
// Define the scope
Logger.log("Storage Space used: " + DriveApp.getStorageUsed());
// If allowed to send emails, send the email with the PDF attachment
if (MailApp.getRemainingDailyQuota() > 0)
GmailApp.sendEmail(email, subject, body, {
htmlBody: body,
attachments:[blobs]
});
}
Edit: To combine specific sheets and convert it into and pdf.
Firstly you will have to obtain the sheet object of all the sheets you want to merge into the pdf
var sheet2 = ss.getSheetByName("<Your Sheet name>")
var sheet3 = ss.getSheetByName("<Your Sheet name>")
Then pass the sheetId of each of the sheet to the URL query delimited by %EE%B8%80
, Like so
url += url_ext + sheet.getSheetId()+ "%EE%B8%80"+sheet2.getSheetId()+ "%EE%B8%80"+sheet3.getSheetId()
var response = UrlFetchApp.fetch(url, {
headers: {
'Authorization': 'Bearer ' + token
}
});