INFORMATION

Anyone else get stuck with fixing their familys computer problems? Recently I had a family technical issue that was actually fun to solve and didnt involve removing malware. My family owns Rasmus Auctioneers, a global auctioneer company based in Alexandria, Virginia. The company uses Google Apps for their entire business workflow, so their documents, calendars, etc. are all connected.

We manage the shipping process on-site using a tablet. In this fast-paced environment and with limited time, we need a quick, easy solution. When an auction ends, the clients fill out a Google Form that saves their shipping and payment information to a Google Spreadsheet. This information then needs to be sent to a specific mover for processing -- but the mover doesnt need all the data, just a few important pieces. All of this needs to happen with as little human intervention as possible.

Our solution was to create an Apps Script within the spreadsheet, which means our new functionality works in the tablets used on the auction floor. The function below runs when the sheet is opened, automatically sorting the sheet to put the newest client on top. A simple dialog determines which data is important for a particular auction; the script then finds the data we need, sends it to the right person, and adds a calendar event as a reminder.

function sendData() {
  // Sort the rows 
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.sort(4);

  // What row do we want? 
  var myValue = Browser.inputBox("What row?");   

  // Variables
  var emailRecipients = "me@mydomain.com";
  var emailSubject = "Moving data for ";
  var valueToShow = "";

  // Get cell values
  var clientName = sheet.getRange("B" + myValue).getValue();
  var movingDate = sheet.getRange("D" + myValue).getValue();  
  valueToShow += "Name: " + 
      sheet.getRange("B" + myValue).getValue() + "

";
  valueToShow += "Moving Date: " + 
      sheet.getRange("D" + myValue).getValue() + "

";
  valueToShow += "Size: " + 
      sheet.getRange("E" + myValue).getValue() + "

";

  // Send email
  MailApp.sendEmail(emailRecipients, emailSubject + clientName, 
      valueToShow);

  // Add to calendar
  CalendarApp.createAllDayEvent("Move today! " + clientName, 
      new Date(movingDate));
}

Looking at the source code, we can see how we collect the data from the relevant columns, add them to a variable, and in one line send it via Gmail. Adding a reminder to our calendar also takes just one line.

Do you have a repetitive process that bugs you? Wish there was a way to automate something? You might be able to solve your problems the same way I helped out my family -- with Apps Script! Feel free to leave a comment on some ideas or suggestions for future projects.

Tom Fitzgerald profile | blog Tom Fitzgerald is an engineer for Google at their Mountain View headquarters. While attempting to keep his HTML5 blog up to date he works on various web development side projects. Originally from Maryland he supports the Baltimore Ravens & Orioles!

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.

Editor’s Note: Guest author Jason Gordon is a co-founder of Beth Macri Designs — Arun Nagarajan

Beth Macri Designs creates jewelry from the point of view of a structural engineer. The forms are designed using generative 3D software systems and materialized using 3D printing technologies. Our company understands that to make beautiful fine jewelry, 3D printing is only the first step; traditional jewelry craft is then employed for final production. After our first product, The Hidden Message Necklace, was recently featured on The View as part of its Valentines Day Gift Guide, we had a lot of orders to ship out. As soon as the mail leaves the building, though, the process is literally out of our hands: something unexpected was bound to happen to at least one or two packages. Several package-tracking services exist, but getting the names and tracking numbers into them was a cut-and-paste operation.

I knew that all of the tracking numbers were being delivered by email and I had already set up a Gmail filter to archive them and apply a label. With a little help from Google Apps Script, I knew I could automatically parse those emails and add them to my account on PackageTrackr (which syncs to their newer service, Fara).

The script supports reading emails from multiple shipping providers and is set up so one could easily add more. Every 30 minutes on a time-driven trigger, using the Gmail service, the script runs and looks through unread emails from the shipping provider label, then parses the name and tracking number out of each one. The provider, tracking number, and recipient are stored in a JavaScript array.

function getUSPSConversations(){
  return GmailApp.search("in:usps is:unread subject:(Click-N-Ship)");
}

function matchUSPSHTML(data){
  var out = [];
  var track_num = data.match( 
      /TrackConfirmActionWinput.actionWtLabels=(d+)/g);
  var to = data.match(/Shipped.to.*[

]*.*>([a-zA-Zs-_]*)<br>/g);
  for(i in track_num){
    var o = new Object();
    var track = track_num[i].match(/(d+)/g);
    var person = to[i].match(/>([a-zA-Zs-_]+)<br>/);
    var myPerson = person[1].replace(/(

|
|
)/gm,"")
    o["number"]=track[0];
    o["carrier"]="USPS";
    o["person"]=myPerson;
    out.push(o);
  }
  return out;
}

You can parse all of your different shipping providers in one run of the script. After all of the shipment emails are read, it composes an email to PackageTrackr to give it all of the tracking numbers it just harvested.

var user = Session.getActiveUser().getEmail();
if(data.length > 0){    
  for(d in data){
    body += this["formatForPackageTrackr"](data[d]["number"], 
        data[d]["carrier"], data[d]["person"]);
  }
  
  GmailApp.sendEmail("track@packagetrackr.com", "Add Packages",
      body, {bcc: user});
}

function formatForPackageTrackr(tracking_num, service, person){
  return "#:" + tracking_num + " " + service + " " + person + "
";
}

Down the line, other shipping providers could be added such as UPS and Fedex. Additionally, more tracking services could be added instead of just PackageTrackr.

Jason Gordon   profile Jason Gordon is a co-founder at jewelry startup Beth Macri Designs. He is responsible for software development, logistics and e-commerce. While working at Beth Macri Designs, Jason gets to find creative ways to put his software development skills to work to improve logistics and user experience.

Editor’s Note: Guest author Niels Buekers is a Google Apps consultant at Capgemini Belgium. — Arun Nagarajan

During a recent Google Apps migration project, we received several requests to create custom groups of contacts so that users could more easily email frequent collaborators. Before switching to Google Apps, users created their own private distribution lists — but this approach led to overlapping groups that quickly fell out of sync.

The problem was a perfect case for Google Apps Script. We built a great solution that gives users as much power as possible with just a quick administrator review.

The situation before: either manually adding each contact or using a private contacts group.

Solution overview

To start the process, a user adds a specific label to a Gmail message. A script that runs on a timed trigger then generates a request to create a group for all the addresses in the message. The script writes this data to a spreadsheet that tracks group names and administrator approval.

/**
 * Retrieves all group_request threads and creates a request.
 */
function processInbox() {
  // Get threads that have the group_request label.
  var groupRequestLabel = GmailApp.getUserLabelByName(group_request);
  var threads = groupRequestLabel.getThreads(0, 10);
  
  // For each thread, retrieve all recipients and create a group request.
  for (var i = 0; i < threads.length; i++) {
    var firstMessage = threads[i].getMessages()[0];
    var sender = firstMessage.getFrom();
    var recipients = [];
    
    // Add sender.
    recipients.push(parseAddresses(sender));
    
    // Add recipients.
    if (threads[i].getMessages()[0].getTo()) {
      var toRecipients = parseAddresses(firstMessage.getTo());
      recipients.push(toRecipients);
    }
    
    // Add CCs.
    if (threads[i].getMessages()[0].getCc()){
      var ccRecipients = parseAddresses(firstMessage.getCc());
      recipients.push(ccRecipients);
    }

    // Write all recipients to a cell in the spreadsheet
    // and send emails to ask for group name and approval.
    createGroupRequestForRecipients(recipients,
        Session.getActiveUser().getEmail());
    
    // Remove label from this thread now that it has been processed.
    threads[i].removeLabel(groupRequestLabel);
  }
};

Handling the request

Once the request has been processed and written to the spreadsheet, the script sends the user an email that asks her to suggest a name for the group in an Apps Script web app. A second email asks the administrator to visit the web app to approve or decline the request. The results are again stored in the spreadsheet.

The spreadsheet contains a second script, which is triggered for each modification. Once the script confirms that the request has been approved, it uses the Apps Script Domain Service to create the new group.

/**
 * Creates a new group in the Google Apps cPanel with the provided name
 * and members.
 */
function createGroupWithAddresses(addresses,groupName){
  var group = GroupsManager.createGroup(groupName, groupName, groupName,
    GroupsManager.PermissionLevel.DOMAIN);
  var splitAddresses = addresses.split(,);
  for (var i = 0; i < splitAddresses.length; i++) {
    Logger.log(Adding  + splitAddresses[i]);
    group.addMember(splitAddresses[i]);
  }
};

The result after successfully running the script.

This solution provides a simple way for users to request new Google groups, without all the overhead of manually creating an admin-managed distribution list.

Niels Buekers   profile | Twitter Niels is a Google Apps consultant at Capgemini Belgium, with interest in both the technical track and change management. He recently visited Google’s London office to participate in a Google Apps Script hackathon, which resulted in the above solution. Niels is a strong believer in cloud solutions and loves to spread the word about Google Apps.

We recently added a new feature to Apps Script: the ability to interact with your Google Docs List. With this new integration, scripts can create, rename, or move files and folders in your Google Docs. In addition, scripts can now create, modify, and clear plain-text files and search through your documents for a specified query string.

For instance, take a company whose website is hosted on Google Sites. They have a Specials page where they want to list seasonal sales depending on upcoming holidays. They store the details about the seasonal sales in plain-text files that are saved in their Google Docs List. By using the Google Docs List and and Sites services within Apps Script along with time-based triggers to run the script once per day, they can keep their Specials page updated automatically. Here’s a code snippet that demonstrates how to update the Specials page:

//The Mother’s Day sale runs from May 1 - May 7, 2011
var MOTHERS_DAY_START = new Date("May 1, 2011");
var MOTHERS_DAY_END = new Date("May 7, 2011");
//The Valentine’s Day sale runs from Feb 6 - Feb 13, 2011
var VALENTINES_DAY_START = new Date("February 6, 2011");
var VALENTINES_DAY_END = new Date("February 13, 2011");

function updateSpecials() {
  var today = new Date();
  var site = SitesApp.getSite("example.com", "giftshop");
  // Get all the web pages in the Site
  var pages = site.getWebPages();
  // Loop through the web pages to find the specials page
  for (var i = 0; i < pages.length; i++) {
    if (pages[i].getPageName() == "specials") {
      var page = pages[i]; 
    }  
  }
  // Set up the default wording for the specials page
  var pageText = "There are no specials at this time.";

  // If today’s date is within the Mother’s Day sale range
  if (today >= MOTHERS_DAY_START && today <= MOTHERS_DAY_END) {
    // Find the sale text that’s stored in the file mom.txt
    pageText = DocsList.find("mom.txt")[0].getContentAsString();
  }
  // If today’s date is within the Valentine’s Day sale range
  else if (today >= VALENTINES_START && today <= VALENTINES_END ) {
    // Find the sale text that’s stored in the file valentines.txt
    var pageText = DocsList.find("valentines.txt")[0].getContentAsString()   
  }
  // Set the content of the specials page
  page.setContent(pageText);  
}​

If this script is then set up to run using a trigger that calls it at the same time each day, then the Specials page will be kept automatically up-to-date.

To help you learn more, weve created a tutorial that demonstrates how to search and display information about files, create files, and read content from files.

Note that certain features of the DocsList service, such as creating files, are only available to Google Apps accounts. For complete information on interacting with your Google Docs List using Apps Script, see the DocsList Service documentation.

We look forward to seeing how you use this integration. If youd like to learn more about Apps Script and meet the Apps Script team in person, join us at the upcoming Apps Script hackathon in New York City on June 24.

Posted by Jan Kleinert, Google Developer Relations