Tutorial

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.

Search engines have been using structured data for years to understand the information on web pages and provide richer search results. Today, we are introducing schemas in emails to make messages more interactive and allow developers to deliver a slice of their apps to users’ inboxes.

Schemas in emails can be used to represent various types of entities and actions. Email clients that understand schemas, such as Gmail, can render entities and actions defined in the messages with a consistent user interface. In the case of Gmail, this means that the emails can display quick action buttons that let users take actions directly from their inboxes, as in the following screenshot:

Using schemas to add quick action buttons to the emails you send is easy. All it takes is adding some markup to your HTML emails, together with your regular content, in one of the supported formats - Microdata and JSON-LD.

As an example, the following JSON-LD markup can be used to define a movie and the corresponding one-click action to add the movie to your queue:

<script type="application/ld+json">
{
  "@context": "schema.org",
  "@type": "Movie",
  "name": "The Internship",
  ... information about the movie ...
  "action": {
    "@type": "ConfirmAction",
    "name": "Add to queue",
    "actionHandler": {
      "@type": "HttpActionHandler",
      "url": "https://my-movies.com/add?movieId=123",
      "method": "POST",
    }
  }
}
</script>

Gmail renders the markup above with a button labelled “Add to queue” next to the email subject line. When the user clicks on the button, Gmail sends a POST request to the url specified in the action handler. Your app has to handle these requests and respond to the email client with an appropriate HTTP response code (200 for successful requests, 400 for invalid requests, etc.).

Schemas in emails currently support four different types of actions - rate/review, RSVP, one-click action and goto link - and we plan to add more types moving forward. We are collaborating with a number of partners who will launch their integrations in the coming weeks, making the messages they send more useful and interactive for Gmail users. For example, Esna is using this to inform users of missed calls and provide them with a one-click button to be called again, while Seamless is implementing the rate/review action to collect feedback about restaurants.

Other partners who are already implementing schemas in email today include both Billguard, Concur Technologies, Docusign, HelloSign, Insight.ly, Mailchimp, myERP, Netflix, OpenTable, Orangescape, Paperless Post, Spotify, SugarCRM, and Tripit.

To learn more about all supported entities and actions and to find out how to get started with schemas in email, visit http://developers.google.com/gmail.

Claudio Cherubino   profile | twitter | blog Claudio is an engineer in the Google Drive Developer Relations team. Prior to Google, he worked as software developer, technology evangelist, community manager, consultant, technical translator and has contributed to many open-source projects. His current interests include Google APIs, new technologies and coffee.

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

We are currently rolling out a change to the organization of existing resources in collections in Google Docs. This change is completely transparent to users of the Google Docs web user interface, but it is technically visible when using the Google Documents List API to make requests with the showroot=true query parameter or specifically querying the contents of the root collection. In order to understand this change, first read how Google Docs organizes resources.

The change involves Google removing those resources from a user’s root collection that already exist within another collection accessible to the given user. That is, if “My Presentation” is currently in root and in the “My Talks” collection, after this change it will only exist in the “My Talks” collection.

We are making this change in order to make the organization of resources less confusing for API developers. This change allows clients to know that a resource either exists in root or in some collection under root. Clients can still retrieve all resources, regardless of which collections they’re in, using the resources feed.

The change is rolling out gradually to all Google Docs users over the next few months.

Developers with further questions about this change should post in the Google Documents List API forum.

Russ Jorgensen LinkedIn Russ Jorgensen joined Google in 2010 and is responsible for supporting and enhancing APIs which third-party applications can use to access and manage users collections of Google Docs. Prior to working at Google, Russ was an embedded software engineer for 22 years at Bell Labs building telecommunications products such as PBXs and wireless communication systems.