Create & Send Invoice

 How to Automatically Create And Send Invoices With Google Sheets?

Pre-requisite: Within the Google Drive create a template of the invoice.

Sample script given below. This will have to modified based on the specific invoice template.

function Create_And_Send_Invoices() {

  var spSheet = SpreadsheetApp.getActiveSpreadsheet();

  var invoiceSheet = spSheet.getSheetByName("Customer_Details");

  var priceSheet = spSheet.getSheetByName("Price_LookUp");

  var strDate = Utilities.formatDate(new Date(),"GMT", "dd-MMM-yyyy");

  var invoiceFolder = DriveApp.getFolderById("id of Folder");

  var invoiceTemplate = DriveApp.getFileById("id of invoice template");

  var invoiceNo = "";

  var custID = "";

  var custName="";

  var custEmail = "";

  var quantityItem1=0;

  var quantityItem2=0;

  var quantityItem3=0;

  var quantityItem4=0; 

  var priceItem1=0;

  var priceItem2=0;

  var priceItem3=0;

  var priceItem4=0;

  var invoiceMonth = "";

  var invoiceYear = "";

  var totalPiceItem1=0;

  var totalPiceItem2=0;

  var totalPiceItem3=0;

  var totalPiceItem4=0; 

  var taxPercentage = 0;

  var taxAmount = 0;

  var subtotalPrice = 0;

  var totalPrice = 0;

  priceItem1 = priceSheet.getRange("B2").getValue();

  priceItem2 = priceSheet.getRange("B3").getValue();

  priceItem3 = priceSheet.getRange("B4").getValue();

  priceItem4 = priceSheet.getRange("B5").getValue();

  taxPercentage = priceSheet.getRange("F4").getValue();

  var totalRows = invoiceSheet.getLastRow();

  for(var rowNo=2;rowNo <=totalRows; rowNo++){

    invoiceNo= invoiceSheet.getRange("A" + rowNo).getValue();

    custID = invoiceSheet.getRange("B" + rowNo).getValue();

    custName= invoiceSheet.getRange("C" + rowNo).getValue();

    custEmail= invoiceSheet.getRange("D" + rowNo).getValue();

    quantityItem1= invoiceSheet.getRange("E" + rowNo).getValue();

    quantityItem2= invoiceSheet.getRange("F" + rowNo).getValue();

    quantityItem3= invoiceSheet.getRange("G" + rowNo).getValue();

    quantityItem4= invoiceSheet.getRange("H" + rowNo).getValue();

    totalPiceItem1 = quantityItem1 * priceItem1;

    totalPiceItem2 = quantityItem2 * priceItem2;

    totalPiceItem3 = quantityItem3 * priceItem3;

    totalPiceItem4 = quantityItem4 * priceItem4;

    subtotalPrice = totalPiceItem1 + totalPiceItem2 + totalPiceItem3 + totalPiceItem4;

    taxAmount = parseFloat(taxPercentage * subtotalPrice).toFixed(2);

    totalPrice = Number(subtotalPrice) + Number(taxAmount);

    invoiceMonth = priceSheet.getRange("F2").getDisplayValue();

    invoiceYear = priceSheet.getRange("G2").getDisplayValue();

    invoiceSheet.getRange("I" + rowNo).setValue(totalPrice)

    if(totalPrice > 0) {

     // Create invoice and send email 

      var rawInvoiceFile = invoiceTemplate.makeCopy(invoiceFolder);

      var rawFile = DocumentApp.openById(rawInvoiceFile.getId());

      var rawFileContent = rawFile.getBody();

      rawFileContent.replaceText("Bill To Party : XXXXX", "Bill To Party : " + custName );

      rawFileContent.replaceText("Invoice No: XXXXX", "Invoice No: " + invoiceNo );

      rawFileContent.replaceText("I1Q", quantityItem1);

      rawFileContent.replaceText("I2Q", quantityItem2);

      rawFileContent.replaceText("I3Q", quantityItem3);

      rawFileContent.replaceText("I4Q", quantityItem4);

      rawFileContent.replaceText("I1P", priceItem1);

      rawFileContent.replaceText("I2P", priceItem2);

      rawFileContent.replaceText("I3P", priceItem3);

      rawFileContent.replaceText("I4P", priceItem4);

      rawFileContent.replaceText("T1", totalPiceItem1);

      rawFileContent.replaceText("T2", totalPiceItem2);

      rawFileContent.replaceText("T3", totalPiceItem3);

      rawFileContent.replaceText("T4", totalPiceItem4);  

      rawFileContent.replaceText("Sub Total Amount", subtotalPrice);

      rawFileContent.replaceText("Tax Percentage", taxPercentage);

      rawFileContent.replaceText("Tax Amount", taxAmount);

      rawFileContent.replaceText("Total Amount", totalPrice);

      rawFileContent.replaceText("Date:XXXXX", "Date:" + strDate)

      rawFile.saveAndClose();

      var pdfInvoice = rawFile.getAs(MimeType.PDF)

      pdfInvoice = invoiceFolder.createFile(pdfInvoice).setName("Invoice_" + custID);

      invoiceFolder.removeFile(rawInvoiceFile);

      var mailSubject = "Invoice for " + invoiceMonth + " " + invoiceYear;

      var mailBody = "Invoice for the month of " + invoiceMonth + " is generated.Details are attched.";

      GmailApp.sendEmail(custEmail, mailSubject, mailBody, {attachments:[pdfInvoice.getAs(MimeType.PDF)]})     

    } 

  } 

}