Tutorial

Editor’s Note: Guest author Mark Showalter is a Senior Research Scientist at the SETI Institute. — Arun Nagarajan

In 2011 and 2012, while studying the region around Pluto with the Hubble Space Telescope, I discovered the dwarf planet’s fourth and fifth known moons. Like all new astronomical objects, they started out with rather prosaic names — “S/2011 (134340) 1” and “S/2012 (134340) 1”, or, for short, P4 and P5.

I soon found my inbox stuffed with hundreds of naming suggestions. With so much interest, it didn’t seem fair to leave the job to just a handful of scientists. Instead, we decided to let the public propose and vote on the names of Pluto’s moons.

We knew that the web servers at the SETI Institute, my research home, could never handle the bandwidth required for such a task. However, the Institute has built strong relationships with Google through our extensive use of G+, and our friends there were thrilled to let us use Google services for the demanding task. I asked my husband Frank Yellin, who works on the Gmail team, for help in setting up the forms and collecting the data. Google Forms and Google Sheets were obvious choices, but with the volume of contributions and votes we were expecting, we knew we’d need programmatic help checking for duplicate nominees, filtering out inappropriate names, and tallying the votes.

Frank is a longtime Java engineer, so he tried a Java solution first. As the votes started to pour in at the rate of several per second, however, it became clear that the program could barely keep pace. Votes were coming in almost as fast as they were being downloaded and tallied. In a panic, Frank realized it was time to learn Apps Script — in fact, time to learn JavaScript altogether.

With some help from his colleagues (“How do I split a string?” “How do I make a hash table?”), he turned the project around in a few hours. Processing that had taken tens of minutes using Java took mere seconds in Apps Script, since nothing but the results ever had to leave the data center.

We were right to be prepared. By the time we closed the write-in ballot, we had received 30,000 write-in nominees and more than 450,000 votes.

We are now using the results of the poll to support our proposal for the formal names of P4 and P5. That decision is currently in the hands of the International Astronomical Union. When the final decision is made, Pluto and Charon and Nix and Hydra will be joined by two more representatives of the ancient underworld.

Dr. Mark Showalter   profile Planetary scientist Mark Showalter is a Senior Research Scientist at the SETI Institute. His primary interest in the dynamics of planetary rings. To date, this interest has led him to discover five new moons and three new rings.

A few weeks ago, the Google Sheets team introduced improved control over protected sheets and ranges. Developers have told us theyre looking for the same power in Google Apps Script — after all, ever since we added data validation to the Spreadsheet service, programmatic control over protected ranges has been the most popular request on the Apps Script issue tracker.

Today, we are excited to give you that granular control.

With the new Protection class in the Spreadsheet service, your scripts can touch every aspect of range or sheet protection, just like in the new UI. (The older PageProtection class, which had more limited features, will be deprecated, but will stick around in case you need to work with older spreadsheets. The new Protection class only applies to the newer version of Sheets.)

Code samples

So lets see the new stuff in action. Lets say you want to prohibit anyone other than yourself from editing cells A1:B10:

// Protect range A1:B10, then remove all other users from the list of editors.
var ss = SpreadsheetApp.getActive();
var range = ss.getRange(A1:B10);
var protection = range.protect().setDescription(Sample protected range);

// Ensure the current user is an editor before removing others. Otherwise, if the users edit
// permission comes from a group, the script will throw an exception upon removing the group.
var me = Session.getEffectiveUser();
protection.addEditor(me);
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
  protection.setDomainEdit(false);
}

Or maybe you want to remove all range protections in the whole spreadsheet:

// Remove all range protections in the spreadsheet that the user has permission to edit.
var ss = SpreadsheetApp.getActive();
var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for (var i = 0; i < protections.length; i++) {
  var protection = protections[i];
  if (protection.canEdit()) {
    protection.remove();
  }
}

Or perhaps you want to protect an entire sheet, but carve out a small hole in it — an unprotected range within a protected sheet — that others can still edit:

// Protect the active sheet except B2:C5, then remove all other users from the list of editors.
var sheet = SpreadsheetApp.getActiveSheet();
var protection = sheet.protect().setDescription(Sample protected sheet);
var unprotected = sheet.getRange(B2:C5);
protection.setUnprotectedRanges([unprotected]);

// Ensure the current user is an editor before removing others. Otherwise, if the users edit
// permission comes from a group, the script will throw an exception upon removing the group.
var me = Session.getEffectiveUser();
protection.addEditor(me);
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
  protection.setDomainEdit(false);
}

Bam! Easy. Hope you find this useful, and happy scripting!

Posted by Sam Berlin, engineer, Google Sheets

Have you ever written a particular piece of code over and over again?  Or used scripts to do something that you thought others might want to do as well?  Starting today, you’ll be able to share and reuse those scripts as libraries, right from inside Google Apps Script.

Why use a Script Library

I often write scripts which check the National Weather Service for relevant weather-related information.  This allows me to send myself an email if it’s going to rain, reminding me to bring an umbrella to work, or to annotate my spreadsheet of running workouts with the temperature of the day.

Remembering how to query the National Weather Service every time I write a script is a daunting task, however.  They have a complicated XML format that  is tricky to parse.  As a result, I end up just copying and pasting code each time.  This is not only error-prone, but also has the big disadvantage that I have to fix all of my scripts one by one whenever the Weather Service’s XML format changes.

The code I use to query the National Weather Service is a perfect use case for a library.  By using a library, I no longer have to copy and paste code in my script project.  Since logic is centralized, updates need to be applied just once.  And now I am able to share my library with other developers who can benefit from the work I’ve already done.

Writing a Library

Libraries are written just like any other Apps Script project.  A good library has a clean API which is also well documented.  Here’s a code snippet from my WeatherService library:

/**
 * Queries the National Weather Service for the weather
 * forecast of the given address. Example:
 * 
 * <pre>
 * var chances = WeatherService
 *                 .getPrecipitation("New York, NY");
 * var fridayChance = chances[“Friday”];
 * Logger.log(fridayChance + “% chance of rain on Friday!”);
 * </pre>
 * 
 * @param {String} address The address to query the
 *          temperature for, in any format accepted by
 *          Google Maps (can be a street address, zip
 *          code, city and state, etc)
 *          
 * @returns {JsonObject} The precipitation forecast, as
 *          map of period to percentage chance of
 *          precipitation. Example:
 * 
 * <pre>
 * { Tonight: 50, Friday: 30, Friday Night: 40, ... }
 * </pre>
 */
function getPrecipitation(address) {
  // Code for querying weather goes
  // here...
}

Notice how detailed the documentation is. We know that good documentation makes for a great library. So, for every library Apps Script will also auto-generate a documentation page based on the code comments using the JSDoc format.  If you want a method in your code to not be exposed to users, simply end its name with an underscore.

Saving Versions

Before code can be used as a library, a version of it needs to be saved.  Versions are a new concept in Apps Script, and they represent a snapshot of your project which won’t change even as changes are made to the script code. Versions are useful because they allow you to change your library code without breaking existing users.  Once you’re happy with the changes you’ve made, you can then save a new version. Please see the user guide for saving a version and sharing your code as a library is easy.

Using Libraries

Using a library only takes a few steps. To be able to use a library, the owner of the library must share the library and its project key with you. You can follow these instructions to then use a library. To use this National Weather Service library, please visit this page for project key.

Useful Features of Libraries

Script Libraries come with three interesting features.

1. Documentation - In the Script Libraries dialog, you can click on the title link to navigate to documentation page for the library. See example of a generated documentation.
2. Development Mode can be used to test changes to a library without saving a new version. See our User Guide for more details
3. Autocomplete in Script Editor - Typing in the editor will auto-complete your library function names.

Interesting Libraries You Can Use

To get started on using Script Libraries, you can find a list of useful libraries contributed by two of our top contributors - James Ferreira and Romain Vialard. You can also find a detailed user guide on managing versions and libraries. We hope you enjoy using libraries.

Gustavo Moura Gustavo has been a Software Engineer at Google since 2007. He has been part of the Google Docs team since 2009. Prior to that, he worked on AdWords. In his free time he plays soccer.

Editor’s Note: Guest author Steve Webster works at Dito. Dito has developed applications such as Dito Directory which is available in the Google Apps Marketplace.

When composing Gmail conversations, the auto-complete feature allows us to see our matching personal contacts as we type and quickly make our contact selections. This time-saving feature can be duplicated when creating Google Apps Script applications. For instance, if you design an application that requires sending emails, you can leverage this auto-complete feature by using a personal contact list.

Defining the Requirements

By observing the behavior while composing Gmail conversations, we can define the requirements of our application.

1. As the user begins typing, a list of matches based on first and last name and email address need to appear under the text box. In other words, the user can begin typing the contacts first name, last name, or their email address.

2. If the desired contact email is listed at the top of the matching list, the user can simply press the Enter key to select it.

3. Another option is to click on any of the contacts in the list.

4. Just in case the user would like to enter an email that is not in their contact list, they may enter the email and press the Enter key.

As an added feature if the email is not formatted correctly, then the invalid email is ignored and not selected. For our application when emails are selected, they will be compiled in a separate list on the right where only the email address is stored. If an email is selected by accident, the user can remove the email by clicking on it.

Designing the Application

The application was designed to mimic the behavior of composing Gmail messages. By doing so, the application avoided the use of buttons, providing an improved user experience.

1. Apps Script Services

The Apps Script’s Spreadsheet Service was used to store a user’s contact data. The Ui Service provided the application interaction with the user, and the Contacts Service was leveraged to gather all the user’s contacts. You may apply a Google Apps domain only filter for the contacts by changing the global variable to “true” in the script.

2. Visualize the Layout

Before writing code, the layout was sketched out to include a text box, some space beneath to list matches, and an area to the right to display the selected emails.

3. Choose your widgets

A text box widget was chosen to allow email entry, and two open list boxes were leveraged to display contact matches and selected emails. List boxes provided the use of click handlers to process email selections.

4. Challenges

To mimic the Gmail auto-complete behavior, the text box needed the ability to handle both keystrokes and a pressed Enter key. To accomplish this, a KeyUpHandler calls a function to identify contact matches via a search. The same function used an e.parameter.keyCode == 13 condition to determine when the enter key is pressed.

//create text box for auto-complete during email lookup in left grid
  var textBox = app.createTextBox().setName(textBox)
                   .setWidth(330px).setId(textBox);
  var tBoxHandler = app.createServerKeyHandler(search_);
  tBoxHandler.addCallbackElement(textBox);
  textBox.addKeyUpHandler(tBoxHandler);
   ...
function search_(e){
  var app = UiApp.getActiveApplication();
  app.getElementById(list).clear(); 
  var searchKey = new RegExp(e.parameter.textBox,"gi");
  if (searchKey == "") app.getElementById(textBox).setValue();
  var range = sheetOwner.getRange(1, 1, sheetOwner.getLastRow(), 2).getValues();
  var listBoxCount = 0;
  var firstOne = true;
  for (var i in range){
     // if first/last name available, display name and email address
     if (range[i][0].search(searchKey) != -1 || range[i][1].search(searchKey) != -1){
       if (range[i][0].toString()){
          app.getElementById(list).addItem(range[i][0].toString()+
           .. +range[i][1].toString(), range[i][1].toString()); 
          var listBoxCount = listBoxCount + 1;
       } else { // else just display the email address
          app.getElementById(list).addItem(range[i][1].toString()); 
          var listBoxCount = listBoxCount + 1;
       }
       if (firstOne) var firstItem = range[i][1].toString();
       var firstOne = false;
     }  
  }
  // set the top listbox item as the default
  if (listBoxCount > 0) app.getElementById(list).setItemSelected(0, true);
  // if enter key is pressed in text box, assume they want to add
  // the email that’s not in the list
  if (e.parameter.keyCode==13 && listBoxCount < 1 && searchKey !== "") { 
  ...

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

You can find Dito’s Email Auto-Complete Script here. To view a video demonstration click here. You can also find Dito Directory on the Google Apps Marketplace.

Posted by Steve Webster, Dito

Want to weigh in on this topic? Discuss on Buzz