Editor’s Note: Guest author Romain Vialard works at Revevol, an international service provider dedicated to Google Apps and other Cloud solutions. -- Jan Kleinert
In a previous post, Improving Revevol’s Productivity with Google Apps Script, we demonstrated how Apps Script helped us handle a lot of training requests. For any given client, using tools we built with Google Apps Script, we are able to quickly find the perfect trainer depending on variables like the date, the place, the language and the training scope. To ensure that the training we do meets a consistent quality bar, we send a survey to all the participants at the end of the training. This post discusses how we use Google Apps to conduct these surveys to glean insight into the quality of our training.
We started our survey project by using simple Google Forms to poll our users. Each form creates a spreadsheet per language, each with thousands of submissions. From this data, we need to create visualizations to quickly make sense of all the information we gather. We want our international clients to each be presented with a unique dashboard for trainings in all their subsidiaries, our change managers to be able to see the results of any specific training to be sure that everything went well, and our trainers to see only the data they need.
We used Apps Script to tie all the pieces together to fulfill these requirements. We created a translation table in a spreadsheet to automate the translation of each survey, and persist the results using JSON two-dimensional arrays in a spreadsheet cell. Using this data, we present a web based front-end to show several charts and bring controls to filter the data in many ways. Each client is provided with a special access key that allows them to view the dashboards relevant to their organization. Clients log in via their existing Google Accounts, and the application presents and enforces appropriate access control rights that are depending upon their role in the organization.
With the recent addition of libraries in Apps Script, we were able to build this dashboard in a very short amount of time using a few of the notable script libraries linked to from the Apps Script documentation.
With ArrayLib’s method filterByText(data, columnIndex, value), we are able to implement filtering to enforce access controls by role:
filterByText(data, columnIndex, value)
if (e.parameter.selectedTrainer != 'All' && e.parameter.selectedTrainer != undefined) data = ArrayLib.filterByText(data, 1, e.parameter.selectedTrainer); if (e.parameter.selectedClient != 'All' && e.parameter.selectedClient != undefined) data = ArrayLib.filterByText(data, 2, e.parameter.selectedClient.split(','));
With PivotChartsLib, we can create charts based on our survey results in only a few lines of code:
var grid = app.createGrid(3, 2); var chart = PivotChartsLib.createColumnChart(data, 10); grid.setWidget(0, 0, chart); var chart = PivotChartsLib.createPieChart(data, 9); grid.setWidget(0, 1, chart); var chart = PivotChartsLib.createColumnChart(data, 6); grid.setWidget(1, 0, chart);
Apps Script is all about Google Apps. Applications running on Apps Script handle authentication as well as integrating seamlessly with spreadsheets as well as other parts of Google Apps. With Apps Script, we have a powerful tool to tie together all of the more general services from Google Apps and build rich, domain specific applications for our clients.
Hey Tel Aviv developers!
We are organizing a hackathon focusing on Google Drive next week. If you’d like to learn more about the Google Drive SDK and have fun developing your first Google Drive application, join us there!
The event will take place at the Afeka Tel Aviv Academic College of Engineering. We’ll start with an introduction to the Google Drive SDK at 17:30 on Tuesday September 4th 2012 and the hackathon will run through the whole of the next day. We’ll also have some of the Android team to talk a bit about Android, so be sure to check that out. See the detailed agenda of this 2-day event.
Don’t forget to register and have a look at this document to help you prepare.
See you there!
Editor's note: This has been cross-posted with the Google Analytics blog and the Google Developers blog -- Jan Kleinert
Many people have been asking for a simple way to put Google Analytics data into a Google Spreadsheet. Once the data is inside a Google Spreadsheet, users can easily manipulate Google Analytics data, create new visualizations, and build internal dashboards.
So today we released a new integration that dramatically reduces the work required to put Google Analytics data into any Apps Script supported product, such as Google Docs, Sites, or Spreadsheets.
Here’s an example of Google Analytics data accessed through Apps Script and displayed in a Google Spreadsheet.
We know that a popular use case of this integration will be to create dashboards that automatically update. To make this easy to do, we’ve added a script to the Spreadsheets script gallery that handles all this work - no code required. The script is called Google Analytics Report Automation (Magic).
This script is a great template for starting your own project, and we’ve had many internal Google teams save hours of time using this tool. Here’s a video demoing how to build a dashboard using this script:
You can find this script by opening or creating a Google Spreadsheet, clicking Tools -> Script Gallery and searching for “analytics magic”.
Of course many developers will want to write their own code. With the new Analytics – Apps Script integration, you can request the total visitors, visits, and pageviews over time and put this data into a spreadsheet with just the following code:
// Get Data. var results = Analytics.Data.Ga.get( tableId, startDate, endDate, 'ga:visitors,ga:visits,ga:pageviews', {‘dimensions’: ‘ga:date’}); // Output to spreadsheet. var sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(); sheet.getRange(2, 1, results.getRows().length, headerNames.length) .setValues(results.getRows()); // Make Sandwich.
To get started now, read our Automated Access to Google Analytics Data in Google Spreadsheets tutorial. Also check out the Google Analytics Apps Script reference docs.
Are you ready to start building solutions using Google Analytics and Google Apps Script?
We’d love to hear new ways you use this integration to help manipulate, visualize and present data to solve business problems. To encourage you to try out this integration, we are giving out Google Analytics developer t-shirts to the first 15 developers to build a solution using both APIs.
To be eligible, you must publish your solution to either the Chrome Web Store or the Spreadsheets Script Gallery and include a description of a business problem the script solves. We’ll then collect these scripts and highlight the solutions in an upcoming blog post. After you publish your script, fill out this form to share what you’ve built.
We’re looking forward to seeing what you can do with this integration.
Note from editor: The syntax for this feature has changed since this was first posted. The posted has been edited to reflect the change.
Try to think back to when the word “viral” had only negative connotations . . . if you are a web developer trying to market your app on a minimal budget, you may not remember those dark days at all! Viral marketing is currently not just the cheapest but arguably the most effective way to spread the word about your app and to drive user adoption. Through file sharing and MIME type-filtered upsells, Google Drive integration gives apps some powerful “viral” marketing capabilities.
Users love to share files. Google Drive makes this easy for them to do, and we know from experience that they do it often. When users share or sync files that they can’t open using an installed viewer, Drive displays a link to a Chrome Web Store list of apps that can open that file type -- potentially, your app. This can be a powerful mechanism for distributing your app to the users that actually need it.
For example, let’s say someone asks me to review a project plan saved in an .mpp file. I currently don’t have a viewer to open such a file. Am I out of luck? No — help is right there for me at the bottom left of the screen:
.mpp
If I click on this link, I’m redirected to a Chrome Web Store list of installable apps that have registered themselves to open .mpp files. Currently, this includes some excellent options for Drive-integrated project management web apps:
Interested in getting your app in a list like this? It’s not difficult. First, add a special web intent to your Chrome Web Store manifest. This intent should include the MIME types and extensions you'd like your app to be searchable by. Though the type field accepts only MIME types, it allows you to model file extensions as the special type application/vnd.google.drive.ext-type.<EXTENSION>.
type
application/vnd.google.drive.ext-type.<EXTENSION>
{ "name" : "ProjectManagmentApp", "version" : "1", "description" : "A web app to manage projects", "container" : "GOOGLE_DRIVE", "api_console_project_id" : "1234567891011", "gdrive_mime_types": { "http://drive.google.com/intents/opendrivedoc": [ { "type": ["application/vnd.ms-project", "application/vnd.google.drive.ext-type.mpp"], "href": "http://projectapp_web_url/", "title" : "Open", "disposition" : "window" } ] }, ...
Once an intent like this is published in your app listing, you’ll be featured in Chrome Web Store “upsell” lists like the one depicted above, and users viewing the list will be a click away from installing your app. For full detail on adding this web intent to your manifest and testing it for your Chrome Web Store listing, see Help Users Find your App in the Drive SDK documentation.
We recommend that any and all listed apps should list their MIME types for filtering in this way. However, doing so is especially beneficial for apps that can open any of these following types, for which there is currently no registered viewer at all:
message/rfc822
text/x-vcard
application/x-font-ttf
image/gif
Inevitably, users who lack valid viewers will end up with shared files of these MIME types. And just when they are about to throw up their hands, they’ll find your app at the top of the list of apps that can help them open the file. This creates the conditions for a very positive first user experience for your app.
If you have questions or comments about how to add this feature to your app, don’t hesitate to let us know on our Stack Overflow tag, google-drive-sdk.
We know many organizations using Google Drive storage or Google Maps Coordinate have hundreds or even thousands of users to which licenses need to be assigned. That’s why we’re pleased to announce a more streamlined method for managing these licenses using the Enterprise License Manager API.
The API offers a simple and faster way to access the functionality of the License Manager user interface. It can be used to programmatically assign, unassign and re-assign Google Drive storage or Google Maps Coordinate licenses to users in your domain. Google Apps reseller administrators and their customers can also use the API for the same programmatic license assignment.
To begin using the Google Enterprise License Manager API today, follow the instructions in the API documentation. You will need to sign in to the Google APIs Console and activate the API. If you have any questions about this API, please ask them in the Domain Information and Management APIs forum.
Hello Austin! We had such a blast with our previous Apps Script hackathons in Washington DC, Mountain View, Chicago and NYC that we’re spreading the joy to our friends in Austin. If you’re an Austin-based developer looking to learn more about Google Apps Script, meet the Apps Script team or just to meet other like-minded developers and build cool things, don’t miss out on this event! We will be holding a hackathon on Thursday, September 13, 2012 between the hours of 2-8pm Central Time at our Austin, TX office.
We’ll be covering the basics of Apps Script for new developers as well as going over a brief overview of all the new features we’ve launched since Google I/O. We’ll be providing the four Ps: power, ping and pizza pie (that’s power strips, wireless internet and food for those of you that aren’t sure!), as well as experts on hand to help you turn your ideas into great apps. Check out the details of the event and RSVP once you know you can make it!
The week before Google I/O, we launched Google Developers Live, a new online channel to connect us with developers from all around the world, all year round.
Google Developers Live features interactive broadcasts about many different products and with many different formats. For Google Drive and Apps Script alone, we have aired app reviews, presentations about Google Drive and Apps Script, question and answer sessions, and a doc feedback session.
We are really interested in knowing from you about your favorite shows. Which types of event would you like to see more of in the future? Are you more interested in introductory material such as getting started tutorials, or more advanced topics?
Please share your feedback with us by adding a comment to this post or by reaching out to us on Google+.
Remember, we go live every Monday and Thursday and our complete schedule can be found at https://developers.google.com/live/drive. See all of you on Google Developers Live!
Three months ago, we launched Google Drive along with the Google Drive SDK. The SDK allows applications to manage files that users have uploaded to Drive and to integrate deeply in the Google Drive UI. Today, we’ve just extended the SDK to allow developers to interact with native Google Docs types such as Google Spreadsheets, Presentations, Documents, and Drawings.
We now provide an easy way to export native Google documents through the Google Drive API. We also allow native Google documents to be opened directly from within the Google Drive UI using third-party applications.
If your application is already a Drive-installable app, you can enable this feature by checking the Allow users to open files that can be converted to a format that this app can open option in the Google APIs Console under Drive SDK > Import:
When this feature is enabled, your application will show up under the “Open with” menu in the Google Drive Web UI for the file formats you support. Here’s how it works: if your application supports opening one of the possible export formats for the Google document, users will be able to open that Google document with your application. So for instance, if your application is configured to open PDF files, then because Google Documents are exportable to PDF, users will be able to use your application to open those documents as shown below.
When opening a native Google Document with your application from the Google Drive UI, we will pass the following JSON Object in the state URL parameter of the OAuth 2.0 redirect request that is forwarding the user to your website.
state
{ "exportIds": ["file_id"], "action":"open" }
Then you can use the file ID contained in the JSON object to query the Google Drive API and fetch the file’s metadata. Note that the state URL parameter is different when opening regular files as we use the JSON attribute exportIds instead of ids.
exportIds
ids
Unlike the metadata of regular files which contain a downloadUrl attribute which you can use to download the file’s content, the metadata for native Google documents contains a collection of export URLs. These URLs - one for each supported export format - are listed under the attribute exportLinks, as shown in the HTTP request/response below.
downloadUrl
exportLinks
Request:
GET /drive/v2/files/<file_id> HTTP/1.1 Host: www.googleapis.com Authorization: Bearer <access_token>
Response:
HTTP/1.1 200 OK Status: 200 ... { "kind": "drive#file", "id": "<file_id>", ... "exportLinks": { "application/vnd.oasis.opendocument.text": "https://docs.google.com/...", "application/msword": "https://docs.google.com/...", "text/html": "https://docs.google.com/...", "application/rtf": "https://docs.google.com/...", "text/plain": "https://docs.google.com/...", "application/pdf": "https://docs.google.com/..." }, ... }
You can query any of these export URLs using an authorized request to download the Google document in your prefered export format.
Below is the full list of supported export formats -- and their associated MIME types -- for the different types of native Google documents:
Google Documents:
Google Spreadsheets:
Google Presentations:
Google Drawings:
Please check out the Google Drive SDK documentation if you’d like to learn more, and feel free to ask any questions you may have on Stack Overflow.
If your application needs a way to let users easily choose a file from their Drive, this is for you.
Users can browse and select files from their Drive file list using the Google Picker API. The Google Picker API provides a user interface containing a list of all the user's files in Google Drive.
Since the user interface is generated by the Picker API, there is very little effort in adding the Picker to an existing site. This article will show how to use the picker for your application, and discuss some of the configuration options.
First create a view on the data describing the type of Picker that we will be using. In this case, we’ll use google.picker.ViewId.DOCS. For more types of Picker, see the documentation.
google.picker.ViewId.DOCS
var view = new google.picker.View(google.picker.ViewId.DOCS);
You can set the MIME types to filter the list of files. This allows you to specify your application’s specific file types to display to the user.
view.setMimeTypes("text/plain,text/html");
Use a PickerBuilder to set the required configuration parameters for your Picker.
PickerBuilder
var picker = new google.picker.PickerBuilder() .enableFeature(google.picker.Feature.NAV_HIDDEN) .setAppId("your app id") .addView(view) .setTitle("Select a Text File") .setCallback(pickerCallback).build();
Once configured, the picker can be popped up to the user as often as you like, using
picker.setVisible(true)
When a user selects a file with the Picker, the callback set in setCallback is called with the data from the dialog. Pass this callback as the action to perform when a user selects a file in the Picker.
setCallback
function pickerCallback(data) { if (data.action == google.picker.Action.PICKED) { var fileId = data.docs[0].id; alert('The user selected: ' + fileId); } }
Check the data’s action, in this case google.picker.action.PICKED, and if it is appropriate, access the file ID as the the first element of the docs attribute.
google.picker.action.PICKED
Here are some additional tips on customizing your Picker.
var picker = new google.picker.PickerBuilder() .enableFeature(google.picker.Feature.MULTISELECT_ENABLED)
var picker = new google.picker.PickerBuilder() .enableFeature(google.picker.Feature.NAV_HIDDEN)
For a complete example, including how to load the Picker library, please visit our the Drive SDK documentation. Also, see the Picker API documentation for more information.
Have you tried using the Google Drive API? If so, you’re aware that it allows you to programmatically manage a user’s Google Drive and build applications to manipulate files stored in the user’s account. However, the API might still be capable of doing a few things you didn’t know about. Here is a list of five specific use cases and how each of them can be addressed with the API.
When a file in Google Drive is shared publicly, it can be downloaded without authentication at the URL provided by the API in the webContentLink field of the Files resource. To retrieve that value, send a GET request to retrieve the file metadata and look for the webContentLink element in the JSON response, as in the following example:
webContentLink
{ "kind": "drive#file", "id": "0B8E...", "etag": "WtRjAP...", "selfLink": "https://www.googleapis.com/drive/v2/files/0B8E...", "webContentLink": "https://docs.google.com/a/google.com/uc?id=0B8E...", ... }
When setting permissions for a file with the Drive API, you can choose one of owner, writer and reader as the value for the role parameter. The Drive UI also lists another role, commenter, which is not allowed for that parameter.
owner
writer
reader
role
commenter
In order to grant comment-only access to a user with the Drive API, you have to set the role parameter to reader and include the value commenter in the list of additionalRoles, as in the following example:
additionalRoles
{ "kind": "drive#permission", ... "role": "reader", "additionalRoles": [ "commenter" ], ... }
It is possible to restrict the list of files (and folders) returned by the Drive API by specifying some search criteria in the q query parameter. Each file has a parents collection listing all folders containing it, and the root folder in Google Drive can be conveniently addressed with the alias ‘root’. All you need to do to retrieve all files in that folder is add a search query for element with ‘root’ in their parents collection, as in the following example:
‘root’
GET https://www.googleapis.com/drive/v2/files?q='root' in parents
Remember to URL-encode the search query for transmission unless you are using one of the available client libraries.
Your application might need to know if users have enough available quota to save a file, in order to handle the case when they don’t. Quota information is available in the About feed of the Drive API:
{ "kind": "drive#about", ... "quotaBytesTotal": "59055800320", "quotaBytesUsed": "14547272", "quotaBytesUsedInTrash": "511494", ... }
The feed includes three values related to quota management: quotaBytesTotal, quotaBytesUsed and quotaBytesUsedInTrash. The first value indicates the total amount of bytes available to the user (new accounts currently get 5GB for free) while the second one tells how many of those bytes are in use. In case you need to get more free space, you can use the last value to know how many bytes are used by files that have been trashed. An application might use this value to recommend emptying the trash bin before suggesting to get additional storage.
quotaBytesTotal
quotaBytesUsed
quotaBytesUsedInTrash
Google Drive allows users to store any kind of file and to install applications to open file types that are not directly supported by the native Google applications. In case you need to know what applications are installed and what file types each of them can open, you can retrieve the Apps feed and look for the primaryMimeTypes and secondaryMimeTypes elements for supported MIME types or primaryFileExtensions and secondaryFileExtensions for file extensions:
primaryMimeTypes
secondaryMimeTypes
primaryFileExtensions
secondaryFileExtensions
{ "kind": "drive#app", "name": "Pixlr Editor", ... "primaryMimeTypes": [ "image/psd", "image/pxd", "application/vnd.google-apps.drive-sdk.419782477519" ], "secondaryMimeTypes": [ "image/png", "image/jpeg", "image/gif", "image/bmp" ], "primaryFileExtensions": [ "pxd", "psd" ], "secondaryFileExtensions": [ "jpg", "png", "jpeg", "bmp", "gif" ], … }
Note: to access the Apps feed you have to request access to the https://www.googleapis.com/auth/drive.apps.readonly OAuth scope.
https://www.googleapis.com/auth/drive.apps.readonly
