Use Google App script in Google Sheets to send invoices in pdf using GMail

I've build a Google Sheet to create and email invoices to customers.
Invoices are emailed using GMail, stored in a specific folder of your Google Drive.

You can :

  • set the name of the sender
  • set the name of the pdf
  • set the number of day to determin the due date
  • set the subject of the email
  • set the ID of the Drive Folder you want the invoice to be stored in
  • ....

Of course, the template can be customised to your wishes and needs.
Create a new invoice based on the template with a single click, choose a customer, check the data on the invoice and send the email with a single click.

You can create a copy of the Google Sheet here :

Open the sheet

Below you can find the code.

Contact me for a quote in case you need help to customize this project to your needs.

 

/*
INSTRUCTIONS on how to enable DRIVE API

To use an advanced Google service, follow these instructions:
In the script editor, select Resources > Advanced Google services....
In the dialog that appears, click the on/off switch next to DRIVE API
At the bottom of the dialog, click the link for the Google Developers Console.
In the console, click into the filter box and type 'DRIVE', then click the name once you see it.
On the next screen, click Enable API.
Close the Developers Console and return to the script editor. Click OK in the dialog. The advanced service you enabled will now be available in autocomplete.
*/


function sendEmailWithPdf() {
  
  //## Email Variables ##//
  
  var   from = GmailApp.getAliases()[0]; //get emailaddress
  var   source = SpreadsheetApp.getActiveSpreadsheet(); //get the active spreadsheet
  var   invoicenr = source.getActiveSheet().getRange('I12').getValue(); //get the invoicenumber based on value in cell I12   
  var   string = String(invoicenr);
  var   sheetName = source.getActiveSheet().getName();
  var   amount = source.getActiveSheet().getRange('I42').getDisplayValue(); //get the total amount of the invoice
  var   bicKbc = source.getActiveSheet().getRange('B5').getValue();  // get the BIC code for KBC
  var   ibanKbc = source.getActiveSheet().getRange('B6').getValue();  // get the IBAN code for KBC
  var   maand = source.getActiveSheet().getRange('A23').getValue();  // get the month
  var   emailSender = source.getActiveSheet().getRange('I4').getValue();  // get the emailadress
  var   nameSender = source.getActiveSheet().getRange('B1').getValue();  // get the name of the sender based on the value B1
  var   body = 'Your text here';
  var   date = Utilities.formatDate(source.getActiveSheet().getRange('I13').getValue(), "GMT+2", "dd/MM/yyyy"); //format the date to a specific format
  //var date = source.getActiveSheet().getRange('I13').getValue();
  var   datepdf = date = Utilities.formatDate(source.getActiveSheet().getRange('I13').getValue(), "GMT+2", "dd/MM/yyyy"); //format the date to a specific format
  //var datepdf = date = source.getActiveSheet().getRange('I13').getValue();
  var   pdfName = 'Your text here' + datepdf + " - F"+invoicenr; //create name of the invoice based on value in cell G12       
  var   dateExpire = Utilities.formatDate(source.getActiveSheet().getRange('I14').getValue(), "GMT+2", "dd/MM/yyyy"); //format the date to a specific format
  //var   dateExpire = source.getActiveSheet().getRange('I14').getValue();
  var   subject = "Your invoice F" + invoicenr + " of " + date; //put the number of the invoice in the subject line
  var   mailTo = source.getActiveSheet().getRange('B17').getValue();
  var   htmlBody = "Best, <br/><br/>" + //create the body of the email, using the values of the variables declared above
    "In attachment you can find your invoice F" + invoicenr + " of date " + date + "." +
    "<br/><br />The amount of this invoice is : " + amount +                  
    "<br/>Please make the payment before " + dateExpire + "." +  
    "<br/><br/>Payment details : <br/>" + amount + "<br/>Notification : F" + invoicenr + " - " + date + "." +"<br/><br/>" + bicKbc + "<br/>" + ibanKbc + "<br/>" +             
    "<br/><br/>You can always reach us via " + emailSender + "." +
    "<br/><br/><br/>Kind regards," +
    "<br/>" + nameSender +
    "<br/><br/><br/>You can open the pdf in attachment using <a href='https://get.adobe.com/reader/' target='_blank'>Adobe Reader</a>. ";
 
  var attachmentName = pdfName + '.pdf';  
  
  //## Create pdf ##//
  
var sourcesheet = source.getSheetByName(sheetName); 


var config = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Config");  
var FOLDERID = config.getRange("B21").getValues(); // Folder id to save pdf in a folder.
var folder = DriveApp.getFolderById(FOLDERID); 
var newSpreadsheet = SpreadsheetApp.create(pdfName); // Name new Spreadsheet
source.getActiveSheet().getRange("A11:J").copyTo(source.getActiveSheet().getRange("A11:J"), {contentsOnly:true}); //copy values only so that formules do not exist anymore
var sheet = sourcesheet.copyTo(newSpreadsheet); //copy activesheet to new spreadsheet
newSpreadsheet.getSheetByName('Blad1').activate(); //select "sheet1"
newSpreadsheet.deleteActiveSheet(); //delete "sheet1"
var newFile = folder.createFile(newSpreadsheet);
var pdf = DriveApp.getFileById(newSpreadsheet.getId());
var theBlob = pdf.getBlob().getAs('application/pdf').setName(pdfName); //create pdf from new sheet in folder

var   url,  //create variable from url of the new sheet
        sheets = newSpreadsheet.getSheets()
      
  
    url = Drive.Files.get(newSpreadsheet.getId())
          .exportLinks['application/pdf']; //create the pdf 
    url = url + '&size=a4' + //paper size
    '&portrait=true' + //orientation, false for landscape
    '&fitw=true' + //fit to width, false for actual size
    '&sheetnames=false&printtitle=false&pagenumbers=false' + //hide optional
    '&gridlines=false' + //false = hide gridlines, true = show
    '&fzr=false'; //do not repeat row headers (frozen rows) on each page, true = show
   var token = ScriptApp.getOAuthToken();
   var response = UrlFetchApp.fetch(url, {
      headers: {
         'Authorization': 'Bearer ' + token
    }
 });
  

  
  
 DriveApp.getFilesByName(pdfName).next().setTrashed(true); //delete newSpreadsheet
  
  //## Send email ##//
  
 
  var optAdvancedArgs = {name: nameSender, htmlBody: htmlBody, replyTo : emailSender, from:from, attachments: [response.getBlob().setName(attachmentName)], };  //attach pdf
 
  GmailApp.sendEmail(mailTo, subject, body, optAdvancedArgs); //send email with advanced arguments
  
  source.getActiveSheet().getRange('A7').setValue("SENT"); //input 'SENT' in in cell A7
 
  }