Google Apps Developer Blog

Introducing Versions and Libraries in Apps Script

2012-05-22T06:48:00.000-07:00

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 s aving 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.

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.
Development Mode can be used to test changes to a library without saving a new version. See our User Guide for more details
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.

MindMeister mind mapping and Google Drive

2012-05-21T08:13:00.000-07:00

Editor’s note: This is a guest post by Laura Bârlădeanu, lead programmer at MindMeister.
-- Steve Bazyl

MindMeister is a market innovator for providing collaborative online mind mapping solutions. Launched in May 2007, our site has since attracted hundreds of thousands of businesses, academic institutions and creative consumers who have mapped over 100 million ideas online. We were one of a few web applications invited to take part in the Google Drive launch earlier this year.

Requirements

The goal was to provide users with an intuitive integration between Google Drive and Mindmeister that would cover all the cases provided by the Google Drive guidelines at that time:

Create mind maps directly from Google Drive
Open mind map files from Google Drive using MindMeister
Create a mind map file on Google Drive from MindMeister

Aside from these main integration points, we wanted to make use of the SDK and provide many useful Google Drive features, so we added a few more requirements to the list:

Export all the user’s maps as a backup .zip file on Google Drive
Import a file from Google Drive using the Google File Picker
Attach a file from Google Drive directly to a node in a mind map
Provide users the possibility to share mind maps with their Google contacts
Provide users with an application setting that would allow them to sync all their mind maps with Google Drive
Allow Google users opening the same file from Google Drive to collaborate in real time on the mind map directly in MindMeister
Enable users to login with their Google account without providing any extra information

Authentication and authorization

Google Drive applications are required to use OAuth 2.0 as an authorization mechanism, and are recommended to use OpenID Connect for login. The authorization scope for Drive files is added by default for all registered drive apps. Additionally, the application can require extra scopes that would fit its needs. For our requirements, we needed the following scopes:

https://www.googleapis.com/auth/drive.file (Drive)
https://www.google.com/m8/feeds/ (Contacts)
https://www.googleapis.com/auth/userinfo.profile (User information)
https://www.googleapis.com/auth/userinfo.email (User email)

However, we didn’t want the user to turn away from our application by being asked for too many scopes straight from the beginning. Instead, we defined sets of actions that required a subset of these scopes:

[‘drive’, ‘profile’, ‘email’] - any Google Drive action
[‘profile’, ‘email’] - login with a Google account
[‘contacts’, ‘profile’, ‘email’] - access the user’s Google contacts

Whenever the user wanted to execute an action that would require more scopes than they initially provided, we redirected them to a Google authorization dialog that requested the extra scope. Upon authorization, we stored the individual refresh tokens for each combination of scopes in a separate model (UserAppTokens).

Whenever the application needed the refresh token for a set of scopes (eg. for [‘profile’, ‘email’]) it would fetch the refresh token from the database which corresponded to a superset of the required scopes (eg. [‘drive’, ‘profile’, ‘email’] would fit for the required [‘profile’, ‘email’]). The access token would then be obtained from Google and stored in the session for future requests.

Challenges

The main challenge we encountered during design and implementation was dealing with the special cases of multiple users (Google users or internal users) editing on the same map which is a Google Drive file, as well as dealing with the special cases of the map being edited in multiple editors. We also had to find a solution for mapping the Google Drive user’s permissions (owner, reader, or writer) to the MindMeister’s existing permission mechanism.

The MindMeister application is registered for opening four types of files: our own .mind format, MindManager’s .mmap format, Freemind’s .mm format, as well as .xmind. However, since these formats are not 100% compatible with each other, there is always a chance of losing more advanced features when opening a file in a format other than .mind. We wanted to provide the user with the possibility to chose whether the opened file would be saved in its original format, thus risking some features loss, or saving the file in MindMeister format. This option should be per user, per file and with the possibility to be remembered for future files.

Solutions

After analyzing the requirements and the use cases, we designed the following architecture:

Out of sync maps and files

Using the revision fields in both Map and DriveData we always know if the map has been edited on MindMeister’s side without it being synced with the corresponding file on Google Drive. On the other hand, the token field from DriveData represents the file’s MD5 checksum at the moment of the last update and is supplied via the Google Drive SDK. So if the file is edited externally using another application than MindMeister, we have a mechanism in place for detecting this issue and presenting the user with a few courses of action.

Handling 3rd party formats

Upon opening a file that has a different format than .mind, the user is prompted with an option dialog where they can chose if they want the file to be saved back in the same format or in MindMeister’s own format. These options are then remembered in the current session and the per map settings are stored in the extension (the original format) and save_extension (the format to save back in) fields present in the DriveData model.

Handling user’s permissions

A map on MindMeister can always be shared with other MindMeister users and the collaborators can have reading or writing access to the map. However, only some of these users will have a corresponding Google account with access to the MindMeister Google Drive application and furthermore, only some of them will have access to the same file on Google Drive with writing permission. This is why it is important for us to know which users can write back to the file and the solution for these access levels was achieved with the help of the permission field in the DriveDataRight model.

Results

Now more than two weeks on from the Google Drive launch and we can confidently say that our integration was successful, with more than 14,000 new users using Google login and with over 7,000 users that have enabled the Google Drive integration. All in all, the Google Drive SDK was very easy to use and well documented. The developer support, especially, was always there to help and our contacts were open to our suggestions.

Laura Bârlădeanu

Laura is the lead programmer at MindMeister, an online mind mapping tool built in HTML5 that features real-time collaboration.

Insider Tips for Using Apps Script and Spreadsheets

2012-05-09T14:56:00.001-07:00

My role in Google Docs is to help manage many projects across Google Docs/Drive. As a part of my job, I ask for a fair amount of data from all of those teams and generate reports on project/feature status. To make this much simpler for everyone involved, I have created a lot of simple tools using Google Spreadsheets and Apps Script (as well as a lot of complex tools) that make it easier for collaborators to enter data and for me to collect that data and create reports. Below is a pair of foundational techniques that I include in nearly every Spreadsheet/Apps Script tool I build.

Load Settings From a Configuration Sheet

I have a dozens of scripts generating reports. I use a technique where I set up a dedicated sheet for script configuration and read values from the sheet during script execution. A simple configuration sheet makes this much more straightforward.

With a globally accessible array, globals, you can then load the “settings” from the configuration (sheet SHT_CONFIG, here) at any entrypoint to the script.

// globally accessible variables
var SHT_CONFIG = 'Config';
var globals = new Array();

function entryPoint() {
  globals = (globals.length == 0) ? LoadGlobals(
      SpreadsheetApp.getActive(), SHT_CONFIG)
      : globals;
  // your code goes here
}

The LoadGlobals function, below, parses the data in the first three columns of the workbook and sheet name passed to it. You can even include a fourth column (or more!) explaining what the variables do, and they’ll just be ignored - though hopefully not by your users!

// Generate gloabal variables to be loaded into globals array
function LoadGlobals_(wb, configSheet) {
  var configsheet = wb.getSheetByName(configSheet);
  var tGlobals = new Array();

  // Config data is structured as VARIABLE, ISARRAY, VALUE(S)
  // and includes that as the header row
  var cfgdata = configsheet.getDataRange().getValues();
  for (i = 1; i < cfgdata.length; i++) {
    switch (cfgdata[i][1]) {
      case 'ARRAY':
        // treat as an array - javascript puts a null value in the
        // array if you split an empty string...
        if (cfgdata[i][2].length == 0) {
          tGlobals[cfgdata[i][0]] = new Array();
        } else {
          tGlobals[cfgdata[i][0]] = cfgdata[i][2].split(',');
        }
        break;
      // Define your own YOURDATATYPE using your customTreatment function (or
      // just perform the treatment here)
      case 'YOURDATATYPE':
        tGlobals[cfgdata[i][0]] = customTreatment(cfgdata[i][2]);
        break;
      default: // treat as generic data (string)
        tGlobals[cfgdata[i][0]] = cfgdata[i][2];
    }
  }
  return tGlobals
}

As long as you’ve loaded the global values during the script execution, you can refer to any of the values with, for example, globals.toList. For instance:

function getToList() {
  return globals.toList.join(‘,’);
  // or
  return globals[‘toList’].join(‘,’);
}

Stop Worrying About Column Numbers

Asking colleagues to enter tracking data so they don’t have to report their own statuses is one thing. Asking them to enter tracking data in a specific format, within a specific column layout, in a way that doesn’t mesh with their existing processes is entirely different. So, I use the following technique, where I rely on column names and not column ordering. The code below lets me do just that by fetching a key-value object for column headings and their position in a worksheet.

// Returns key-value object for column headings and their column number.
// Note that these are retrieved based on the array index, which starts at 0
// the columns themselves start at 1...
// pass header row of data (array) and an array of variables/column names:
// eg: BUG_COL_ARRAY['id'] = 'Id';
function ColNumbers(hArray, colArray) {
  for (oname in colArray) {
    this[oname] = getColIndex(hArray, colArray[oname]);
  }
}

// -----------------------------------------------------------------------------
function getColIndex(arr, val) {
  for ( var i = 0; i < arr.length; i++) {
    if (arr[i].toLowerCase() == val.toLowerCase()) {
      return i;
    }
  }
  return -1;
}

With the associative array defined, below, I can ask Apps product managers to add (or rename) columns to their feature tracking sheets and then extract features from every apps product team in one fell swoop (a future post). Each product team can set their columns up in whatever order works best for them.

// key columns in the app feature sheets
var COLS_KEYAPPCOLS = new Array();
COLS_KEYAPPCOLS[‘feature’] = ‘Feature Title’;
COLS_KEYAPPCOLS[‘desc’] = ‘Description’;
COLS_KEYAPPCOLS[‘visible’] = ‘Visible’;
COLS_KEYAPPCOLS[‘corp’] = ‘Corp Date’;
COLS_KEYAPPCOLS[‘prod’] = ‘Prod Date’;

What does this do for me, really? I reuse this code for every project of this sort. The steps to reuse are then:

Include the code
Build the associative array
Create a new ColNumbers object, as below

var curFeatures = curSheet.getDataRange().getValues();
var curCols = new ColNumbers(curFeatures[0], COLS_KEYAPPCOLS);

I can, from now on, refer to the Description column using something like curCols.desc when referencing any of the products’ data. The Spreadsheets team may list new feature descriptions in the second column, and the Documents team may list new feature descriptions in the fourth column. I no longer worry about that.

As a bonus, I can define the columns and ordering to be used in a report in my config sheet (see above). If I’ve defined reportcols as feature, desc, prod in my config sheet, I can generate a report very simply:

// Iterate through the rows of data, beginning with 1 (0 is the header)
for ( var fnum = 1; fnum < curFeatures.length; fnum++) {
  // Iterate through each of the fields defined in reportcols
  for ( var cnum = 0; cnum < globals.reportcols.length; cnum++) {
    outputvalue = curFeatures[fnum][curCols[globals.reportcols[cnum]]];
    // outputvalue is what you want to put in your report.
  }
}

You could do that a lot more simply, but if we want to use the ‘Corp Date’ I only need to change the value in the config sheet to feature, desc, corp and I’m done - you’d have to change the code.

Collecting and crunching data in a Google Spreadsheet becomes a lot easier if you use Apps Script. Trust me, it makes your life a lot easier. Try it now by copying this spreadsheet

Keith Howson

Editor’s Note: Keith is a Technical Program Manager with the Google Docs teams. He is a heavy user of Apps Scripts and Spreadsheets, and leverages the two to help the Docs teams run at full steam. He also just completed his first triathlon with Team in Training, to raise money for the Leukemia and Lymphoma Society - Go TEAM!

Going beyond the basics: putting Drive features to work at Lucidchart

2012-05-08T14:24:00.000-07:00

Editor’s note: This is a guest post by Ben Dilts, CTO & Co-founder of Lucidchart.
-- Steve Bazyl

The release of Drive SDK allowing deep integration with Google Drive shows how serious Google is about making Drive a great platform for third parties to develop.

There are a handful of obvious ways to use the SDK, such as allowing your users to open files from Drive in your application, edit them, and save them back. Today, I'd like to quickly cover some less-obvious uses of the Drive API that we’re using at Lucidchart.

Automated Backups

Applications have the ability to create new files on Google Drive. This is typically used for content created by applications. For example, an online painting application may save a new PNG or JPG to a user's Drive account for later editing.

One feature that Lucidchart has long provided to its users is the ability to download their entire account's content in a ZIP file, in case they (or we!) later mess up that data in some way. These backups can be restored quickly into a new folder by uploading the ZIP file back to our servers. (Note: we’ve never yet had to restore a user account this way, but we provided it because customers said it was important to them.)

The problem with this arrangement is that users have to remember to do regular backups, since there's no way for us to automatically force them to download a backup frequently and put it in a safe place. With Google Drive, we now have access to a reliable, redundant storage mechanism that we can push data to as often as we would like.

Lucidchart now provides automated backups of these ZIP files to Google Drive on a daily or weekly basis, using the API for creating new files on Drive.

Publishing Content

Another use for the files.create call is to publish finished content. Lucidchart, like most applications, stores its editable files in a custom format. When a user completes a diagram or drawing, they often download it as a vector PDF, image, or Microsoft Visio file to share with others.

Lucidchart is now using the create file API to export content in any supported format directly to a user's Google Drive account, making it easy to sync to multiple devices and later share those files.

Indexable Text

Google Drive can't automatically index content created by Lucidchart, or any other application that saves data in a custom format, for full-text search. However, applications now have the ability to explicitly provide HTML content to Google Drive that it can then index.

Indexable text provided to the Drive API is always interpreted as HTML, so it is important to escape HTML entities. And if your text is separated into distinct pieces (like the text in each shape in Lucidchart), you can improve full-text phrase searching by dividing your indexable text into one div or paragraph element per piece. Both the files.create and files.update calls provide the ability to set indexable text.

We hope that this overview helps other developers implement better integrations into the Google Drive environment. Integrating with Drive lets us provide and improve a lot of functionality that users have asked for, and makes accessing and using Lucidchart easier overall. We think this is a great result both for users and web application developers and urge you to check it out.

Ben Dilts

Ben is the co-founder and CTO of Lucidchart, an online diagramming application built in HTML5 that features real-time collaboration. Previously, he was CTO of Zane Benefits where he led the development of an online health benefits administration platform which was featured on the front page of The Wall Street Journal. Ben holds a BS in Computer Science from Brigham Young University.

Apps Script Dashboard and Quotas

2012-05-07T06:52:00.000-07:00

Apps Script developers have consistently expressed the need to monitor the health of various Apps Script services. Additionally, at every forum, event, hackathon or hangout, we have heard you express a need to know and understand the quota limits in Apps Script.

Apps Script Dashboard is born!

We heard your message loud and clear, so we started working on a dashboard for Apps Script. Today, we are launching the Google Apps Script Dashboard. This experimental dashboard can be used to monitor the health of 10 major services. It also provides a detailed view into the quota restrictions in Apps Script.

Features of the Apps Script Dashboard

The dashboard offers a view into past and present states of 10 major Apps Script services. The past view goes back one week.
Each Apps Script service has three states on the dashboard: Normal Service, Known Issues and Investigating.
The Known Issues state signals that we know about the issues in that service and are working to fix them.
Quotas are displayed for three different types of user accounts: Consumer accounts (for example @gmail.com accounts), Google Apps (free) accounts, and Google Apps for Business, EDU and Government accounts.

Interesting Facts About Quotas

Did you know that consumer accounts (for example @gmail.com accounts) have quota of 1 hour of CPU time per day for executing triggers? Imagine the extent of automation that can happen for each user with triggers. And how about 20,000 calls to any external APIs. Now that packs in a lot of 3rd party integration with the likes of Salesforce.com, Flickr, Twitter and other APIs. So, if you are thinking of building extensions in Google Apps for your product, then don’t forget to leverage the UrlFetch Service which has OAuth built-in. Event managers can create 5,000 calendar events per day and SQL aficionados get 10,000 JDBC calls a day.

Check out the Dashboard for more.

Saurabh Gupta profile | twitter | blog

Saurabh is a Developer Programs Engineer at Google. He works closely with Google Apps Script developers to help them extend Google Apps. Over the last 10 years, he has worked in the financial services industry in different roles. His current mission is to bring automation and collaboration to Google Apps users.

How we built a Social Network for Productivity on Top of Google Docs, Calendar, Contacts and Reader

2012-05-02T10:36:00.003-07:00

Editor’s note: This is a guest post by Martin Böhringer, Co-Founder and CEO of Hojoki.
-- Steve Bazyl

Honestly, we love Google’s productivity tools. Back in 2010, when I was a computer science researcher at university, I was a heavy user of Google Docs, Google Calendar, Google Contacts and Google Reader. So it was the most natural thing to put these tools to the front for integrations into our startup, Hojoki.

Hojoki integrate productivity cloud apps into one newsfeed and enables sharing and discussions on top of the feed. We’ve integrated 17 apps now and counting, so it’s safe to say that we’re API addicts. Now it's Time to share with you what we learned about the Google Apps APIs!

The goal: a newsfeed

Our initial reason for building Hojoki was because of the fragmentation we experience in all of our cloud apps. And all those emails. Still, there was this feeling of “I don’t know what’s going on” in our distributed teamwork. So we decided to build something like a Google+ where streams get automatically filled by activities in the apps you use.

This leads to a comprehensive stream of everything that’s going on in your team combined with comments and microblogging. You can organize your stream into workspaces, which are basically places for discussions and collaboration with your team.

Information needs

To build this, we first need some kind of information on recent events. As we wanted to be able to aggregate similar activities and to provide a search, as well as splitting up the stream in workspaces, we also had to be able to sort events as unique objects like files, calendar entries and contacts.

Further, it’s crucial to not only know what has changed, but who did it. So providing unique identities is important for building federated feeds.

Google APIs

Google’s APIs share some basic architecture and structure, described in their Google Data Protocol. Based on that, application-specific APIs provide access to the application’s data. What we use is the following:

Google Docs: Google Documents List API version 3.0
Google Calendar: Google Calendar API v2
Note: there is a new API v3 for Google Calendar
Google Contacts: Google Contacts API v2

The basic call for Google Contacts for example looks like this:

https://www.google.com/m8/feeds/contacts/default/full

This responds with a complete list of your contacts. Once we have this list all we have to do is to ask for the delta to our existing knowledge. For such use cases, Google’s APIs support query parameters as well as sorting parameters. So we can set “orderby” to “lastmodified” as well as “updated-min” to the timestamp of our last call. This way we are able to keep the traffic low and get quick results by only asking for things we might have missed.

If you want to develop using those APIs you should definitely have a look at the SDKs for them. We used the Google Docs SDK for an early prototype and loved it. Today, Hojoki uses its own generic connection handler for all our integrated systems so we don’t leverage the SDKs anymore.

Real world problems

If you’re into API development, you’ve probably already realized that our information needs don’t fit into many of the APIs out there. Most of the APIs are object centric. They can tell you what objects are included in a certain folder, but they can’t tell you which object in this folder has been changed recently. They just aren’t built with newsfeeds in mind.

Google Apps APIs support most of our information needs. Complete support of OAuth and very responsive APIs definitely make our lives easier.

However, the APIs are not built with Hojoki-like newsfeeds in mind. For example, ETags may change even if nothing happened to an object because of asynchronous processing on Google’s side (see Google’s comment on this). For us this means that, once we detect an altered ETag, in some cases we still have to check based on our existing data if there really have been relevant activities. Furthermore, we often have trouble with missing actors in our activities. For example, up to now we know when somebody changed a calendar event, but there is no way to find out who this was.

Another issue is the classification of updates. Google’s APIs tell us that something changed with an object. But to build a nice newsfeed you also want to know what exactly has been changed. So you’re looking for a verb like created, updated, shared, commented, moved or deleted. While Hojoki calls itself an aggregator for activities, technically we’re primarily an activity detector.

Final solution

You can think of Hojoki as a multi-layer platform. First of all, we try to get a complete overview on your meta-data of the connected app. In Google Docs, this means files and collections, and we retrieve URI and name as well as some additional information (not the content itself). This information fills a graph-based data storage (we use RDF, read more about it here).

At the moment, we subscribe to events in the integrated apps. If detected, they create a changeset for the existing data graph. This changeset is an activity for our newsfeed and related to the object representation. This allows us to provide a very flexible aggregation and filtering on the client side. See the following screenshot. You can filter the stream for a certain collection (“Analytics”) or only for the file history or for the Hojoki workspace where this file is added (“Hojoki Marketing”).

What’s really important in terms of such heavy API processing is to use asynchronous calls. We use the great Open Source project async-http-client for this task.

A wishlist

When I wrote that “we subscribe to events” this is a very nice euphemism for “we’re polling every 30s to see if something changed”. This is not really optimal and we’d love to change it. If Google Apps APIs would support a feed of user events modelled in a common standard like ActivityStrea.ms, combined with reliable ETags and maybe even a push API (e.g. Webhooks) this would also make life easier for lots of developers syncing their local files with Google and help to reduce traffic on both sides.

Martin Böhringer

Martin is Co-Founder and CEO of Hojoki. Hojoki integrates Google Docs, Calendar, Contacts and Reader next to other apps like Dropbox and Evernote into one newsfeed, showing changes by co-workers in one central place. Martin holds a PhD in information systems and likes to perform on stage at Tech Events.

Introducing Google Drive and the Google Drive SDK

2012-04-24T09:42:00.000-07:00

Editor's note: This post is cross-posted from the Google Developers Blog.

Today, we're announcing Google Drive—a place where people can create, share, collaborate and keep all of their stuff. Drive is a natural step in the evolution of Google Docs. Drive is built to work seamlessly with other Google applications like Google+, Docs and Gmail, and your app can too. Joining the launch today are 18 web apps that have integrated with Drive using the Google Drive SDK.

Integrating your application with Google Drive makes it available to millions of users. Drive apps are distributed from the Chrome Web Store, and can be used with any modern browser. Plus, your app can take advantage of Google's sharing, storage, and identity management features.

Create and collaborate

Google Drive allows for more than storage. Google Docs is built right into Drive, and your app can join the party. For example, Lucidchart is an online visual diagramming tool integrated with Google Drive. You can start a new Lucidchart or share your diagrams with friends or coworkers straight from Drive, just like a Google document or spreadsheet.

Store everything safely and access it everywhere

With Google Drive you can store all of your files and access them from anywhere. For example, MindMeister, an app for creating mind maps online, also lets you open files from popular desktop mind mapping applications. By integrating with Google Drive, MindMeister users can open their mind maps stored in Drive from any modern browser.

Search everything

Your app can also take advantage of Drive's storage, indexing, and document viewers. For example, HelloFax is a web application that lets you sign and fax documents from your browser. HelloFax users can now store all their inbound and outbound faxes in Google Drive, making them easy to find later. Plus, with automatic OCR, users can even search and find text in faxed images. Your application can store files of any type up to 10 GB in size or create file-like shortcuts to your application's data.

Want your application to work with Google Drive? Full documentation on the Google Drive SDK is available at developers.google.com/drive, or if you're itching to start building, head to our Getting Started guide. Our team will be on Stack Overflow to answer any questions you have when integrating your app with Google Drive. You can also bring your questions to our Hangout this Thursday at 10:30 AM PDT / 17:30 UTC.

Look for more posts about working with the Drive SDK on the Google Apps Developer Blog in the coming weeks.

Mike Procopio profile

Mike is a Software Engineer for Google Drive, focusing on all things Drive apps. He gets to leverage his passion for the developer and user experience by working on the next-generation APIs that help unleash Google Drive. Before joining Google in 2010, he was a machine learning researcher, and enjoys engaging in illuminating statistical discussions at every opportunity.

Approval Workflow using Apps Script

2012-04-17T15:08:00.000-07:00

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.

Crowd Sourcing with Google Forms and Fusion Tables

2012-04-13T09:39:00.000-07:00

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.

Introducing the Script Service

2012-04-05T12:00:00.000-07:00

Triggers in Apps Script allow you to schedule your script’s execution in response to an event, at a specific time and date, or at given time intervals. Publishing scripts as services allows you to deploy your web apps with a click of a button. The new Script service lets you perform both of these tasks programmatically. With the Script service at your disposal, you can create smooth install flows, chain your scripts’ executions, generate summaries of current triggers, and even programmatically publish the scripts as services.

Create smooth install flows

You can now set up your triggers from a menu. Here is an example of how you would schedule function foo() to run one year from now via a custom menu in Google Spreadsheets:

function onOpen() {
  SpreadsheetApp.getActive()
    .addMenu("Setup",
             [{name: "Create Trigger", 
               functionName: "createTrigger"}]);
}

function createTrigger() {
  var now = new Date();
  ScriptApp.newTrigger("foo")
           .timeBased()
           .atDate(parseInt(now.getFullYear())+1, 
                            now.getMonth(),
                            now.getDate())
           .create();
}

Chain script executions

By programmatically setting triggers, you are able to schedule a future script’s executions from the script that is currently running. Here is an example of a function that schedules another function to execute two hours after it completes:

// this function is run to send the initial reminder and schedule a subsequent one
function sendRequest() {
  GmailApp.sendEmail("class@example.edu",
                     "Assignment #1 is due in 2 and a half hours!",
                     "Your first assignment is due in 2 and a half hours!");
  var now = new Date();
  var later = new Date(now.getTime() + 2 * 60 * 60 * 1000);
  ScriptApp.newTrigger("sendReminder").timeBased().at(later).create();
}

// this function will execute two hours later, sending another reminder
function sendReminder() {
  GmailApp.sendEmail("class@example.edu",
                     "Assignment #1 is due in 30 minutes!",
                     "Your first assignment is due in half an hour!");
}

Generate a summary of current triggers

With the ability to schedule triggers programmatically, it is important to keep track of the existing ones. The function below logs all the triggers that are associated with the current script. Alternatively, you could also write this summary to a spreadsheet or email it to yourself.

function logMyTriggers() {
  var triggers = ScriptApp.getScriptTriggers();
  for(i in triggers) {
    Logger.log("Trigger ID: " + triggers[i].getUniqueId()
               + "\nTrigger handler function: "
               + triggers[i].getHandlerFunction()
               + "\nTrigger type: " + triggers[i].getEventType()
               + "\n----------------------");
  }
}

Publish scripts programmatically

You can now let your users publish your scripts through a Google Spreadsheets custom menu as a part of the script’s set up process. Below is an illustration of this process using an example of a simple note-taking web app. Once a user clicks the menu item, the script becomes published as a service and the URL is presented back to the user via a dialog box.

function doGet(e) {
  var app = UiApp.createApplication();
  createApp(app, false);
  return app;
}

function doPost(e) {
  var now = new Date();
  MailApp.sendEmail(Session.getEffectiveUser(),
                    "Notes taken at " + now, e.parameter.notes);
  var app = UiApp.getActiveApplication();
  createApp(app, true);
  return app;
}

function createApp(app, isSubmitted) {
  app.setWidth(550)
     .setHeight(700);
  var vp = app.createVerticalPanel();
  var title = app.createHTML("Enter your notes into the text area below." +
                             "<br>Hit submit to email them to yourself!")
                 .setStyleAttribute("fontSize", "25px");
  var lbl = app.createLabel("Notes submitted!")
               .setStyleAttribute("fontSize", "17px");
  var form = app.createFormPanel();
  var notes = app.createTextArea()
                 .setWidth("500px")
                 .setHeight("500px")
                 .setName("notes");
  var sb = app.createSubmitButton("Submit");
  vp.add(title)
    .add(notes)
    .add(sb);
  if (isSubmitted)
    vp.add(lbl);
  form.add(vp);
  return app.add(form);
}

function onInstall() {
  onOpen();
}

function onOpen() {
  SpreadsheetApp.getActive()
    .addMenu("Setup",
             [{name: "Publish as a service.", 
               functionName: "setup"}]);
}

function setup() {
  var url = "";
  try {
    ScriptApp.getService()
             .enable(ScriptApp.getService().Restriction.MYSELF);
    url = ScriptApp.getService().getUrl();
    Browser.msgBox("Your web app is now accessible at the following URL:\n"
                   + url);
  } catch (e) {
    Browser.msgBox("Script authorization expired.\nPlease run it again.");
    ScriptApp.invalidateAuth();
  }
}

The Script service provides you with the means of creating even more powerful applications and makes the development and deployment process smooth.

For more information, please visit the Script service Reference Documentation and User Guide.

Anton Soradoi profile

Anton is a Developer Support Specialist at Google. He works closely with Apps Script developers to help them discover its full potential. Prior to Google, he worked as a Web Developer.

A First Attempt at Apps Script with Spreadsheets

2012-03-30T08:20:00.000-07:00

The Apps Script team held a hackathon in Washington DC on March 7. Over 80 developers attended and we had some great demos at the end of the evening. One of the demos was from Rusty Mellinger, who explains his script in this blog post. If you missed the DC hackathon, sign up for our next one in Chicago on April 19. -Jan Kleinert

I was lucky enough to attend Google’s Apps Script Hackathon at their office in DC, recently, and got a chance to play with Apps Script. After a quick walk-through tutorial, Jan gave us a couple of hours to hack around with it.

Scripts in Apps Script are written in JavaScript and stored, edited, and run on Google's servers, interfacing with a big list of included services. You can call the scripts from spreadsheets, Google Sites, or from hits to a generated URL.

Roommate Payment Spreadsheet

My roommates and I keep a spreadsheet on Google Docs to track who owes what, but since we’re a house full of software engineers, it’s gotten pretty complicated. Each row records the details of a single transaction: who paid, the total, and what percentages of the payment are on behalf of which roommates. All these interpersonal debts are added up into the (J5:M8) matrix, cancelled out across the diagonal into (P5:S8) to get a single debt for each roommate pairing, and then those are totalled into the final "Shake Out", (F4:F7), which says whether you owe or are owed. Maybe Apps Script could make my life simpler here?

Automatic Emails

First, I’m currently owed a fair amount of money, so I set up automated reminder emails to the roommates who are behind:

// Send emails to everybody with their current status.
function emailDebtors() {
  var ss       = SpreadsheetApp.getActiveSpreadsheet();
  var results  = ss.getRange( "G4:G7" ).getValues();
  var emails   = ss.getRange( "O3:R3" ).getValues();
  var numUsers = 4;
  
  for(var i = 0; i != numUsers; i++) {
    var val = Math.round(results[i][0]);
    
    if (val > 0) {
      // This guy owes money in the shake-out.
      MailApp.sendEmail(
        emails[0][i], "You're a deadbeat!",
        "You owe $" + val + ". Please pay it!");
    }
  }
}

This just pulls the current totals from the (G4:G7) "Shake Out", as well as their respective email addresses from (O3:R3). When this function is called, if any of them owe more than $0, they get a friendly reminder!

Custom Menus

I could set that up to trigger daily or weekly, but it only really needs to happen when somebody needs to collect what they’re owed, so I’ve added it as an option to the sheet’s menu on start-up.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ { name: "Email debtors", 
                        functionName: "emailDebtors"}];
  ss.addMenu( "SkyCastle", menuEntries );
}

Easy! Now when somebody wants to collect, they just click the “SkyCastle -> Email debtors” option and the appropriate reminder emails are sent out, from their own Gmail address.

Historical Charting

I still had a couple of hours, and wanted to play with the UI and Google Charts services, so I decided to chart the “Shake Out” values over the history of the spreadsheet. The existing cells are hard-coded to operate on the total sums from the full sheet, so I had to re-implement the math to track it line-by-line. (This isn’t all bad, because I can use it to double check the existing arithmetic, which was sorely needed.)

The basic sketch is as follows:

var data = Charts.newDataTable()
                 .addColumn(Charts.ColumnType.NUMBER, "Row");
for (var i = 0; i != 4; i++) {
  data.addColumn(Charts.ColumnType.NUMBER, names[i]);
}

for (var i = 0; i != NUMROWS; i++) {
  var row = Array(5);
  // …
  // Process the current line here, and compute the shake-out.
  // …
  data.addRow(row);
}
data.build();

I’ve omitted the actual calculation, because it’s just a bunch of hacks specific to our spreadsheet formulas. Each row contains the row number, and the accumulated shake-out thus far, and gets added to the `data` table. I break out of the loop once I go off the end of my data and start hitting `NaNs`.

To create the line chart and add it to a new UI window:

var chart = Charts.newLineChart()
      .setDataTable(data)
      .setDimensions(700, 400)
      .setTitle("Debt History")
      .build();

var uiApp = UiApp.createApplication()
                 .setWidth(700)
                 .setHeight(400)
                 .setTitle("Payment History");
uiApp.add(chart);
ss.show(uiApp);
return uiApp;

After adding this function as another option in our custom `SkyCastle` menu and clicking it, we see a nice graph. (I’m almost always on the bottom, but that’s because I make the actual rent and utility payments.) The final entries are equal to the original "Shake Out" cells, so our old arithmetic seems correct, too.

Lessons Learned

The built-in debugger isn’t bad; use the `Select function` dropdown and click the bug icon. I also used Logger.log() liberally while trying to get things working right. (Go to `View -> Logs` in the Script Editor to view that output.)

Apps Script seems to work well, overall, and hooks into a nice and expanding array of Google products and data sources. The GWT-backed UI service is a clever idea, though I barely had a chance to touch it.

Thanks again to Jan and Google for hosting this Hackathon; I can’t wait for the next one!

Rusty Mellinger

Rusty Mellinger co-founded Illogic Inc, making heavy use of Google Apps and GWT.

Making PATCH requests from App Engine

2012-03-29T08:11:00.000-07:00

PATCH requests allow you to perform partial updates on many of our REST APIs and in most cases can save bandwidth.

If you have ever tried to do a PATCH request on an App Engine application, you probably realized that it is not possible and that the list of HTTP Methods allowed is whitelisted to the following methods only: GET, POST, HEAD, PUT and DELETE. Trying to perform a PATCH request raises the following Exception:

java.net.ProtocolException: PATCH is not one of the supported http methods: [GET, POST, HEAD, PUT, DELETE]

There is a workaround to this. Most of our APIs support the X-HTTP-Method-Override header. This header can be used in a POST request to “fake” other HTTP methods. Simply set the value of the X-HTTP-Method-Override header to the HTTP method you would like to actually perform.

For example, to make a PATCH request to the Google Tasks API to update only the Notes field of a particular task you could use the following HTTP request:

POST /tasks/v1/lists/@default/tasks/TASK_ID HTTP/1.1
Host: www.googleapis.com
X-HTTP-Method-Override: PATCH
Authorization:  Bearer 
Content-Type:  application/json
Content-Length: 31

{“Notes” : “Patch is working!”}

Which would be equivalent to this HTTP Request, which is not supported on App Engine:

PATCH /tasks/v1/lists/@default/tasks/TASK_ID HTTP/1.1
Host: www.googleapis.com
Authorization:  Bearer 
Content-Type:  application/json
Content-Length: 31

{“Notes” : “Patch is working!”}

For instance, in an App Engine Java environment you could construct and execute this request this way:

URL url = new URL("https://www.googleapis.com/tasks/v1/" +     
    "lists/@default/tasks/" + TASK_ID);
HTTPRequest request = new HTTPRequest(url, HTTPMethod.POST);
request.addHeader(new HTTPHeader("X-HTTP-Method-Override", "PATCH"));
request.addHeader(new HTTPHeader("Authorization", "Bearer " +
    ACCESS_TOKEN));
request.setPayload("{\"Notes\" : \"Patch is working!\"}".getBytes());

URLFetchService fetchService = URLFetchServiceFactory.getURLFetchService();
HTTPResponse response = fetchService.fetch(request);

This trick can also be used if your application server is behind a firewall, behind a proxy server or in any other environment where HTTP methods other than POST might not be allowed. In that case you could use the X-HTTP-Method-Override header the same way to workaround these limitations.

You may also use our Google APIs Client library for Java or our Google APIs Client library for Python, both of which have support for PATCH requests and use the X-HTTP-Method-Override header when appropriate.

Nicolas Garnier profile | twitter | events

Nicolas joined Google’s Developer Relations in 2008. Since then he's worked on commerce oriented products such as Google Checkout and Google Base. Currently, he is working on Google Apps with a focus on the Google Calendar API, the Google Contacts API, and the Tasks API. Before joining Google, Nicolas worked at Airbus and at the French Space Agency where he built web applications for scientific researchers.

Integrating Google Docs with Salesforce.com using Apps Script

2012-03-12T15:01:00.000-07:00

Editor's Note: Ferris Argyle is going to present Salesforce Workflow Automation with Google Spreadsheet and Apps Script at Cloudforce. Do not miss Ferris's talk - Saurabh Gupta

As part of Google's Real Estate and Workplace Services (REWS) Green Team, the Healthy Materials program is charged with ensuring Google has the healthiest workplaces possible. We collect and review information for thousands of building materials to make sure that our offices are free of formaldehyde, heavy metals, PBDEs and other toxins that threaten human health and reduce our productivity.

A Case for using Google Docs and Salesforce.com

My team, as you might imagine, has a great deal of data to collect and manage. We recently implemented Salesforce .com to manage that data, as it can record attributes of an object in a dynamic way, is good at tracking correspondence activity and allows for robust reports on the data, among many other functions.

We needed Saleforce.com to integrate with our processes in Google Apps. We wanted to continue collecting data using a Google Docs form but needed it integrated with Salesforce.com because we:

Liked the way the form looked and functioned
Wanted to retain continuity for our users, including keeping the same URL
Wanted a backup of submissions

And this is where Google Apps Script came to our rescue. We found that we could use Google Apps Script to create a new Case or Lead in Salesforce.com when a form is submitted through our Google Docs form. This allowed us to continue using our existing form and get our data directly and automatically into Salesforce.com.

Google Docs + Apps Script + Salesforce.com = Integrated Goodness!

Salesforce.com has two built-in options for capturing data online - Cases and Leads. Google Docs Forms can capture data for both of them. Set up your Case or Lead object with the desired fields in Salesforce.com. The next step is to generate the HTML for a form. You will use the IDs in the Salesforce.com-generated HTML when writing your Google Apps script.

A) Getting the HTML in Salesforce.com:

1. Login to Salesforce.com and go to Your Name > Setup > Customize > Leads or Self-Service (for Cases) > Web-to-Lead or Web-to-Case

2. Make sure Web-to-Lead/Web-to-Case is enabled. Click on Edit (Leads) or Modify (Cases) and enable if it is not.

3. Click on the 'Create Web to Lead Form' button (for Leads) or the 'Generate the HTML' link (for Cases)

4. Select the fields you want to capture and click 'Generate'. Save the HTML in a text file. You can leave 'Return URL' blank

B) Setting up Google Apps Form/Spreadsheet:

Create your form and spreadsheet (or open up the one you already have and want to keep using). This is very easy to do. Go to your Docs and click on 'Create' to open a new form. Use the form editor to add the desired fields to your form- they'll show up as column headings in the corresponding spreadsheet. When someone fills out your form, their answers will show up in the right columns under those headings.

C) Writing the Google Apps Script:

The script is set up to take the data in specified cells from the form/spreadsheet and send it into designated fields in your Salesforce.com instance (identified by the org id in the HTML generated above). For example, the form submitter's email is recorded through the form in one cell, and sent into the email field in either the Lead or Case object in Salesforce.com.

1. Create a new script (Tools > Script Manager > New).

2. Write the script below using the pertinent information from your Salesforce.com-generated code (shown further down).

function SendtoSalesforce() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var row = sheet.getLastRow();
  var firstname = sheet.getRange(row, 2).getValue();
  var lastname = sheet.getRange(row, 3).getValue();
  var email = sheet.getRange(row, 4).getValue();
  var company = sheet.getRange(row, 5).getValue();
  var custom = sheet.getRange(row, 6).getValue();
  var resp = UrlFetchApp
      .fetch(
          'https://www.salesforce.com/servlet/servlet.WebToLead?encoding=UTF-8',
          {
            method: 'post',
            payload: {
              'orgid' : '00XXXXXXXX',
              'first_name' : firstname,
              'last_name' : lastname,
              'email' : email,
              'company' : company,
              '00YYYYYYYY' : custom,
              'external' : '1'
            }
          });
  Logger.log(resp.getContentText());
}

Define your variables by directing the script to the correct cell (row, column number). Then in the payload section, match the field id from your Salesforce.com HTML (red) to the variable you defined (blue). For example, the email address of the submitter is defined as variable 'email', can be found in the 4th column of the last row submitted, and the id for that field in Salesforce.com is 'email'.

Note that any custom fields you've created will have an alpha-numeric id.

3. Save your script and do a test run.

D) Wiring Script to a Form Submission.

To send your data automatically into Salesforce.com, you need to set a trigger that will run the script every time a form is submitted. To do this, go to your script and click Resources>Current script's triggers.

1. Create a Trigger for your function so that it runs when a form is submitted.

2. Post the link to your form on your website, send it in an email, link to it on G+, etc. Get it out there!

That's it! Now when someone submits a form, the information will come into your spreadsheet, and then immediately be sent into Salesforce.com. You can adjust your Salesforce.com settings to create tasks when the information comes in, send out an auto-response to the person filling out the form and set up rules for who is assigned as owner to the record. You'll also have the information backed up in your spreadsheet.

This has been a great solution for our team, and we hope others find it useful as well!

Beth Sturgeon

Beth Sturgeon is a member of Google's Green Team in Mountain View, which makes sure that Google's offices are the healthiest, most sustainable workplaces around. Prior to Google, she had a past life as a wildlife researcher.

Sheetcaster: 3D in Apps Script

2012-03-08T07:00:00.000-08:00

Editor’s note: This is a guest post by Thomas Coudray, Amaury de la Vieuville, and Ahmed Bougacha. Thomas, Amaury, and Ahmed attended the Google Apps Script Hackathon in Paris, and in this post they are sharing their creative use of Google Apps Script to render a 3D scene in a Google Spreadsheet. -- Jan Kleinert

Recently, we heard about the Google Apps Script Hackathon arriving in Paris, France. We did not know much about Apps Script - heck, even JavaScript! Perfect occasion to learn something. We spent most of the event hacking around with the ever-growing collection of Google APIs. As a tribute to the folks over at id Software, we settled on one of the most fun (however useless) ways to use it: rendering a 3D scene in a spreadsheet.

The rendering is done using a technique called ray-casting, made popular by the 90s id Software game Wolfenstein 3D. Ray-casting is a really brilliant and straightforward algorithm:

First, we render the background: color the upper (sky) and lower (floor) halves of the screen in different colors. We store the pixel colors in a matrix, the screen buffer:

screen = new Array(SIZE_Y);
for (var lin = 0; lin < SIZE_Y; lin++) {
  screen[lin] = new Array(SIZE_X);
  for (var col = 0; col < SIZE_X; col++) {
    screen[lin][col] = colorToString((lin < MID) ? UPPER_BG_COLOR
                                                 : LOWER_BG_COLOR);
  }
}

Note that we draw the screen only once the buffer is fully colored, to avoid the overhead of coloring cells individually.

Then for each column of the screen:

Cast a ray
Move along the ray until hitting a wall, calculate the distance to that wall
Draw a column whose height is inversely proportional to that distance

The trick is in the drawing: the upper and lower halves of the screen are symmetrical in shape, and the only computed value is the display height of the wall. The screen really is just a fancy formatting for an integer array of columns.

The camera is represented using:

Its (real-valued) x/y coordinates in the map plane
Its angle relative to some predefined direction

We store these 3 values at the bottom of the sheet, to ensure persistence (else, each refresh would bring us back to the start location!).

function Camera() {
  this.x = CAMERA_X;
  this.y = CAMERA_Y;
  this.theta = CAMERA_THETA;
  
  this.saveToSheet = function(sheet) {
    // The player state has to be saved between each frame
    sheet.getRange(STORE_LIN, 1, 1, 1).setValue(this.x);
    sheet.getRange(STORE_LIN, 2, 1, 1).setValue(this.y);
    sheet.getRange(STORE_LIN, 3, 1, 1).setValue(this.theta);
  };
  
  this.readFromSheet = function(sheet) {
    this.x = sheet.getRange(STORE_LIN, 1, 1, 1).getValue();
    this.y = sheet.getRange(STORE_LIN, 2, 1, 1).getValue();
    this.theta = sheet.getRange(STORE_LIN, 3, 1, 1).getValue();
  };

  ...
}

The map is a logical matrix, thus limiting us to discrete boxes for walls: for every cell, there either is (1), or is not (0), a wall:

// starting 10x10 map
var S = 10;
var map =
  [
    [1, 1, 1, 1, 1, 1, 1, 1, 1, 1],
    [1, 0, 1, 0, 0, 0, 0, 0, 0, 1],
    [1, 0, 1, 0, 1, 0, 1, 1, 0, 1],
    [1, 0, 0, 0, 0, 0, 0, 0, 0, 1],
    [1, 1, 1, 0, 1, 1, 0, 1, 0, 1],
    [1, 0, 0, 0, 1, 0, 0, 1, 0, 1],
    [1, 0, 0, 0, 1, 0, 0, 1, 0, 1],
    [1, 0, 1, 1, 1, 1, 0, 1, 0, 1],
    [1, 0, 0, 0, 0, 0, 0, 0, 0, 1],
    [1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
  ];

It is also possible to modify the map in real-time: write a character in the boxes you want to swap, then hit Refresh map.

Moving involves adding (or subtracting for backwards movements) to the xy coordinates, using basic trigonometry, but only after checking the validity of the move (i.e. that it will not collide with a wall):

function Camera() {
  ...

  this.move = function(distance) {
    // return whether valid move or not
    x = this.x + Math.cos(this.theta) * distance;
    y = this.y + Math.sin(this.theta) * distance;
    if (isValidPos(x, y)) {
      this.x = x;
      this.y = y;
      return true;
    }
    return false;
  };
}

function moveUp() {
  readMapFromSheet(sheet);     // Retrieve the map from the sheet
  var camera = new Camera();
  camera.readFromSheet(sheet); // Retrieve the camera state from the sheet
  camera.move(0.5);
  raycast(camera);
}

Turning left (respectively right) is even simpler, adding (respectively subtracting) small constants to the camera angle (mod 2 PI):

function Camera() {
  ...

  this.rotate = function(alpha) {
    this.theta = (this.theta + alpha + 2 * Math.PI) % (2 * Math.PI);
  };
}

function lookRight() {
  readMapFromSheet(sheet);
  var camera = new Camera();
  camera.readFromSheet(sheet);
  camera.rotate(-0.25);
  raycast(camera);
}

Actual actions (moving/turning) are shown in a menu:

spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var subMenus = [
      {name:"Reset",functionName:"onOpen"},
      {name:"Refresh map",functionName:"refresh"},
      {name:"Move forward",functionName:"up"},
      {name:"Look left",functionName:"left"},
      {name:"Look right",functionName:"right"},
      {name:"Move backward",functionName:"down"},
      {name:"Turn around",functionName:"turn"},
  ];
  spreadsheet.addMenu("Sheetcaster", subMenus);

The ray is cast as follows:

Its origin is the camera's 2D coordinates in the map plane
Its direction is calculated off the camera's and the column index (the center column will have the exact same direction as the camera; the other columns' directions depend on the field of view parameter)

/*
 * Given a value on the x axis (screen column),
 * return the ray that will be cast
 */
function getRay(camera, x) {
  var cos = Math.cos(camera.theta);
  var sin = Math.sin(camera.theta);

  // from -1 to 1: 0 being when x is the middle column
  var k = ((SIZE_X / 2) - x) / SIZE_X; 

  return new Vector_(
    cos / 2 - k * sin * K_FOV,
    sin / 2 + k * cos * K_FOV
  );
}

Moving the ray is the most involved step:

Calculate the distance to the next vertical and horizontal borders
Move to the closest border

while (!hit) {
  // Next potential wall is on the x axis
  if (dist.x < dist.y) { 
    // Distance from the camera, delta: 
    /  Distance between each horizontal wall along the ray  
    dist.x      += delta.x; 
    // step.x is either 1 or -1, depending on the ray direction
    mapCoord.x  += step.x;  
    hit = readMap_(mapCoord.x, mapCoord.y);
  } else { // Next potential wall is on the y axis                 
    dist.y     += delta.y;
    mapCoord.y += step.y;
    hit = readMap_(mapCoord.x, mapCoord.y);
  }
}

The height of the drawn column is nothing fancy: the further the wall, the smaller-looking the wall, hence the smaller the height of the column.

Again, nothing really complicated. However, the simplicity of this wall-height technique is the reason behind its major caveat: there is no clean way to look up or down: you can only turn left or right, and move forward or backward.

Displaying the rendered image is done using a spreadsheet. Each cell becomes a small square pixel, its color being the background color of the cell. We pass our scren buffer matrix to the handy setBackgroundColors:

sheet.getRange(1, 1, SIZE_Y, SIZE_X).setBackgroundColors(screen);

As you probably noticed, the low display density makes the sharp, jagged, edges really visible. Fear not, reader, for we also implemented anti-aliasing!

The anti-aliasing algorithm is even simpler:

Accumulate the length of runs (successions of same-sized columns)
Draw a gradient, from the background (wall and floor) to the wall, above (and below) the columns

When the runs are really small (< 5 columns), we attenuate the gradient intensity, as it would only add another pixel above (below) the column, thus rendering the antialiasing utterly useless.

Real-time was not an objective, the main problem being controlling the player/camera. Scripted movements should however be quite easy to implement with a fixed duration loop, restarting itself using an Apps Script recurrent time-driven trigger (a minute-long loop, repeated every minute). This is left as an exercise to the reader.

Please feel free to copy the script and walk around this Apps Script virtual world.

Thomas Coudray

Thomas is interested in low level computing and application security.

Amaury de la Vieuville

Amaury is passionate about algorithmic problem-solving and software engineering.

Ahmed Bougacha

Ahmed is interested in kernels, compilers and theoretical computer science.

Create a Spreadsheet User Directory with Apps Script

2012-02-22T12:30:00.000-08:00

As a consultant helping companies move to the Google cloud, I receive many feature requests before, during, and after each migration. Often I’m asked about re-creating small and specific solutions that support particular business needs not fully covered by Google Apps out of the box. In many cases, a simple Google Apps Script solution satisfies the business requirement.

What is the Google Spreadsheet User Directory?

The “Google Spreadsheet User Directory” is a solution I’m frequently asked about. Google Apps Domain administrators can use a simple Apps Script that can be saved into a Google Spreadsheet and then set to run on a schedule, via a “time-driven” trigger. By using the Google Profiles API (available only for domain administrators), domain administrators can create a Google Spreadsheet which contains Google Apps domain user information.The user profile data can then be consumed and used by other business logic code, either in the spreadsheet itself or elsewhere.
Using Apps Script to provide this kind of solution was an obvious choice for the following reasons.

Apps Script makes the Google Spreadsheet User Directory a simple, flexible solution that the customer can quickly understand and extend. The JavaScript syntax is easy to learn and program in, and there is no need to compile and deploy code.
The Apps Script code is conveniently integrated into Google Spreadsheets, so there is no need to use any other software. Advanced functions can be exposed to end users for data manipulation through the spreadsheet menu, and scheduling an Apps Script to run at a regular interval is trivial via the Spreadsheet “Triggers” mechanism.
Google Apps Script provides services for accessing Google Profiles, Contact Info, and Google Groups plus Google Docs, Google Sites, Google Charts, and more. The Google Spreadsheet User Directory script makes use of both the new Apps Script Domain Services API and the GData Profiles API, via the “UrlFetch” service.
The Apps Script code can be easily shared through Google Spreadsheet templates and through the Google Script gallery.

Using the Google Spreadsheet User Directory

The Google Spreadsheet User Directory code consists of a primary scanUserProfiles() function and some supporting “utility” functions. The three steps for setting up the code to run are: 1. Set up the “Consumer_Key” and “Consumer_Secret” ScriptProperties and run the scanUserProfiles() function in the Apps Script integrated development environment to get the first “Authorization Required” screen. (I’ve included an illustration below... Choose “Authorize.”).

2. Since scanUserProfiles() uses OAuth with UrlFetch to get User Profile information via the GData API, it needs to be run at least one more time inside of the Apps Script IDE, so that the OAuth “Authorize” prompt can be shown to the programmer and accepted.

3. After authorization, the scanUserProfiles() script is free to make authorized requests to the Google User Profiles feed, as long as the developer who saved it has “domain admin” rights.

Design of the Google Spreadsheet User Directory

The following snippets show the OAuth setup, the user profiles Url setup, and the initial UrlFetch.

var oAuthConfig1 = UrlFetchApp.addOAuthService("googleProfiles");
oAuthConfig1.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=https:// www.google.com/m8/feeds/profiles");
oAuthConfig1.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
oAuthConfig1.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken?oauth_callback=https:// spreadsheets.google.com/macros");
oAuthConfig1.setConsumerKey(ScriptProperties.getProperty("Consumer_Key"));
oAuthConfig1.setConsumerSecret(ScriptProperties.getProperty("Consumer_Secret"));
var options1 = {
    oAuthServiceName : "googleProfiles",
    oAuthUseToken : "always",
    method : "GET",
    headers : {
      "GData-Version" : "3.0"
    },
    contentType : "application/x-www-form-urlencoded"
};

var theUrl = "";
if (nextUrl == "") {
  theUrl =
    "https://www.google.com/m8/feeds/profiles/domain/" + domain +
      "/full?v=3&max-results=" + profilesPerPass + "&alt=json";
} else {
  theUrl = nextUrl;
}

if (theUrl != "DONE") {
  var largeString = "";
  try {
    var response = UrlFetchApp.fetch(theUrl, options1);
    largeString = response.getContentText();
  } catch (problem) {
    recordEvent_(problem.message, largeString, ss);
  }
}

var provisioningJSONObj = null;
    var jsonObj = JSON.parse(largeString);
    var entryArray = jsonObj.feed.entry;

The "nextUrl" variable above (line 74) is being pulled from a cell in the spreadsheet, where I'm saving the "next" link from the fetched data. (If there’s no “next” link, I save "DONE" to the same spreadsheet cell.) To fetch JSON, I’m appending the parameter &;alt=json on lines 75 and 76. After I’ve got my JSON object, I create an array to store the data that I will be writing out to the spreadsheet. I set the array default values and make liberal use of try-catch blocks in this code, since there’s no telling which of these fields will be populated, and which will not.

for (var i=0; i<entryArray.length; i++) {
    var rowArray = new Array();
    rowArray[0] = "";
    rowArray[1] = "";
    rowArray[2] = "";
    try { rowArray[0] = entryArray[i].gd$name.gd$fullName.$t; } catch (ex) {} //fullname
    try { rowArray[1] = entryArray[i].gd$name.gd$givenName.$t; } catch (ex) {} //firstname
    try { rowArray[2] = entryArray[i].gd$name.gd$familyName.$t; } catch (ex) {} //lastname

At the end of the data collection process for a single record/row, I add the rowArray to another single-element array called valueArray (line 207), to create a 2-D array that I can use with range.setValues to commit my data to the spreadsheet in one shot (line 209).

var updateRow = getNextRowIndexByUNID_(rowArray[3],4,stageSheet);
var valueArray = new Array();
valueArray.push(rowArray);
var outputRange = stageSheet.getRange(updateRow, 1, 1, 12);
outputRange.setValues(valueArray);

The function getNextRowIndexByUNID (line 205) just finds the next available row on the “staging” sheet of the spreadsheet, so I can write data to it. The code is inside of a “for” loop (starting on line 106) that executes once for each entry in the current JSON object (created lines 96 and 97).

} else {
    // COPY CHANGES TO "PRODUCTION" TAB OF SPREADSHEET
    var endTime = new Date();
    setSettingFromArray_("LastPassEnded",getZeroPaddedDateTime_(endTime),settingsArray,setSheet);
    if (parseInt(getSettingFromArray_("StagingCopiedToProduction",settingsArray)) == 0) {
     // THIS DOES A TEST-WRITE, THEN A "WIPE," THEN COPIES STAGING TO
      // PRODUCTION
     var copied = copySheet_(ss,"Staging","Employees");
     if (copied == "SUCCESS") {
       var sortRange = empSheet.getRange(2,1,empSheet.getLastRow(),empSheet.getLastColumn());
       sortRange.sort([3,2]); // SORT BY COLUMN C, THEN B
       // RESET SETTINGS
       setSettingFromArray_("NextProfileLink","",settingsArray,setSheet);
       setSettingFromArray_("LastRowUpdated",0,settingsArray,setSheet);
       setSettingFromArray_("StagingCopiedToProduction",1,settingsArray,setSheet);
     }
    }
} // end if "DONE"

If the script finds “DONE” in the “NextProfileLink” cell of the spreadsheet, it will skip doing another UrlFetch to the next feed link (line 81). Instead, it will copy all records from the “staging” sheet of the spreadsheet to the “production” one, via a utility function called “copySheet” (line 273). Then it will sort the range, reset the copy settings, and it will mark another designated cell, “StagingCopiedToProduction” as “1” in the spreadsheet, to stop any further runs that day.

Scheduling the Google Spreadsheet User Directory Script to Run

Below are the triggers I typically set up for the Spreadsheet User Directory. I recommend setting scanUserProfiles() to run on an interval of less than 30 minutes, since the Google-provided token in each “NextProfileLink” url lasts about that long. I also recommend running the WipeEventLog() utility function at the end of each day, just to clear data from prior runs from the EventLog tab of the spreadsheet.

Conclusion

Above I’ve outlined how to create a basic User Directory out of a Google Spreadsheet and Apps Script that will always keep itself current. Since Google Spreadsheets support the Google Visualization API and a query language for sorting and filtering data, all kinds of possibilities open up for creating corporate “directory” gadgets for Google Sites (see the image at right) and for enabling business processes that require workflows, role lookups, or the manipulation of permissions on content in the various Google Apps.
Using Apps Script made this solution quick and easy to produce and flexible enough to be extended and used in many different ways. The code is easy to share as well. If you’d like to give the Google Spreadsheet User Directory a try, then please copy this spreadsheet template, and modify and re-authorize it to run in your own domain. Enjoy!

Shel Davis

Guest author Shel Davis is a senior consultant with Cloud Sherpas, a company recently named the Google Enterprise 2011 Partner of the Year. When Shel is not working on solutions for customers, he’s either teaching classes on Google Apps and Apps Script (Google Apps Script Training), or he’s at home, playing with his kids.

Come Learn About Apps Script in Washington, DC

2012-02-16T11:30:00.000-08:00

Editor's note: This has been cross-posted from the Google Code blog -- Jan Kleinert

Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services. If you want to learn more about Google Apps Script, collaborate with other developers, and meet the Apps Script team, here’s your chance! We will be holding an Apps Script hackathon in Washington, DC on Wednesday, March 7 from 2pm - 8pm.

After we cover the basics of Apps Script, you can code along with us as we build a complete script, or you can bring your own ideas and get some help and guidance from the team. There will be food, power, and Apps Script experts available to help throughout the day. Just bring your laptop, ideas, enthusiasm, and basic knowledge of JavaScript. Check out out the details of the event and be sure to RSVP to let us know you’re coming.

Jan Kleinert profile | twitter

Jan is a Developer Programs Engineer based in NYC, focusing on helping developers get the most out of Google Apps Script. Prior to Apps Script, she worked on Commerce, helping merchants integrate with Google Checkout and on Chrome, helping developers build great web apps.

Charts, Gmail, Maps and Properties Services Graduating from Experimental

2012-02-08T11:59:00.000-08:00

The Google Apps Script team strives to achieve a very high level of responsiveness to our community. We iterate fast to deliver new features and functionality. Releasing new Apps Script services under the “Experimental” flag allows us to gather valuable feedback from you. Real world exposure to these new experimental Apps Script services makes it easy for us to prioritize features, improve API design and documentation, and identify bugs and use cases. On top of that, experimental releases give us a way to support our more advanced users, who are perfectly happy to live on the cutting edge and get their hands on cool new Apps Script services as early as possible.

We put thorough consideration into the use of the Experimental flag. Not every new Apps Script service that we launch is experimental. In October, we launched the Lock and Cache service as non-experimental. We believed that these two services were fundamentally mature, and thus able to be launched as non-experimental.

Today, we are graduating four services from experimental status:

You can expect us to continue on this path of careful deliberation in deciding when to use the experimental flag. We will continue to evaluate the design, documentation, and overall strengths of each experimental API and continue to work hard to graduate them to fully supported APIs. We welcome your feedback in helping us make Apps Script better. You can post your feature requests on our tracker and ask any question in our forum. If you are curious on how to use the above mentioned APIs then check out the tutorials for the Charts and Gmail Services, the Maps Service and excellent example of using Properties Service.

The Apps Script team is standing by to help you!

Saurabh Gupta profile | twitter | blog

Reading Query Results from Calendar in Pages

2012-01-26T12:01:00.000-08:00

What’s the difference between reality and theory? In theory, there is no difference. But reality often imposes unanticipated constraints on developers. These may come in the form of bandwidth restrictions, memory limits, timeouts, or other requirements of the systems that interact with your application.

My team recently built an application that helps us analyze the scheduling and usage of conference rooms at Google. We use the new Calendar API v3 on Google App Engine to read the rooms’ schedules, which we combine with actual occupancy data to calculate utilization and other metrics.

As you might imagine, Google has a lot of conference rooms (I believe the last official count was “more than twelve.”) And many of the rooms seem to be booked fairly solid. That means we need to read a lot of data from Calendar. So much, in fact, that our queries time out if we try to read an entire calendar at once. But the API team anticipated “Google scale” use and designed a mechanism that allows us to retrieve data in batches.

The idea is simple. When you create a request, you specify the page size: the maximum number of results you’d like Calendar to return in one batch. Calendar returns the data you requested, along with an opaque page token, which you can think of as a bookmark. To retrieve the next batch of data, you ask the API for the next page token and include the new token in your next request. The page token keeps track of the results you’ve already seen, so Calendar can send the next batch each time. You repeat this process until you’ve exhausted all the results.

Here’s how we did this in Java:

public void getRoomEvents(String roomEmail) throws IOException {
    // Create a request to list this room’s events (see code, below)
    Calendar.Events.List listRequest = getListRequest(roomEmail);
    do {
      // Retrieve one page of events
      Events events = executeListRequest(listRequest);
      List eventList = events.getItems();

      // Process each event
      for (Event event : eventList) {
        processEvent(event);
      }

      // Update the page token
      listRequest.setPageToken(events.getNextPageToken());

    // Stop when all results have been retrieved
    } while (listRequest.getPageToken() != null);
  }

  // Create a request to list the events for a room
  private Calendar.Events.List getListRequest(String roomEmail)
        throws IOException {
    return calendarClient.events().list(roomEmail)
        .setMaxResults(1000) // Limit each response to 1000 events
        .setPageToken(null)  // Start with the first page of results
        // Return an individual event for each instance occurrence of a
        // recurring event
        .setSingleEvents(true); 
  }

We call getRoomEvents() for each room, using the room’s email address to identify it to Calendar. (You can retrieve events from your own calendar by substituting your own email address.) Then getListRequest() creates a request that we will send to Calendar. The request asks for a list of up to 1000 events from the room’s calendar.

The remainder of getRoomEvents() is a loop that executes the request, processes the results, and updates the page token in preparation for the next request. The loop continues, retrieving and processing each subsequent page of results, until the entire list has been returned. The call to getNextPageToken() indicates the end of the results by returning a null value.

By paginating our requests we avoid timeouts and reduce memory requirements. As an added benefit, each request completes fairly quickly, which means it’s also quick to retry if an error should occur. And finally, a multithreaded application may be able to process one or more pages of results while it retrieves the next, speeding execution. These advantages have led developers at Google to adopt pagination as a best practice. Look for it in our APIs when you need to exchange large amounts of data, and consider adding it to your own services.

If you have questions about our services or APIs, or if you want to see what other developers are doing with Google Calendar, check the discussions and documentation in the Google Apps Calendar API forum.

Adam Liss profile

Adam is an engineer who believes that "technical" shouldn't necessarily mean "difficult." He enjoys building infrastructure and tools that make Googlers more productive. Before joining Google in 2010, he built network-security appliances and one of the first wireless application delivery platforms.

Tips on using the APIs Discovery Service

2012-01-24T13:03:00.000-08:00

Our newest set of APIs - Tasks, Calendar v3, Google+ to name a few - are supported by the Google APIs Discovery Service. The Google APIs Discovery service offers an interface that allows developers to programmatically get API metadata such as:

A directory of supported APIs.
A list of API resource schemas based on JSON Schema.
A list of API methods and parameters for each method and their inline documentation.
A list of available OAuth 2.0 scopes.

The APIs Discovery Service is especially useful when building developer tools, as you can use it to automatically generate certain features. For instance we are using the APIs Discovery Service in our client libraries and in our APIs Explorer but also to generate some of our online API reference.

Because the APIs Discovery Service is itself an API, you can use features such as partial response which is a way to get only the information you need. Let’s look at some of the useful information that is available using the APIs Discovery Service and the partial response feature.

List the supported APIs

You can get the list of all the APIs that are supported by the discovery service by sending a GET request to the following endpoint:

https://www.googleapis.com/discovery/v1/apis?fields=items(title,discoveryLink)

Which will return a JSON feed that looks like this:

{
    "items": [
        …
        {
            "title": "Google+ API",
            "discoveryLink": "./apis/plus/v1/rest"
        },
        {
            "title": "Tasks API",
            "discoveryLink": "./apis/tasks/v1/rest"
        },
        {
            "title": "Calendar API",
            "discoveryLink": "./apis/calendar/v3/rest"
        },
        …
    ]
}

Using the discoveryLink attribute in the resources part of the feed above you can access the discovery document of each API. This is where a lot of useful information about the API can be accessed.

Get the OAuth 2.0 scopes of an API

Using the API-specific endpoint you can easily get the OAuth 2.0 scopes available for that API. For example, here is how to get the scopes of the Google Tasks API:

https://www.googleapis.com/discovery/v1/apis/tasks/v1/rest?fields=auth(oauth2(scopes))

This method returns the JSON output shown below, which indicates that https://www.googleapis.com/auth/tasks and https://www.googleapis.com/auth/tasks.readonly are the two scopes associated with the Tasks API.

{
    "auth": {
        "oauth2": {
            "scopes": {
                "https://www.googleapis.com/auth/tasks": {
                    "description": "Manage your tasks"
                },
                "https://www.googleapis.com/auth/tasks.readonly": {
                    "description": "View your tasks"
                }
            }
        }
    }
}

Using requests of this type you could detect which APIs do not support OAuth 2.0. For example, the Translate API does not support OAuth 2.0, as it does not provide access to OAuth protected resources such as user data. Because of this, a GET request to the following endpoint:

https://www.googleapis.com/discovery/v1/apis/translate/v2/rest?fields=auth(oauth2(scopes))

Returns:

{}

Getting scopes required for an API’s endpoints and methods

Using the API-specific endpoints again, you can get the lists of operations and API endpoints, along with the scopes required to perform those operations. Here is an example querying that information for the Google Tasks API:

https://www.googleapis.com/discovery/v1/apis/tasks/v1/rest?fields=resources/*/methods(*(path,scopes,httpMethod))

Which returns:

{
    "resources": {
        "tasklists": {
            "methods": {
                "get": {
                    "path": "users/@me/lists/{tasklist}",                         
                    "httpMethod": "GET",
                    "scopes": [
                        "https://www.googleapis.com/auth/tasks",
                        "https://www.googleapis.com/auth/tasks.readonly"
                    ]
                },
                "insert": {
                    "path": "users/@me/lists",
                    "httpMethod": "POST",
                    "scopes": [
                        "https://www.googleapis.com/auth/tasks"
                    ]
                },
                …
            }
        },
        "tasks": {
            …
        }
    }
}

This tells you that to perform a POST request to the users/@me/lists endpoint (to insert a new task) you need to have been authorized with the scope https://www.googleapis.com/auth/tasks and that to be able to do a GET request to the users/@me/lists/{tasklist} endpoint you need to have been authorized with either of the two Google Tasks scopes.

You could use this to do some automatic discovery of the scopes you need to authorize to perform all the operations that your applications does.

You could also use this information to detect which operations and which endpoints you can access given a specific authorization token ( OAuth 2.0, OAuth 1.0 or Authsub token). First, use either the Authsub Token Info service or the OAuth 2.0 Token Info Service to determine which scopes your token has access to (see below); and then deduct from the feed above which endpoints and operations requires access to these scopes.

                        
[Access Token] -----(Token Info)----> [Scopes] -----(APIs Discovery)----> [Operations/API Endpoints]

Example of using the OAuth 2.0 Token Info service:

Request:

GET /oauth2/v1/tokeninfo?access_token= HTTP/1.1
Host: www.googleapis.com

Response:

HTTP/1.1 200 OK
Content-Type: application/json; charset=UTF-8
…

{
    "issued_to": "1234567890.apps.googleusercontent.com",
    "audience": "1234567890.apps.googleusercontent.com",
    "scope": "https://www.google.com/m8/feeds/ 
              https://www.google.com/calendar/feeds/",
    "expires_in": 1038
}

There is a lot more you can do with the APIs Discovery Service so I invite you to have a deeper look at the documentation to find out more.

Nicolas Garnier profile | twitter | events

Google Apps EMEA Developer Tour (continued)

2012-01-19T00:00:00.000-08:00

Two months ago we announced that a few of us from the Google Apps Developer Relations team would be going around EMEA to meet with developers and talk about Google Apps technologies. We have met great developers from Germany, France, Russia, Czech Republic, Egypt, Switzerland, Israel, and Spain during Google Developer Days, hackathons, developer conferences and GTUG meetings.

This year we are continuing the tour with a series of Google Apps Script hackathons taking place in Vienna, Milan, Madrid, Munich and Dublin over the next few months. These hackathons provide a fun and hands-on way to learn about Google Apps Script and a good opportunity to give us your feedback on this technology.

For more information about the tour and to register for these events, please visit the Google Apps EMEA Developer Tour website.

We plan to organize many other Google Apps events close to you in the near future. Look for updates on the Google Apps EMEA Developer Tour website or keep an eye out for further announcements on this blog.

Nicolas Garnier profile | twitter | events

Calendar API v3 Best Practices: Reminders

2012-01-18T13:55:00.000-08:00

We recently posted some best practices for working with recurring events in Google Calendar API v3. In this blog post we’ll highlight another improved area in the v3 API: event reminders.

Reminders

Google Calendar API v3 offers developers flexible control over event reminders, including per-calendar default settings and custom overrides for individual events.

The user’s default reminders for events on a given calendar can be found in the corresponding entry in the Calendar List collection. The Calendar List collection acts a bit like a list of bookmarks, containing entries for the calendars that the user owns or has looked at in the past (it corresponds to the content of the "My Calendars" and "Other Calendars" list on the bottom left in the Web version of Google Calendar). Each entry is annotated with user-specific settings for the individual calendar, such as the preferred color in the UI and the default reminders.

Google Calendar currently supports three ways of reminding its users of events: "popup", prompting a message directly in the browser, mobile phone or desktop client, as well as "email" and "sms" for messages sent through the respective channels. To change the defaults, update the Calendar List entry and include the reminder method and how many minutes in advance the user should be alerted. In the following example, we set an email reminder to be sent 60 minutes before an event, and a popup reminder 10 minutes before.

{
 "summary": "Work Calendar",
 ...
 "defaultReminders": [
   {
     "method": "email",
     "minutes": 60
   },
   {
     "method": "popup",
     "minutes": 10
   }
 ]
}

A Calendar List entry with title and default reminders.

The default reminders will be applied to all existing and future events on this calendar, provided they don’t have custom reminders set already. In contrast to earlier versions of the API, newly created events will also have reminders set by default.

Sometimes, there are events that we want a special reminder for, or none at all. To override the defaults for a specific event, switch the useDefault flag in the reminders section to false, and include a set of custom reminders, or leave the list empty. When you define a set of override reminders for a recurring series, they are automatically applied to each of its occurrences, unless they have been overridden explicitly. Like the default reminders on the calendar, these are personal reminders for the user that is logged in, and will not influence the settings others might have for the same calendar or event. Here is an example that overrides the default reminders with a 15 minute SMS reminder for that specific event.

{
 "summary": "API Office Hours",
 ...
 "reminders": {
   "useDefault": false,
   "overrides": [
     {
       "method": "sms",
       "minutes": 15
     }
   ]
 }
}

An event representation with title and reminder overrides.

The defaults for the given calendar are included at the top of any event listing result. This way, reminder settings for all events in the result can be determined by the client without having to make the additional API call to the corresponding entry in the Calendar List collection.

In this post and an earlier post about best practices with recurring events, we have covered some improved areas of the latest version of the Google Calendar API. Have a look at the migration guide for a more complete view of other changes we made in the new version, and let us know what you think.

If you have any questions about handling reminders or other features of the new Calendar API, post them on the Calendar API forum.

Peter Lundblad profile

Peter joined Google in 2006. He's been leading the Calendar API team for the last 2 years. He's previously worked on video uploads for YouTube.

Fabian Schlup profile

Fabian is a Software Engineer at Google in Zürich, working on Calendar and Tasks, with a focus on APIs.

Fun with Apps Script at the Google Apps Developer Hackathon in NYC

2012-01-04T11:05:00.000-08:00

Editor’s Note:This is a guest post from John Watkinson. John is a developer and the co-founder of Docracy, a start-up company that hosts crowd-sourced legal documents and provides free e-signing services. He recently attended, and won first place, at the Google Apps Hackathon in NYC. Here's John's story about the event.

Our company is focused on documents in the cloud, so we attended the Google Apps Developer Hackathon on December 1st in New York City to learn more about the various Google Apps APIs, and how we can best integrate with them. During the initial presentations from Ryan Boyd and Saurabh Gupta of the Google Apps team, we were delighted to learn about Apps Script. It provides a powerful JavaScript interface into Google Apps, including Docs, Calendar, Gmail, Maps and many others.

Hack #1: “The Wolf”

After the tech presentations, we formed teams and started hacking with the technology. I joined a team with Matt Hall (also from Docracy), Nick Siderakis, Jeff Hsin and Scott Thompson. We struck upon the idea of creating a function that would make calls to Wolfram Alpha via their developer API. It took all five of us hacking away for a few hours to parse the Alpha results properly, but eventually we had an Apps Script custom function for Spreadsheet called “wolf”. It had a kind of magic feeling about it, as you could pass simple English-language queries into the function and get back exact numeric results in your Google Spreadsheet. Some example working queries:

=wolf("distance from the earth to the moon in km") Answer: 403,778km
=wolf("population of canada in 1960") Answer: 17.9 million
=wolf("average january temperature in buenos aires") Answer: 64F
=wolf("9th digit of pi") Answer: 5
=wolf("count of olympic medals won by japan in 2008") Answer: 25

Any function in Apps Script can be used as a spreadsheet function directly, so our main wolf function is the entry point to our script. We use Apps Script’s UrlFetch service to make our API call to Wolfram Alpha (note, the full URL is truncated for brevity):

var response = UrlFetchApp.fetch("http://api.wolframalpha.com/..." +
  encodeURIComponent(input));

The response from Alpha is an XML file that includes a lot of metadata we don’t need. So, we used a recursive function and the XML manipulation tools in Apps Script to track down the element of interest to us (below is a simplified version):

function findText(e) {
  if (e.getName().getLocalName() == 'plaintext') {
    return e.getText();
  } else {
    var children = e.getElements();
    for (var i = 0; i < children.length; i++) {
      var result = findText(children[i]);
      if (result) return result;
    }
    return null;
  }
}

A little bit more work is required to parse the result, as sometimes Alpha will report numbers in words (i.e. “40.7 million”), but otherwise that’s it!

For a small script, it definitely feels pretty powerful! Our little invention earned us a first place finish at the hackathon, and the prizes were little indoor RC-controlled helicopters. I managed to irreparably damage mine in a gruesome crash into my refrigerator already, but I hear that many of the others are still serviceable and flying missions daily.

Hack #2: Sheet Sweeper

Once we were comfortable with the Apps Script Services (and aware of its capabilities with spreadsheets in particular), a side project emerged that used some of the other integration features available. Apps Script functions can listen for events that are triggered by the editing of a spreadsheet cell (if the user grants the appropriate permissions). Using these onEdit Event Handlers, we wrote an implementation of the classic game Mine Sweeper that is playable right in the spreadsheet!

Watch on YouTube

You can play the game by making a copy of this spreadsheet and then clicking the menu item SheetSweeper > New Game. When you play the game, the script responds to onEdit events that are dispatched from the spreadsheet.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{name: "New Game", functionName: "startGame"}];
  ss.addMenu("SheetSweeper", menuEntries);
}

Then when the user edits a cell, the script responds and implements the mine sweeper logic:

function onEdit(event) {
  var ss = event.source.getActiveSheet();
  var cell = ss.getActiveCell();
  if (cell.getValue() == 'x') {
    // Flagging a mine
    flagCell(cell);
  } else {
    // Clearing a cell
    clearCell(cell);
  }  
}

We had a great time at the hackathon, and are happy to have been introduced to Apps Script. We believe it has an exciting future ahead of it.

John Watkinson profile

John is a developer and the co-founder of Docracy, a start-up company that hosts crowd-sourced legal documents and provides free e-signing services. He previously co-founded the mobile development shop Larva Labs, which developed the popular Androidify app with the Google Creative Lab.

Automatically setting a vacation responder from a calendar event

2012-01-03T10:37:00.000-08:00

Are you ever at an airport with no internet access, and realize that you forgot to set your "Out of Office" (OOO) message?" I forget regularly, but always remember to block off my calendar. Why not have the calendar automatically update my OOO message? We can! With the Calendar API, we can retrieve calendar events and set a vacation responder for the event duration for any user in the domain using the Email Settings API.

The first step is to authorize the Calendar and Email Settings client to make any call to the APIs respectively. The new Calendar API requires google-api-client library while the Email settings API still works with the gdata-client library in Python. These libraries use different underlying protocols and handle authorization differently.

Fortunately we can use the same code to get an OAuth 2.0 token for both the Calendar API and the Email Settings API.

from oauth2client.file import Storage
from oauth2client.client import AccessTokenRefreshError
from oauth2client.client import OAuth2WebServerFlow
from oauth2client.tools import run

SCOPES = ('https://www.googleapis.com/auth/calendar '
     'https://apps-apis.google.com/a/feeds/emailsettings/2.0/')

FLOW = flow_from_clientsecrets('client_secrets.json', scope=SCOPES)

storage = Storage('vacation.dat')
credentials = storage.get()
if credentials is None or credentials.invalid:
    credentials = run(FLOW, storage)

Now that we have obtained the OAuth 2.0 token from OAuth2WebServerFlow, we can use it in either library. To authorize the Calendar service:

# Create an httplib2.Http object to handle the HTTP 
# requests and authorize it with our good Credentials.

http = httplib2.Http()
http = credentials.authorize(http)

# Build authorized service for Calendar API
service = build('calendar', 'v3', http=http)

Authorizing the Email Settings client requires adapting the credentials:

auth2token = gdata.gauth.OAuth2Token(client_id=client_id,
  client_secret=client_secret,
  scope=SCOPE,
  access_token=credentials.access_token,
  refresh_token=credentials.refresh_token,
  user_agent='vacation-responder-sample/1.0')

email_client = auth2token.authorize(
  gdata.apps.emailsettings.client.EmailSettingsClient(
    domain=domain))

Now its time to update the vacation responder based on calendar event using the authorized client. Lets query for events containing the string ‘vacation’ from the Calendar API. The following code snippet shows the retrieval of events.

# Convert the date to the RFC 3339 timestamp format
# for Calendar API.
cur_datetime = datetime.datetime.now()
cur_date = cur_datetime.strftime('%Y-%m-%dT%H:%M:%SZ')

events = service.events().list(calendarId=username,
      q='vacation', timeMin=cur_date, singleEvents=True,
      orderBy='startTime').execute()

By specifying a user’s email address as the calendarID, we can query against any primary calendars in our domain that are shared and readable by the administrator. If the user opts to hide their calendar, we won’t find any of their events. For additional details about the query on calendar events, refer to the calendar query parameters from the reference guide.

Next we can obtain the start and end time of the event and update the vacation responder using the Email Settings client if the event’s duration is day long or more.

if 'items' in events:
  for event in events['items']:
    startDate = event['start']
    endDate = event['end']
    # If event is set for the day not just for a time slot.
    if 'date' in startDate:
      email_client.UpdateVacation(username=username,
            enable=True,
            subject='Out of office', 
            message='If urgent call me.',
            domain_only=True, 
            start_date=startDate['date'],
            end_date=endDate['date'])
      print '\nVacation responder set for the days between %s to %s'  % 
            (startDate['date'], endDate['date'])
      break

You can download the sample and build your own application on top of it. We hope that this sample makes it easier to get started, particularly for apps that need to combine APIs from the two API clients. Please feel free to reach us in the Google Domain Info and Management Forum with any questions you have or write us feedback on this post.

Shraddha Gupta profile

Shraddha is a Developer Programs Engineer for Google Apps. She has her MTech in Computer Science and Engineering from IIT Delhi, India.

Improved Customer Engagement & Discovery in the Google Apps Marketplace

2011-12-28T13:43:00.000-08:00

We recently updated the Google Apps Marketplace with several new features to help developers better engage with their customers and improve discoverability of apps in the marketplace.

Reply to Comments & Reviews

It’s no secret that engaging your customers and responding to their feedback is critical to success. It’s now possible to engage in conversations with customers based on comments & reviews for your app in the marketplace.

Rich Application Snippets in Google Search

Google Search recently introduced rich snippets for applications several months ago with enhanced search results for applications from marketplaces like Android Market and others. Marketplace apps will soon be appearing as rich snippets with ratings and pricing details.

New Category Home Pages

Lastly, we introduced new home pages for each category in the Marketplace to feature the top installed and newest apps for that category.

We hope that you find value in these changes and wish everyone a happy new year!

Steven Bazyl profile | twitter | events

Steve is a Developer Advocate for Google Apps and the Google Apps Marketplace. He enjoys helping developers find ways to integrate their apps and bring added value to users.

2011 - A year of features, use cases and developers of Apps Script

2011-12-28T11:00:00.000-08:00

2011 was the year of momentum for Google Apps Script. As 2012 dawns upon us, let us take a moment to reflect on the past year.

We started 2011 with a bang! In January we released a cloud-based Debugger into Apps Script’s IDE that proved to be very useful for developers. The Script Editor was upgraded bringing about many features and bug fixes. In March we implemented a very powerful feature of embedding Apps Script in Google Sites pages as Gadgets, making it easy to enhance Sites in amazing ways. We also improved Contacts Services, making it more stable with an improved API.

At Google I/O in May we launched Document Services, Gmail Services and the drag ‘n’ drop GUI Builder. These were major steps forward in making sure that Apps Script provides a full set of APIs to allow developers to build rich workflow and automation solutions.

We were very busy during the summer months preparing for a series of launch for later part of 2011. In September, we launched Charts Services. It allows users to dynamically create Charts and embed them in emails, UiApp or export as images. We also released three Google API services for Prediction, UrlShortener and Tasks APIs.

Lock and Cache Services launched in October. These services are important for building performant and scalable applications. We also improved the Script Editor by adding Project Support and made other UI improvements. November brought about the launch of Client Handlers and Validators. This is only the beginning of our commitment to allow developers to build more advanced UI using Apps Script.

In December we continued to improve the reliability and stability of Apps Script runtime. We capped the year by releasing Groups and Domain Services. And who can forget the very useful AdSense Services for AdSense advertisers!

Throughout the year we expanded our outreach channels. There were Apps Script sessions at Google I/O and Bootcamp, and several attendees got their last minute tickets through the Apps Script I/O challenge. We were at Google Developer Day and DevFest events, met with GTUGs, and hosted hackathons throughout the world. Our blog also featured scripts like Revevol’s Trainer Finder, Corey’s Gmail Snooze, Dave’s Flubaroo, Top Contributor’s Mail Merge, Saqib’s Idea Bank, and Drew’s Calorie Counting that showed the power of Apps Script.

Recently we started Office Hours in G+ hangout. These hangouts proved to be very popular, personal and effective means to share ideas with Apps Script community. Join us some day!

In our efforts to help educators, we worked with a New York city school to help them make most out of Google Apps. We also hosted many EDU focused webinars and workshops. In great Google tradition, Apps Script team participated in CAPE and Google Serve.

2012 is going to be an even more exciting and promising year. Tighten your seat belts because we intend to keep firing on all cylinders!

Saurabh Gupta profile | twitter | blog