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:
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
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!
Hi all, Template not found :(
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?
Hi, Does this have the ability to use predecessors?
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.
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!
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
Any progress on resolution of the issue (reported on 27 Feb)?
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.
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.
Post a Comment