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
Charts are a great way to visualize information. They let you arrange data in meaningful ways, allow you to tell a story, and can really catch the reader's eye. But when dealing with large datasets, visualizing all the data at once can be somewhere between a tough to impossible mission. You usually need to filter the data and concentrate on a specific part that is of interest, and then when you want to see a different part of the data you'll need to filter it again and refresh the view. That's why we're happy to announce the introduction of dashboards in Apps Script, which allow you to compose multiple charts and filters into a single experience!
What is a dashboard? A dashboard is a visual structure that lets you organize and manage multiple charts that share the same underlying data. The three building blocks of a dashboard are the data source, charts, and controls. Controls are user interface widgets (category pickers, range sliders, or autocompleting text boxes) that someone can interact with in order to drive the data managed by a dashboard to the charts that are part of it.
Dashboards in Apps Script Because of its interactive nature, a dashboard is built in a Google Apps Script UI application. A UI application can be embedded in a Spreadsheet or a Site or served as HTML using the "Deploy as web app" option. They are perfect for creating interactive reports, where users can gain extra insight through exploring the data.
Creating a simple dashboard UI Application Have a look at the following example dashboard where a category picker and a range slider are used to drive the data visualized by a pie chart and a table chart.
Note that a dashboard is an interactive entity. Playing with its controls will change the charts in real time. You can see exactly how this dashboard was created, and learn more about how to build dashboards in general, by reading through our new Building a Charts Dashboard tutorial.
Going further Dashboards can of course be much more complex than the above example. Here is a video demonstrating a more complex dashboard embedded in a Spreadsheet:
To conclude, dashboards are powerful gadgets that allow you to handle and get valuable insights on complex datasets. Now made easy to build in Apps Script, try it on your data.
The Google Drive SDK opens up a great number of possibilities of integration with third-party applications and services. One such integration is via the new Web Intents API.
Web Intents lets web developers integrate their web apps with third-party services and avoid implementing the same feature with similar services over and over again. With Web Intents, a service can register itself to handle specific functionality, such as saving a file or sharing data. Client applications can then discover and interact with the service to use that specific functionality.
In this post, we’ll look at code for two example apps: a service that exposes a “save” web intent, and a client that consumes it to save files to Google Drive.
To demonstrate web intents, we wrote a proof of concept app called Cloudfilepicker.com. This is a service that lets users or applications save and retrieve data from Drive via the Web Intents API. With Cloudfilepicker, any application that fires a “save” intent can save to Google Drive without directly implementing the API.
To build a service application like cloudfilepicker.com that interacts with Drive and Web Intents, create a Chrome app whose manifest exposes a http://webintents.org/save intent action. For example:
http://webintents.org/save
{ "name": "Google Drive Web Intent", "version": "1", "app": { "launch": { "local_path": "index.html" } }, "intents": { "http://webintents.org/save": [ { "type" : ["image/png", "image/jpg", "image/jpeg"], "href": "save.html", "title": "Save to Drive" } ] } }
The intent declaration in the app manifest describes the functionality that your application offers, the data it can work with, and what to launch when the user chooses your application.
In order to use the Drive API, the page has to get an OAuth token for the user. The complete JavaScript implementation of the OAuth flow is available in the project repository.
Once the app receives a valid OAuth token, we can load the Drive client library and perform the file upload request using multipart upload. A Web Intent with action http://webintents.org/save can provide the file data in two ways: as a base64-encoded string or a blob, and our app should support both. With the former, we have to pass the string to the request, while with the latter we have to read the blob content and base64 encode it before we can pass it to insertBase64Data:
insertBase64Data
const boundary = '-------314159265358979323846'; const delimiter = "\r\n--" + boundary + "\r\n"; const close_delim = "\r\n--" + boundary + "--"; function makeApiCall(authResult) { gapi.client.load('drive', 'v2', function() { if(window.webkitIntent) { var data = window.webkitIntent.data; if(data.constructor.name == "Blob") { insertFileData(data, authResult, processResponse); } else if(typeof(data) == "string") { var meta = { 'title': "Test Image " + (new Date()).toJSON(), 'mimeType': window.webkitIntent.type }; insertBase64Data(data.replace(/data:image\/([^;]*);base64,/,""), window.webkitIntent.type, meta, authResult); } } }); } function insertBase64Data(data, contentType, metadata, authRequest, callback) { var multipartRequestBody = delimiter + 'Content-Type: application/json\r\n\r\n' + JSON.stringify(metadata) + delimiter + 'Content-Type: ' + contentType + '\r\n' + 'Content-Transfer-Encoding: base64\r\n' + '\r\n' + data + close_delim; var request = gapi.client.request({ 'path': '/upload/drive/v2/files', 'method': 'POST', 'params': {'uploadType': 'multipart'}, 'headers': { 'Content-Type': 'multipart/mixed; boundary="' + boundary + '"' }, 'body': multipartRequestBody}); if (!callback) { callback = function(file) { console.log(file.id); }; } request.execute(callback); } function insertFileData(fileData, authRequest, callback) { var reader = new FileReader(); reader.readAsBinaryString(fileData); reader.onload = function(e) { var contentType = fileData.type || 'application/octet-stream'; var metadata = { 'title': fileData.fileName, 'mimeType': contentType }; var base64Data = btoa(reader.result); insertBase64Data(base64Data, contentType, metadata, authRequest, callback); } }
Now, any web app that wants to save data to Google Drive could use this service instead of implementing the same functionality by invoking webkitStartActivity with a save intent.
webkitStartActivity
An example client app is http://www.imagemator.com. This app lets users manipulate and save images, but has no direct Drive API integration. When the user invokes the “save” intent in imagemator.com, if they have the “Save to Drive” app installed they will see Drive as an option in the list of apps that can fulfill that action:
If the user selects “Save to Drive”, the browser will send a request to the page listed as the href property of the intent declaration -- save.html in the sample manifest above. The following code shows how to trigger the save request:
href
var fileData = canvas.toDataURL(); var intent = new WebkitIntent({'action': 'http://webintents.org/save', 'type':'image/png', 'data': fileData}); var onSuccess = function(data) { // handle any data that might be sent back }; window.navigator.webkitStartActivity(intent, onSuccess);
To learn more about the Web Intents check webintents.org, and visit https://developers.google.com/drive to learn about the Google Drive SDK. The complete sample described in this post is also available on https://github.com/PaulKinlan/WebIntents/tree/master/server/demos/cloudfilepicker.
Are you an expert in the Drive API or Google Apps APIs? Are you an expert in Google Apps Script? If you are, then you must have been busy coding away for the Google Apps Developer Challenge since its launch earlier last month.
Now it’s time to wow the judges, as the Google Apps Developer Challenge is now open for submissions! If you would like to submit your application, please submit on the website using one of the three following categories:
If you are not ready, don’t worry -- as you still have time. The submission deadline is 24 August 2012 at 23:59:59 PT. Remember to use the many resources available to you in order to get ready. Ask questions on the Google+ Office Hours and Google Developers Live. Read up on Apps Script and the Drive and Apps APIs on Google Developers. Review the latest updates since Google I/O as well as the pre-submission checklist. Post questions and comments using the hashtag #gappschallenge on Google+. Review, and most importantly, finalize your application!
Best of luck!
Editor’s Note: This blog post is authored by Naveen Venkat, from Zoho. We've welcomed many of Zoho's apps in the Google Apps Marketplace and are happy to see them join the Drive developer community as well. -- Steven Bazyl
Zoho is a suite of online applications targeted at small and medium sized businesses. We offer over 25 services ranging from the basic productivity suite all the way up to business applications like CRM, project management, invoicing, custom app building platform and much more. We’ve just rolled out Zoho Office integration with Google Drive.
Zoho's applications can be broadly classified into 3 categories: Productivity, Business and Collaboration apps. Productivity apps are the basic needs of an office environment and include the likes of a word processor, spreadsheet, presentation tool, calendar etc. Business apps include CRM, projects, custom application building platform, invoicing and bookkeeping services. Collaboration tools facilitate real time collaboration across geographical locations. Email, chat, discussions, docs wiki etc are what round out this category of services.
We have many Google and Google Apps customers using Zoho applications through Google Apps Marketplace, Google Chrome Webstore or directly using "Sign in using Google" on our login page. Tightly integrating Zoho's applications with Google's services is very crucial for us. During the launch of Google Drive, Google announced the availability of a SDK and the related Google Drive APIs that would facilitate third party apps to integrate with the service. We used this opportunity and initially started integrating Zoho Show (presentation tool) with Google Drive. At that time, we felt that the APIs are very extensive and pretty straight forward. This inspired us to integrate our entire Zoho Office Suite with Google Drive. In just a couple of weeks, we were able to integrate Zoho Writer (word processor), Zoho Sheet (spreadsheet tool), and Zoho Show (presentation tool) with Google Drive.
Some of the goals that we had in our mind when we started our integration were, to be able to :
Above all, we also focussed on enabling SSO to login into Zoho with Google accounts without having to provide any extra information.
The first step in integrating an app with Google Drive is to register the app in the Google APIs console. Because we have three different apps, we created three different projects - Writer, Sheet and Show so that they would be listed separately in the contextual menu of Google Drive. In the console, we also have to enable Drive APIs and SDK to get access to Drive APIs for the App ID. The console then prompts you to create OAuth 2.0 client ID for authenticating the user with the app. Once this is done, we had to provide Google some basic information about our app such as brand icons, default and secondary mime types, authorization scopes etc.
The next step is to create a Google Chrome Web listing which allows users to install your app in Google Drive. Since we already have Zoho apps listed in the Chrome Web Store it was easy for us to make a few changes in our existing listing. We’ve added the following code in the manifest files to enable Drive for our apps.
"container" : "GOOGLE_DRIVE", "api_console_project_id" : "<APP_ID>",
APP_ID is the application’s ID which can be found in the Google APIs console.
APP_ID
This makes the Chrome Web Store listing Drive enabled, so now users simply have to install our Chrome Web Store apps and the Drive features will be enabled:
Google Drive apps are required to use OAuth 2.0 for authorization and are strongly encouraged to use OpenID Connect for authentication. Our Zoho Office Suite already supported OpenID authentication, so it was easy for us to implement it for the Z Writer, Sheet and Show apps for Google Drive.
To enable authentication for Drive users on our Zoho app we’ve added the following scopes to our Drive apps’ configuration page in the Google APIs Console:
https://www.googleapis.com/auth/userinfo.email https://www.googleapis.com/auth/userinfo.profile
This will make sure access to the authentication information of the user is granted alongside access to the Drive API allowing us to access Google’s OpenID Connect endpoints.
Our previous outings with GData APIs introduced us to OAuth2.0. OAuth 2.0 takes care of the authorization flow for a new user and smoothly transitioning the user to the Zoho application environment.
Users can create a new document from Google Drive’s UI or edit their existing Google Drive documents with Zoho’s editors. And when the user hits save, the modified document will be saved in Google Drive as a new document, thereby leaving the original Document untouched. This new document comes with extensions recognized by Zoho's apps. For example, a text document, Test.doc, from Google drive, when edited in Zoho Writer and saved, will be saved as Test.zdoc in Google Drive.
Users can invite their Google contacts to collaborate on their Google Drive documents and edit them using Zoho editors.
One of the main challenges that we faced during the integration was when multiple users need to collaborate on the same document in Zoho's editors.
In the recently launched Drive version v2 Google supports Permissions Feed to manage resource sharing. Based on the document permissions, we’ll allow the user to access the document for collaboration. Thus the Google users with whom the document has been shared can collaborate with each other in Zoho’s editor, without having to sign-in with Zoho explicitly. Zoho apps support inline collaboration which facilitates the shared users to collaborate seamlessly.
Thanks to Google team for their continued support right through the integration.
Editor’s Note: This blog post is authored by Peldi Guilizzoni, from Balsamiq. As a user of Balsamiq myself, it was great to see them join as one of the first Drive apps! -- Steven Bazyl
Hi there! My name is Peldi and I am the founder of Balsamiq, a small group of passionate individuals who believe work should be fun and that life's too short for bad software.
We make Balsamiq Mockups, a rapid wireframing tool that reproduces the experience of sketching interfaces on a whiteboard, but using your computer, so they’re easier to share, modify, and get honest feedback on. Mockups look like sketches, so stakeholders won’t get distracted by little details, and can focus on what’s important instead.
We sell Mockups as a Desktop application, a web application and as a plugin to a few different platforms. An iPad version is also in the works.
We believe that tools should adapt to the way people like to work, not the other way around. That's why when Google Drive came out, we jumped at the chance to integrate Mockups with it. This is the story of how the integration happened.
First of all, a little disclaimer. Although my job these days is to be CEO and all that, I come from a programming background. I started coding at 12, worked at Macromedia/Adobe for 6 years as a programmer. I'd say I'm a pretty good programmer…just a bit rusty, ok? I realize that the decision to write the code for Mockups for Google Drive myself instead of asking one of Balsamiq's better programmers to do it might have been a bit foolish, but we really wanted to be a launch partner and the programmers were already busy with lots of other stuff, plus I didn't want to pass up on the chance to work on something cool after dinner for a while. ;) OK so now you know the background, let's get started.
Once I got access to the Google Drive API documentation and looked around a bit, I started by following the detailed "sample application" tutorial.
The sample was written in python, used OAuth, the Google API Console, and ran on Google App Engine, all technologies I hadn't been exposed to before.
Following along brought me back to my childhood days of copying programs line by line from PC Magazine, not really understanding what I was doing but loving it nonetheless. :)
The trickiest part was figuring out how OAuth worked: it's a bit of a mess, but after you play with it a little and read a few docs, it starts making sense, stick with it, it's the future! ;) Plus the downloadable sample app had hidden all that stuff in a neat little library, so you don't have to worry about it so much.
Setting up the sample application took around 2-3 hours, easy peasy. Once that was done, I just had to convert it to become Balsamiq Mockups for Google Drive. Because I had done this before for other platforms, this was finally something I was comfortable with doing. The bulk of our code is encapsulated into our Flash-based Mockups editor, so all I had to do was to write a few functions to show the editor to the user and set it up using our internal APIs. Then I had to repurpose the "open with" and "edit" APIs from the sample app to work with the Mockups editor. All and all, this took maybe a day of work. Yay!
Once the proof of concept was up, I started turning the code into a real app. I cleaned up the code, added some comments, created a code repository for it in our bazaar server, set up a staging environment (a parallel Google App Engine application and unpublished Chrome Web Store listing) and integrated the build and deployment into our Jenkins server.
One tricky bit worth mentioning about integrating with Jenkins: the Google App Engine deployment script appcfg.py asks for a password interactively, which is a problem if you want to deploy automatically. The solution was to use the echo pwd | appcfg.py trick found here.
After some more testing and refinements, shipping day came, Balsamiq Mockups for Google Drive was live.
It was a very exciting day. Getting mentioned in the official Google blog was quite awesome. The only stressful moment came because for some reason my Google App Engine account was not set up for payments (I could have sworn I had done it in advance), so our app went over our bandwidth quota an hour after launch, resulting in people receiving a white blank screen instead of the app. Two people even gave us bad reviews because of it. Boo! :(
In the days that followed, things went pretty well. People started trying it out, and only a few bug reports came. One very useful Google App Engine feature is the "errors per second" chart in the dashboard, which gives you an insight on how your app is doing.
I noticed that we had a few errors, but couldn't figure out why. With the help of the docs and our main Developer Relations contact at Google, we narrowed them down to a couple of OAuth issues: one was that the library I was using didn't save the refresh_token properly, and another that had to do with sessions timing out when people use the editor for over an hour and then go to save their work.
Fixing these bugs took way longer than what I wanted, mostly due to the fact that I'm a total OAuth and Python n00b.
After a few particularly frustrating bug hunting sessions, I decided to rewrite the backend to Java. The benefits of this approach are that a) we get static type checking and b) I can get help from some of our programmers since Java is a language we're all already familiar with here.
Since by now the Java section of the Google Drive SDK website had been beefed up, the rewrite only took a day, and it felt awesome. Sorry python, I guess I'm too old for you.
The hardest part of the java rewrite was the Jenkins integration, since the echo pwd trick doesn't work with the java version of appcfg. To get around that, I had to write an Expect script, based on this Fábio Uechi blog post. By the way, I would recommend reading the Expect README, it has an awesome 1995 retro feel to it.
Overall, integrating Balsamiq Mockups with Google Drive was a breeze. Google is a technology company employing some of the brightest people in our industry, and it shows. The APIs are clean and extremely well tested. The people at Google are very responsive whenever I have an issue and have been instrumental in making us successful.
While the application is still pretty young - we are working on adding support for Drive images, linking, symbols… - we are very happy with the results we're getting already. The Drive application netted around $2,500 in its first full month of operation, and sales are growing fast.
Alright, back to coding for me, yay! :)
Peldi
Today at Google I/O, we announced many enhancements to Google Apps Script to help you build richer applications and share your apps with users. Apps Script began as a tool for helping users get more done with their Google spreadsheets. Over time, Apps Script has grown to handle much more. It's a platform to extend Google spreadsheets and Sites, and a convenient way to create web applications.
We launched script.google.com as a destination for Apps Script developers. You can now create scripts from script.google.com or from Google Drive. Plus, your projects are now stored in Google Drive, and you can share them just like a Google document or spreadsheet.
HtmlService can help you create beautiful interfaces using Apps Script. HtmlService allows you to create web apps using HTML, client-side JavaScript, and CSS. You can also use jQuery to build web apps. HtmlService uses Google Caja to ensure that the HTML content served by your web apps is safer for you and your users.
We also launched a better way to store your application's data, ScriptDb . Every script now has an embedded JSON database. You no longer have to rely on a spreadsheet to store the data. Using ScriptDb, you can store a larger volume of data and search easily. We designed ScriptDb to be easy to use. It doesn't need connection strings or special passwords, and you can directly store and search your JavaScript objects without the need to convert them to a different format. You can learn more about ScriptDb on the Google Apps Script Developers page.
There are also now more options for deploying your web app. Your apps can now, with authorization, run as the user behind the keyboard, not just the script owner. This brings a new level of versatility to web apps built with Apps Script.
Finally, we wanted to make it easy to distribute your apps. You can now publish your apps in the Chrome Web Store . Register and package your app directly from the Publish menu in Google Apps Script. Then customize your listing from the Chrome Web Store and publish your app to the world.
We added a lot of new functionality to Apps Script, so in addition to our developer reference documentation , we've also created a new user guide . If you need further help you can reach out to us on Stack Overflow . You can also make feature requests and report issues on the Apps Script page on Google Code.
Building with Google Apps Script has become a lot easier and more powerful. We can't wait to see what you build. Happy scripting!
Editor’s Note: Kevin Winter is a guest author from the AdWords API Developer Relations team. He implemented these features in his 20% time. - Jan Kleinert
Google Spreadsheets provides powerful charting functionality to let you analyze your data many different ways. We recently added the ability to programmatically create, modify and delete Spreadsheet charts using Google Apps Script.
Charts that a user creates in a Google Spreadsheet are called Embedded Charts. Apps Script can be used to manage these types of Spreadsheet Charts. Each EmbeddedChart belongs to a Sheet. Embedded charts are named this way because they are embedded onto a Sheet within the spreadsheet. Embedded charts align to the upper left hand corner of the specified column and row in the spreadsheet.
EmbeddedChart
Sheet
Let’s say I want to track my gas mileage using Google Spreadsheets. First, I create a Spreadsheet, and set up columns and formulas. Next, I add a form interface to make it easy to add new entries and a chart to visualize my mileage. Now I can start recording data - but each time I add enough entries to go past the ranges the chart uses, I need to manually increase them. How about we use some Apps Script magic to solve this?
The script below iterates through all charts on this sheet and determines if any of the ranges need to be expanded (i.e. if there are more rows with data to display). It then updates the title, builds the new EmbeddedChart object and saves it to the sheet. It could also add a menu interface or a trigger to execute this periodically or when the spreadsheet is edited.
function expandCharts() { var sheet = SpreadsheetApp.getActiveSheet() // Get a list of all charts on this Sheet. var charts = sheet.getCharts(); for (var i in charts) { var chart = charts[i]; var ranges = chart.getRanges(); // Returns an EmbeddedChartBuilder with this chart’s settings. var builder = chart.modify(); for (var j in ranges) { var range = ranges[j]; // rangeShouldExpand_ is defined later. if (rangeShouldExpand_(range)) { // Removes the old range and substitutes the new one. builder.removeRange(range); var newRange = expandRange_(range); builder.addRange(newRange); } } // Update title. builder.setOption('title', 'Last updated ' + new Date().toString()); // Must be called to save changes. sheet.updateChart(builder.build()); } } function rangeShouldExpand_(range) { var s = range.getSheet(); var numColumns = range.getNumColumns() var values = s.getSheetValues(range.getLastRow(), range.getColumn(), 2, numColumns); for (var i = 0; i < numColumns; i++) { // If the next row has the same pattern of values, // it’s probably the same type of data and should be expanded. if (!values[0][i] && !values[1][i] || !!values[0][i] && !!values[1][i]) { continue; } else { return false; } } return true; } function expandRange_(range) { var s = range.getSheet() var startRow = range.getRow(); var startCol = range.getColumn(); var numRows = range.getNumRows(); var numCols = range.getNumColumns(); while (rangeShouldExpand_(range)) { numRows++; range = s.getRange(startRow, startCol, numRows, numCols); } return range; }
What if you wanted to create a new chart from scratch? You can do that too!
var sheet = SpreadsheetApp.getActiveSheet(); var chart = sheet.newChart() .setPosition(5, 6, 5, 5) .setChartType(Charts.ChartType.AREA) .addRange(sheet.getActiveRange()) .build(); sheet.insertChart(chart);
In the above code example, we’ve gotten a reference to an EmbeddedChartBuilder, set its position within the sheet, change the chartType, add the currently selected range and insert the new chart.
EmbeddedChartBuilder
The EmbeddedChartBuilder allows you to modify the chart in a couple ways:
addRange
removeRange
setOption
setChartType
setPosition
Embedded charts use more options than regular Apps Script charts, so options are handled slightly differently. Developers can pass a dotted field path to change options. For example, to change the animation duration, you can do this:
builder.setOption("animation.duration", 1000);
Now that we’ve created a bunch of charts, your spreadsheet is probably pretty cluttered. Want to clear it and start afresh with charts? Just remove them all:
var sheet = SpreadsheetApp.getActiveSheet(); var charts = sheet.getCharts(); for (var i in charts) { sheet.removeChart(charts[i]); }
Just like standalone charts, you can use embedded charts elsewhere. You can add them to a UIApp or a sites page as well as sending them as an email attachment:
// Attach all charts from the current sheet to an email. var charts = SpreadsheetApp.getActiveSheet().getCharts(); MailApp.sendEmail( "recipient@example.com", "Income Charts", // Subject "Here's the latest income charts", // Content {attachments: charts });
We hope you found this blog post useful. Enjoy editing embedded charts using Google Apps Script!
At the end of last year we launched the UserManager Apps Script service, allowing Google Apps domain administrators to write scripts to programmatically create, delete and edit their user accounts.
We are now extending the family of Domain services with two new additions: NicknameManager and GroupsManager.
The NicknameManager service allows domain administrators to define alternate email addresses (i.e. “nicknames”) with a single line of code, as in the following example:
var nickname = NicknameManager.createNickname("user", "nick");
With the GroupsManager service, Google Apps domain administrators can create and delete groups, and manage their members and owners. The following example shows how to list all members of a group given its unique groupId:
groupId
function listMembers(groupId) { var members = GroupsManager.getGroup(groupId).getAllMembers(); for (var i in members) { var member = members[i]; Logger.log(i + ": " + member); } }
With the same service, one line of code is enough to add a member to a group:
GroupsManager.getGroup(groupId).addMember(memberId);
If you want to know more about the new NicknameManager and GroupsManager services, please check our documentation, and don’t hesitate to get in touch with us if you have questions or suggestions.
The Google Picker API provides developers with an easy-to-use file dialog that can be used to open Google Drive files directly from their web app independently of the Drive UI. The Drive SDK documentation includes an example showing how to incorporate the Google Picker with just a few lines of JavaScript.
Another powerful use case for the Picker API is to allow users to upload files to Drive with the same consistent UI. A single Picker dialog can incorporate multiple views and users can switch from one to another by clicking on a tab on the left:
The following code sample opens the Picker dialog and registers a simple callback function to handle the completed upload event:
<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="content-type" content="text/html; charset=utf-8"/> <title>Google Picker Example</title> <!-- The standard Google Loader script. --> <script src="http://www.google.com/jsapi"></script> <script type="text/javascript"> // Use the Google Loader script to load the google.picker script. google.setOnLoadCallback(createPicker); google.load('picker', '1'); // Create and render a Picker object for searching images // and uploading files. function createPicker() { // Create a view to search images. var view = new google.picker.View(google.picker.ViewId.DOCS); view.setMimeTypes('image/png,image/jpeg'); // Use DocsUploadView to upload documents to Google Drive. var uploadView = new google.picker.DocsUploadView(); var picker = new google.picker.PickerBuilder(). addView(view). addView(uploadView). setAppId(YOUR_APP_ID). setCallback(pickerCallback). build(); picker.setVisible(true); } // A simple callback implementation. function pickerCallback(data) { if (data.action == google.picker.Action.PICKED) { var fileId = data.docs[0].id; alert('The user selected: ' + fileId); } } </script> </head> <body> </body> </html>
There is an important difference between this upload example and the code used to open files: in addition to the standard view, an instance of DocsUploadView is added to the Picker object, thus providing upload capability.
DocsUploadView
For more information about this component and all other available views, please refer to the Google Picker Reference Guide.
Updated to add links to the #gappschallenge hashtag and to Google Apps Script.
In the past year, the Google team has been engaging with local developers by running various Google conferences and Google+ Hackathons, showcasing creative applications, and supporting Tech Hubs. Since we are always looking for opportunities to encourage (and challenge!) you, we are looking forward to giving developers the opportunity to take on this year’s challenge, which will focus on Google Apps Script, Google Apps and Google Drive APIs.
With the Google Apps Developer Challenge, we hope developers across the globe will find new and innovative ways to use Apps Script, Apps and Drive APIs to build cool apps. This challenge is particularly unique as the APIs are available to a large community of developers who code in a variety of languages that include Java, PHP, Python, and .Net.
We will be working in collaboration with our Google Developer Groups (also known as GTUGs) and Google Business Groups to organize events and prepare for this challenge. Make sure to join your local community so that you are aware of meet ups.
How familiar are you with the various Google Apps and Drive APIs? If you aren’t familiar, make sure to read up about Google Apps Script, Google Apps and Drive APIs on Google Developers. Use the Chrome Web Store as a source of inspiration. Create an innovative application using Google Apps Script, Google Apps, and Drive APIs. If your application is the best within one of the three categories defined below in your region, you could win a prize of $20,000 dollars! Google is also committed to nurturing the next generation of computer scientists as well as encouraging more women to get into coding, so we have special prizes for all-student or all-female teams that make the second round — $1,000 dollars.
The first round of submissions will start on the 24th of August 2012. The categories are
Make sure you read all the details about the competition on the Google Apps Developer Challenge page and follow the hashtag #gappschallenge on Google+ for any additional updates.
What are you waiting for? Get coding!
Editor’s Note: This blog post is authored by Blair Kutzman, who developed the Gmail Delay Send script. - Eric Koleda
Update: To start using this script simply open this page and follow the instructions.
In today’s connected world, when you get your email could be just as important what’s in it. In 2011 over 107 trillion emails were sent to 1.97 billion internet users. Studies have shown that the average person can only effectively process 50 emails in a day. That leaves 100 emails per person per day that are not processed effectively. How can you be sure that the emails you send fall into the former category and not the latter?
Luckily, there are tools to assist with email overload. One such tool is Gmail Delay Send.
Gmail Delay Send is a Google Apps Script that allows you to schedule emails to be delivered on a specified date and time. Using this tool you can ensure your email is sent to its destination at a time when you can capture your recipient’s full attention. For example, receiving an email at 4:59 PM on friday might not receive the same attention as an email received on Monday at 10:00 AM.
A primary requirement of Gmail Delay Send was that it needed to work everywhere Gmail is available. There are already many browser add-ons and services available to enhance Gmail with similar functionality, so the purpose was not to duplicate that work. In order for the service to be available on all platforms, it needed to utilize native Gmail features.
We needed a native way that Gmail could:
Gmail already contains a 'Draft' folder which is exactly what is required for item 1. The real problem was where and how to store the metadata for item 2, without any new Gmail functions. I chose to encode the metadata in the subject of the message because it would contain less text, which would mean a smaller chance of mis-parsing. Text such as “5 hours” and “next tuesday” were turned into a date-time using an open source library called datejs and a few modifications. See below for details of how this potentially cumbersome process was improved.
The script works as follows:
Although using datejs to parse the dates from the subject line was easy to implement, it introduced some usability issues. First, how would a user know if a certain string can be parsed by datejs (eg. is “5 minutes before 4PM” parsable)? To assist the user in knowing which dates datejs supports, the script offers a mechanism to test a given string directly in the spreadsheet that Gmail Delay Send is installed inside of. In this way a user can test various strings to see if they are valid and, if so, when they would be sent. A wiki page is dedicated to helping people through this process.
Another possibly confusing part of using Gmail Delay Send was setting up triggers. Thanks to some recent improvements of the Script Services, this is now done automatically for users as they install.
Adding retry logic to the script was another important step in improving its reliability and user experience. Occasionally, users were getting emails from their trigger informing them that a certain Google Script API could not be contacted. Some retry logic was required to make things work correctly. As shown in the snippet below, the function executeCommand() takes any function and will try to execute it a specified number of times, retrying if an error is thrown:
function executeCommand(fp) { var msg; var ret_val; var last_error; for(var retries = NUM_RETRIES; retries > 0; retries -= 1) { try { ret_val = fp(); break; } catch(err) { last_error = err; msg = "Exception:" + err + " thrown executing function:" + fp; debug_logs.push(msg); Logger.log(msg); Utilities.sleep(SLEEP_TIME); } } if(retries == 0) { msg = "Attempted to execute command:" + fp + " " + NUM_RETRIES + " times without success. Error message: " + last_error + ". Aborting :-("; Logger.log(msg); throw(msg); } return ret_val; }
Using this method, statements like those below will automatically retry if the service is not available.
executeCommand( function() { GmailApp.send( … ) }); executeCommand( function() { UrlFetchApp.urlFetch(url) } );
Gmail Delay Send was a fantastic project for learning about Google Apps Script and I hope that it will continue to prove useful to its users. If you’re interested in using Gmail Delay Send or just interested in the development process please check out the homepage or source.
