Managing Projects with Gantt Charts using Google Apps Script

Friday, December 21, 2012 | 10:42 AM

Labels: ,

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.

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.

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);

Ronald Dahrs

Ronald combines his knowledge of project management and software solutions at his company, Forscale. He believes Google Apps is an excellent platform for online project management. He uses Google Apps Script to integrate the services to manage a wide range of projects.

10 comments:

Mike said...

Great article! I'm a huge fan of leveraging Microsoft Excel for Project Management... I've written several articles on the topic over on my blog... Managing Projects with Microsoft Excel... hope this helps. Keep up the great work!

Mario said...

Hi all,

Template not found :(

Dan Lazin said...

Hi, Mario. Sorry for the delay in responding, but the template is still working fine for me using a gmail.com account.

If you are using a Google Apps for Your Domain account or a Google Apps for Business account, it's possible that your domain administrator has disabled access to the template gallery. If that's the case, please try again with a normal gmail.com account.

Alternately, we have noticed that iPads cannot access the template gallery. Are you on an iPad?

Tim said...

Hi,
Does this have the ability to use predecessors?

Ronald Dahrs said...

Hi Tim,
Linking tasks by appointing predecessors is not included. It is a powerful feature but not easy to implement in the spreadsheet in terms of GUI and in terms of consistency when making changes to the schedule like inserting rows.

Carlos Andrés Valencia said...

Hi Ronald, I'm not being able to see th eProject custom menu in the menu bar. Is there somwthing I have to do to enable it?

By the way, I believe this is a great idea and looks really nice. Thanks for sharing!

Dan Lazin said...

Hi, Carlos. What you're seeing is an issue that just popped up this week -- sorry for the inconvenience. Once it's marked resolved on the issue tracker below, Ronald's template will work again.

https://code.google.com/p/google-apps-script-issues/issues/detail?id=2479

Stew said...

Any progress on resolution of the issue (reported on 27 Feb)?

Ronald Dahrs said...

Hi Stew,

In the meantime you can circumvent the template gallery by using the spreadsheet with script template directly: https://docs.google.com/spreadsheet/ccc?key=0AhiVMVNQW_vOdHNPbm10cW9ZTjBocFB4SDRPNXBVWlE&usp=sharing. Make a copy in the File menu when you are signed in.

John Milan said...

I think Google actually took a step backwards with Apps Script replacing Gadgets. For example our Gantt Chart works great in Gadget and iFrame land, but because Apps Script is much more iFrame restrictive, I cannot do the same in the new platform.

If Google could open up just a bit more with iFrame and HTML5 support I'd love to embed our rich, interactive Gantt right inline.