Attaching the snippet of my code. I have an input spreadsheet where the end-user enters in an email address. Then, the code will open my desired spreadsheet (Template1), converts it to a PDF and sends to the recipients email. I have the code working perfectly with the exception that the margins are not being set to zero based on the PDF being sent via email.
unction showmessage11(){
//Message to user to authenticate that the PDF is to be sent to the email address from InvoiceTemplate1 sheet
var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
var row = cell.getRow();
var candidate = getCandidateFromRow(row);
var ui = SpreadsheetApp.getUi();
var response = ui.alert('Are you sure you want to send the Invoice to '+candidate.email+'?', ui.ButtonSet.YES_NO);
if(response == ui.Button.YES)
{
handleApproval(row, candidate);
}
}
function getCandidateFromRow(row)
{
//Persists the Customers First Name, Last Name, Email Address, Invoice #, Personal Message to be included in email and Customer [Project] Name
var values = SpreadsheetApp.getActiveSheet().getRange(2,1,2,37).getValues();
var rec = values[0];
var candidate =
{
first_name: rec[31],
last_name : rec[32],
email: rec[33],
invoice: rec[34],
message: rec[35],
customer: rec[36]
};
candidate.name = candidate.first_name+' '+candidate.last_name;
return candidate;
}
function handleApproval(row, candidate){
//Displays the PDF sheet [Template1] and hides the input sheet [InvoiceTemplate1]
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('D13').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Template1'), true);
spreadsheet.getRange('A3').activate()
SpreadsheetApp.getActive().getSheetByName('Template1').showSheet();
SpreadsheetApp.getActive().getSheetByName('InvoiceTemplate1').hideSheet();
// Get the currently active spreadsheet URL (link)
var ss = SpreadsheetApp.openByUrl("");
// adding from Print_Template1
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sprsheetid = ss.getId();
var sheetid = ss.getSheetId();
var ssa = SpreadsheetApp.getActive();
var sheet = ssa.getSheets()[0]; //Query Sheet
var lastRow = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();
//var url_ext = 'exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
var printRange = '&c1=1' + '&r1=0' + '&c2=44' + '&r2=5'; // B2:APn
var url= ss.getUrl().replace(/edit$/, '') +
"format=pdf&"+
"fmcmd=12&"+
"size=0&"+
"fzr=true&"+
"gid="+322778635+"&"+
"sheetnames=false&"+
"pagenum=UNDEFINED&"+
"gridlines=true&"+
"portrait=true&"+
"scale=2&"+
"fitw=false&"+
"horizontal_alignment=CENTER&"+
"top_margin=0.00&"+
"bottom_margin=0.00&"+
"left_margin=0.00&"+
"right_margin=0.00&"+
"ir=false&"+
"ic=false&"+
"r1=10&"+
"c1=0&"+
"r2=44&"+
"c2=5&"+
"printtitle=false&" +
"attachment=true&"+ printRange;
var blob = DriveApp.getFileById(ss.getId()).getAs("application/pdf");
blob.setName(ss.getName() + ".pdf");
var templ = HtmlService
.createTemplateFromFile('candidate-email');
templ.candidate = candidate;
var message = templ.evaluate().getContent();
MailApp.sendEmail({
to: candidate.email,
subject: "New Invoice Submitted from Test Group!",
htmlBody: message,
attachments: [blob],
});
// Send Alert Email.
// var message = CustomerName + emailmessage; // Second column
// var subject = 'New Invoice Submitted';
// MailApp.sendEmail(emailAddress, subject, message , {
// });
SpreadsheetApp.getActive().getSheetByName('InvoiceTemplate1').showSheet();
SpreadsheetApp.getActive().getSheetByName('Template1').hideSheet();
}
&size=Letter
and&portrait=true
.