I have a sheets file and am trying to export one tab to pdf and email it to a recipient. First it was working, but would pdf the whole workbook vs the specified sheet. Now the attached pdf file is corrupt.
Ideally this would export just he range A1:O31 on "Shop Invoice Template' sheet, but I'm going on hour of testing and nothing seems to be working.
`function emailSpreadsheetAsPDF() {
DocumentApp.getActiveDocument();
DriveApp.getFiles();
// This is the link to my spreadsheet with the Form responses and the Invoice Template sheets
// Add the link to your spreadsheet here
const ss = SpreadsheetApp.openByUrl("SHEETFULLURL");
// We are going to get the email address from the cell and sheet below
// Change the reference of the cell or the name of the sheet if it is different
const value = ss.getSheetByName("Shop Invoice Template").getRange("P1").getValue();
const email = value.toString();
// Subject of the email message
const subject = "ServiceUp Payment Notification";
// Email Text. You can add HTML code here - see ctrlq.org/html-mail
const body = "Sent via Generate Invoice from Google Form and print/email it";
// Again, the URL to your spreadsheet but now with "/export" at the end
// Change it to the link of your spreadsheet, but leave the "/export"
const url = 'https://docs.google.com/spreadsheets/d/SHEETID/export?';
const exportOptions =
'exportFormat=pdf&format=pdf' + // export as pdf
'&size=letter' + // paper size letter / You can use A4 or legal
'&landscape=false' + // orientation portal, use false for landscape
'&fitw=true' + // fit to page width false, to get the 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
'&if=false' +
'&ic=false' +
'&r1=51' +
'&c1=0' +
'&r2=102' +
'&c2=20'+
'&gid=SHEETID'; // the sheet's Id. Change it to your sheet ID.
// You can find the sheet ID in the link bar.
// Select the sheet that you want to print and check the link,
// the gid number of the sheet is at the end of your link.
var params = { method: "GET", headers: { "authorization": "Bearer " + ScriptApp.getOAuthToken() } };
// Generate the PDF file
var response = UrlFetchApp.fetch(url + exportOptions, params).getBlob();
// Send the PDF file as an attachment
GmailApp.sendEmail(email, subject, body, {
htmlBody: body,
attachments: [{
fileName: ss.getSheetByName("Shop Invoice Template").getRange("P2").getValue() + ".pdf",
content: response.getBytes(),
mimeType: "application/pdf"
}]
});
// Save the PDF to Drive. The name of the PDF is going to be the name of the Company (cell B5)
const nameFile = ss.getSheetByName("Shop Invoice Template").getRange("P2").getValue().toString() + ".pdf";
DriveApp.createFile(response.setName(nameFile));
}
`