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!
