Google Apps Developer Blog: Managing Projects with Gantt Charts using Google Apps Script

Managing Projects with Gantt Charts using Google Apps Script

December 21, 2012

Editor’s Note: Guest author Ronald Dahrs runs Forscale, an IT and project management company based in the Netherlands. -- Arun NagarajanGoogle 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!; January 4, 2013 at 11:46 AM
Mario said...: Hi all,

Template not found :(; January 18, 2013 at 1:39 PM
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?; February 11, 2013 at 5:41 PM
Tim said...: Hi,
Does this have the ability to use predecessors?; February 20, 2013 at 8:27 AM
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.; February 21, 2013 at 12:53 AM
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!; February 27, 2013 at 8:55 AM
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; February 27, 2013 at 9:12 AM
Stew said...: Any progress on resolution of the issue (reported on 27 Feb)?; March 5, 2013 at 3:14 AM
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.; March 12, 2013 at 2:28 AM
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.; June 21, 2013 at 9:51 AM

Apps Developer Blog

Managing Projects with Gantt Charts using Google Apps Script

10 comments :

Labels

Archive

Feed

Company-wide

Products

Developers