Google Drive autofill template script

script.js

// create a menu
function onOpen() {
  var menuEntries = [ {name: "Create invoice", functionName: "CreateInv"}];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.addMenu("Invoice Generator", menuEntries);
}

function CreateInv() {
  
  // specify doc template and get values from spread
  var sleepINT = 1500
  var templateid = "1WOXmMtwkCWojZO01WglBD3RYfzAOsTde-0FfeYr0NRM"; // template file id
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.toast("ENGINE INITIALIZING & Feeding the unicorns");
  Utilities.sleep(sleepINT);
  var sheet = ss.getActiveSheet();
  var data = sheet.getRange(2,1,1,15).getValues(); // starting with row 2 and column 1 as our upper-left most column, 
                                                   // get values from cells from 1 row down, and 15 columns along - hence (2,1,1,15)
  //sheet.getRange("F7").setValue('=IMAGE("https://s-media-cache-ak0.pinimg.com/564x/58/5d/8f/585d8f802867c25df8f1ecc0cf7cadc8.jpg",1)');                                                 
  ss.toast("10%: data captured"); 
  Utilities.sleep(sleepINT);
  
  
  //Create and set a counter for the invoice number, then grab the new invoice number
  var oldInvoiceNumber = sheet.getRange("G2").getValue();
  oldInvoiceNumber += 1;
  sheet.getRange("G2").clear();
  sheet.getRange("G2").setValue(oldInvoiceNumber);
  sheet.getRange("G2").setBackgroundColor("#cecece");
  var newInvNumber = sheet.getRange("G2").getValue();
  
  ss.toast("20%: invoice number updated"); 
  Utilities.sleep(sleepINT);
  
  // Make a copy of the invoice template, then Fill up it up with the data from the spreadsheet.
  //NOTE: body.replace method does not have to be in any specific order.
  
  for (var i in data) {
    var row = data[i];
    var docid = DriveApp.getFileById(templateid).makeCopy().getId();
    var doc = DocumentApp.openById(docid);
    var body = doc.getActiveSection();
    body.replaceText("%NAME%", row[0]);
    body.replaceText("%ADD_LN1%", row[1]);
    body.replaceText("%ADD_LN2%", row[2]);
    body.replaceText("%EMAIL%", row[3]);
    body.replaceText("%PHONE%", row[4]);
    body.replaceText("%DATE%", row[5]);
    body.replaceText("%INV_NUM%", row[6]);
    body.replaceText("%DESC%", row[7]);
    body.replaceText("%FEE%", row[8]);
    body.replaceText("%GST%", row[9]);
    body.replaceText("%INC_DESC%", row[10]);
    body.replaceText("%INC_FEES%", row[11]);
    body.replaceText("%INC_GST%", row[12]);
    body.replaceText("%T_PAID%", row[13]);
    body.replaceText("%T_OUTS%", row[14]);
    
    doc.saveAndClose();
    
    ss.toast("30%: template data replaced");
    Utilities.sleep(sleepINT);
    
    //copy the modified template to the specified folder, then delete the first copy we made (to modify it) 
    var file = DriveApp.getFileById(doc.getId());
    var newfolder = DriveApp.getFolderById("0B2Y8ZbVb0AHYajZpck03NnI5VVk");
    var oldfolder = DriveApp.getFolderById("0B2Y8ZbVb0AHYU0lPd1lWc3hGd2s");
    newfolder.addFile(file);
    oldfolder.removeFile(file);
    
    ss.toast("40%: invoice has been put in correct folder");
    Utilities.sleep(sleepINT);
    
    //customize the title for the invoice
    var usernamefordoctitle = sheet.getRange(2, 1, 1, 1).getValues() // this is grabbing the customer name field (A2)
    var name = doc.getName();
    doc.setName(newInvNumber + ' - Invoice for ' + usernamefordoctitle);
    ss.toast("50%: named new invoice");
    Utilities.sleep(sleepINT);

    //create and organize pdf version
    var pdffolder = DriveApp.getFolderById("0B2Y8ZbVb0AHYVmZEVjl0ZUJMV00");
    var pdfFILE = DriveApp.getFileById(doc.getId()).getAs('application/pdf');
    pdfFILE.setName(doc.getName() + ".pdf");
    var theFolder = pdffolder;
    var theFile = DriveApp.createFile(pdfFILE);
    theFolder.addFile(theFile);
    ss.toast("60%: PDF generated");
    Utilities.sleep(sleepINT);
    
    var email_status = sheet.getRange("D4").getValue();
    
    if (email_status == "YES" ) {
        //send a pdf copy to customer 
        var pdfEMAIL = DriveApp.getFileById(doc.getId()).getAs('application/pdf').getBytes();
        var message = "Hi " + usernamefordoctitle + "!, please kindly find your invoice attached.\nMany Thanks!\nMe";
        var emailAdd = sheet.getRange("D2").getValue()
        var emailTo = emailAdd; // add customer email here
        var subject = "Invoice for " + usernamefordoctitle + " from ME" + " - Invoice Number : " + newInvNumber;
        
        var attach = {fileName:"INVOICE " + newInvNumber + " " + usernamefordoctitle + '.pdf',content:pdfEMAIL, mimeType:'application/pdf'};
        MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
        ss.toast("70%: emailed customer");
        Utilities.sleep(sleepINT);
        
        }
        
    else {
        
        ss.toast("No email sent");
        
        }
      }
        
  //update worksheet with values
  //FETCH VALUES
  var date = sheet.getRange("F3").getValue();
  var customer = sheet.getRange("A2").getValue();
  var email = sheet.getRange("D2").getValue();
  var phone = sheet.getRange("E2").getValue();
  var hours_worked = sheet.getRange("D6").getValue();
  var fee_charged = sheet.getRange("O2").getValue();
  var hourly_rate = sheet.getRange("F5").getValue();
  var InvNumber = sheet.getRange("G2").getValue();
  var totalpaid = sheet.getRange("N2").getValue();
  var totalout = sheet.getRange("O2").getValue();
  var incomeTAX = sheet.getRange("N7").getValue();
  var Super = sheet.getRange("N11").getValue();
  var time = Utilities.formatDate(new Date(), "GMT+11:00", "dd/MM/yyyy HH:mm:ss");
  
  //Add values to worksheet
  var tss = SpreadsheetApp.openById('1h0qtrqVlS2xzkrggNGyfSmXUEQVP5mTxrfDSr9LkQoY');
  var ts = tss.getSheetByName('worksheet');
  ts.appendRow([date,customer,email,phone,hours_worked,fee_charged,totalpaid,totalout,hourly_rate,InvNumber,incomeTAX,Super,time]);
  ss.toast("80%: updated worksheet")
  Utilities.sleep(sleepINT);
  ss.toast("90%: feeding the unicorns some more")
  Utilities.sleep(sleepINT);
  ss.toast("100%: high-fiving the neighbour")
  Utilities.sleep(sleepINT);
  ss.toast("WOOOOOOOOO Invoice " + newInvNumber + " has been created. It's in a new doc with the ID " + docid); 
  Utilities.sleep(10000);
}