IT Lesson

Here’s the scenario: you create a form, you have a script that triggers onFormSubmit and all is well... until it gets popular. Occasionally you start having interlacing modifications from separate invocations of your script to the spreadsheet. Clearly, this kind of interlacing is not what you intended for the script to do. Up until now, there was no good solution to this problem -- except to remain unpopular or just be lucky. Neither are great solutions.

Now, my friend, you are in luck! We’ve just launched the LockService to deal with exactly this problem. The LockService allows you to have only one invocation of the script or portions thereof run at a time. Others that would’ve run at the same time can now be made to wait nicely in line for their turn. Just like the line at the checkout counter.

The LockService can provide two different kinds of locks-- one that locks for any invocation of your script, called a public lock, and another that locks only invocations by the same user on your script, called a private lock. If you’re not sure, using a public lock is the safest bet.

For example, in the scenario in the previous paragraph you would want something like this:

function onFormSubmit() {
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.
  // got the lock, you may now proceed
  ...whatever it used to do here....
  lock.releaseLock();
}

It’s best to release the lock at the end, but if you don’t, any locks you hold will be released at the end of script execution. How long should you wait? It depends on two things mainly: how long the thing you’re going to do while holding the lock takes, and how many concurrent executions you expect. Multiply those two and you’ll get your timeout. A number like 30 seconds should handle a good number of cases. Another way to pick the number is frankly to take an educated guess and if you guess too short, the script will occasionally fail.

If you want to avoid total failure if you can’t get the lock, you also have the option trying to get the lock and doing something else in the event of not being able to get it:

function someFunction() {
  var lock = LockService.getPublicLock();
  if (lock.tryLock(30000))  {
    // I got the lock!  Wo000t!!!11 Do whatever I was going to do!
  } else {
    // I couldn’t get the lock, now for plan B :(
    GmailApp.sendEmail(“admin@example.com”, “epic fail”,
        “lock acquisition fail!”);
  }
}

So now your scripts can be as popular as they can get with no worries about messing up shared resources due to concurrent edits! Check out the LockService documentation for more information.

Drew Csillag Drew is a Software Engineer and Manager at Google on the Google Apps Script project, based in New York. He previously worked on Billing at Google, and for the 13 years before, he has worked on everything from hardware up to GUI frontends and everything in between.

Editors Note: This article is written by Steve Webster and Vinay Thakker who work at Dito. Dito has developed applications such as Dito Directory which is available in the Google Apps Marketplace.

You just created your own contact group in Google Apps Contact Manager and now you want to share this contact group with a few other coworkers (not the entire company). Over the last couple of years, our team at Dito often got this request from our customers. We decided to leverage Google Spreadsheets & Google Apps Script to allow sharing of user’s “personal contact group” with only a select group of coworkers.

How does it work?

The Apps Script implements a three step wizard. Upon completion of the wizard, the script sends the sharing recipients a link to open the spreadsheet to import the user’s recently shared contact group. The three steps in the wizard are.

• Step 1 lists all the current contact groups in user’s account. The user can select the group he/she wants to share.
• Step 2 allows user to select the colleagues with whom the user wants to share his/her personal contact group with.
• Step 3 lets the user submit the sharing request.

Designing using Apps Script Services

Apps Script has various services which can be used to build the user interface, access the user’s contact list and send emails without the need to compile and deploy any code.

1. Security (guide)

Before a script can modify a user’s contacts, it needs to be authorized by that user. The authorization process takes place when a user executes the script for the first time. When a user makes a request to share his/her contacts, our script sends a link to the intended recipients by email. Upon clicking this link and the “Run Shared Contact Groups” button in the spreadsheet, the recipient will first need to grant authorization to execute the script. By clicking the “Run Shared Contacts Groups” button again, the script will proceed with creating the shared contact group.

2. Spreadsheet Service

In developing this script, there was a fair amount of data that needed to be exchanged between different users. We used Apps Script’s Spreadsheet Service for temporarily storing this data.

// grab the group titled "Sales Department"
var group = ContactsApp.getContactGroup("Sales Department");
// from that group, get all of the contacts
var contacts = group.getContacts();
// get the sheet that we want to write to  
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Contact Data");
// iterate through contacts
for (var i in contacts) {
//save each of the values into their own columns
sheet.getRange(i, 1, 1, 1).setValue(contacts[i].getGivenName());
sheet.getRange(i, 2, 1, 1).setValue(contacts[i].getFamilyName());
...
sheet.getRange(i, 13, 1, 1).setValue(contacts[i].getWorkFax());     
sheet.getRange(i, 14, 1, 1).setValue(contacts[i].getPager());       
sheet.getRange(i, 15, 1, 1).setValue(contacts[i].getNotes());       
}

3. Ui Service

Ui Services in Google Apps Scripts have an underlying Google Web Toolkit implementation. Using Ui Services in Apps Script, we easily built the user interface consisting of a 3 step wizard. In designing Ui using Ui Services, we used two main types of Ui elements - Layout Panels and Ui widgets. The layout panels, like FlowPanel, DockPanel, VerticalPanel, etc., allow you to organize the Ui widgets. The Ui widgets (TextBoxes, RadioButtons, etc.) are added to layout panels. Ui Services make it very easy to assemble and display a Ui interface very quickly.



We built each of the components on their own, and then nested them by using the “add” method on the desired container. The UI widgets in the screenshot above were constructed by the code below:

// create app container, chaining methods to set properties inline.
var app = UiApp.createApplication().setWidth(600).setTitle(Share The Group);
// create all of the structural containers
var tabPanel   = app.createTabPanel();
var overviewContent = app.createFlowPanel();
var step1Content = app.createFlowPanel();
var step2Content = app.createFlowPanel();
var step3Content = app.createFlowPanel();
// create u/i widgets
var selectLabel = app.createLabel("Select one of your Contact Groups you want to share with others.");
var contactGroupDropdown = app.createListBox().setName(groupChooser);
// add all children to their parents
overviewContent.add(selectLabel);
overviewContent.add(contactGroupDropdown);
tabPanel.add(overviewContent,"Overview");
tabPanel.add(step1Content,"Step 1");
tabPanel.add(step2Content,"Step 2");
tabPanel.add(step3Content,"Step 3");
app.add(tabPanel);
// tell the spreadsheet to display the app weve created.
SpreadsheetApp.getActiveSpreadsheet().show(app);

Continuing with this pattern, we created a pretty complex design using the UI Services. The next step in building a useful user interface is actually building in event handlers for the UI Widgets. Event Handlers let Apps Script know which function you want to run when your script needs to respond to a given user interaction. The code below is an example of a DropDownHandler that we used in our script in Step 1 of the wizard.

// create a function to execute when the event occurs. the
// callback element is passed in with the event.
function changeEventForDrowdown(el) {
 Browser.msgBox("The dropdown has changed!");
}
// create event handler object, indicating the name of the function to run
var dropdownHandler = app.createServerChangeHandler(changeEventForDrowdown);  
// set the callback element for the handler object.
dropdownHandler.addCallbackElement(tabPanel);
// add the handler to the "on change" event of the dropdown box
contactGroupDropdown.addChangeHandler(dropdownHandler);

4. Contacts Service

When a user of the script chooses to share a specific group, the script saves that group contact data into a spreadsheet. When a sharing recipient clicks on the run button to accept the contacts share request, the script fetches the contact group data from the spreadsheet and uses the Contacts Service to create contacts for the share recipients.

var group = ContactsApp.createContactGroup(myGroupName);
for (var i = 0; i < sheet.getLastRow(); i++) {
 var firstName = sheet.getRange(i, 1, 1, 1).getValue();
 var lastName = sheet.getRange(i, 2, 1, 1).getValue();
 var email = sheet.getRange(i, 3, 1, 1).getValue();
 var myContact = ContactsApp.createContact(firstName, lastName, email);
 // ...
 // set other contact details
 // ...
 myContact.addToGroup(group);
}

As this application shows, Apps Script is very powerful. Apps Script has the ability to create applications which allow you to integrate various Google and non-Google services while building complex user interfaces.

You can find Dito’s Personal Contact Group Sharing Script here. Click here to view the video demonstration of this application. You can also find Dito Directory on the Google Apps Marketplace.

Posted by Steve Webster and Vinay Thakker from Dito
Want to weigh in on this topic? Discuss on Buzz

Sometimes you just want to sit uninterrupted at your keyboard, bashing out a clever Apps Script to automate your life and your work … but sometimes you want to see how Google experts approach the tough problems. Sometimes you want to draw on other Apps Scripters for inspiration or help.

That’s why the Apps Script team — and many other developer-focused teams at Google — record Google Developers Live episodes in which we highlight a specific topic and drill down to discuss it in detail.

We also hold regular livestreamed office hours via Google+ Hangouts, which we post on YouTube afterwards. In these office hours, we discuss recent releases and give in-depth tutorials on topics interesting to Apps Script users.

Now that the 2013’s GDLs and office hours are underway, let’s recap six topics we discussed in GDL segments over the last few months.

Apps Script services

Triggers are an incredibly powerful part of Apps Script that allow developers to run code non-interactively. In this video, I talk about ways to schedule code via the GUI as well as programmatically, and briefly touch on intermediate topics such as common patterns and pitfalls when working with triggers.

Charts are a great way to visualize data. In this next video, Kalyan Reddy starts with a few slides about Apps Script’s Charts Service, then works his way into code samples for an application that pulls data from the StackOverflow API in order to create an online dashboard that displays contributions from top developers. If you want to follow along, Kalyan’s code samples are available on Github.

Working with other Google APIs

BigQuery is a Google service that allows developers to analyze massive datasets very quickly in the cloud. In this video, Michael Manoochehri from the BigQuery team joins us to talk about how to use Apps Script to automatically export aggregate BigQuery data into Google Sheets to make it easier to share. This show dovetails nicely with Kalyan’s video about charts (above), in which you’ll learn how to quickly wire up visualizations for the exported data.

And what developer doesn’t love Google Analytics? Although Analytics has built-in mechanisms to export data from the UI, it also provides an API for automated data retrieval. Nick Mihailovski from the Google Analytics team joins us to talk about the reasons why people might want to do this, and to demonstrate a toolkit that makes it easy to work with Google Analytics data within Google Sheets.

Third-party APIs

Many Google Apps users are also Salesforce users. In this show, Arun Nagarajan explains how to integrate Google Apps with Salesforce via Apps Script, and shows off a few code samples that demonstrate moving data between Salesforce and Google Apps in either direction. Make sure to grab a copy of Arun’s code samples on Github.

Need to build a robodialer or otherwise automate voice calls? Twilio provides an API for doing just that. Arun and Eric Koleda take us through some of the cool possibilities for integrating Twilio’s API with Google Apps. We had a lot of fun setting up the studio for this one, and it’s one of the most fun to watch. Here’s the code on Github.

Of course, if you want to hear our tricks and tips as soon as possible, you’ll should watch Google Developers Live, well, live — so check out the calendar of upcoming episodes for Apps Script and Drive. If you have any ideas for further segments you’d like to see, leave a suggestion in the comments below! We’d love to hear your feedback.

Cheers!

Ikai Lan   profile Ikai is a Developer Programs Engineer working on Google Apps Script but transitioning to the YouTube team. Ikai is an avid technologist, consuming volumes of material about new programming languages, frameworks or services, though more often than not youll find him advocating pragmatism over dogma in the solutions he proposes. In his free time, he enjoys the great outdoors, winning Chinese language karaoke contests and playing flag football. He resides in New York City, where he watches in anguish as his favorite sports teams from the San Francisco Bay Area implode season after season.

Editor’s Note: Guest author Alex Moore is the CEO of Baydin, an email productivity company. --Arun Nagarajan

As the CEO of an email productivity company, not a day goes by when I don’t learn about a new email pain point. I love solving email problems for our customers, but many of their problems do not lend themselves to a full browser-extension and server solution, like the products we make. Apps Script is perfect for solving some of these problems in a quick, lightweight, customizable way.

The Awaiting Response script is a perfect example of one of these solutions. My friend Matt Galligan, the CEO of Circa, tweeted a few months back that he wanted a way to find all of the messages that he sent that did not receive a reply.

Boomerang, our flagship extension, provides a way to bring a single message back to your attention if it doesn’t get a response. But Boomerang is not designed for this particular issue — to use Boomerang in this way, you’d need to move every message youd ever sent back to your inbox! Instead, it makes more sense to create a label and use Apps Script to apply it to each of these messages.

The Awaiting Response script searches the Sent folder to identify all messages you sent over the previous week. It then checks each thread to determine if someone else replied to your message. If no one has, the script applies the label AwaitingResponse to the message. You can then easily visit that label to see all those messages in a single glance.

var d = new Date();
d.setDate(d.getDate() - DAYS_TO_SEARCH);
var dateString = d.getFullYe

ar() + "/" + (d.getMonth() + 1) + "/" + d.getDate();
threads = GmailApp.search("in:Sent after:" + dateString);

Apps Script provides access to the full power of Gmail search, right from within your script. This snippet uses Javascript’s Date object to construct a Gmail-formatted search query that finds all of the conversations where you’ve sent a message in the last DAYS_TO_SEARCH days. It then loads the results of that search into an array of Thread objects.

var userEmailAddress = Session.getEffectiveUser().getEmail();
var EMAIL_REGEX = /[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+.[a-z.A-Z]+/g;

# if the label already exists, createLabel will return the existing label
var label = GmailApp.createLabel("AwaitingResponse");

var threadsToUpdate = [];
for (var i = 0; i < threads.length; i++)
{
  var thread = threads[i];
  var lastMessage = thread.getMessages()[thread.getMessageCount()-1];
  lastMessageSender = lastMessage.getFrom().match(EMAIL_REGEX)[0];
  if (lastMessageSender == userEmailAddress)
  {
    threadsToUpdate.push[thread];
  }
}

label.addToThreads(threads)

And this part of the script is where the heavy lifting happens. We iterate through each message in the list of search results, applying a regular expression to the From header in the message to extract the sender’s email address. We compare the sender’s address to the script user’s email address. If they don’t match, we know someone else sent the last message in the conversation. So we apply the AwaitingResponse label to the conversation. If the script user sent the last message, we simply move along to the next message.

Add in a little bit of glue and a couple configuration options, and you have a flexible, simple script that gives you the superpower of always knowing which messages might need you to check back in.

Matt adapted his own version of the script to run automatically each day and to only apply the label to messages sent more recently than the last week.

He has also set up the script to exclude messages that include labels where, for example, he has already used Boomerang to track the messages for later. It would also be a snap to update the script to handle aliases (for example, if you use your Gmail account to send a message using your corporate email address) or to look for messages that require a reply from you.

You can get the script here. To customize it, just create your own copy and edit it right inside the built-in editor. With Awaiting Response, Apps Script helped us solve a customer problem in about fifteen minutes, without having to build an entire product.

Alex Moore is the CEO of Baydin, an email productivity company. Baydin makes software that combines AI and behavioral science to ease the burden on overloaded emailers, including the popular Boomerang email scheduling extension, which has been downloaded over two million times. When taking a break from his email, Alex makes a chicken florentine that tastes like angels singing. He is a rabid Alabama football fan.

Posted by Louis Gray, Googler

It’s in the news lately that by 2030, 50% of the population will be obese. Well, I’m a bit (ok, maybe more than a bit) overweight now and was looking to do something about it. So for my diet, I’ve started counting calories. I’ve tried a number of other diets with mixed results, but calorie counting for me always works. However, counting calories can be a bit of a pain. There are a number of ways to figure out how many calories are in things, such as the container the item comes from, websites like http://caloriecount.about.com/, http://fatsecret.com/ or search on your favorite search engine. Finding out the calorie content of a food isn’t really so much of a problem in and of itself because after awhile you just know how many calories are in the usual things you eat. But where is the best place to keep track the current running total for the day? It has to be easily accessible to me or I don’t do it. I don’t always have paper, nor am I in places where I have connectivity. Android, Gmail and of course, Google Apps Script to the rescue!

Counting Calories using Apps Script

The process is simple. I send an email to myself with the subject “@FOOD” where the body contains the number of calories in the food and the name of the food, one per line. I wrote a script which, every 15 minutes, scans my email and computes the total of calories I’ve consumed in the last 24 hours and updates a spreadsheet with the total.

Why do it this way? Using Gmail for the recording makes it so if I’m offline on my phone, the gmail app will send it when I’m online. Putting it in a Google spreadsheet means I can make a shortcut for it in chrome, and a desktop web shortcut icon on my Android phone for easy access. Additionally, using a spreadsheet allows me to perform other calculations, make charts, etc.

How do you set it up?

First create a new spreadsheet, and click on Tools > Script Editor. Click on File > New > From Script Template. Search for “Calorie Counting” and you will be able to locate the script. Then, click Install and you are all set. Save the script, run it, at which point you’ll get two authorization dialogs, click ok through them. Run it again to make sure it populates the sheet properly. Then, in the script editor, click Triggers>Current script’s triggers and add a new trigger:



And you’re all set! Your spreadsheet, after the script runs will look like this:



From here the possibilities are endless. I’m thinking I could make a UiApp script which uses the new Charts bean to draw a graph. Perhaps make a service to view/change calories because I mess things up every once in awhile. You could also add code for “@WEIGH” messages to track weight and could graph that too. Your imagination is the limit! And if you have an even better idea for how to use Apps Script to improve Gmail and Spreadsheets, you can post it to our Gallery (Script Editor > Share > Publish Project) to share with the world.

Drew Csillag Drew is a Software Engineer and Manager at Google on the Google Apps Script project, based in New York. He previously worked on Billing at Google, and for the 13 years before, he has worked on everything from hardware up to GUI frontends and everything in between.

Right now, Apps Script developers have three competing ways to create user interfaces: Ui Service, a visual tool for Ui Service called GUI Builder, and Html Service, which we launched at Google I/O in 2012. We designed Html Service specifically to help developers build complex applications by letting them work with familiar libraries like jQuery and jQuery UI.

Today, we are deprecating GUI Builder and five UiApp widgets — but not Ui Service itself. This will help us further focus our development efforts on Html Service.

The GUI Builder will continue to be available until September 9, 2013. After that point, you will not be able to create or manage GUI Builder components, although existing components will still function. The five deprecated UiApp widgets are Hyperlink, InlineHyperlink, LayoutPanel, RichTextArea, and SuggestBox. These widgets will be also available until September 9, 2013, at which point they will cease to function.

To plan for the future, we recommend that you migrate your user interfaces to Html Service, which will offer the best combination of features and support in the long term.

Meanwhile, we have a few awesome new features planned for 2013. Although we’re not quite ready to announce those features, I dropped a few hints when Arun Nagarajan interviewed me for a State of the Script episode on Google Developers Live last month. Give it a watch, and I’m sure you’ll be as excited about the future of Apps Script as we are.

Saurabh Gupta   profile | twitter | blog As the product manager for Google Apps Script, Saurabh is responsible for Apps Script’s overall vision and direction.