0

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();
}
7

0

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.