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.
