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.setActiveSheet(spreadsheet.getSheetByName('Template1'), true);


 // 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$/, '') + 
  "printtitle=false&" +
  "attachment=true&"+ printRange;

  var blob = DriveApp.getFileById(ss.getId()).getAs("application/pdf");

  blob.setName(ss.getName() + ".pdf");

   var templ = HtmlService
  templ.candidate = candidate;
  var message = templ.evaluate().getContent();
    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 , {
    //   });


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.