BOT's #2: How to Build an Automated Gantt Chart with Google Sheets

As long as you are a project manager, at some point, you must work with Gantt Charts. Gantt Charts is a good way to illustrate a project schedule and there are a handful of project management tools in the likes of Asana and Trello that I have used and would recommend.

But what happens if the tools fall short and you want to work with something that will serve the purpose? Google Sheets is that one solution that should come in handy with a few tweaks here and there your way. In this article, I’ll show you how to build an automated Gantt Chart with Google Sheets.

What is your Gantt's Chart goal?

There are a few questions you need to ask yourself before creating your Gantt Chart. Creating a Gantt Chart starts off with knowing the purpose or reasons for which it's being created. This should better inform the data schema and ensure a seamless ETL process (Extraction, Transform, and Loading of data) for visualization.

An example of why you are creating a Gantt Chart could be to visualize a project timeline or classroom schedule. The Timelines or schedules should primarily have tasks or subtasks, those responsible or assigned and other details you find necessary. Some other details could be a more granular aspect such as geography or where the project is to be implemented, details of the tasks and etcetera.

Step by step process of building your Gantt Chart

1. Setting up for Data collection

Depending on how you want to collect your data, Google Forms can be a great way to collect the data especially if you are working with a team to add their details to the schedule or timelines. In separate articles, we’ll explore how to use Google Forms to collect data.

For this specific scenario, we’ll use the spreadsheet tab with prepopulated data as in the image below or as linked here

From the image, you can see we have quite a few column headers. Each of these headers shows the data we are collecting and the most important being the start and end dates. The start and end dates allow us to visualize data over a certain range. As a complementary, in this scenario, we have used the subject column to create custom colors for a specific subject. The subject here could be a project in a different scenario.

Another alternative way, you could probably create custom colors is by using project members for this case we can say the instructor is the equivalent.

2. Creating your visualization

Now that you have the data you can visualize, say you have set up the schema the way we have and entered some information for visualization. In a new Gantt Chart tab, you want to use the following formulas to create your visuals.

1. The role of this formula is to dynamically change the dashboard/Gantt chart Title with the present year or year selected in cell B7

=CONCATENATE("Sample Planner/Calendar"," ",TEXT(B7,"YYYY"))        

2. The role of this formula is to convert the ranges dates into a month and year. For example, October 2019, December 2018, …..

=CONCATENATE(TEXT(H8,"MMMM",TEXT($H$8,"YYYY"))        

3. The role of this formula is to convert the date into a week number. For example, Week 1, Week 2, Week….

=CONCATENATE("Week"," ",WEEKNUM(E8))        

4. The role of this formula is to increase a weekday date in a succeeding cell by 1

=D8+IF(WEEKDAY(D8)=6,3,1)        

5. The role of this formula is to convert the date in a specific cell to a corresponding day of the week abbreviation in a subsequent cell.

=TEXT(E8,"DDD")        

6. In this formula we are running a query to select specific columns of data that meet the condition for being within a specific date range and then ordering by A which means the values sort should be by column A.

=IFERROR((QUERY(Consolidated_Schedule!1:2000,"Select H,D,C where A>=date '"&TEXT(A7,"yyyy-mm-dd")&"' and B<= date '"&TEXT(B7,"yyyy-mm-dd")&"' Order by A")),"")        

7. Finally, we get to the most important formula in this case. This formula will allow us to color-code by subjects for specific dates and bring the magic to the visuals. It’s basically that formula in Google Sheets that allows us to run the Gantt Chart.

=IF(COUNTIF(QUERY(FILTER(Consolidated_Schedule!$A$2:$H,((Consolidated_Schedule!$B$2:$B>=D$8)*(Consolidated_Schedule!$A$2:$A<=D$8))),"Select Col4 Where Col4 = '"&$B9&"'"),$B9)>0, SPARKLINE(RAW_DATA!$G$2:$H$2,{"charttype","bar";"max",max(2);"color1",switch($A9,"Geography","#F6F740","Maths","#85CB33","Physics","#7E3F8F", "Chemistry","#FE7F2D","#a6b07e")})," ")        

I hope going through this article has given you an insight on how to create your own Gantt Chart in Google Sheets. The steps above might have not been so in-depth but an insight on how you could get the job done. Please contact me in case something is not clear or for general feedback.

Resources

Class calendar

About the author

Rogers is a data and emerging tech enthusiast. He is the Co-founder and VP of Operations at OurPass.

OurPass is a global neobank providing businesses of all sizes with access to every banking, payment, and business management tool they need to start, grow and scale their businesses.

Our mission is to create a borderless world of successful businesses and we are constantly building new tools to ensure that whatever stage a business is in, they have access to every tool they need to thrive, all in one place.

Founded in 2021 as a one-click checkout company, we made a pivot in July 2022 when we saw that beyond offering a niche service, we could provide end-to-end solutions that helped entrepreneurs grow every aspect of their businesses.

In just a few months since our transition, we have become a leading business bank serving thousands of customers including some of the biggest retail outlets in Nigeria such as Spar, Shoprite, and Eat N Go (parent company of Dominos, Cold Stone Creamery, and Pinkberry), processing about a million transactions monthly.

OurPass also holds a Microfinance Banking License from the Central Bank of Nigeria.

To unlock limitless smart business banking that OurPass app offers, download the app from the?App Store?or?Google Play Store, create an account, and join thousands of businesses growing with us.

Connect with us, join our vibrant community on?Facebook,?Twitter,?Instagram,?LinkedIn,?and?YouTube, and stay up to date with the latest information about our services.