Create PDF and Send it by eMail with Google Apps Script

Business Case Background

When new management took over the  farmers market  business, they inherited the existing business processes as well. One of the key business processes is vendor application, where prospective vendors apply to participate at the market. The existing business process used paper forms. Vendors would pull a PDF from the old website, print them up, then send them in via snail mail. The snail mail would need to get picked up on a regular basis from a  PO BOX. The data, such as business name, key contact info, address and such are master data that needed to be used in a number of other business processes. Once the completed paper forms were in hand, it was extremely laborious to conduct administrative tasks, such as communicating to the vendors since the reference data was on paper.  They regularly received upwards of 100 applications, most of which were not suitable and did not meet the vendor requirements that were established.

A farmers market does not typically have the budget to fund a staff of professionals to run the business. A few, very overworked managers have to take on this labor of love. A solution to reduce the amount of work in processing the vendor applications was needed going forward. That information had to be digitized to be to make it more usable. Automation was needed to save time on administrative realities of running a market.

  • Reduce the administrative work required to process incoming vendor applications.
  • Incentivize high quality vendors and reduce the amount of low quality applications we received. To do this,  a vendor application fee was introduced: applications could only be submitted with an application fee.
  • Create a digital database with master data that could be used in other business processes such as //  vendor email communications // financial accounting // mapping placement of the vendors on a map // sorting, counting and allocating the number of booth spaces needed such as single and double booths.
  • Perform vendor fee accounting.
  • Create a shareable record for vendors and market managers to review.

Here is what was able to did to help streamline this business process for them.

Form Based Data Capture

After I stood up a brand new WordPress instance and migrated the domain to a new registrar, I build and configured what many in the community thought was a beautiful website to represent the new management at the Farmers Market.

The Vendor Application Form needed to capture and key vendor information into Google Sheets and accept payment at minimum. Though WordPress integrations with Google Sheets are plentiful, form plugins for a WordPress instance that would also accept payment were not available.

In searching for a solution I found FormStack that had slick integrations with Paypal allowing prospective vendors to use that payment method.

In addition, Formstack offered integrations with Constant Contact, the eMail Marketing software allowing vendors to opt into newsletter subscription. This was important because they would then see how the newsletter marketing promoted them, and incentivised them to participate in crafting their own weekly marketing messages.

By using Formstack

  • I was able to create forms with conditional logic, so that if a vendor self identified as a “farmer”, the appropriate questions for a farmer would be presented and if they were a “food artisan”, the right questions for a food artisan would be shown.
  • Embed the form inline into the WordPress implementation.
  • Process payments through Paypal where funds were routed to the appropriate operating bank account.
  • Send notifications when an application was submitted.
  • Use a webhook to return applicants to a Thank You landing page on the WordPress implementation.
  • Have completed form data populated to Google Sheets. A new form submission would result in a new row in Sheets.

Formstack is a very powerful digital form composer that met these needs and offered the appropriate third party integrations for the customer’s requirements.

[/av_heading]

 

Automating with Google Apps Script on Form Submission.

There are some powerful advanced features lurking beneath the surface of Google Sheets. If you are a Microsoft Excel user you may be familiar with macros that allow advanced functionality to be custom coded with VBA. Sheets offers something similar in Google Apps Script, except in the cloud. Google Apps Script permits the writing and executions of functions (within certain quota limits).

High level overview of what the script does:

  1. Grabs the Google Sheet with all the Formstack results.
  2. Reads all the values from the columns of the last row (the most recently submitted form data) and transform them into variables.
  3. Populates the variable data into a Google Doc Template by replacing placeholder text with the variables, for example, replace the “vendor email” placeholder text with the actual vendor’s email address.
  4. Save a copy of the template with the customer specific data as a PDF.
  5. Send a customized email to the vendor with the PDF attached.

The code is commented.

var sheetID = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"; //Usually the formstack application results spreadsheet
var docTemplate = "xxxxxxxxxxxxxxxxxxxxxxxxxxxx"; //The completed application document template for the current season
var docName = "2015 Indoor Market Application Details";

//check the sheets last row and make a comparison to see if a new entry has been made.
function checkSheet() {
    var ss = SpreadsheetApp.openById(sheetID);
    var sh = ss.getSheets()[0]; // first sheet
    var currentbottomrow = sh.getLastRow();

    var gscriptProps = PropertiesService.getScriptProperties();
    var priorbottomrow = gscriptProps.getProperty('LastRowFound');

    if (priorbottomrow < currentbottomrow) {
        newEntryOccured(currentbottomrow);
        gscriptProps.setProperties({
            'LastRowFound': currentbottomrow
        }); //update script property with the last row found - uncomment for prod
    }
}

//If a new entry has been made, parse the data from the sheet and automate changes to the sheet, generate a PDF from a document template, and email the PDF.
function newEntryOccured(rownum) {
    var sheet = SpreadsheetApp.openById(sheetID).getSheets()[0]
    var BottomRowValues = sheet.getRange(rownum, 1, 1, 38).getValues();

    //Fix the zip code in the sheet because it is treated numerically - drops the 0 in 02360 to become 2360
    var BottomRowFixBizZip = sheet.getRange(rownum, 40, 1).setFormula('=IF(LEN(S' + rownum + ') < 5,"0" & S' + rownum + ', S' + rownum + ')');

    //Generate a complete business address
    var BottomRowFixBizAddress = sheet.getRange(rownum, 41, 1).setFormula('=O' + rownum + ' & " " & P' + rownum + ' & " " & Q' + rownum + ' & " " & R' + rownum + ' & " " &AN' + rownum + '');

    //Generate a script timestamp
    var BottomRowTimeStamp = sheet.getRange(rownum, 42, 1).setValue(Date());

    //Evaluate the vendor fee based on the field setup option - Dont forget to update the per diem fee
    var BottomRowCostFormula = sheet.getRange(rownum, 2, 1).setFormula('=IF(LEFT(I' + rownum + ',3)="Per",(LEN(J' + rownum + ')-LEN(SUBSTITUTE(J' + rownum + ',",",""))+1)*35,MID(I' + rownum + ',SEARCH("$",I' + rownum + ')+1,3))');

    //Set a formula for total
    var BottomRowCalculateBalance = sheet.getRange(rownum, 5, 1).setFormula('=B' + rownum + '+C' + rownum + '-D' + rownum);

    //Set a formula for calculating balance
    var BottomRowCalculateBalance = sheet.getRange(rownum, 7, 1).setFormula('=E' + rownum + '-F' + rownum + '-C' + rownum);

    //Parse the spreadsheet and assign values to variables
    var businessname = (BottomRowValues[0][0]);
    var vendorfee = (BottomRowValues[0][1]);
    var applicationfee = (BottomRowValues[0][2]);
    var deductions = (BottomRowValues[0][3]);
    var totalobligation = (BottomRowValues[0][4]);
    var payments = (BottomRowValues[0][5]);
    var balancenowowed = (BottomRowValues[0][6]);
    var paymenthistorynotes = (BottomRowValues[0][7]);
    var chooseyourfieldsetupoption = (BottomRowValues[0][8]);
    var perdiemattendanceplan = (BottomRowValues[0][9]);
    var firstname = (BottomRowValues[0][10]);
    var lastname = (BottomRowValues[0][11]);
    var email = (BottomRowValues[0][12]);
    var businessphone = (BottomRowValues[0][13]);
    var businessaddressaddress = (BottomRowValues[0][14]);
    var businessaddressaddress2 = (BottomRowValues[0][15]);
    var businessaddresscity = (BottomRowValues[0][16]);
    var businessaddressstate = (BottomRowValues[0][17]);
    var businessaddresszip = (BottomRowValues[0][18]);
    var businesswebsite = (BottomRowValues[0][19]);
    var vendorcategory = (BottomRowValues[0][20]);
    var farmproducts = (BottomRowValues[0][21]);
    var farmlocations = (BottomRowValues[0][22]);
    var totalacreageinproduction = (BottomRowValues[0][23]);
    var checkallofthecultivationpracticesthatapply = (BottomRowValues[0][24]);
    var provideadditionaldetailaboutyourgrowingpractices = (BottomRowValues[0][25]);
    var listitemsyouwillsell = (BottomRowValues[0][26]);
    var listlocallysourcedingredientsandnameyoursources = (BottomRowValues[0][27]);
    var listfooditemsyouwillresell = (BottomRowValues[0][28]);
    var listlocallysourcedingredientsandnameyourlocalsources = (BottomRowValues[0][29]);
    var listnonfooditemsyouwillsell = (BottomRowValues[0][30]);
    var listlocallysourcedmaterialsandnameyourlocalsources = (BottomRowValues[0][31]);
    var otherfarmersmarkets = (BottomRowValues[0][32]);
    var nonfarmersmarketssalesvenues = (BottomRowValues[0][33]);
    var applicationcount = (BottomRowValues[0][34]);
    var browser = (BottomRowValues[0][35]);
    var ipaddress = (BottomRowValues[0][36]);
    var uniqueid = (BottomRowValues[0][37]);
    var time = (BottomRowValues[0][38]);

    // Get the document template, copy it as a new temp doc, and save the Doc’s id
    var copyId = DriveApp.getFileById(docTemplate)
        .makeCopy(docName + ' for ' + businessname)
        .getId();

    // Open the temporary document
    var copyDoc = DocumentApp.openById(copyId);

    // Get the document’s body section
    var copyBody = copyDoc.getActiveSection();
    // Replace the placeholder text with actual values
    copyBody.replaceText('keyFName', firstname);
    copyBody.replaceText('keyLName', lastname);
    copyBody.replaceText('keyeMail', email);
    copyBody.replaceText('keyBizName', businessname);
    copyBody.replaceText('keyMemberOption', chooseyourfieldsetupoption);
    copyBody.replaceText('keyBizURL', businesswebsite);
    copyBody.replaceText('keyBizStreet1', businessaddressaddress);
    copyBody.replaceText('keyBizStreet2', businessaddressaddress2);
    copyBody.replaceText('keyBizCity', businessaddresscity);
    copyBody.replaceText('keyBizState', businessaddressstate);
    copyBody.replaceText('keyBizZip', businessaddresszip);
    copyBody.replaceText('keyBizPhone', businessphone);
    copyBody.replaceText('keyOtherMkts', otherfarmersmarkets);
    copyBody.replaceText('keyProductsForSale', farmproducts + " " + listitemsyouwillsell + " " + listfooditemsyouwillresell + " " + listnonfooditemsyouwillsell);
    copyBody.replaceText('keyGrowingMethods', checkallofthecultivationpracticesthatapply);
    copyBody.replaceText('keyWhereElse', otherfarmersmarkets + " " + nonfarmersmarketssalesvenues);
    copyBody.replaceText('keyPerDiemDates', perdiemattendanceplan);


    // Save and close the temporary document
    copyDoc.saveAndClose();

    // Convert temporary document to PDF
    var pdf = DriveApp.getFileById(copyId).getAs("application/pdf");

    // Compose a customized HTML message
    var subject = businessname + " | Farmers’ Market Indoor Season";
    var body = "<p><a href=\"http:\/\/www.plymouthfarmersmarket.org\" target=\"_blank\"><img alt=\"Plymouth Farmers\' Market\" src=\"http:\/\/www.plymouthfarmersmarket.org\/pfm\/wp-content\/uploads\/2013\/04\/logo-300x110.png\" style=\"height:110px; width:300px\" \/><\/a><\/p>\r\n\r\n<p>Dear " + firstname + ",&nbsp;<\/p>\r\n\r\n<p>Thank you for your interest in becoming a vendor of the <strong>2015 Indoor Season of the Plymouth Farmers Market<\/strong>&nbsp;and thank you for submitting your application for " + businessname + ".<br \/>\r\nWe are in the process of reviewing vendor applications and we will notify you of your application status by&nbsp;<strong>October 11<\/strong>.<\/p>\r\n\r\n<p>We have attached a copy of the application&nbsp;containing the information you have provided to us and a copy of the vendor policies for your reference.<br \/>\r\nPlease review the information and please&nbsp;<a href=\"mailto:localfoods@plymouthfarmersmarket.org\" target=\"_blank\">contact us<\/a>&nbsp;with any updates or questions you may have.<br \/>\r\n<br \/>\r\nThank You for Your Interest,<br \/>\r\n-<strong>The Plymouth Farmers&#39; Market - Market Selection Team<\/strong><\/p>\r\n\r\n<p><a href=\"http:\/\/www.plymouthfarmersmarket.org\/\" target=\"_blank\">www.plymouthfarmersmarket.org<\/a><\/p>"

    //Generate and send the email with the attached PDF.
    MailApp.sendEmail({
        to: email,
        cc: "localfoods@plymouthfarmersmarket.org",
        name: "localfoods@plymouthfarmersmarket.org",
        replyTo: "localfoods@plymouthfarmersmarket.org",
        subject: subject,
        htmlBody: body + "<br>",
        attachments: pdf,
    });

    // Delete temp file
    DriveApp.getFileById(copyId).setTrashed(true);

}

Triggering the Code

Finally, we have to run this code. A trigger is needed to do this. Every minute, the trigger calls  function checkSheet()  which stores the bottom row value into the script properties. Every time it is called, it evaluates what the bottom row is and then checks to see if the current bottom row is greater than the bottom row the last time it was checked. If it is, we know we have a new row to process and proceed with processing the row by calling the  newEntryOccured(rownum) function

Hope this helps you with your initiatives and please, let me know what your questions are in the comments section below.

Also, please feel free to contact me if you need help thinking through automating your business processes..

 

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *