Google Apps Developer Blog

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

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

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.

AdWords Analysis in Google Apps Script

2012-12-13T08:59:00.000-08:00

Editor’s Note: Guest author David Fothergill works at QueryClick, a search-engine marketing company based in the UK. — Eric Koleda

Working in Paid Search account management, I've often found tremendous wins from making reports more useful and efficient. Refining your analytics allows you to streamline your workflow, allowing more time for strategic and proactive thinking — and that's what we're paid for, not endless number-crunching.

The integration between Google Analytics and Apps Script has opened up lots of opportunities for me to make life easier through automation. In a recent blog post on my agency's website, I outlined how an automated report can quickly “heatmap” conversion rate by time and day. The aim of the report is to provide actionable analysis to inform decisions on day-part bidding and budget strategies.

In that post, I introduce the concepts and provide the scripts, sheet, and instructions to allow anyone to generate the reports by hooking the scripts up to their own account. Once the initial sheet has been created, the script only requires the user to provide a Google Analytics profile number and a goal for which they want to generate heatmaps. In this post, we’ll break down the code a bit.

Querying the API

This is a slight amendment to the code that queries the Core Reporting API. Apart from customising the optArgs dimensions to use day and hour stats, I have modified it to use goal data from the active spreadsheet, because not all users will want to measure the same goals:

function getReportDataForProfile(ProfileId, goalNumber) {
  //take goal chosen on spreadsheet and select correct metric
  var tableId = 'ga:' + ProfileId;
  if (goalNumber === 'eCommerce Trans.') {
    var goalId = 'ga:Transactions' ;
  } else {
    var goalId = 'ga:goal' + goalNumber + 'Completions';
  }
  // Continue as per example in google documentation ...
}

Pivoting the Data

Once we’ve brought the Google Analytics data into the spreadsheet in raw form, we use a pivot table to plot the hour of the day against the day of the week.

For this type of report, I'd like to use conditional formatting to heatmap the data — but conditional formatting in Google Sheets is based on fixed values, whereas we want the thresholds to change based on cell values. However, thanks to the flexibility of scripts, I was able to achieve dynamic conditional formatting.

Conditional Formatting Using Scripts

The script needs to know the boundaries of our data, so I’ve set up several cells that display the maximums, minimums, and so forth. Once these were in place, the next step was to create a function that loops through the data and calculates the desired background color for each cell:

function formatting() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().
      getSheetByName('Heatmap');
  
  var range = sheet.getRange('B2:H25');
  range.setBackgroundColor('white');
  var values = range.getValues()
      
  //get boundaries values for conditional formatting
  var boundaries = sheet.getRange('B30:B35').getValues();

  //get range to 'heatmap'
  var backgroundColours = range.getBackgroundColors();

  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      // Over 90%
      if (values[i][j] > boundaries[1][0]) {
        backgroundColours[i][j] = '#f8696b';
      }
      // Between 80% and 90%
      if (values[i][j] < boundaries[1][0] 
          && values[i][j] >= boundaries[2][0])  {
        backgroundColours[i][j] = '#fa9a9c';
      }
      // Between 60% and 80%
      if (values[i][j] < boundaries[2][0] 
          && values[i][j] >= boundaries[3][0])  {
        backgroundColours[i][j] = '#fbbec1';
      }
      // Between 40% and 60%
      if (values[i][j] < boundaries[3][0] 
          && values[i][j] >= boundaries[4][0])  {
        backgroundColours[i][j] = '#fcdde0';
      }
      // Between 20% and 40%
      if (values[i][j] < boundaries[4][0] 
          && values[i][j] >= boundaries[5][0])  {
        backgroundColours[i][j] = '#ebf0f9';
      }
      // Less than 20%
      if (values[i][j] < boundaries[5][0])  {
        backgroundColours[i][j] = '#dce5f3';
      }
    }
  }
  // set background colors as arranged above
  range.setBackgroundColors(backgroundColours);
}

Calling the functions based on the profile ID and goal number specified in the main sheet gives us a quick, actionable report that can easily be adapted for use across multiple accounts.

function generateHeatmap() {
  try {
    var profileId = SpreadsheetApp.getActiveSpreadsheet()
        .getSheetByName('Heatmap').getRange(4,10).getValue();
    var goalNumber = SpreadsheetApp.getActiveSpreadsheet()
        .getSheetByName('Heatmap').getRange(7,10).getValue();
    if (profileId === '') {
      Browser.msgBox('Please enter a valid Profile ID');
    } else {     
      var results = getReportDataForProfile(profileId, goalNumber);
      outputToSpreadsheet(results);
      formatting();
    }
  } catch(error) {
    Browser.msgBox(error.message);
  }
}

This was my first foray into the slick integration between the Core Reporting API and spreadsheets, but has proven a valuable test case for how effective it will be to roll this method of reporting into our daily process of managing accounts.

We have now started the next steps, which involves building out “client dashboards” that will allow account managers access to useful reports at the press of a button. This moves us toward the goal of minimizing the time gathering and collating data, freeing it up to add further value to client projects.

Editor's Note: If you're interested in further scripting your AdWords accounts, take a look at AdWords Scripts, a version of Apps Script that's embedded right into the AdWords interface.

David Fothergill profile | twitter | LinkedIn

Guest author David Fothergill is a Project Director at the search marketing agency QueryClick, focusing on Paid Search and Conversion Optimisation. He has been working in the field for around 8 years and currently handles a range of clients for the company, in verticals ranging from fashion and retail through to industrial services.

Building Google Drive Apps on Android

2012-12-04T13:45:00.000-08:00

When we launched version 1.0 of Google Play services to all Android 2.2+ devices worldwide in September, one of our main goals was to provide developers with better tools for working with OAuth 2.0 tokens in their Android apps.

Thanks to the new components, Android apps can get access to Google APIs with an easy-to-use authentication flow and can provide a consistent experience to both their users and developers. We recently decided to test that statement by writing a small camera app that automatically uploads photos you take to your Google Drive account.

We documented all the steps required to go from zero to hero in a quickstart guide. By following the step-by-step instructions in the guide, you’ll have a working Android app that uses Google Play services to perform authorization and the Google Drive API to upload files to Drive.

Do you want to learn how to build this app but prefer to watch a video tutorial instead of reading the documentation? We’ve got you covered! Check out the recording of the Google Developers Live session that covers the setup and running of the quickstart app.

If you’re building an Android app that integrates with Drive and have questions for us, please don’t hesitate to let us know on Stack Overflow.

Claudio Cherubino profile | twitter | blog

An easier way to configure Google Apps domains

2012-12-04T08:30:00.000-08:00

Setting up a new domain name and configuring it to work with Google Apps email is about to get a lot easier. We’re working with the top domain registrars worldwide to reduce the number of manual steps necessary for this portion of the signup process. We made improvements earlier this year to allow users to more easily verify their domain with GoDaddy and eNom, now with a new API available to any registrar, users can verify and transfer their email in 3 easy steps, down from 10–and users are no longer required to leave the Google Apps signup flow to complete domain registration.

Customers can experience the new, easier process today with TransIP and Hover domains, as these registrars have completed their integrations with Google Apps signup flow API. More than 10 additional registrars, including some of the largest, are actively building through the API and are currently expected to be available through the simpler setup over the next few months:

Afrihost
Domaindiscount24.com, a subsidiary of Key-Systems.net
Gandi
Go Daddy
eNom
GMO Internet Group
Heartinternet.co.uk
Domainmonster.com
123-reg.co.uk
LCN.com
Melbourne IT
Name.com
Network Solutions, a subsidiary of Web.com
Wix.com
WebNic

If you are a registrar interested in implementing this RESTful API to automate the DNS setup process, please apply here.

Mohan Konanoor

Mohan Konanoor is a Software Engineer working for the Google Apps for Business team. He is currently leading various initiatives around the area of signing up and on-boarding for Google Apps.

Analytics reporting with Google Apps Script at the UK Cabinet Office

2012-12-03T08:30:00.000-08:00

Editor’s Note: Guest author Ashraf Chohan works at the Government Digital Service (GDS), part of the UK Cabinet Office. -- Arun Nagarajan

Recently, when we were preparing the launch of GOV.UK, my team was tasked with creating a series of high-level metrics reports which could be quickly compiled and presented to managers without technical or analytical backgrounds. These reports would be sent daily to ministers and senior civil servants of several government departments, with the data customised for each department.

We decided to use Adobe InDesign to manage the visual appearance of the reports. InDesign’s data-merge functionality, which can automatically import external data into the layout, made it easy to create custom departmental reports. The challenge was to automate the data collection using the Google Analytics API, then organize the data in an appropriate format for InDesign’s importer.

In a previous post on this blog, Nick Mihailovski introduced a tool which allows automation of Google Analytics Reporting using Google Apps Script. This seemed an ideal solution because the team only had basic developer knowledge, much of the data we needed was not accessible from the Google Analytics UI, and some of the data required specific formatting prior to being exported.

We started by building the core reports in a Google spreadsheet that pulls in all of the required raw data. Because we wanted to create daily reports, the start and end dates for our queries referenced a cell which defaulted to yesterday’s date [=(TODAY())-1].

These queries were dynamically fed into the Google Analytics API through Apps Script:

// All variables read from each of the “query” cells  
var optArgs = {
    'dimensions': dimensions,              
    'sort': sort
    'segment': segment
    'filters': filters,         
    'start-index': '1',
    'max-results': '250'                    
  };

  // Make a request to the API.
  var results = Analytics.Data.Ga.get(
      tableId,                  // Table id (format ga:xxxxxx).
      startDate,               // Start-date (format yyyy-MM-dd).
      endDate,                 // End-date (format yyyy-MM-dd).
      endDate,                 // Comma seperated list of metrics.
      optArgs);

Next, we created additional worksheets that referenced the raw data so that we could apply the first stage of formatting. This is where storing the data in a spreadsheet really helps, as data formatting is not really possible in the Google Analytics UI.

For example, the final report had a 47-character limit for page titles, so we restricted the cells in the spreadsheet to 44 characters and automatically truncated long URLs by appending “...”.

Once the initial formatting was complete, we used formulas to copy the data into a summary sheet specially laid out so it could be exported as a CSV file that merges seamlessly into InDesign.

Below is an example of how a report looks on publication. Nearly everything on the page was extracted from the API tool, including the department name and the day number. Because most of the data was automated, it required minimal effort on our part to assemble these reports each morning.

We discovered that an added bonus of pulling data into a Google spreadsheet was that it also allowed us to publish the data to a Google site. This helped us display data to stakeholders without adding lots of users to our Google Analytics account.

The tools let us present Google Analytics data in deeper, more creative ways. That’s really important as we share information with more and more non-technical people, whether they’re inside GDS or beyond.

Ashraf Chohan

Guest author Ashraf Chohan works at the Government Digital Service (GDS), part of the UK Cabinet Office. GDS’s role is to deliver digital transformation of government services. Ashraf is a Product Analyst for GOV.UK, a new site for public services and information.

Managing tasks and reminders through Google Apps Script

2012-11-29T13:16:00.000-08:00

Editor’s Note: Guest author Romain Vialard works at Revevol, an international service provider dedicated to Google Apps and other Cloud solutions. -- Arun Nagarajan

There are many tools available to help you manage a task list and Google Apps comes with its own simple Tasks app. But sometimes it is more convenient and collaborative to simply manage your task list in a shared spreadsheet. This spreadsheet can be a simple personal task list or a project-management interface that requires team-wide coordination.

Google Spreadsheets come with a set of notification rules that might come in handy. For example, you can be notified each time someone adds a new task to the list or each time the status of a task is updated. Furthermore, it is very easy add to add basic reminders through Apps Script with just a few lines of code:

function remindMe() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var data = sheet.getDataRange().getValues();
  for(var i = 1; i < data.length; i++){
    if(data[i][2] > new Date()){
      MailApp.sendEmail(message);
    }
  }
}

The simple remindMe function performs a standard JavaScript-based date comparison on every row and sends an email for tasks that are due. You can then schedule the remindMe function via a programmable trigger based on the settings.

This script is already available in the Script Gallery today. Try it out for yourself!

Once you have installed the script, you get a new menu option in the spreadsheet that opens a simple user interface to set the options you want. As a developer, you can extend this interface further to provide more options and capabilities.

Romain Vialard profile | YouTube

Romain Vialard is a Google Apps Change Management consultant at Revevol. Romain writes scripts to automate everyday tasks, add functionality and facilitate rapid adoption of cutting edge web infrastructures. As a Google Apps Script Top Contributor, he has also built many of the top scripts in the Apps Script Gallery, including the very popular Gmail Meter.