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.

Crowd sourcing has been growing substantially in popularity. More and more businesses and individuals are interested in gathering data from the general public for real-time data analysis and visualization. The concept is being adopted in several fields, including journalism, public health and safety, and business development. During this election year, for example, a journalist might be interested in learning what candidate his or her readers support, and the reasons why they support this candidate.

Google Forms, Fusion Tables, and Apps Script make both data collection and analysis super simple! Using Google Forms, a journalist can quickly create an HTML form for readers to submit their opinions and feedback. Fusion Tables make data analysis easy with several cool data visualization options. Apps Script acts as the glue between Google Forms and Fusion Tables, enabling the Form to send data directly to Fusion Tables.

Let’s take a look at how our journalist friend would use all these tools to collect her reader’s candidate preferences.

Google Forms

Google Forms provides a simple UI tool to develop forms perfect for collecting data from readers. Here’s an example of a simple form the journalist can create to get information from her readers:

Once the form has been created, it can be embedded directly into the journalist’s website or blog using the embeddable HTML code provided by Google Forms.

Google Fusion Tables

Google Fusion Tables makes data analysis simple with its visualization capabilities. Using Fusion Tables, the journalist can create maps and charts of the collected data with just a few clicks of the mouse!

Using some fake data as an example, here’s a pie chart that can be created using Fusion Tables to show the the results of the survey:

With Fusion Tables, it’s also easy to filter data and create a pie chart visualization showing why people like Mitt Romney:

These visualizations can also be embedded in the journalist’s website or blog, as Fusion Tables provides embeddable HTML code for all its visualizations. Now, any time someone visits the webpage with the embedded visualization, they will see the current poll result!

Apps Script

Finally, Apps Script acts as the glue between the Google Form and the Fusion Table, since there is currently no direct way to send Google Form submissions to a Fusion Table. During a hack event last year, I took some time to write an Apps Script script that submits the form data to Fusion Tables. The script uses the onFormSubmit Apps Script functionality as described in this blog post. The Fusion Tables code is based on the code described in this blog post.

To learn how to set up your own Google Form to collect data and save that data in a Fusion Table, please see these instructions.

Kathryn Hurley profile Kathryn is a Developer Programs Engineer for Fusion Tables at Google. In this role, she helps spread the word about Fusion Tables by presenting at conferences and developer events. Kathryn received an MS in Web Science from the University of San Francisco. Prior work experience includes database management, web production, and research in mobile and peer-to-peer computing.

On Tuesday we launched the Google Apps Marketplace at Campfire One and were joined by over 50 developers who built some great applications which are now available to the 2 million businesses and 25 million users that use Google Apps. Were just getting started and cant wait to see what other amazing apps are out there.

If you already have a great web app or even just an idea for one and would like to learn more about integration with Google Apps, join us for a webinar next Wednesday. Well review the Marketplace and Google Apps APIs and answer technical and policy questions from attendees.

Integrate with Google Apps and the Google Apps Marketplace
Wednesday, March 17, 2010
9:00 a.m. PDT

This webinar will include a question and answer session. Post and vote for questions ahead of time and register for the webinar here. We hope youll join us for this informative online event.

UPDATE @ 5:30pm PST: Weve corrected the registration link.

Today was another satisfying day at the conference in sunny (I did see the sun this morning) Orlando. I started the day off with 11 other agilists going for a morning run on the paths and walkways around the Disney complex followed by a great breakfast. The catering at the Dolphin has been fantastic so far.

The first of two notable sessions that I attended was called "Effective Questions for an Agile Coach." The two presenters Arto and Sami from Reaktor were great and cleverly crafted the table discussions so that we would fall into common coaching traps and then helped demonstrate better alternatives. Here is a brief overview of some of the ideas from the presentation:
- By giving advice you are creating motivation from the outside. You need to ask effective questions to let them figure it out for themselves
- The four acceptance tests for good coaching questions are a) leads to exploration, b) aim at descriptive answers, c) avoids judgement and d) avoids unproductive states of mind
- Avoid the question why. Try converting the question into a What, When, How Much or How many. For example, instead of Why, ask What benefit did you expect to receive?
- When trying to help the team solve a problem, follow the GROW model. Grow: first, find their goal. Reality: Second, ask questions to help them describe the current state. Options: Third, ask questions to find at least 3 options. Simply ask how would you solve this. What: Finally, ask questions to find agreement on a path forward.

Some other random thoughts:
- I attended an www.innovationgames.com seminar. After playing The Product Tree game and Scream, I want to explore how to introduce these games in my own projects.
- Dave Thomas was funny and poignant as the keynote speaker although I did wonder if his talk was targeted more at those not at the conference than those of us who have already taken the pill. They videotaped his talk and I suggest looking for it in the next few weeks.
- I took some advice from other conference veterans and walked out of a session that was covering topics I was already familiar with. As a result, I had some great conversations about retrospectives and the intersection of agile and church.
- The soft skill sessions at this conference have been great, but I wonder if there is room for more advanced developer topics.
- Played some beach volleyball at the end of the day with 2 other Canadians and a Swede. Are there any Americans at this conference?

More tomorrow...  looking forward to the open jam on ATDD/BDD wording.

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.

Domain administrators can create new users for their domain using the control panel web UI and the Google Apps Provisioning API. Once the new users are created, editing their contact information (such as their addresses, phone numbers, etc) can be done using the Google Apps Profiles API. These profiles are shown to everybody in the organization when searching for users in GMail’s Contacts Manager and GMail’s autocomplete feature.



Some users want enhanced privacy but unsharing a user’s contact information could only be done using the control panel web UI.





We just introduced a new element in the Google Apps Profiles API that lets domain administrators set this option programmatically. This new field is called gContact:status and is available under a User Profile entry:

<gcontact:status indexed="true"/>

Changing the indexed attribute value to false unshares the user contact’s information when “contact sharing” is enabled on the domain.



For more information about the Google Apps Profiles API and code samples for supported languages using our client libraries, please refer to the developer’s guide. To learn how you can use 2-legged OAuth and batch requests to programmatically unshare users contact’s information with our client libraries, please have a look at this article.

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.

Want to weigh in on this topic? Discuss on Buzz

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.

Im a big fan of using silent brainstorming in order to generate ideas as individuals before processing those ideas as a group. "Priming" is yet another reason why using silence is important.

System 1 & System 2. (Not to be confused with Thing 1 and Thing 2)

Im currently reading Daniel Kahnemans book "Thinking, Fast and Slow" - a behavioural psychology and economics book that describes his research on how our mind thinks. In the book, Kahneman describes the two systems that make up how we think. System one is the unconscious, fast, intuitive, relational thinker. System two is the conscious, slower, lazier, and more logical thinker. For example, when I ask you what 2+2 is, system one jumps in and gives you the answer of 4. When I ask you what 453 * 23 is, system two jumps in to help you calculate the answer.

One of the experiments that Kahneman describes demonstrates how you can prime system one and influence its answers. The experiment asked people to look at one word and then fill in the blank in a subsequent incomplete word. The first word they were shown was either "Eat" or "Wash" and the second incomplete word was "So_p". When shown "Eat", system ones relational thinking kicked in and people more often said "Soup" for the second word. On the other hand, when shown "Wash", system one more often produced the related word "Soap". Showing the first word to the participants primed system one and influenced it to think of a second word that was related to the first.

So, if you start a brainstorming meeting with "What can we do better? My idea is [X].", you have now primed people to think about [X]. However, if you let people generate ideas on their own first you will start with a larger base of ideas to work with. Once people have written down their own ideas [X,Y,Z], saying those ideas out loud will allow system one to find relational words on the whole set rather than just one idea.

Generate ideas in silence, process the ideas out loud.

References:
- Daniel Kahnemans Book: Thinking, Fast and Slow
- Slides and video from my related talk: The Silence of Agile

The Google Apps Marketplace turns one year old today. Over the last year, we’ve seen over 300 business applications join the Apps Marketplace. As a Developer Advocate on the Apps Marketplace, I’ve worked with the developers and product managers of many of these applications to help them understand our APIs and how integrations will help their users.

On our first birthday, I want to share some of my thoughts based on what I’ve seen as an advocate for developers and our mutual customers.

My definition of deeply integrated

A deeply integrated app is one that values my time -- the developers have done everything possible to build an amazing user experience throughout the process of configuring and deploying the application as well as day-to-day use.

When integrations truly impress me, I call them star integrations.

Stop making me create accounts and passwords

This is a core tenant of the Apps Marketplace, and one that I strongly believe in. We knew we wanted to eliminate the proliferation of passwords and create a smooth Single Sign-On (SSO) experience when we were building the Marketplace.

While not many business applications had adopted OpenID for authentication, we rallied behind this open standard (with some additional discovery extensions) to enable SSO. I’m happy and proud to say there are now hundreds of business web applications which are OpenID relying parties. While some only support Google Apps, or Google Apps and Gmail accounts currently, it’s a step in the right direction for our users.

How does Single Sign-On work with the Apps Marketplace? It’s super simple for a user to access all of their apps from the navigation at the top of Gmail, Calendar, Docs and more.

Beyond access from Google’s applications, some apps also allow their users to login via their Google Apps accounts directly from their homepages. This saves time for users and gets these integrations a shiny Star in my book.

Cohuman: Allows me to sign in with my Google Apps account directly from their site, in addition to providing Single Sign-On access from the universal navigation bar

Eliminate my data silos

I want one calendar, one set of contacts, one e-mail inbox, and one repository for my documents. I don’t want to re-enter this information in yet another application-- I already have it in Google Apps.

I often hear developers say ‘2-way synchronization is a hard problem.’ I agree, but sometimes the hard work is worth it to build a great user experience.

Auto-complete my Contacts

I don’t always remember the e-mail addresses of my friends, colleagues and clients, but that info is in my Google Contacts. The good news is, building auto-complete using the Google Contacts API can mostly be done in the front-end, without full synchronization. When you prompt me with a ‘To’ field to invite someone to collaborate on a project with, you should make it super easy for me to fill out.

Mavenlink:Allows me to send invitations to contacts or colleagues to collaborate on a project. They make it very easy by auto-completing addresses using my Google Contacts.

Connect with my Google Docs

I store everything in my Google Docs -- my presentations, my financials and even some of my favorite screenshots. Please, please, please don’t make me export and import docs, but rather enable me to easily use those docs from within my favorite applications.

Manymoon: Automatically manages the ACLs of docs I attach to a task or project, so new project members automatically get access to the project docs

Your integration with Google Docs doesn’t need to be only in your interface. I love it when developers take new features in the Google Apps UI and extend them to be features of their applications. For example, collections (folders) in Google Docs enable you to organize and share your content, but they’re typically just a loose collection of documents. Your application can make them more useful by creating folders and managing their content.

class.io: Creates a Google Docs folder and Google Calendar automatically when a teacher creates a new course. This allows students to access all the information about their class from within the tools they already use.

Manage my central calendar

If I have an upcoming project due, a phone call with an important customer, or a homework assignment to turn in, I want to be able to go to my Google Calendar and see all the events. I don’t want to bounce between a dozen apps (even with the wonders of SSO, that’s painful!). If your application has any date-based data, give me the option of using Google Calendar to store it.

To do this, there are two basic ways to store data from a third-party app in Google Calendar -- in the user’s personal calendar, or by creating a separate (“secondary”) calendar for a project or other entity. I’m often asked which is the best strategy, and this is one of the few things I don’t have an opinion on. Do what your users want, or offer both options.

TripIt: Has a Calendar Sidebar Gadget to give me a quick glance view of my upcoming trips

When an app puts events into my Google Calendar, I’m also able to take advantage of built-in support for things like alerts, which gives me configurable popup, email and SMS notifications of my upcoming events.

Make my data available on the go

I’m not always sitting at my desk waiting for the next phone call or e-mail to flow in -- I’m on US-101 for a few hours every day. I’m fortunate to be on a Google shuttle where my laptop is easily accessible, but not all of your users are that lucky.

Google has made all of my favorite apps, like Google Calendars, Contacts, Mail and Chat all work really well on my Android phone. We’ve also invested in sync for other devices such as the Blackberry and the iPhone.

When you eliminate data silos and allow me to store all my info in Google Apps, you also empower me to use my data everywhere I go, making me a more productive and happier user.

AffinityLive: Creates appointments in Google Calendar, making them available on the web and on your mobile device

Some apps on the Marketplace also provide mobile-optimized web sites or native mobile apps to help you access your app-specific data on the go. But how do you do SSO on a mobile device? This is the biggest challenge Apps Marketplace developers mention when building mobile versions of their apps. There are several different strategies which work well for Marketplace apps. The best solution I’ve seen is using OpenID to open a browser and login to TripIt, with a custom URL scheme registered as the Intent Filter in Android. Sure, opening a browser is a little ugly, but since I’m often already logged into my account, it’s a smoother experience than typing a username+password. Alternatively, the mobile apps for Concur Breeze use a one-time mobile PIN which you must enter when you first setup the app.

Enable me to take action in context

How often do you copy and paste an e-mail address from Gmail into your CRM to lookup a customer? Or look at a Calendar appointment and want more information about the people you’re meeting with? Or receive an e-mail asking you to approve an expense report, requiring you to popup a new window and login to your expenses app?

You shouldn’t have to, and a deeply integrated app won’t make you.

Show me relevant information next to my e-mails

Gmail Contextual Gadgets ranks as my favorite API we’ve released for Google Apps. It allows you to register a regular expression which attempts to match against each Gmail message -- using the e-mail addresses, subject or body of the message. If a match succeeds, your application is triggered as a gadget which appears below each matching e-mail message.

That’s the geeky definition, but what do Gmail Contextual Gadgets do in practice? They save me, and all your other users plenty of precious time, by allowing us to see information take action to e-mails right within the context of those messages.

Harvest: If I forgot to submit my timesheets for the week, it sends me a reminder and I can file the timesheets right from within the email

Solve360: When I receive an e-mail from a potential customer, I can immediately capture the opportunity in my CRM

Become my friend in Google Talk

Gmail gadgets aren’t the only way that apps can make me more efficient in the context of my other tasks. Apps can also take advantage of Google Talk, which is available for Google Apps users in Gmail, in iGoogle and on their mobile devices.

GQueues: Uses the XMPP functionality built into App Engine to allow me to IM the application to add a new task. So, if I have an e-mail open or am running around with my Android, I can pop open a Google Task and quickly add a task.

Make my dates more Interesting

Calendar Sidebar Gadgets are also contextual, allowing you to display relevant information about the event. Imagine if the created event was about a project-- a sidebar gadget could display other upcoming events related to the project, information about the members in the project and give quick access to relevant documents.

Let me pack my bags if we must part ways

Google has an initiative and team called the Data Liberation Front. Their goal is to make sure it’s really easy to move data in and out of Google products, like Calendar, Contacts, Docs and more. They enable data portability through a combination of user-facing UI and developer-facing APIs.

Some of the applications on the Apps Marketplace also make it easy to move data in and out.

Viewpath: If I want to get my data out of Viewpath, I can easily export it as a Microsoft Project file.

Earn your stars!

Get started on your integrations now and earn stars from your customers . Then work with those customers to share the story of how your deep integrations with Google Apps made them happier and more efficient.

If you’re feeling your creativity is limited by the available integration points offered by our APIs, let us know how we can help by chiming into the Buzz stream below. Also chime in if you want to share an awesome integration you’ve built, even if you’re not quite ready to share a customer story.

Want to weigh in on this topic? Discuss on Buzz

Ryan Boyd profile | twitter | events Ryan is a Developer Advocate on the Google Apps Marketplace team, helping businesses build applications integrated into Google Apps. Wearing both engineering and business development hats, youll find Ryan writing code and helping businesses get to market with integrated features.

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