INFORMATION

Editor’s Note: This blog post is authored by Dito’s Steve Webster who is a Google Apps Script Top Contributor - Saurabh Gupta

Ethics Disclosure Review Workflow

Recently a company who operates retail stores throughout a few states reached out to Dito. When their associates conduct business discussions with vendors or customers where monetary exchanges are involved, their Ethics Disclosure policy requires a log for each event, a supervisor approval, and committee review.

The customer’s existing disclosure review process was cumbersome and time consuming. The employees would add a disclosure review request to a spreadsheet with 29 columns. The new review request would then be reviewed by their supervisor. The supervisor and the employee would go back and forth exchanging emails and making changes to a dense spreadsheet until an approval was granted. It was clear that the customer needed a workflow solution. They decided to hire Dito to build a workflow solution based on Google Apps Script.

Workflow Solution based on Google Apps Script

To make the process more user friendly and productive, Dito decided to build a user interface to collect ethics disclosure events, make updates, and automate the routing of email notifications. Writing a Google Apps Script to create a user interface (UI), enabled associates to interact with their contacts to select their supervisor’s email address and simplify the data collection with list boxes. The script sends approval emails with HTML form radio buttons, text box, approve/decline buttons, and a “Post” command to invoke other workflow scripts. Below are some of the main design points for this Approval Workflow script.

1. Disclosure Review Workflow

The Disclosure Review workflow requires (a) Associates to fill out the Ethics Disclosure form. (b) Supervisor to either approve or decline the submission. (c) If supervisor approves, the Ethics Disclosure Committee is notified. (d) If supervisor declines, the associate is notified to make corrections. (e) After improving the submission, the workflow repeats itself.

2. Disclosure Review Request Form

Dito developed a custom review request form. A form was developed using Google Apps Script’s UI Services. The form provides the ability to look up data to populate such things as a drop-down list box. This form allowed real-time email look-ups by using the Apps Script’s Contacts service.  First efforts included per character look-ups in a list box, but since they have over 1,000 users, it was best to enter the first and/or last name of their supervisor before initiating the look-up (see code snippet below).

var byName = ContactsApp.getContactsByName(searchKey);
for (var i in byName) {
  var emailStr  = byName[i].getPrimaryEmail();
  // If there is no primary email, try again for the next email
  if (emailStr == null)
     var emailStr  = byName[i].getEmails()[0].getAddress(); 
  // If emailStr is still null, try again by getting the next email
  if (emailStr == null) 
     var emailStr  = byName[i].getEmails()[1].getAddress();
}

Another dynamic field was the “activity type”.  Depending on the selection more form fields are displayed. For example, if the activity type is “Meals”, display a list box to select lunch or dinner.

3. Approve or Reject directly in Gmail

When an associate submits his/her review request by using the custom form within a spreadsheet, their supervisor receives an email with easy-to-read HTML formatted results.  The approval decision, as well as a comment field (e.g. decline reason), is made within the email. This is more productive and prevents unnecessary back and forth into the spreadsheet.

If the request is declined by the supervisor, the associate who submitted the review request receives an email and can review the details. The email also contains a “Continue” button which opens the form in a new browser tab. After corrections are submitted, the supervisor receives another email and the workflow repeats itself.

When approved, the Ethics Disclosure Committee is notified by sending a group email within the script.

4. Saving Workflow History

Since history records existed in their original spreadsheet form and they wanted to insert these records into the new work flow spreadsheet as a one-time task, an Apps Script was used to copy the data.  Of course their columns did not match the new spreadsheet.  By using a mapping approach and a “read once” and “write once” technique, the Apps Script quickly made the changes.

function myFunction() {
  var ss           = SpreadsheetApp.getActiveSpreadsheet();
  var sheet        = ss.getSheetByName(Sheet 1);
  var rowsWithData = sheet.getRange(2, 1, sheet.getLastRow(), 
                     sheet.getLastColumn()).getValues()
  var sheet1Data = []; 
  var sheet2Data = []; 
  for (var i = 0; i < rowsWithData.length; i++) {
    switch (rowsWithData[i][4])   // This is the activity type
    {
      ...
      case "Gift":
            sheet1Data.push([rowsWithData[i][12], rowsWithData[i][13], 
                             rowsWithData[i][14]]);
            sheet2Data.push([rowsWithData[i][15]]);
            continue;
      ...
      default:
            continue;
    }
  }
  sheet.getRange(2, 6,  sheet1Data.length, 3).setValues(sheet1Data);
  sheet.getRange(2, 12, sheet2Data.length, 1).setValues(sheet2Data);
}

Google Apps Script is very powerful and Dito uses it to build interesting solution for its customers. If you are using Google Apps then be sure to use Google Apps Script. You’ll be amazed with what you can build with it.

Steve Webster   profile Google Sites and Scripts expert from Dito specializing in training and application development. When not busy finding solutions to enhance customer capability in Google Apps, Steve shares examples of his work in the Google Apps Developer Blog.

Update (August 2014): Try the Yet Another Mail Merge add-on for Google Sheets.

Editor’s Note: This blog post is co-authored by James, Steve and Romain who are Google Apps Script top contributors. -- Ryan Boyd

The Google Apps Script team is on a roll and has implemented a ton of new features in the last few months. Some of us “Top Contributors” thought it will be a useful exercise to revisit the Mail Merge use case and discuss various ways in which we can do Mail Merge using Apps Script. Below are several techniques that tap into the power of Google Apps Script by utilizing Gmail, Documents and Sites to give your mailings some zing. Mail Merge is easy and here is how it can be done.

1. Simple Mail Merge using a Spreadsheet

The Simple Mail Merge tutorial shows an easy way to collect information from people in a Spreadsheet using Google Forms then generate and distribute personalized emails. In this tutorial we learn about using “keys,” like ${"First Name"}, in a template text document that is replaced by values from the spreadsheet. This Mail Merge uses HTML saved in the “template” cell of the spreadsheet as the content source.

2. Mail Merge using Gmail and Spreadsheet Services

The Gmail Service is now available in Google Apps Script, allowing you to create your template in Gmail where it is saved as a draft. This gives us the advantage of making Mail Merge more friendly to the typical user who may not know or care much about learning to write HTML for their template. The mail merge script will replace the draft and template keys with names and other information from the spreadsheet and automatically send the email.

To use this mail merge, create a new spreadsheet, and click on Tools > Script Gallery. Search for “Yet another Mail Merge” and you will be able to locate the script. Then, click Install. You’ll get two authorization dialogs, click OK through them. Add your contact list to the spreadsheet, with a header for each column. Then compose a new mail in Gmail. Follow this syntax for the “keys” in your template: $%column header% (see above). Click Save now to save your draft. Go back to your spreadsheet and click on the menu Mail Merge. A dialog pops up. Select your draft to start sending your emails.

You can add CCs, include attachments and format your text just as you would any email. People enjoy “Inserting” images in the body of their emails, so we made sure to keep this feature in our updated mail merge. To automate this process we will use a new advanced parameter of the method sendEmail, inlineImages. When the script runs it looks in the email template for images and make sure they appear as inline images and not as attachments. Now your emails will look just as you intended and the whole process of mail merge got a whole lot simpler.

3. Mail Merge using Document Forms

The next Mail Merge will use a template that is written in a Google Document and sent as an attachment. Monthly reports, vacation requests and other business forms can use this technique. Even very complex documents like a newsletter or brochure can utilize the automation of Google Apps Script to add the personal touch of having your patron’s name appear as a salutation.

Like in the Mail Merge for Gmail, the Google Docs template will use “keys” as placeholders for names, addresses or any other information that needs to be merged. Google Apps Script can add dynamic elements as well. For example you may want to include a current stock quote using the Financial Service, a chart from the Charts Service, or a meeting agenda automatically fetched for you by the Calendar Service.

As the code sample below demonstrates, the Google Apps Script gets the document template, copies it in a new temporary document, opens the temp document, replaces the key placeholders with the form values, converts it to PDF format, composes the email, sends the email with the attached PDF and deletes the temp document.

Here is a code snippet example to get you started. To use this mail merge, create a new spreadsheet, and click on Tools > Script Gallery. Search for “Employee of the Week Award” and you will be able to locate the script.

// Global variables 
docTemplate = “enter document ID here”;
docName = “enter document name here”;

function sendDocument() {
// Full name and email address values come from the spreadsheet form
   var full_name = from-spreadsheet-form
   var email_address = from-spreadsheet-form
// Get document template, copy it as a new temp doc, and save the Doc’s id
   var copyId   = DocsList.getFileById(docTemplate)
                  .makeCopy(docName+ for +full_name)
                  .getId();
   var copyDoc  = DocumentApp.openById(copyId);
   var copyBody = copyDoc.getActiveSection();
// Replace place holder keys,  
   copyBody.replaceText(keyFullName, full_name);
   var todaysDate =  Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy"); 
   copyBody.replaceText(keyTodaysDate, todaysDate);
// Save and close the temporary document
   copyDoc.saveAndClose();
// Convert temporary document to PDF by using the getAs blob conversion
   var pdf = DocsList.getFileById(copyId).getAs("application/pdf"); 
// Attach PDF and send the email
   MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf}); 
// Delete temp file
   DocsList.getFileById(copyId).setTrashed(true);
}

4. Mail Merge using Sites and Spreadsheet Services

For the last example let’s assume you have a great Google Site where you create new letters for your followers. However, you have had some feedback suggest that while many users don’t mind visiting your site, some would prefer to have the newsletter emailed to them. Normally this would require copying and pasting into an email or doc. Why not simply automate this with Google Apps Script?

The body section of a site, the part you edit, can be captured as HTML by the Sites Service and placed in the body of an email. Because the return value is HTML, the pictures and text formatting come through in the email.

Here is a simple example for you to try out:

function emailSiteBody() {  
 var site = SitesApp.getPageByUrl(YourPageURL);
 var body = site.getHtmlContent();
  
 MailApp.sendEmail(you@example.com, Site Template, no html :( , {htmlBody: body}); 
}

It really is that simple. Add a for loop with email values from a spreadsheet and this project is done.

Happy merging!

Updated 10/28: fixed instructions for accessing the complete script source for solution 3.

James Ferreira   profile Author, Scripter, and developer of free apps for non-profits and schools, James has written software to help more than half a million people by extending Google Apps.

Steve Webster   profile Google Sites and Scripts expert from Dito specializing in training and application development. When not busy finding solutions to enhance customer capability in Google Apps, Steve shares examples of his work in the Google Apps Developer Blog.

Romain Vialard   profile | YouTube Google Apps Change Management consultant at Revevol, Romain writes scripts to automate everyday tasks, add functionality and facilitate rapid adoption of cutting edge web infrastructures.

<gcontact:status indexed="true"/>

Alain Vongsouvanh profile | events Alain is a Developer Programs Engineer for Google Apps with a focus on Google Calendar and Google Contacts. Before Google, he graduated with his Masters in Computer Science from EPITA, France.

    
Ajax Form
  
    































Category:


  Travel Coupons
  Electronics Coupons
  Fashion Coupons
  Gift Coupons
  

Website:


  Please select a Website

CouponTitle : 
CouponDescription : 
CouponCode : 
AffiliateLink : 
AffiliateImage : 
 

$username="u462945266_sree";
$password="9963447141";
$database="u462945266_db";
$url = "mysql.neq3.com";

$category = $_POST[Category];
$website = $_POST[Website];
$title  = $_POST[CouponTitle];
$description = $_POST[CouponDescription];
$code = $_POST[CouponCode];
$affiliatelink = $_POST[AffiliateLink];
$affiliateimage = $_POST[AffiliateImage];


mysql_connect($url,$username,$password);

@mysql_select_db($database) or die( "Unable to select database");

$query = "INSERT INTO Coupons(Category,Website,CouponTitle,CouponDescription,CouponCode,AffiliateLink,AffiliateImage) VALUES ($category,$website,$title,$description,$code, $affiliatelink, $affiliateimage)";

mysql_query($query);

mysql_close();
echo "You successfully added your Coupon";  
?>