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.
