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.
onEdit()
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.
addMenu()
hideRows()
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);
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:
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 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"); }
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.
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.
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 ... }
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.
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.
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.
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:
If you are a registrar interested in implementing this RESTful API to automate the DNS setup process, please apply here.
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].
[=(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.
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.
remindMe
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.
Would you like to programmatically publish some web content? Or let your users do so in the context of your Drive app?
This is possible now with Google Drive. Your app can now insert static web assets in a publicly shared folder, and then serve those files directly to users via file names in a relative path. Google Drive site publishing supports JavaScript, so it's even possible to run a JavaScript Drive app directly from Drive.
Publishing from Drive is a simple, two-step dance: create a public folder and use a link to the root folder — the webViewLink — to publish its contents. You can refer to the Drive SDK documentation for full detail on how to work with public folders and content links, but basically the requirements are:
webViewLink
It’s important to emphasize the added simplicity provided by a webViewLink: using this link as a starting point, you can extend the path to any web asset in any subfolder without worrying about retrieving the exact file ID. What used to look like 0B2Gk2F2ImIBiaUkwY3JNX1JMaTg is now a recognizably cute path such as images/kittens.jpg. For the root path to the folder, we’ll display a list of files in the folder, or, if you have an index.html file in your folder we’ll load that as expected.
0B2Gk2F2ImIBiaUkwY3JNX1JMaTg
images/kittens.jpg
index.html
The ability to publish files this way opens lots of possibilities for Drive app developers. Writing a blogging tool, creating a process to publish updates to shared docs, outputting images in a folder in a gallery page — any Drive use case that involves presenting a file in a browser can benefit from site publishing. We look forward to seeing what you create, and we’re happy to answer your questions on Stack Overflow.
Since the public unveiling of the Google Drive SDK in April, companies like Lucidchart or HelloFax have built powerful, slick, useful Google Drive apps, and many more companies are launching compelling integrations every day. During this time, our developer community — especially on Stack Overflow — has grown substantially.
To help support our growing developer community and all the interest in integrating with Google Drive, we’re starting a series of Google Drive developer workshops. For the inaugural event, we are hosting several companies — including Shutterfly, Fedex, Autodesk, Twisted Wave, 1DollarScan and Manilla — to participate in a two-day workshop this week at the Googleplex in Mountain View, California.
During this workshop, Google engineers will be on hand to assist attendees with various parts of their Google Drive integration: things like design and implementation of features, authorization flow, and Android integration. Companies have shown that the Google Drive SDK allows for deep integration in just a couple days and we really hope that attendees of this workshop will enjoy a similar experience. Tune back in later this week to find out more about what we learned and accomplished in our workshop.
If you are interested in attending similar Google Drive workshops near you or if you want to contact the Google Drive team about a potential integration with your product, let us know.
JavaScript has long been the de facto choice for client-side web development, but lately it's been catching on server-side as well. While we like to think that Apps Script has contributed to the trend, projects such as Mozilla's Rhino and Node.js have also done a great deal to popularize the concept. As a result, developers have created a wealth of new open-source JavaScript libraries, and in this post we'll talk about how you can leverage them in your Apps Script projects.
Underscore One library I wanted to use in my scripts was Underscore, which describes itself as "a utility-belt library for JavaScript." It provides a wealth of helper functions that make coding in JavaScript cleaner and more enjoyable. Take, for example, the simple situation where you want to log each value in a range.
// Using plain JavaScript. for (var i = 0; i < values.length; i++) { for (var j = 0; j < values[i].length; j++) { Logger.log(values[i][j]); } }
Although writing for loops like this is a common pattern, it's a fair amount of typing and you need to keep track of counter variables that serve little purpose. Underscore provides an each() method that makes the process much simpler.
for
each()
// Using Underscore. _.each(values, function(row) { _.each(row, function(cell) { Logger.log(cell); }); });
Passing anonymous functions as parameters takes a little getting used to, but if you've worked with jQuery, the pattern feels familiar.
Underscore also has some great extensions, and Underscore.string provides some useful string manipulation features. My favorite is the ability to use sprintf() notation in JavaScript, which can simplify the process of building complex strings.
sprintf()
// Using plain JavaScript. var message = "Hello, " + firstName + " " + lastName + ". Your wait time is " + wait + " minutes."; // Using Underscore.string. var message = _.sprintf("Hello, %s %s. Your wait time is %d minutes.", firstName, lastName, wait);
Integrating with Apps Script The simplest way to include the Underscore library in a project would be to paste its source code directly into your script, but this would lead to a lot of duplication if you end up using it in multiple projects. Earlier this year, we released a feature in Apps Script called libraries that allows you to share scripts and include them in other projects. Packaging a JavaScript library like Underscore as an Apps Script library is possible, but requires some helper functions to work correctly.
When Underscore loads, it creates a global variable named "_" that you use to access its functionality. Apps Script specifically prevents the global scope of a library from interfering with the global scope of the script that includes it, so I built a helper function into the library to pass the variable around.
// In the library. function load() { return _; }
In my script that includes the library, I simply make a call to that function and use the result to set up my own "_" variable.
// In the script that includes the library. var _ = Underscore.load();
To try my copy of the Underscore library in your own project, use the project key "MGwgKN2Th03tJ5OdmlzB8KPxhMjh3Sh48" and the code snippet above. You can browse the full source code here.
Using it with the HtmlService Using the code above, I could easily include the library in my server-side Apps Script code, but I also wanted to use these functions client-side in my web app served by the HtmlService. To accomplish this, I created a copy of the Underscore source code, wrapped it in <script> tags, and stored them in Html files (instead of Script files). These snippet files could then be included in my web app's HtmlTemplates using the helper function below.
<script>
// In the library. var FILES = ['Underscore.js', 'Underscore.string.js', 'Init.js']; function include(output) { for (var i = 0; i < FILES.length; i++) { var file = FILES[i]; output.append( HtmlService.createHtmlOutputFromFile(file).getContent()); } }
This function was called in the web app's HtmlTemplate using the simple code below.
<!-- In the web app that includes the library. --> <html> <head> <? Underscore.include(output) ?> </head> ...
Other libraries Integrating with Underscore was fairly easy, but trying the same approach with other open-source libraries may be a bit more complicated. Some libraries won't run correctly in the Apps Script environment if they rely on certain capabilities within the browser or Node.js runtime. Additionally, to be served by the HtmlService, the code must pass the Caja engine's strict validation, which many popular libraries don't meet. In some cases, you may be able to manually patch the library to work around these issue, but this usually requires a deep understanding of how the library works.
We hope you're inspired to use Underscore and other open-source libraries in your own work. If you find a library that works great with Apps Script, share it with me on Google+ and I'll help get the word out.
Ever wondered how to move your organization’s emails from a shared mailbox or a public folder when migrating to Google Apps for Business?
We’ve just launched the Google Apps Groups Migration API that provides Google Apps developers the ability to build tools that can move shared emails from any data source (typically shared mailboxes, public folders and discussion databases) to their domain’s Google Groups discussion archives. Google Groups provides a simple and easy way to ‘tag’ the migrated emails into manageable groups that can be easily accessed by users with group membership.
This new api complements existing Google Groups api’s like Google Apps Provisioning API which can be used to create new groups (to which the shared emails can then be migrated using the newly launched API) and Google Apps Groups Settings API which can be used to control access to the group. The addition of the Google Apps Groups Migration API thus makes the ‘shared folder’ experience seamless even after migration to Google Apps. To learn more and try out this new feature visit Google Developers.
Anyone else get stuck with fixing their family's computer problems? Recently I had a family technical issue that was actually fun to solve and didn't involve removing malware. My family owns Rasmus Auctioneers, a global auctioneer company based in Alexandria, Virginia. The company uses Google Apps for their entire business workflow, so their documents, calendars, etc. are all connected.
We manage the shipping process on-site using a tablet. In this fast-paced environment and with limited time, we need a quick, easy solution. When an auction ends, the clients fill out a Google Form that saves their shipping and payment information to a Google Spreadsheet. This information then needs to be sent to a specific mover for processing -- but the mover doesn't need all the data, just a few important pieces. All of this needs to happen with as little human intervention as possible.
Our solution was to create an Apps Script within the spreadsheet, which means our new functionality works in the tablets used on the auction floor. The function below runs when the sheet is opened, automatically sorting the sheet to put the newest client on top. A simple dialog determines which data is important for a particular auction; the script then finds the data we need, sends it to the right person, and adds a calendar event as a reminder.
function sendData() { // Sort the rows var sheet = SpreadsheetApp.getActiveSheet(); sheet.sort(4); // What row do we want? var myValue = Browser.inputBox("What row?"); // Variables var emailRecipients = "me@mydomain.com"; var emailSubject = "Moving data for "; var valueToShow = ""; // Get cell values var clientName = sheet.getRange("B" + myValue).getValue(); var movingDate = sheet.getRange("D" + myValue).getValue(); valueToShow += "Name: " + sheet.getRange("B" + myValue).getValue() + "\r\n"; valueToShow += "Moving Date: " + sheet.getRange("D" + myValue).getValue() + "\r\n"; valueToShow += "Size: " + sheet.getRange("E" + myValue).getValue() + "\r\n"; // Send email MailApp.sendEmail(emailRecipients, emailSubject + clientName, valueToShow); // Add to calendar CalendarApp.createAllDayEvent("Move today! " + clientName, new Date(movingDate)); }
Looking at the source code, we can see how we collect the data from the relevant columns, add them to a variable, and in one line send it via Gmail. Adding a reminder to our calendar also takes just one line.
Do you have a repetitive process that bugs you? Wish there was a way to automate something? You might be able to solve your problems the same way I helped out my family -- with Apps Script! Feel free to leave a comment on some ideas or suggestions for future projects.
In an earlier blog post, we announced the Election Info sample app. We briefly talked about how we were able to use Apps Script to easily create a comprehensive sample application that provided timely voting information. This post shows just how easy it is to use Apps Script to get information from an external API and integrate with various Google services to create a rich web application and provide a meaningful user experience.
First, we use UrlFetchApp to get JSON from the Google Civic Information API and use Utilities.jsonParse to convert it to a useful javascript object.
var url = 'https://www.googleapis.com/civicinfo/us_v1/voterinfo/2000/lookup'; var address = { "address" : "1263 Pacific Ave. Kansas City KS" }; var options = { method : "post", contentType : "application/json", payload: Utilities.jsonStringify(address) }; var responseText = UrlFetchApp.fetch(url, options).getContentText(); var response = Utilities.jsonParse(responseText);
After getting the response object, we can simply drill into it to access various data provided by the API.
One of the things the response object provides us with is the election date. Using Apps Script's Calendar service, it is really easy to create an event on voting day in the user's calendar with the polling address. First, we create a Date object from the date string. We then create an all-day event on the default calendar on this date, passing along the polling address we get from the response object.
// create a Date object from the response date string // ("2012-11-6" --> Date object) var [year, month, day] = response.election.electionDay.split('-'); // javascript months are zero-indexed var electionDate = new Date(year, month-1, day); // get the first polling location's address var pollAddress = response.pollingLocations[0].address; var cal = CalendarApp.getDefaultCalendar(); cal.createAllDayEvent("Go Vote!", electionDate, {location:pollAddress});
Using the Maps service, we can generate static maps with the user's home or polling address as shown in the following code snippet. We display these maps on the web app page, then embed them in the reminder email and bring-along document as we will show in the following sections.
var userAddress = response.normalizedInput; var normalizedAddress = userAddress.line1 + ' ' + userAddress.city + ', ' + userAddress.state + ' ' + userAddress.zip; // normalizedAddress looks like "501 Kildaire Rd Chapel Hill, NC 27516" var staticMapUrl = Maps.newStaticMap().setSize(600, 300) .addMarker(normalizedAddress) .getMapUrl();
We also provide a simple method for users to email themselves all of this information. Using the Gmail service, we can send an HTML email that embeds the voting information and the static maps we generated above. The Apps Script documentation contains great tutorials such as the Maps tutorial we used to generate the directions below.
var email = Session.getActiveUser().getEmail(); var body = 'Election Date: ' + electionDate + '<br/>' + 'Your polling address: ' + pollAddress + '<br/>' + 'Polling Hours: ' + pollingHours + '<br/>' + '<img src="' + directions.mapUrl + '"/> <br/>' + 'Directions: ' + dirList; MailApp.sendEmail(email, 'Upcoming Election Voting Information', 'Voting Info', {htmlBody: body});
Using the Document service, we were able to easily generate a bring-along document with polling address, hours, and directions. The follow code excerpt shows how easy it is to add different elements like headers, tables, and paragraphs to a document. Apps Script also provides an extensive list of methods to programmatically control the look and presentation of the various elements.
var title = "Voting Information"; var doc = DocumentApp.create(title + " for " + homeAddress); var reportTitle = doc.appendParagraph(title); reportTitle.setFontFamily(DocumentApp.FontFamily.ARIAL) .setFontSize(22).setForegroundColor('#4A86E8') .setBold(true) .setAlignment(DocumentApp.HorizontalAlignment.CENTER); var header = doc.addHeader(); header.appendParagraph('Generated by the Election Info application ' + 'built on Google Apps Script') .setAlignment(DocumentApp.HorizontalAlignment.CENTER) .setAttributes({ITALIC : true}); var tableStyle = {}; tableStyle[DocumentApp.Attribute.PADDING_BOTTOM] = 0; tableStyle[DocumentApp.Attribute.PADDING_TOP] = 0; tableStyle[DocumentApp.Attribute.PADDING_LEFT] = 0; tableStyle[DocumentApp.Attribute.PADDING_RIGHT] = 0; var addressTable = doc.appendTable([ ['Your address: ' + homeAddress], ['Your Polling Location: ' + pollAddress], [''] ]).setAttributes(tableStyle); // add appropriately sized poll location image addressTable.getCell(1,0).appendImage(pollImg.getBlob()) .setHeight(300).setWidth(600); // populate last row of the table with polling hours addressTable.getCell(2,0).clear().appendParagraph("Polling Hours: "); addressTable.getCell(2,0).appendParagraph( UserProperties.getProperty(Keys.POLLING_HOURS));
Here is an image which shows the generated bring-along document embedded with static map images from the Maps service.
Apps Script allowed us to easily take information from an external API and tie it into various Google services to provide a great user experience. Stay tuned for an upcoming blog post showing how we created the front end!
"No file is an island." John Donne said something a bit like this in 1620 A. D., hundreds of years before the internet was ever invented, and it just gets more and more true as time goes on. In our online world of information, entertainment, and socializing, everyone is connected -- and everyone wants to collaborate.
Would you like to open a collaborative space in your Drive app by injecting comments and discussion threads in your users' files? This is now easily done with the Drive API. Using the new comments and replies resources together with a simple anchoring scheme to nail down the location of comments in your document, you can provide discussion threads much like the ones found in Google Docs.
Our new commenting model has two layers:
In a typical scenario, an app gets the head revision of a file, lists the existing discussions, and inserts or deletes comments and replies as needed. It’s recommended that apps should also perform user permission checks and make sure commenters are authorized. These best practices, along with a complete reference for anchoring comments in files, are detailed in Managing Comment and Discussions in the Drive SDK.
For a great example of commenting best practices, you won’t need to look any further than the Google docs in your Drive. The features you see in our own implementation -- highlighted anchoring, UI options to reply, resolve, edit and delete -- are all available for you to add to your own app.
We look forward to seeing how you integrate comments and discussions in to your Drive app! Do a better job than Google docs, and we promise to be more pleased than surprised. If you have questions or feedback about comments and discussions, don’t hesitate to let us know on our Stack Overflow tag, google-drive-sdk.
Some enterprise applications need to programmatically access their users’ data without any manual authorization on their part. For example, you might want to use the Tasks API to add a task to all of your employees’ Google Tasks lists during the holiday season to remind them of something like, “Come pick up your holiday gift at the front desk!” Or, you might want to run some company-wide analysis of the content of your employees’ Google Drive.
In Google Apps domains, the domain administrator can grant applications domain-wide access to its users' data — this is referred as domain-wide delegation of authority. This basically allows applications to act on behalf of Google Apps domain users when using APIs.
Until recently this technique was mostly performed using 2-Legged OAuth 1.0a (2-LO). However, with the deprecation of the OAuth 1.0 protocol and the resulting programmed shutdown of 2-LO, the recommended authorization mechanism is now to use OAuth 2.0 and service accounts.
Unlike regular Google accounts that belong to an end user, service accounts are owned by your application and therefore identify your application. They can be created in the Google APIs Console and come with their own OAuth 2.0 credentials.
Google Apps domain administrators can delegate domain-wide authority to the service account’s credentials for a set of APIs. This results in allowing the application, by using the service account’s credentials, to act on behalf of the Google Apps domain’s users.
If you’d like to learn more, have a look at the recently published Google Drive SDK documentation on using OAuth 2.0 and service accounts for domain-wide delegation of authority.. These documents provide a step by step process and code samples to help you get started with service accounts.
From nations choosing presidents to offices selecting which coffee to brew, we often find ourselves involved in election systems designed to choose the best option. This spring my alma mater's solar vehicle team, CalSol, needed to elect new leaders. Our previous system was painfully slow, involved "raising hands" in a room, and excluded any team members who could not attend a specific meeting. I set out to solve these problems and the result was an easy method for running fair elections in a matter of minutes.
I was able to build the system completely on Google products and technologies:
I used a lesser known voting system called instant-runoff voting (IRV), or the alternative vote, which asks voters to rank candidates rather than cast a single vote. These votes, along with a secret voting key which I provided to each member, are recorded with a Google Form that automatically populates a spreadsheet. The code in Apps Script looks through the spreadsheet to count the votes while ensuring that each voting key is only used once. The secret keys not only prevent voters from casting multiple votes, but they also allow voters to change their vote by submitting the form again.
Below is a simplified snippet of code that shows the general process used to calculate the winner.
/* Some code omitted for clarity */ /* candidates is a list of names (strings) */ var candidates = get_all_candidates(results_range); /* votes is an object mapping candidate names -> number of votes */ var votes = get_votes(results_range, candidates, keys_range, valid_keys); /* winner is candidate name (string) or null */ var winner = get_winner(votes, candidates); while (winner == null) { /* Modify candidates to only include remaining candidates */ get_remaining_candidates(votes, candidates); if (candidates.length == 0) { Browser.msgBox("Tie"); return; } votes = get_votes(results_range, candidates, keys_range, valid_keys); winner = get_winner(votes, candidates); } Browser.msgBox("Winner: " + winner);
I learned that putting a little effort into Apps Script can make people happy and save a lot of time. The team feedback was outstanding. One CalSol member said the process was an "Excellent, clean, and professional voting process. This should become a standard [for the team]." I was elated when I was able to close the polls during a meeting and announce the winners of twelve independent elections in just a matter of minutes.
If you like, you can watch a video demonstrating how to create and run your own election using this script:
Try the script yourself to make sure your coffee preferences are heard!
It’s time for the 2012 General Election in the United States and along with it comes the tedious process of finding your voter registration, polling sites, times, directions, etc. The previously announced Google Civic Information API provides a great service to programmatically obtain much of this information based on the your home address. Google Apps Script makes it really quick and easy to build a web application that queries this information and uses various Google services to organize and track your information.
Election Info is a sample application built using Apps Script that can:
HtmlService
UrlFetch
MapsService
UserProperties
As you can see, this is a comprehensive sample app that is useful while also highlighting key Apps Script capabilities.
Install the app from the Chrome Web Store. Check back soon as we will be writing a blog post with details and sample code on how the sample was built.
Since we released version 2 of the Google Drive SDK at Google I/O, we’ve been quietly updating the DrEdit sample application to use the new API. As part of the update, the UI for DrEdit has been rewritten to use AngularJS, a modern web application toolset developed by Google and used in apps at DoubleClick. You might be wondering -- why go through the trouble of rewriting the UI for a basic sample app just to show off some new API features? Turns out it was more of a happy coincidence, but a valuable one and great learning experience!
I had the pleasure of co-presenting a session on building great apps for Google Drive, and a big focus of the talk was on all the little things that go into making an app intuitive and user-friendly. This is particularly important for Google Drive, where many users are already familiar with the built-in apps like Docs, Presentations, and Spreadsheets.
The first version of DrEdit was a good demo app, but didn’t follow all of our recommendations. I didn’t want to tell developers all the things they should be doing without having tried them myself. I decided to write a separate sample for the talk and needed a solid base to build on. It was the perfect opportunity to learn a new tool!
Angular doesn’t aim to abstract away HTML, Javascript & CSS. Rather, it enhances HTML to make building dynamic apps easier. One benefit, besides a nice short learning curve, is the positive interaction with other tools. To give the app some structure, I used Bootstrap. For example, the HTML for displaying the authenticated user’s info and a small dropdown to link to their profile in the navigation bar only required a few minor changes from typical Bootstrap usage (shown in bold) to wire up to a controller.
<ul class="nav pull-right" ng-controller="UserCtrl"> <li class="dropdown"> <a class="dropdown-toggle" data-toggle="dropdown" href="#"> {{user.email}} </a> <ul class="dropdown-menu"> <li><a href="{{user.link}}" target="_blank">Profile</a></li> </ul> </li> </ul>
Even models are plain javascript objects. Anything reachable through a scope (the binding between a view and controller) is considered part of the model. These can be primitives, hashes, or objects. No need to extend a base class or access properties through special properties. Rather than use change listeners that require special instrumentation, Angular uses dirty checking to detect model changes and update views.
The one catch with this approach is it requires any changes to the model to be made inside the scope of a scope.$apply(fn) call. In most cases, this is done automatically. When working with external libraries or raw XMLHttpRequests that can fire asynchronous callbacks, calling $apply yourself is necessary to make sure mutations are tracked correctly.
scope.$apply(fn)
$apply
Speaking of asynchronous tasks…
No, I’m not talking about the hit song by 80’s band Naked Eyes, rather Angular’s $q service based on one of the proposed CommonJS Promises APIs. If you’re already familiar with JQuery’s deferred object or any of the other related implementations, this is familiar territory. If not, time to learn. Working with deferred objects can be a lot easier than the traditional callback approach. You can compose async tasks either serially or in parallel, chain callbacks, and return deferred objects from functions like normal results.
Where this mostly comes into play is Angular’s $http service. If you’ve used jQuery, you’ll find it similar to jQuery.ajax() & the jqXHR result. It is based on the deferred/promises API and also ensures callbacks are executed correctly inside $apply for safe & efficient model mutations. This combination makes it easy to work with remote services in Angular.
$http
Trying to learn some new frameworks while preparing for Google I/O and helping developers to launch apps on our updated API all within a few weeks was a lot to take on. A few corners were cut and there are a few things I’d like to revisit when time permits:
<editor content=”myModel.text”/>
I know I’ve only scratched the surface and have a lot more to learn. Even so, it was incredibly fun diving head first into AngularJS, and I highly recommend considering it if you’re dissatisfied with your current framework or just want to learn something new!
Do you like to store photos in Google Drive? You are not alone! Photographs are one of the most common file types stored in Google Drive. The Google Drive API now exposes Exif data for photos, so that Google Drive Apps can use it. The Exif data contains information about camera settings and photo attributes.
Despite being an awful photographer, I love photographing benches, and here is one I took while at the beach. Let’s have a look at some of these new fields for this photo.
When I examine the metadata for this image using a drive.files.get call, there is now a field, imageMediaMetadata, containing the detailed photo information:
drive.files.get
imageMediaMetadata
"imageMediaMetadata": { "width": 2888, "height": 1000, "rotation": 0, "date": "2012:07:08 15:22:25", "cameraMake": "NIKON CORPORATION", "cameraModel": "NIKON D90", "exposureTime": 8.0E-4, "aperture": 5.6, "flashUsed": false, "focalLength": 105.0, "isoSpeed": 200 }
So whether you are just storing your amateur snaps like me, or using Google Drive to store serious photographs, we hope this will be useful information for Drive apps. For example, a photo organizing application will be able to create thumbnail and information views for photos without ever having to download them.
For more information, please visit our documentation, and if you have any technical questions, please ask them on StackOverflow. Our team are waiting to hear from you.
Whenever you upload a file to Google Drive, we try to be smart and understand more about the new file. We index its text content, generate thumbnails and even use Google Goggles to recognize images. However, as any kind of files can be uploaded to Drive, there are cases where it is impossible for Drive to understand what the file content is. For instance, when inserting or updating a shortcut, the file content is not known to Drive and a thumbnail can’t be automatically generated.
Developers can now use the Google Drive SDK to provide thumbnail images for those files. The new thumbnail property on the File resource includes two sub-properties that you can set when uploading a new file or updating an existing one: “image” to contain the base64-encoded image data and “mimeType” to specify one of the supported image formats: “image/png”, “image/gif”, or “image/jpeg”.
As thumbnails must reflect the current status of the file, they are invalidated every time the file content changes, so your application should make sure to always upload a new thumbnail together with the updated content.
For more information and to learn about all requirements and recommendations about this new feature, please refer to the Uploading thumbnails section of the Google Drive SDK documentation, and don’t hesitate to ask us your technical questions!
The Drive SDK allows apps to store all kinds of files and file-like items in user-managed cloud storage. Files can be standard document formats like PDF, images, video & audio clips, or even your proprietary application data files. Storing files in Drive makes it easy for users to organize, search, and securely share them with their coworkers, friends, or family.
However, some applications work better with document or application data stored in a database. For example, let’s imagine a modern, web-based project management tool that provides lots of awesome features via data objects that are assembled dynamically at runtime for presentation to the user. In such cases, there is no single file to store all the data that comprises the project -- though there is of course a named “file” item that users will want to save and list in their Drive. Drive applications like this can create file-like entries called shortcuts that allow users to organize, access, and share items as if they were files stored in Drive.
Creating a shortcut is not much different than creating a regular file. Just set the MIME type to application/vnd.google-apps.drive-sdk, and make sure you don’t upload any actual content in the call to files.insert. Here’s an example of creating a shortcut using Python:
application/vnd.google-apps.drive-sdk
files.insert
shortcut = { 'title': 'My project plan', 'mimetype': 'application/vnd.google-apps.drive-sdk', 'description': 'Project plan for the launch of our new product!' } file = service.files().insert(body=shortcut).execute() key = file['id'] # Key to use when re-opening shortcuts
For examples in other supported languages, see the Drive SDK documentation.
Opening shortcuts in Drive always launches the application that created them. Shortcuts can even be synchronized to the desktop. Opening a shortcut from the desktop will launch the application that created it in a new browser tab.
Shortcuts require special consideration when it comes to sharing and security. Since the actual content is not stored in Drive, applications are responsible for enforcing permissions and ensuring that only authorized users are allowed to read or update content. Follow these best practices when working with shortcuts:
files.get
userPermission
reader
commenter
Honoring permissions not only ensures the protection of user data, but also provides a consistent user experience and added value to Drive applications. Users should be able to safely share an item in Drive without worrying about the particular implementation details of the application that created it.
If you have any questions about shortcuts, don’t hesitate to ask us on our Stack Overflow tag, google-drive-sdk
