A Gantt chart is typically used in project management software to help stakeholders keep track of the project schedules and timelines.
This chart is very easy to understand as you can quickly see the project, timelines, and deadlines, relationships between activities and tasks, as well as project different phases in a visual bar-style display.
You can easily add this same Gantt chart functionality to your own JavaScript project management spreadsheet applications by following the below instructions.
We will be using the SpreadJS spreadsheet conditional formatting features and the associated Spread Designer that is included with the developer license to complete this sample without writing a single line of JavaScript code!
Refer to the documentation if you need to install the Designer.
Tasks and Dates
For this sample, let's create a timeline for creating a quick new website.
The primary data source of a Gantt Chart is the task list and their corresponding start and end dates. The following table could be the corresponding tasks and dates that could be stored in the database or other files.
Next, we will add the project timeline that will include the calendar dates, the Gantt Chart Bars, and the option to automatically update the bar timelines if the ticket or start/end dates changes.
Set up the Timeline
1. We start by adding a start week date to our table. For example, add "3/1/2021" in cell E4. 2. Format the date by showing only the day number. We can do that by following the instructions below:
- Select the cell
- Right-click and select the "Format Cells" option
- In the number Tab, Category Box, select "Custom"
- Type d in the Type field on the right side of the dialog box.
- Select OK.
3. In cell F4, enter =1+E4 and drag to enter dates for 3 weeks (till cell Y4).
4. Now let's add the week headers. Select cells E4:K4 then select the Merge & Center ribbon option. 5. Add the date by referencing E3, "=E3". Do the same for the other two weeks, L4:R4 and S4:Y4. For these formulas, use "=E3+7" for the second week and "=L3+7" for the third.
6. Next let's add the weekday names. For this sample, we only want to show the first letter of the week's name. This is done by entering the following formula in cell E5: "=LEFT(TEXT(E4,"ddd"),1)". Drag fill this cell to the right to apply this formula to other the cells in this row.
The following image shows these results. You can also visually separate the timelines by adding borders and colors as we have also done here.
Add the Bars to the Gantt Chart via Conditional Formatting
We will use Conditional Formatting to create the Gantt chart bars in our example. These cells will be filled grey when the date in the timeline is between the Start and End date of the Task. We will add this rule using formulas.
In SpreadJS Designer:
1. Select the area where the Gantt Bars will be. In this example, cells: E7:Y10,E12:Y15,E17:Y19,E21:Y22,E24:Y26 - we have excluded the cells allocated for the project Phases. 2. In the Home tab, select the Conditional Formating menu. 3. Click New Rule.
4. As a rule type select "Use formula to determine which cells to format".
5. Enter your formula, in our case "=AND(E$4>=$B7,E$4<=$C7)"
6. Click Format → Fill → Select a Yellow shade as background.
7. Click OK
The chart is beginning to make sense now as the cells should highlight with the proper timeline dates.
Make the Timeline More Dynamic
To make the timeline more dynamic we can proceed in this way:
Add project Start and Week into our spreadsheet. These variables would be entered by the user and depending on their values the timeline would change. For easier understanding, we can add a specific name for these cells as we will need to use those later.
To set a name for a cell do as follow:
1. On the Formulas Tab, select Name Manager. 2. In the popup click the New button and set the name of the cell. You can also add a comment and change the referents. 3. The same process can be repeated to set a name for the week
4. After setting the names, the first date of the Gantt Timeline would not be "3/1/2021" as we set it at the beginning but we would have to change it with: =project_start+(week-1)*7
After having done that, if we were to change the start date of the project or the week the entire timeline would change.
Add Column for Progress (% Complete)
During this step, we have to create a new column after TASK in our Gantt table, called Progress.
This column would contain the completion percentage of a certain task. To make things more interesting we can use Conditional Formatting to better see the percentage.
- Select the area where the Progress Bars would be.
- In the Home tab, select the Conditional Formating menu.
- Select Data Bars and then, Solid Fill.
We can calculate the progress and Start and End date of the phases as well, so we have more information when seeing the chart.
Show Progress in the Chart by Shading a Portion of the Bars with a Different Color
To shade a portion of the bars based on the progress we can use Conditional Formatting. Just as we did, when we added the gray bars by using another formula we can add the shading option as well. So:
1. Create custom names for cells belonging to Progress (progress), Start Date(start), and End Date(end). To do that we can follow the logic used when created a custom name for Project Start and Week.
Note: The reference is not absolute. The custom name will be used for all the rows of the specific column, starting from row 7.
2. Select the area where the Gantt Bars are. In this Example cells: F7:Z10,F12:Z15,F17:Z19,F21:Z22,F24:Z26 - we have excluded the cells allocated for the project Phases.
3. Conditional Formating → New Rule → "Use formula to determine which cells to format".
4. Enter formula "=1AND(F$4>=start,F$4<=start+(progress(end-start+1))-1)"
5. Click Format → Fill → Select Green as background.
6. Click OK
Note: If the task has a 5 days duration and it is 50% completed, only 2 days would be highlighted.
Add Status and Priority Column
For adding the Status and Progress Column, we are going to use Conditional formatting once again.
First, we add the Status column and as values, we reference the progress cells. The status indicators would be with different colors depending on the progress value.
After that, we are going to below the above instructions:
- Select the area.
- Select the Conditional Formating → New Rule→ Format all cells on their values".
- On Format Style, select "Icon Sets".
- Select the right Icon Styles and then click on "Show Icon Only".
- Assign the intervals for the status circles. 0% - black; 1-33% - red; 34-66% - yellow; 67-100% - green.
- Click OK.
Then we will have to add another column for the task Priority. In that column, we need to determine the priority of the tasks: 1-very high, 4-low.
After that, follow the below instructions:
- Select the area.
- Select the Conditional Formating → New Rule→ Format all cells on their values".
- Select the right Icon Styles, then "Show Icon Only" and "Reverse Icon Order".
- Assign the intervals.
- Click OK.
Use Outline Colum to Hierarchize the Phases
For the moment the phases are distinguished only because we have bolded the font. SpreadJS allows the users to use the outlineColumn property to show hierarchical data in a tree view. To do that follow the instructions:
1. Select the task cells and open the context menu (right-click) 2. Select Format Cells
3. Increase the text alignment Intend to 1. By doing that the tasks cells' text would be moving a little bit in the right.
4. Select the Column where Phases and Task text cells are located. Right-click and select Outline Column.
5. Increase the Max Level to 1, and select the Show Indicator. You can select Custom Indicator Image and use another indicator if wanted. As default, the indicator is a small triangle.
Below you can see how our Gantt Chart would behave. The timeline is dynamic so every time we change the Project Start Date, our Gant timeline would change as well.
For display purposes, we have included only 3 weeks in the timeline but you can see the status of other weeks by changing the Week variable.
You can also change the Start and End Date of the chart and their progress and it would be mirrored into the Gantt Chart.
Conclusion
SpreadJS customizable JavaScript spreadsheet is the most flexible component to create custom Gantt views with detailed task schedules, progress, and updates. You can use formulas and formatting to enhance with additional columns and calculations to fit your needs.
-
Get the free trial of SpreadJS and discover how easily you can embed Excel-like JavaScript spreadsheets, and much more business-needed functionalities, into your enterprise applications.
-
Download here the example and follow along.
-
Contact us to learn more:
us.sales@grapecity.com -
Visit the SpreadJS page:
/spreadjs
Jola Hoxha