VERSION 2 - Autofil Document with google scripts and email as pdf!

Hi! Some of you have tried my first version of this script to mixed success. This version is vastly improved and more fail-safe. It also has a few more additions, including saving the pdf to google drive as well as emailing, updating another spreadsheets and so forth.

(PLEASE NOTE: There is a bug that has appeared since a recent google update where a blank sheet is being added in between each page. I believe it's occuring during the getbytes() call, but still debugging)

This demo is in the form of an invoice generator I recently created for someone. 

All you need to do go to the following link below:

https://drive.google.com/open?id=0B2Y8ZbVb0AHYU0lPd1lWc3hGd2s

And make a copy of each file and put them in a folder.

Create a new foler within that called invoice, and another within the invoice folder called pdfs.

That done, open the Invoice gen sheet, and open script editor.

Update the file and folder IDs to be those of your files / folders.

run the script and carefully observe whats happening in the invoice folder, pdf folder, (and out of interest - the timesheet spreadsheet).

If you are completely lost don't worry - am in europe at the moment, but will make a screencast when I get back to australia to walk you through all the code and the logic behind it.

CODE FOR INVOICE GEN:

(scroll to the right to view all code)

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

Danny Baker

Danny Blaker, Melbourne

Danny has a wealth of experience in the start-up and technology sectors spanning over 10 years, and is the founder and co-founder of numerous companies and initiatives, such as Unudge, & Geartooth.

Danny's diverse skill set encompasses disruptive marketing strategy, business strategy, product design, audio production, data analysis (R, R Markdown, Python), graphics design (photoshop, Indesign, Illustrator), communications, social media marketing, project management, growth strategy, UX design, front-end web development (WP, Joomla, JS, Python, HTML, CSS), and corporate law.

Danny is also a spreadsheet expert and an online instructor, teaching at Udemy.com. His courses have amassed over 11,000 students to date.

He also blogs regularly – you can find his posts at www.dannyblaker.com/blog.

You can reach Danny on twitter @DannyBlaker