Google Apps Developer Blog

Optimizing Drive API calls

2013-06-18T14:03:00.000-07:00

Ever look at the data returned when using the Drive API? A files.list call, even if just returning a single file, can yield upwards of 4kb of data. Drive has a rich set of metadata about files, but chances are your application only needs a small fraction of what’s available.

One of the simplest but most effective optimizations you can make when building apps with the Drive API is limiting the amount of data returned to only those fields needed for your particular use case. The fields query parameter gives you that control, and the results can be dramatic.

A simple example of this is using the files.list call to display a list of files to a user. The naive query, https://www.googleapis.com/drive/v2/files?maxResults=100, generated more than 380kb of data when I ran it against my own corpus. But to render this list nicely, an app only needs a few bits of information -- the document title, icon & thumbnail URLs, the mime type, and of course the file ID.

Using the fields query parameter, the results can be trimmed to just the necessary fields and those needed for fetching subsequent pages of data. The optimized query is https://www.googleapis.com/drive/v2/files?maxResults=100&fields=items(iconLink%2Cid%2Ckind%2CmimeType%2CthumbnailLink%2Ctitle)%2CnextPageToken.

After modifying the query the resulting data was only 30k. That’s more than a 90% reduction in data size! Besides reducing the amount of data on the wire, these hints also enable us to further optimize how queries are processed. Not only is there less data to send, but also less time spent getting it in the first place.

Steven Bazyl profile | twitter

Steve is a Developer Advocate for Google Drive and enjoys helping developers build better apps.

Requesting Google Groups in a Domain through Apps Script

2013-06-17T10:59:00.000-07:00

Editor’s Note: Guest author Niels Buekers is a Google Apps consultant at Capgemini Belgium. — Arun Nagarajan

During a recent Google Apps migration project, we received several requests to create custom groups of contacts so that users could more easily email frequent collaborators. Before switching to Google Apps, users created their own private distribution lists — but this approach led to overlapping groups that quickly fell out of sync.

The problem was a perfect case for Google Apps Script. We built a great solution that gives users as much power as possible with just a quick administrator review.

The situation before: either manually adding each contact or using a private contacts group.

Solution overview

To start the process, a user adds a specific label to a Gmail message. A script that runs on a timed trigger then generates a request to create a group for all the addresses in the message. The script writes this data to a spreadsheet that tracks group names and administrator approval.

/**
 * Retrieves all 'group_request' threads and creates a request.
 */
function processInbox() {
  // Get threads that have the group_request label.
  var groupRequestLabel = GmailApp.getUserLabelByName('group_request');
  var threads = groupRequestLabel.getThreads(0, 10);
  
  // For each thread, retrieve all recipients and create a group request.
  for (var i = 0; i < threads.length; i++) {
    var firstMessage = threads[i].getMessages()[0];
    var sender = firstMessage.getFrom();
    var recipients = [];
    
    // Add sender.
    recipients.push(parseAddresses(sender));
    
    // Add recipients.
    if (threads[i].getMessages()[0].getTo()) {
      var toRecipients = parseAddresses(firstMessage.getTo());
      recipients.push(toRecipients);
    }
    
    // Add CCs.
    if (threads[i].getMessages()[0].getCc()){
      var ccRecipients = parseAddresses(firstMessage.getCc());
      recipients.push(ccRecipients);
    }

    // Write all recipients to a cell in the spreadsheet
    // and send emails to ask for group name and approval.
    createGroupRequestForRecipients(recipients,
        Session.getActiveUser().getEmail());
    
    // Remove label from this thread now that it has been processed.
    threads[i].removeLabel(groupRequestLabel);
  }
};

Handling the request

Once the request has been processed and written to the spreadsheet, the script sends the user an email that asks her to suggest a name for the group in an Apps Script web app. A second email asks the administrator to visit the web app to approve or decline the request. The results are again stored in the spreadsheet.

The spreadsheet contains a second script, which is triggered for each modification. Once the script confirms that the request has been approved, it uses the Apps Script Domain Service to create the new group.

/**
 * Creates a new group in the Google Apps cPanel with the provided name
 * and members.
 */
function createGroupWithAddresses(addresses,groupName){
  var group = GroupsManager.createGroup(groupName, groupName, groupName,
    GroupsManager.PermissionLevel.DOMAIN);
  var splitAddresses = addresses.split(',');
  for (var i = 0; i < splitAddresses.length; i++) {
    Logger.log('Adding ' + splitAddresses[i]);
    group.addMember(splitAddresses[i]);
  }
};

The result after successfully running the script.

This solution provides a simple way for users to request new Google groups, without all the overhead of manually creating an admin-managed distribution list.

Niels Buekers profile | Twitter

Niels is a Google Apps consultant at Capgemini Belgium, with interest in both the technical track and change management. He recently visited Google’s London office to participate in a Google Apps Script hackathon, which resulted in the above solution. Niels is a strong believer in cloud solutions and loves to spread the word about Google Apps.

From a need to a startup, thanks to a Google hackathon

2013-06-10T09:00:00.000-07:00

Editor’s Note: Guest author Arnaud Breton is a co-founder of UniShared — Nicolas Garnier

A few weeks ago, Clément and I started to take online courses (based on videos), mainly on Coursera, edX (the MIT-Harvard venture), and Udacity, the biggest online course providers.

Right from the start, we found that it was really painful to take digital notes while watching the video lectures: we had to switch between multiple windows, struggling to interact with them (no shortcuts to play/pause, etc) and no ways of matching the notes to videos... Finally we ended up taking notes with the old fashioned and archaic pens & papers.

After giving it a thought, we were convinced that we could make this much better. We both live in Mountain View, fifteen minutes away from the Google HQ, where a Google Drive hackathon was taking place so we decided to take this opportunity to find and build a solution to our problem. And we came up with the idea of a simple Google Drive app, which would embed both the video lectures and the notes, in the same window, letting us leverage all the shortcuts. Additionally, it would enable us to synchronize the notes with the videos, to be able to smoothly go to the related part of the videos while studying our notes. During the two days - and two nights - we developed that missing app, helped by the amazing Googlers from the Drive team and meeting other amazing people who came to also give birth to their ideas or to help us.

As a tech guy, it was a new exciting challenge to build this app, especially in such a short time. Fortunately, leveraging Google robust and powerful infrastructure and frameworks made it possible. It seemed obvious for us to leverage the Google Drive and Youtube APIs, to host our app on Google App Engine and to build it with the amazing AngularJS.

At the end of the hackathon, we managed to get a viable app, enabling students to create, save and synchronize their notes and videos. We were even rewarded by the Drive team who gave us a prize for our efforts!

VideoNot.es was born!

A few hours later, we started to spread the word about our new app and were convinced that it could be as useful for other students as it was for us. Immediately, hundreds of students started to use it, giving us useful feedback on what mattered the most to them. Based on their feedback, we continued to improve our app, with the number of users increasing. Today, one month after the hackathon, more than 300 VideoNotes are created daily all around the world, and the number continues to grow day after day!

We also got featured on The Next Web describing VideoNot.es as “a really useful tool to make, store and share notes about online videos as you watch them”

So, what’s next for us?
Obviously, continuing to improve the app based on feedback is our top priority.

Also, we have discovered new use-cases where VideoNot.es can be useful. For example journalists contacted us to tell us that they use it to write articles based on interview records. But also people in charge of the transcription of videos are also very excited about VideoNot.es.

It got us more convinced than ever that note-taking can be drastically improved, especially by adapting it to its learning context. We want to go even further. Bringing back social interactions in online classrooms, letting students easily share their notes, again leveraging Google Drive was the first step. Adding real-time collaboration and Q&A features is the second step and we will definitely take advantage of the Google Drive Realtime API for this.

Thanks a lot to the Drive team who organized this hackathon to give our idea a try through their amazing tools! And this is just the beginning of the adventure, that started from a problem we faced, whose solution has been found at the Google hackathon, and which is just starting to reach its potential.

Arnaud Breton profile

Arnaud Breton is passionate about the impact that technologies have on the world. He is the co-founder and CTO of UniShared / VideoNot.es.

For shipping jewelry, Apps Script is golden

2013-06-07T12:17:00.000-07:00

Editor’s Note: Guest author Jason Gordon is a co-founder of Beth Macri Designs — Arun Nagarajan

Beth Macri Designs creates jewelry from the point of view of a structural engineer. The forms are designed using generative 3D software systems and materialized using 3D printing technologies. Our company understands that to make beautiful fine jewelry, 3D printing is only the first step; traditional jewelry craft is then employed for final production. After our first product, The Hidden Message Necklace, was recently featured on The View as part of its Valentine's Day Gift Guide, we had a lot of orders to ship out. As soon as the mail leaves the building, though, the process is literally out of our hands: something unexpected was bound to happen to at least one or two packages. Several package-tracking services exist, but getting the names and tracking numbers into them was a cut-and-paste operation.

I knew that all of the tracking numbers were being delivered by email and I had already set up a Gmail filter to archive them and apply a label. With a little help from Google Apps Script, I knew I could automatically parse those emails and add them to my account on PackageTrackr (which syncs to their newer service, Fara).

The script supports reading emails from multiple shipping providers and is set up so one could easily add more. Every 30 minutes on a time-driven trigger, using the Gmail service, the script runs and looks through unread emails from the shipping provider label, then parses the name and tracking number out of each one. The provider, tracking number, and recipient are stored in a JavaScript array.

function getUSPSConversations(){
  return GmailApp.search("in:usps is:unread subject:(Click-N-Ship)");
}

function matchUSPSHTML(data){
  var out = [];
  var track_num = data.match( 
      /TrackConfirmAction\Winput\.action\WtLabels\=(\d+)/g);
  var to = data.match(/Shipped.to.*[\r\n]*.*>([a-zA-Z\s-_]*)<br>/g);
  for(i in track_num){
    var o = new Object();
    var track = track_num[i].match(/(\d+)/g);
    var person = to[i].match(/>([a-zA-Z\s-_]+)<br>/);
    var myPerson = person[1].replace(/(\r\n|\n|\r)/gm,"")
    o["number"]=track[0];
    o["carrier"]="USPS";
    o["person"]=myPerson;
    out.push(o);
  }
  return out;
}

You can parse all of your different shipping providers in one run of the script. After the all of the shipment emails are read, it composes an email to PackageTrackr to give it all of the tracking numbers it just harvested.

var user = Session.getActiveUser().getEmail();
if(data.length > 0){    
  for(d in data){
    body += this["formatForPackageTrackr"](data[d]["number"], 
        data[d]["carrier"], data[d]["person"]);
  }
  
  GmailApp.sendEmail("track@packagetrackr.com", "Add Packages",
      body, {bcc: user});
}

function formatForPackageTrackr(tracking_num, service, person){
  return "#:" + tracking_num + " " + service + " " + person + "\n";
}

Down the line, other shipping providers could be added such as UPS and Fedex. Additionally, more tracking services could be added instead of just PackageTrackr.

Jason Gordon profile

Jason Gordon is a co-founder at jewelry startup Beth Macri Designs. He is responsible for software development, logistics and e-commerce. While working at Beth Macri Designs, Jason gets to find creative ways to put his software development skills to work to improve logistics and user experience.

What does storage quota unification mean for the Drive API?

2013-05-28T09:00:00.000-07:00

Earlier this month, we announced that storage quota is now shared between Google Drive, GMail, and Google+ photos. As part of this change, the Google Drive API and the Google Documents List API will be updated over the next few weeks to start returning the updated storage quota information.

In the Google Drive API, this appears in the about collection.

GET https://www.googleapis.com/drive/v2/about

{

"kind": "drive#about",

"quotaBytesTotal": 16106127360,

"quotaBytesUsed": 17936436,

…

}

In the Documents List API, this appears in the metadata feed.

GET https://docs.google.com/feeds/metadata/default

<entry>

<gd:quotaBytesTotal>16106127360</gd:quotaBytesTotal>

<gd:quotaBytesUsed>17936436</gd:quotaBytesUsed>

…

</entry>

The new values in the quotaBytesTotal field reflect total storage quota across all the unified products. If you rely on this value, you may notice a change in your apps, but we expect it to behave as if a user has just purchased more storage.

The new values for quotaBytesUsed field will reflect the total amount of storage used across the unified products. (This field used to reflect the total amount of storage used by Google Drive.)

Ali Afshar profile | twitter

Tech Lead, Google Drive Developer Relations. As an eternal open source advocate, he contributes to a number of open source applications, and is the author of the PIDA Python IDE. Once an intensive care physician, he has a special interest in all aspects of technology for healthcare

Admin SDK and Google+ APIs for business

2013-05-16T14:34:00.002-07:00

Every day, millions of businesses, schools and government agencies rely on Google Apps to get their work done. And each of these organizations has an administrator (or a team of admins) responsible for tasks like creating new accounts, managing mobile devices, and specifying exactly which products and features their employees can use.

Today, we're announcing the Admin SDK, which enables developers to build customized administrative tools for organizations that use Google Apps. The new Admin SDK consolidates many of the existing domain APIs into a new uniform structure and introduces new functionality with the Directory API and Reports API. We’re starting to pilot Google+ Domains API.

Directory API

The new Directory API provides a simple, RESTful interface to support all basic operations required to query & manage users, groups, organizational units, Chromebooks and mobile devices.

Reports API

The new Reports API gives developers a consolidated view of reporting and auditing for domains. Developers can build applications that can monitor and search across usage statistics and activities within a domain.

Google+ Domains API

Businesses are using Google+ to help employees collaborate more easily and get things done. Developers will soon be able to auto-provision Circles, read/write posts, and more from the new APIs. Let us know if you're interested in learning more about this API when it's available.

To begin using the Admin SDK follow the instructions in the API documentation. You will need to sign in to the Google APIs Console and activate the Admin SDK. If you have any questions, join the conversation at Stack Overflow.

Note about API deprecation:
With the introduction of the Directory and Reporting APIs in the new Admin SDK the following APIs will be deprecated per their standard deprecation policy: Google Apps Profiles, Provisioning, Admin Audit, Reporting, Reporting Visualization.

Ajay Guwalani

Ajay Guwalani is Product Manager on Google Apps Admin APIs. His current focus is to build next generation admin APIs to make enterprise developers and admins happy.

Introducing Actions in the Inbox, powered by schemas

2013-05-15T14:32:00.000-07:00

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

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

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

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

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

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

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

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

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

Claudio Cherubino profile | twitter | blog

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

New Apps Script features at Google I/O—again!

2013-05-14T17:33:00.000-07:00

This Wednesday is the start of our annual developer conference, Google I/O, and we can’t wait to share a bunch of new features that will help developers do more with Apps Script. So let’s not wait! Check out these new features launching today:

Scripts in Google Docs

Many of you have told us that you want to be able to extend Google Docs just like Google Sheets, with custom menus, dialogs, and triggers. Starting today, you can do just that (plus custom sidebars, too). To learn more about Apps Script in Docs—including a couple of secret features that we can’t tell you about yet!—please tune into the live stream with me and Jonathan Rascher on Thursday at 3:30pm PT.

Forms Service / Scripts in Google Forms

In response to another top request, you can now use the Forms Service to programmatically create and modify Google Forms, including triggers and a better way to respond to form submissions. (We’ve created a new 5-minute quickstart to get you going.) You can also extend the Google Forms editor with the same custom menus, dialogs, and sidebars as Google Docs. If you’re at I/O, learn how to build Forms with Apps Script by joining Eric Koleda and Matthew Ziegelbaum on Wednesday at 1:55pm PT.

Drive Service

For those of you who use the DocsList Service to automate your Google Drive, a newer version is now available. Drive Service comes with new features like setting the owner of a file or folder or changing the sharing settings. We designed the new service from the ground up to make it easier to work with large numbers of files and also fixed a lot of bugs. If you’re at I/O, Arun Nagarajan and John McGowan will give you more insight into Drive integration on Thursday at 1:40pm PT.

Faster HtmlService

At Google I/O 2012, we launched HtmlService to let you build custom user interfaces with secure client-side scripting. Starting today, you can enable an experimental version of the client-side sandbox that runs significantly faster in any browser that supports ECMAScript 5 strict mode.

Improved Authorization Flow and API Console Integration

You’ve also told us that authorizing a script takes too many steps. Now, you can opt in to an experimental new authorization flow that requires fewer clicks. In addition, every script that uses the new flow automatically creates a project in the Google APIs Console. This makes it much easier to use Google APIs that aren’t built in to Apps Script. To upgrade a script to the new flow, select File > Upgrade authorization experience. If you’re at I/O, Arun Nagarajan and Christoph Schwab-Ganser will demonstrate the new flow in their session on using the YouTube Analytics API with Apps Script on Wednesday at 1:55pm PT.

As you can see, we’ve been working hard to improve Apps Script for you. We hope you enjoy the new features!

Saurabh Gupta profile | twitter | blog

As the product manager for Google Apps Script, Saurabh is responsible for Apps Script’s overall vision and direction.

New features for the Google Drive Realtime API

2013-04-30T10:22:00.000-07:00

We recently announced the launch of the Google Drive Realtime API that lets developers create collaborative apps with the same technology that powers Google Docs, Sheets, and Slides. Today we’ve added a couple of small, but very useful, features that let developers do even more with the Realtime API: undo and redo.

The new undo and redo features provide developers a way to easily undo (or redo) local changes without worrying about the complexities that can happen in a collaborative environment. The Realtime API automatically resolves potential conflicts from overlapping edits by collaborators to undo only the local changes.

The functions themselves are very simple to implement. The following code demonstrates how straightforward adding this functionality to your app can be:

if (model.canUndo) {
  model.undo();
}

You could connect this code directly to an undo button in your app’s UI to undo the last change a local user made. No extra hard work required!

Undo and redo also come with an associated event emitted by the model class that lets you know when the features are available. You just need to attach an event listener to the model and wire up the appropriate UI changes to enable/disable undo/redo buttons. For example, you could add two buttons inside the <body> tag of your HTML document:

<button id="undoButton" disabled>Undo</button>
<button id="redoButton" disabled>Redo</button>

Then, add the following code inside the onFileLoaded callback inside your script to connect the logic to the buttons:

var model = doc.getModel();
var undoButton = document.getElementById('undoButton');
var redoButton = document.getElementById('redoButton');

undoButton.onclick = function(e) {
  model.undo();
};
redoButton.onclick = function(e) {
  model.redo();
};

Then add an event handler to enable and disable the buttons when local changes are available:

var onUndoRedoStateChanged = function(e) {
  undoButton.disabled = !e.canUndo;
  redoButton.disabled = !e.canRedo;
};
model.addEventListener(gapi.drive.realtime.EventType.UNDO_REDO_STATE_CHANGED, onUndoRedoStateChanged);

For a complete example of this implementation, see the Realtime Quickstart.

The Realtime API makes implementing undo/redo features very straightforward for most applications. For more information, see the Realtime API documentation.

Greg Knoke Google+

Greg Knoke is a technical writer in the Google Drive Developer Relations Team. Prior to joining Google, he worked as a scientist developing image and signal processing algorithms. His current interests include new technologies, content management, information architecture, cooking, music, and photography.

How Apps Script Makes Classroom Observation Quicker and Easier

2013-04-29T11:26:00.001-07:00

Editor’s Note: Guest author Martin Hawksey is an advisor at the Jisc Centre for Educational Technology and Interoperability Standards. — Dan Lazin

When I started looking at Google Apps Script in 2010, one of the things that attracted me was the ease with which a non-developer like me could start customising Google Apps with only a few lines of code. Since then, the rich community of users and examples has continued to grow, and I’ve built event booking systems, entire student feedback solutions, and even integrated with Mozilla Open Badges.

Recently, Justin Marckel, the assistant principal at Cornatzer Elementary School in North Carolina, asked for help in modifying one of my existing Apps Script examples. Justin was recording teachers’ classroom activities using a Google Form, then manually copying and pasting data into separate spreadsheets for each teacher to review. Justin wanted to know whether there was a way for a Google Form to store the results in a master spreadsheet, then filter results to each teacher’s spreadsheet.

The basic pseudocode would be:

on form submit
  if teacher’s spreadsheet doesn’t exist, then
    create spreadsheet
    add teacher as viewer
    store id
  else
    get id
    open teacher’s spreadsheet
  copy values to teacher’s spreadsheet

Here’s a closer look at each of the steps.

Handling a form submission event

Apps Script offers three triggers specific to Google Sheets: “on open,” “on edit,” and “on form submit.” Looking at the Understanding Events documentation, we can see that a form submit trigger gives us a few options for how to pull the submitted values out of the event parameter (usually called e). We can get the data as an array via e.values, a Range object via e.range, or a JavaScript object that pairs the form questions with the respondent’s answers via e.namedValues. In this project, the e.values array is most convenient, and it will look something like this:

['2010/03/12 15:00', 'bob@example.com', 'Bob', '27', 'Susan', '25']

First, though, we have to add the form-submission trigger. The user could add it manually from the script editor’s Resources menu, but in this case, let’s manage triggers programmatically:

function setup(){
 if (ScriptApp.getScriptTriggers().length === 0) {
   ScriptApp.newTrigger('doOnFormSumbit')
       .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
       .onFormSubmit()
       .create();
 }
}

Creating and managing permissions on a spreadsheet

One of the big advantages Apps Script is that you’re automatically working in a Google-authenticated environment. The result is that you can programmatically create a new spreadsheet with one line of code, then add a teacher as a viewer in just one more line:

var newSS = SpreadsheetApp.create('Spreadsheet Name');
newSS.addViewer('email-address-of-teacher');

Writing data to a spreadsheet

Writing data to a sheet requires more than a one-liner just because we need to specify which cells to write to. The Range.setValues() method expects a 2D array; because we’ve already retrieved the response to the form as an array, it’s easy to throw those values into a row of cells:

var destSS = SpreadsheetApp.openById(id); // open teacher spreadsheet
var destSheet = destSS.getSheets()[0]; // grab first sheet
var insertRow = destSheet.getLastRow() + 1; // next row to enter data
destSheet.getRange(insertRow, 1, 1, e.values.length)
   .setValues([e.values]);

Simple, effective and efficient

The completed project is here. The bulk of the form-submission handling (including error logging) happens in around 50 lines of code, and I was able to complete the project within an hour. Now Justin no longer needs to copy, paste, and set up separate spreadsheets, potentially saving him hours of work. Justin recently contacted me to say:

“We have successfully used our program over the past couple of months to provide teachers with meaningful and efficient feedback. It has been successful at several other schools as well, and I got word today that our school district is looking at adopting it as a district-wide tool.”

This is just one of a growing number of examples of how Google Apps Script is directly benefitting educators by allowing custom solutions with the security, convenience, and power of Google Apps.

Martin Hawksey profile | twitter | blog

Martin is an advisor at the Jisc Centre for Educational Technology and Interoperability Standards (CETIS), a national advisory and innovation centre that works on on educational technology and standards for the UK Higher Education and Post-16 Education sectors. Martin is an active contributor to the Apps Script community and regularly shares projects on his blog, MASHe.

More ways for apps to write to Drive

2013-04-04T12:11:00.000-07:00

Today we’re introducing two new ways for apps to build even richer integrations with Drive: app data folders and custom properties.

In order to run smoothly, your app may depend on data it stores in Drive. But occasionally, users may accidentally move or delete the very file or folder your app needs to function. The app data folder is a special folder in Drive that can only be accessed by your app. The app folder’s content is hidden from the user and from other apps, making it ideal for storing configuration files, app state data, or any other files that the user should not modify.

Although users cannot see individual files in the app data folder, they are able to see how much app data your app is using and clear that data in the Manage Apps dialog.

Apps can also now add custom properties to any Drive file. The new properties collection gives your app the power to create searchable fields that are private to your app or shared across apps. For example, a classroom app could keep track of the grade for a document or a project management app could keep track of the current status of a document going through a review process.

To learn more check out the technical documentation for both app data folders and custom properties, and if you have questions don’t hesitate to post on StackOverflow.

Nicolas Garnier Google+ | Twitter

Nicolas Garnier joined Google’s Developer Relations in 2008 and lives in Zurich. He is a Developer Advocate for Google Drive and Google Apps. Nicolas is also the lead engineer for the OAuth 2.0 Playground.

Apps Script helps name Pluto's new moons

2013-03-29T09:36:00.000-07:00

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

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

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

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

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

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

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

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

Dr. Mark Showalter profile

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

Hack to School

2013-03-25T11:29:00.000-07:00

Editor’s Note: Guest author Andrew Stillman is a teacher who works at New Visions for Public Schools, a non-profit that provides direct support services to 76 New York City high schools. — Arun Nagarajan

On March 16th, as a green tide tide of college students flowed into Manhattan for a day of rousing revelry, more than 50 young coders from New York-area computer science programs and 30 teachers were drawn instead to Kean University in New Jersey by the gravity of St. Hacktrick’s Day, our first Apps Script for EDU Codeathon. Inspired by the viral popularity of the Flubaroo, Doctopus, and autoCrat scripts for teachers, St. Hacktrick’s Day aimed to pair coders with educators to produce more free, smart tools for education.

Teacher Daniel Scibienski works as an elementary ESL teacher in NJ. He helped organize and emcee the event, and was on the winning team that built a picture-prompt generator for Google Docs.

Most of the student scripters were on their first day of spring break, making our huge turnout for this event all the more remarkable. Product designers — all working educators who took time out on a Saturday — traveled from as far north as Ulster County, NY and as far south as Virginia, while we had others who joined teams via G+ Hangouts from Singapore, Montreal, Vancouver, and London.

This team built a class-roster Google Site replicator using Apps Script, cookies, and Coke. Their EDU design partner was located in the UK!

Unlike a typical hackathon, teams weren’t simply building their own ideas — instead, to ensure their scripts would be truly useful in the classroom, we solicited project proposals through a Google Moderator board. By the day of the event, we had 48 ideas with 187 votes from educators around the world.

In all, 17 teams built demo-ready prototypes in less than 6 hours of coding. The Apps Script team rounded up a few Nexus 7 tablets for the winners below and invited them to present their projects to the Google Docs engineering team:

Popular vote: Picture Prompt Generator
Summary: Inserts kid-friendly pictures from Google Image Search into student documents. Elementary students then write stories based on the visual prompts.
Design: Daniel Scibienski
Code: Ashish Nandwani and Krutika Shah

Judges' choice: Plagiarism Detector
Summary: Uses a similarity algorithm to rank Google Documents by originality.
Design and code: Alice Lin, Basim Baig, and Jackie Wei (Stony Brook University)

Judges' choice: Unpivot Google Form Data
Summary: Removes duplicates from Google Form data and transforms it for use in a pivot table.
Design: Ron Turchyniak
Code: Andrew Ireland, Sangwook Lee, and Steve Byung Park (Stony Brook University)

Teams have been asked to open-source their code and donate it to New Visions for Public Schools, the support organization I work for, and to consider improving their projects for use by educators everywhere. We’ll keep you posted as these resources become available.

Big thanks to our participants, to organizers Meredith Martin, Dave Zirkle, Daniel Scibienski, Emily Graves, Diana Potts, Lisa Thumann, Andrew Carle, and to Google’s Arun Nagarajan, Saurabh Gupta, and Zach Yeskel.

Andrew Stillman profile

Andrew Stillman is a career STEM educator who works as Program Officer for Digital Instruction at New Visions for Public Schools, a non-profit that provides direct support services to 76 New York City high schools. Andrew founded YouPD.org and has written a number of popular Apps Scripts for schools designed to improve efficacy through better workflows, communications, and data management in Google Apps.

Build collaborative apps with Google Drive Realtime API

2013-03-19T09:01:00.000-07:00

Google Drive lets users create Docs, Sheets, and Slides, collaborate on them in real time, and have their changes saved instantly and automatically. With the new Google Drive Realtime API, you can now easily add the same real-time collaboration that powers Google Drive to your own apps. This new API handles network communication, storage, presence, conflict resolution, and other collaborative details so you can focus on building great apps.

Developing for the Drive Realtime API is almost as simple as working with local objects. The API provides collaborative versions of familiar data objects such as maps, lists, strings, and JSON values and automatically synchronizes and stores modifications to these objects. Your application reads from and writes to these objects like any other local object. Change event handlers can be added to collaborative objects so that your app can react to changes from other collaborators.

Because the Drive Realtime API is based on operational transformation (OT), local changes are reflected instantly, even on high-latency networks. The Drive Realtime API automatically transforms changes to the data model so that every collaborator stays in sync.

If basic collections aren't enough for your application, the Drive Realtime API supports custom objects and references, including trees and arbitrary graph structures. As with other collaborative objects, the Drive Realtime API automatically synchronizes these objects with other collaborators and stores them in Drive.

Because presence is important in collaborative applications, the Drive Realtime API also keeps track of who is connected to your application and provides your app with events for when collaborators join, leave, or make changes.

Widget using the Drive Realtime API and showing the collaborators on a document

Neutron Drive, Gantter and draw.io have enabled realtime collaboration in their apps using the Google Drive Realtime API. Check out these apps to see the Drive Realtime API in action.

Collaborative code editing with Neutron Drive

Neutron Drive is an online editor for text and source code files stored in Google Drive. You can now collaboratively edit any text or source code files stored in Drive and get a collaboration experience similar to Google Docs — shared typing, a view of active collaborators, cursor positioning, and selected text. This all comes in addition to the syntax highlighting and other advanced features offered by Neutron Drive. To learn more, watch the video below:

Collaborative project scheduling with Gantter

Gantter is a free online project scheduling tool and Gantt diagram editor. It now allows you to collaboratively — and in real time — work on your project schedules. It even features an embedded chat powered by the Drive Realtime API. Watch the video below to see Gantter’s new realtime collaboration features in action.

Collaborative diagraming with draw.io

draw.io is a diagraming application that enables you to draw a wide variety of diagrams such as flowcharts, UML diagrams and even electronic circuits. You can now see updates from other collaborators instantaneously, with colored visual cues indicating who has changed the diagram and where that change occurred. Try the new draw.io collaborative beta at rt.draw.io and watch the video below.

Learn more about the Drive Realtime API

We also built a collaborative colored cube puzzle so you can have some fun while trying out the Drive Realtime API and a Drive Realtime API Playground to take you through the API step-by-step. Both apps are open source so check out our Github repos.

Have a look at the Google Drive Realtime API technical documentation and start making your app realtime-enabled!

Brian Cairns profile

Brian joined the Google Drive team in 2011 and lives in Boulder, Colorado. He is currently the lead software engineer for the Drive Realtime API.

Retiring a Few Apps Script Components

2013-03-13T16:08:00.000-07:00

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

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

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

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

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

Saurabh Gupta profile | twitter | blog

As the product manager for Google Apps Script, Saurabh is responsible for Apps Script’s overall vision and direction.

Even more Image Metadata for the Google Drive SDK

2013-03-12T14:16:00.001-07:00

Lots of photographers, both professionals and amateurs, have started using Google Drive to store their photos online. We recently launched new features such as a way to quickly preview files and today I wanted to share more details about the image media metadata capabilities of the Drive SDK.

All digital cameras add some Exif information to the photos they take, and we exposed an initial set of Exif fields via the Google Drive API at the end of 2012. That set of metadata has now been expanded to include 9 new fields, such as the sensor type or the metering mode.

For instance, take a look at this recently taken photo:

Photo credit: Claudio Cherubino

What follows is the image media metadata as returned by the Drive API (in bold the new fields):

"imageMediaMetadata": {
  "width": 2048,
  "height": 1536,
  "rotation": 0,
  "date": "2013:02:18 12:51:51",
  "cameraMake": "Panasonic",
  "cameraModel": "DMC-GF2",
  "exposureTime": 0.0025,
  "aperture": 7.1,
  "flashUsed": false,
  "focalLength": 14.0,
  "isoSpeed": 100,
  "meteringMode": "Pattern",
  "sensor": "One-chip color area",
  "exposureMode": "Auto",
  "colorSpace": "sRGB",
  "whiteBalance": "Auto",
  "exposureBias": 0.0,
  "maxApertureValue": 3.6289062
 }

You might have noticed that a number of fields have been added to the response while others (“subjectDistance” and “lens”) were not returned. This is expected as the camera doesn’t have to populate all Exif fields and in that case the corresponding properties will simply not be included in the API response.

For more information and to check the description of all metadata fields returned by the API, check the Files resource Reference Guide. If you have technical questions, please post them on Stack Overflow, my team monitors the google-drive-sdk tag and is happy to help.

Claudio Cherubino profile | twitter | blog

Google Drive Hackathon in Mountain View, CA

2013-03-06T09:15:00.000-08:00

Hey Silicon Valley developers,

We are organizing a Google Drive hackathon next week. If you’d like to learn more about the Google Drive SDK, meet with Google Drive engineers and have fun developing your first Google Drive application or integrating your existing application, join us.

The event will take place at the Googleplex in Mountain View, CA. We’ll start with an introduction to the Google Drive SDK at 4:00 p.m. on Wednesday March 13th 2013 and the hackathon will run through the next day at 3:00 p.m. See the detailed agenda of this event, don’t forget to RSVP and tell your friends about it.

Also there will be some exciting Google prizes for the best apps.

See you there!

Nicolas Garnier Google+ | Twitter

How to make files searchable in Google Drive

2013-02-27T14:35:00.000-08:00

When a file of a common type is uploaded to Google Drive, it is automatically indexed so users can easily search for it in their Drive files. Google Drive also tries to recognize objects and landmarks in images uploaded to Drive.

For instance, if a user uploaded a list of customers as an HTML, XML, PDF or text file he could easily find it later by searching for one of its customer’s name that is written inside the file. Users could also upload a picture of their favorite green robot, then search for “Android” and Google Drive would find it in their Drive:

Searching for “Android” finds images containing the Android logo.

Metadata such as the file’s title and description are always indexed so users can always find a file by name. However, Google Drive does not automatically index the content of less common or custom file types. For example if your application uploads or creates files using the custom MIME-type custom/mime.type, then Drive would not try to read and index the content of these files and your users would not be able to find them by searching for something that’s inside these files.

To have Google Drive index the content of such files you have to use one of the following two options available when uploading files through the Google Drive API.

useContentAsIndexableText URL parameter

We recently added a way for you to indicate that the file you are uploading is using a readable text format. In the case where your file data format is text based — for instance if you are using XML or JSON — you can simply set the useContentAsIndexableText URL parameter to true when uploading the file’s content to Drive. When this flag is set Google Drive will try to read the content of the file as text and index it.

indexableText attribute

There is a more flexible approach which is to set the indexableText attribute on the File Metadata. You can set the value of the indexableText attribute which is a hidden — write-only — attribute that we will index for search. This is very useful if you are using a shortcut file — in which case there is no content uploaded to Google Drive — or if you are using a non-text or binary file format which Google Drive won’t be able to read.

Have a look at our Google Drive API references or watch our latest Google Developer Live video about the topic to learn more.

Nicolas Garnier Google+ | Twitter

Introducing Google+ Sign-In for your Drive Apps

2013-02-26T11:35:00.000-08:00

What does the new Google+ Sign-In mean for your Drive app, and why should you use it?

Google+ Sign-In is not just about Google+

All APIs can be authorized using the “Sign in with Google” button, including the Drive API. To authorize additional scopes, just pass them in the markup for the “Sign in with Google” button like we’ve done in this example.

<span class="g-signin"
      data-scope="https://www.googleapis.com/auth/drive.file">

Ready-made button to allow users to connect with Google

The “Sign in with Google” button can cater to whatever kind of application you create: web, client, or mobile. Now you can choose the authorization flow you like and get a token using the OAuth 2.0 client-side flow or server flow. There are loads of features, and the button is highly customizable.

I’ve saved my favorite feature until the end: when the user authorizes an application on the web, the mobile version of the app can be installed over the air onto their mobile device. Just add your Android package name when you create the button like in this second example, and your app will be automagically installed.

<span class="g-signin"
      data-apppackagename="org.aliafshar.android.driveapp">

I know many of your Drive apps have mobile and web components, so this should be really useful for you. This helps you provide your users with a beautiful and seamless experience on all of their devices.

All-in-all, we think you’ll find these features useful and recommend that you use the Google+ Sign-In as the preferred way to authorize a user with the Google Drive API from inside a user interface. Check out how to get started with Google+ Sign-In in the language of your choice.

Ali Afshar profile | twitter

Crowdsourcing Localization with Google Apps Script

2013-02-13T09:45:00.000-08:00

Editor’s Note: Guest author John Gale is a Solutions Developer at Appogee, a Google Cloud Service Partner. — Arun Nagarajan

Ever since we launched Appogee Leave — the first tool in the Google Apps Marketplace for tracking employees’ absences and time off — customers have been asking, “Can you support my native language?”

Our partners offered to help crowdsource the translation, but it was a challenge to know where to begin. We started by identifying a few needs:

Users must be able to provide new translations
Users must be able to flag bad translations and recommend changes
Developers must be able to integrate completed translations
Customer service must be able to keep users informed of progress

With just a couple days’ effort in Google Apps Script, we created a complete application for crowd-sourced localization that handles each of those requirements. You can get a glimpse of the system in the screenshot below.

Source: Appogee

Let’s take a look at a few specific Apps Script tricks we used to make the whole thing work.

Avoid Collisions with Lock Service

Like many Apps Script users, we store almost all of the data for our translation system in Google Sheets, including both the list of English terms we want to translate and users’ translations.

During testing, we found that if two users submitted translations at the same time, the spreadsheet wrote both sets of changes to the same place, causing us to lose one user’s updates. To solve this, we use Apps Script’s semaphore-based Lock Service. In the code below, a public lock ensures that a user has temporary exclusive use of the spreadsheet so that their correction is added even if another user also submits a correction.

function submit(e){
  /*  get the fields from the UI callback  */
  var incorrect = e.parameter.foreignWordIncorrectTxt;
  var correct = e.parameter.foreignWordCorrectTxt;
  var reason = e.parameter.reasonTxt;
  var lang = e.parameter.hiddenLang;

  /*  validate the input; return the user a message if invalid  */
 
  /*  open the spreadsheet  */
  var active_user_email = UserProperties.getProperty('user_email') || "";
  var master_spreadsheet = SpreadsheetApp.openById(MASTER_SPREADSHEET_KEY);
  var correction_sheet = master_spreadsheet.getSheetByName('Corrections');
    
  /*  get a lock and update the spreadsheet  */
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);    
  correction_sheet.appendRow([
      lang, incorrect, correct, reason, active_user_email
    ]);
  SpreadsheetApp.flush();
  lock.releaseLock();

  /*  reset the UI  */
  return reset();
}

You’ll note that this code opens the spreadsheet before obtaining a lock. At this point, we are only reading, not writing, and thus do not yet require a lock. We then tell Apps Script we are prepared to wait up to 30 seconds for our turn to lock the worksheet. On the rare occasion that a lock is not available within 30 seconds (usually because somebody else has an exclusive lock), the code throws an exception and stops execution.

Once we have acquired the lock, we quickly write the correction to the spreadsheet — including a call to SpreadsheetApp.flush() to ensure the data is written immediately — and release the lock.

Save Time with Cache Service

Because the translations are stored in a spreadsheet along with information about who provided them, it’s easy to recognize our top contributors through a leaderboard. The leaderboard data is a good candidate for caching because it’s shown to a large number of people, but only changes when we receive new updates from top-ranking users.

Like the Lock Service described earlier, the Cache Service provides both public and private variants. The public cache is useful for storing data that should be available to all users, such as the leaderboard. The private cache is more appropriate for storing information about a user, such as the translations they have submitted so far.

Since the Apps Script cache can only store strings, complex objects must first be converted. Lucky for us, Apps Script provides JSON utilities that make this conversion easy, as shown in this example:

function getBoardData(){
  var cache = CacheService.getPublicCache();
  var leaderboard_data = cache.get('leaderboard_data');
  if (leaderboard_data == null) {
    leaderboard_data = getTopTen();
    cache.put('leaderboard_data',
              Utilities.jsonStringify(leaderboard_data),
              3600);
  } else {
    leaderboard_data = Utilities.jsonParse(leaderboard_data);
  }
  return leaderboard_data;
}

Our hope is that the leaderboard will encourage users to provide more translations by introducing some friendly competition.

Thanks to Google Apps Script and the techniques shown above, we built a powerful crowdsourcing translation system without unnecessary complexity or development effort. If you’d like to help translate Appogee Leave, we’d love to have your contribution.

Source: Appogee

John Gale profile | twitter

John Gale is a Solutions Developer at Appogee, a Google Cloud Service Partner. John develops software that helps customers make the most of the Google Cloud platform. He has worked on a range of cloud-based applications including Google Apps Marketplace applications, client projects involving Lotus Notes application migration, and mobile data-capture applications.

Apps Script Rewind

2013-01-25T10:13:00.000-08:00

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

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

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

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

Apps Script services

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

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

Working with other Google APIs

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

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

Third-party APIs

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

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

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

Cheers!

Ikai Lan profile

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

Join the Google Drive SDK Early Access Program

2013-01-22T10:57:00.001-08:00

We're looking for a small set of developers that are committed to building apps for Google Drive to join our Google Drive SDK early access program. Participants will get early access to upcoming features and the opportunity to shape the direction of the SDK. This is an ongoing program covering multiple aspects of the API, and there are two new features that we're ready to share with developers.

Google Drive Realtime API

Some of you might have already heard of the upcoming Google Drive Realtime API at Google IO 2012. The Google Drive Realtime API will allow you to use the technology that powers the realtime collaboration features of Google products such as Google Docs and Google Sheets in your own application. It will handle all aspects of data transmission, storage, and conflict resolution when multiple users are editing.

We are looking for trusted testers for what will be a short and intense pre-release phase of the Drive Realtime API. Good candidates will be:

People who are able to start testing and integrating the API very soon ( next week!)
Web based applications: The API is currently JavaScript-only
Those who have a good use case for a realtime technology (like web based editors or even web based games...)
Committed to having their integration ready for the launch of the API

Google Drive API Push Notifications

We also told developers about an upcoming Push Notifications system at Google IO 2012. Push Notifications will allow you to get near-instant notifications when files are modified in Google Drive. In the past you would typically have had to frequently poll the Drive API to check if files have been modified to obtain similar results, Push notifications makes this super efficient.

Please fill out our signup form to tell us more about your use case and we’ll contact you shortly.

Nicolas Garnier Google+ | Twitter

Kicking Apps and Making Names

2013-01-03T11:49:00.000-08:00

Way back in the dawn of time, before I joined Google — OK, fine, two months ago — I was a video-game designer. Occasionally, I had to come up with names for people or places. And I'm no good at naming things.

So once, instead of manually naming hundreds of towns in a (fictitious) foreign country, I weaseled my way out of creativity by writing a ridiculous set of custom spreadsheet functions. My spreadsheet analyzed a list of real placenames from a similar country, then spit out plausible fake names with the same lexical structure.

It worked — but I was pushing spreadsheet functions so far that the “code” (if you can call it that) became difficult to maintain. At the same time, the reason I used a spreadsheet in the first place was so I could lean on the analytical power of pivot tables.

That’s what made Google Apps Script perfect for revamping the project. With Apps Script, I can still use pivot tables, then do the heavy lifting in JavaScript and package everything up as a tidy web app. I call it Name Generator … because I’m terrible at naming software, too.

Now, before you say, “There’s no way I’d call my daughter Harliance,” remember that the goal wasn’t to produce real names. The goal was to produce names that were good enough for a video game. Perhaps Harliance is a cyborg woman of negotiable virtue in dystopian future-America? You should probably pick your daughter’s name the old-fashioned way.

So let’s look at a few of the techniques that NameGen uses.

1. We start out in Google Sheets, first dropping a list of real names into column A, then slicing it into overlapping three-letter segments using the formula =MID($A2,COLUMN(B2)-1,3) (that’s the version of the formula you’d use in cell B2; from there, just copy and paste the formula across the rest of the sheet and the cell references will update accordingly). Here’s a sample of one of the spreadsheets so you can see how the data is set up.

2. We then create a pivot table for each column in that first sheet, just summarizing the column by COUNTA (the number of times each segment occurs). For example, since Lakisha, Nakia, and Nakisha (from our list of real names) share “aki” as letters 2 through 4, the pivot table for Segment 2 shows “aki: 3.”

The plan is that NameGen will randomly pick one of the starting three-letter segments, then look at the last two letters of its selection so that it can find an overlapping segment from the next column. The script then uses the pivot-table statistics to weight its selections toward more common segments. It continues until a segment ends in a blank. This diagram shows how it might build the name Calina:

3. This is where Apps Script takes over. Just once per source list of names, we run the utility function below (shown slightly simplified) to convert the spreadsheet data to a more useful format and store it in ScriptDb. The script can then pull the data from ScriptDb in about 0.5s, versus about 5s to read directly from the spreadsheet. Note that we’re using Script Properties to store the spreadsheet ID rather than cluttering up the code with extra variables.

function updateDb() {
  var language = 'americanFemale';

  // Look up the spreadsheet ID in Script Properties, then grab its sheets. 
  var ssId = ScriptProperties.getProperty(language);
  var sheets = SpreadsheetApp.openById(ssId).getSheets();
  var dictSize = sheets[0].getLastRow() - 1;
  var segment = {};

  // Transform each sheet into the segment object we want later.
  for (var i = 0; i < sheets.length; i++) {    

    // Retrieve the list of real names (first loop) or a pivot table.
    if (i === 0) {
      segment.data = sheets[0].getRange('A:A').getValues();
    } else {
      segment.data = sheets[i].getDataRange().getValues();
    }
    
    // Store other properties so we can retrieve the right record later.
    segment.index = i;
    segment.language = language;
    segment.size = dictSize;

    // Save the object as a ScriptDb record, then start the loop again.
    ScriptDb.getMyDb().save(segment);
  }
}

4. Now, every time the app runs, it jumps straight to the generateNames() function shown below (again slightly simplified). After it queries the database, it’s straight JavaScript — but one Apps Script–specific trick you’ll notice is that we assemble the data into an array using segments[current.index] = current.data rather than segments.push(current.data). Because ScriptDb returns the records in an unpredictable order, we gave our objects an index property to store the correct order.

function generateNames(language, numNames) {
  
  // Query the database to find all results for this language.
  var results = ScriptDb.getMyDb().query({language: language});
  var current = {};
  var segments = [];
  
  // Assemble the DB records into an array so we can pass it around.
  while (results.hasNext()) {
    current = results.next();
    segments[current.index] = current.data;
  }
  
  var names = [];
  var segment = '';
  
  for (var i = 0; i < numNames; i++) {
    var name = '';
    
    // For each requested name, pick one segment, making
    // sure it overlaps with the previous two letters.    
    for (var j = 1; j < segments.length; j++) {
      segment = randomSegment(segments[j], name);
      name = name.slice(0, name.length - 2);
      name += segment;
      
      // If the segment wasn't full length (end of a name), done!
      if (segment.length < 3) {
        break;
      }
    }
    
    names.push(name);
  }
  
  return names;
}

I haven’t explained the randomSegment() function, but you can probably guess at how it works based on the description above. Still, if you want to dig in further, you can view the full source code here.

5. The only remaining step is to expose the results to the world through a web app. Apps Script provides several ways of doing this; I used Html Service, but didn’t require any of the advanced features. Here’s how little HTML we need to turn NameGen into a functional app:

<html>
  <body>
    <script>
      function sendRequest() {
        var language = document.getElementById('language').value;
        var numNames = document.getElementById('numNames').value;
        google.script.run.withSuccessHandler(updateField).
            generateNames(language, numNames);
      }
    
      function updateField(names) {
        var output = "";
        for (var i = 0; i < names.length; i++) {
          output += (names[i] + '<br/>');
        }
        document.getElementById('resultsBox').innerHTML = output;
      }
    </script>
    <select id="language">
      <option value="americanFemale">American Females</option>
      <option value="americanMale">American Males</option>
      <option value="american">American Towns</option>
      <option value="british">British Towns</option>
      <option value="french">French Towns</option>
      <option value="irish">Irish Towns</option>
      <option value="italian">Italian Towns</option>
      <option value="spanish">Spanish Towns</option>
    </select>
    <select id="numNames">
      <option value=1>1</option>
      <option value=10 selected>10</option>
      <option value=100>100</option>
      <option value=1000>1000</option>
    </select>
    <input id="generateButton" type="button"
     value="Generate" onclick="sendRequest()">
    <div id="resultsBox">
    </div>
  </body>
</html>

And presto chango, you have a web app that harnesses the tremendous power of Google’s infrastructure … to produce names that could only ever exist in a parallel universe. It’s like Adriano Celentano’s "Prisencolinensinainciusol" — a convincing rendition of an American pop song, unless you actually speak English, in which case it’s total gibberish.

Dan Lazin profile | twitter

Dan is a technical writer on the Developer Relations team for Google Apps Script. Before joining Google, he worked as video-game designer and newspaper reporter. He has bicycled through 17 countries.

Managing Projects with Gantt Charts using Google Apps Script

2012-12-21T10:42:00.000-08:00

Editor’s Note: Guest author Ronald Dahrs runs Forscale, an IT and project management company based in the Netherlands. -- Arun Nagarajan

Google Apps is well-suited for project management because it’s a cloud-based productivity suite that helps you and your team connect and get work done from anywhere on any device. Using Google Apps Script, we can push the capabilities even further to create advanced scheduling and management tools. A common tool in project management circles is the Gantt chart: a schedule of the tasks in the project and how they relate to each other over time.

The spreadsheet that generated that Gantt chart is available in the template gallery today. In this post, we’ll explore the basics of how the template works and explain a few of the Apps Script techniques that transform Google Sheets into such a powerful project management tool.

When you open the template, you’ll see stubs for each type of task, but the screenshot above shows an example of a slightly larger project plan — in fact, the same data used to generate the Gantt chart below.

The template’s sophisticated formulas rely on the structure of the table to enable schedule awareness and task dependencies. However, we still ensure that the user can rename, rearrange, or add columns by using a hidden header to identify each column. This diagram demonstrates the spreadsheet’s structure:

In Apps Script, we use the spreadsheet’s onEdit() event to monitor user interaction with the schedule portion of the spreadsheet and update the Gantt chart accordingly. The powerful JavaScript language does all the required summary calculations based on the provided dates and completion percentages.

We have also used Apps Script’s addMenu() method to build a custom menu that calls row-oriented functions like indenting tasks to get a so-called Work Breakdown Structure with summary tasks. If you just want to see an overview, the custom menu allows you to collapse tasks, which we accomplished through the hideRows() method.

For changes that do not trigger an onEdit() event (for example, clearing a row), the user can use the menu’s Refresh command to recalculate the schedule.

The template stores user preferences as Script Properties and offers an interactive user interface built in UiApp to change those settings:

Finally, to render the Gantt chart, we use cell background colors to visually group and highlight the appropriate cells. This creates the effect of a continuous calendar with clearly visible start and finish dates for each task.

var ganttColors = ganttRange.getBackgroundColors();
var ganttValues = ganttRange.getValues();

// update Gantt colors and values 
ganttRange.setBackgroundColors(ganttColors).setValues(ganttValues);

Ronald Dahrs

Ronald combines his knowledge of project management and software solutions at his company, Forscale. He believes Google Apps is an excellent platform for online project management. He uses Google Apps Script to integrate the services to manage a wide range of projects.

The Hottest Script in Hollywood

2012-12-19T09:20:00.002-08:00

In just a few hours at the recent Apps Script hackathon in Los Angeles, we saw attendees build everything from website monitoring to room booking to financial tracking apps. For those of you who couldn’t make it, attendees were given a brief introduction to Apps Script and a few hours to let their imaginations run wild. Apps Script’s ease of use enabled them to quickly create fully functioning, useful apps. Here are a few interesting things we saw from local developers:

Website Monitor by Eduardo Arino de la Rubina

These days, small businesses are quickly increasing their online presence; a website outage during a critical period can be devastating to a mom-and-pop shop. Eduardo realized that existing network-monitoring solutions require a significant investment in technology and infrastructure that is beyond the reach of many small-business users. Using Apps Script’s UrlFetch and Spreadsheet services, he was able to quickly create a website monitor packaged in an easy-to-use spreadsheet that, given a list of URLs, tries to fetch each one and records the latency and content length.

The code is available here.

Get A Room by Danny Favela

Get A Room allows users to book meeting rooms by taking advantage of Apps Script’s tight integration with Google Calendar and events. The app, built entirely on a Chromebook utilizing Apps Script's cloud friendliness, displays building floorplans with buttons that users can click to book a room. In response to a booking request, the app fetches the room’s calendar and creates a new event. It also updates the UI by replacing the floor plan with a modified image to show the newly booked room. Here is a snippet of the booking code:

// Click handler for the interaction to book a room
function bookBoardroomHandler(e) {
 var app = UiApp.getActiveApplication();

 // Perform the calendar-booking operations
 bookBoardroom();
 
 // Swap the images as visual confirmation
 app.remove(app.getElementById('imageDefaultLayout'));
 app.add(app.getElementById('imageBoardroom'));

 app.close();
 return app;
}

function bookBoardroom(e) {
 var calendarBoardroom = CalendarApp.getCalendarsByName("Boardroom");
 calendarBoardroom[0].createEventFromDescription("Boardroom Meeting");  
}

Stock Info by Matt Kaufman

Matt decided to build a web service that provides information about publicly traded stocks. The app’s backend consists of a spreadsheet with stock symbols of interest. Using Apps Script’s FinanceService, Matt loops through the spreadsheet on a timed trigger and appends the latest stock information for each symbol. He then uses HtmlService to create a web app that outputs an XML page of the stock info based on a symbol parameter in the URL. Here’s a picture of his script in action:

These are just some examples of how quickly useful apps can be created with Apps Script. Thanks to all the attendees for coming out! If you couldn’t make it to the hackathon, check out these tutorials to see how you can get started making great apps.

Kalyan Reddy profile | Stack Overflow

Kalyan is a Developer Programs Engineer on the Google Apps Script team based in NYC. He is committed to increasing developer productivity by helping them fully utilize the power of Apps Script. In his free time, he enjoys participating in the Maker community and hacking together robots.