Manual ProjectSheet (Pro)
Google Sheets add-on
ProjectSheet is a Google spreadsheet with Add-on functionality which you can get and manage from a Google spreadsheet Add-ons menu. The add-on creates two sheets for Task schedule and Resources and a sidebar on the right side of the sheets with project scheduling functions. The sheets can be selected from tabs at the bottom of the spreadsheet.
You can edit the sheets with the standard spreadsheet functions in combination with the special add-on functions from the sidebar.
You can close the sidebar by clicking on X at the right top.
Re-opening the sidebar is done using menu Add-ons > ProjectSheet > Open sidebar.
You can provide all empty or copied Google spreadsheets with ProjectSheet from menu Add-ons > ProjectSheet > Add ProjectSheet.
In case of an empty spreadsheet wait until the sheets are completed and the sidebar appears.
The spreadsheet can be customized, but within restrictions because of ProjectSheet functionality:
- Do not change or extend the Gantt chart in the sheet itself.
Adjust the timescale with the settings in the sidebar.
- Do not delete or copy ProjectSheet columns.
You can hide ProjectSheet columns and insert custom columns left of the Gantt chart.
- Keep the number of header rows unchanged, including hidden row 3.
- Do not delete or copy ProjectSheet worksheets in the same spreadsheet.
Custom worksheets may be added.
- ProjectSheet columns cannot keep cell formula's.
Cell formulas in custom columns can use cell values from ProjectSheet columns.
As soon as you start using ProjectSheet for the first time, Google gives an authorization screen. Here you can see that the add-on modifies the spreadsheet and that it requires access to an external service because of the Pro functionality.
If you do not want ProjectSheet anymore in a certain spreadsheet, you can disable it at menu Add-ons > Manage add-ons.... It opens a dialog with all your add-ons. Select MANAGE at ProjectSheet and un-select 'Use in this document'. After you did this you can delete the tabs without rebuild. In the same menu you can 'Remove' ProjectSheet completely from all your spreadsheets.
The task columns are in the task sheet.
This column is read-only and states the WBS (Work Breakdown Structure) code per task. Changes in the WBS (like inserting tasks) may result in renumbering of WBS codes. Changing a task's level of indent is done by WBS functions in the sidebar.
The code consists of steps divided by dots. Step numbers consist of consecutive numbers starting at 1. The amount of steps equals the level of the WBS code. Codes having an equal start belong together.
|1||Summary task A|
|1.2||Summary task C|
Tasks B, C and F have the same level. They each have two steps.
Tasks D and E belong to the same summary task C, they share the same first two steps.
The WBS column is read-only. The WBS code of all tasks are re-calculated automatically after each affecting change.
The task description is the starting point of the schedule. The description states what needs to be done or what needs to be delivered. The text will indent according to its WBS level. Notes can be added to a Task description by using the right mouse click menu.
Newly added tasks will automatically get a WBS code with the same outline level as the previous task. A task and its WBS code will disappear entirely if its description is deleted.
Start date, finish date
These columns contain the start date and finish date of each task. These dates are shown as a bar in the Gantt chart if they are inside the time scale.
Summary tasks receive their dates from their subtasks, they cannot be entered.
Double-clicking an entered date gives a calendar pop-up to adjust the date.
The progress of each task. It is expressed in %.
Summary tasks receive their progress from their subtasks, there is no need to enter them.
The task duration is entered in working days. Two weekend days are taken into account and are not counted in the duration and proceed the week start day as selected in the sidebar.
The finish date follows a change in the duration. The duration follows a change in the finish date. Work hours follow duration days with 8 hours per day if they are coupled with this option in the sidebar.
Removing the duration removes the start and finish dates.
Summary task days cannot be entered, they are calculated based on their start and finish dates.
A duration of 0 gives a milestone.
Tasks can be linked:
|chain||start date follows finish date of its previous task in the WBS, on the same level; this is indicated with '>' at the start of the Gantt bar|
|fixed||start date is fixed and will not shift with its summary task; this is indicated with 'X' at the start of the Gantt bar|
Work hours (Pro)
Enter here the hours for a task. A summary task adds the underlying hours. When a task's duration is increased, the work hours needs to be updated manually.
The Pro version has the option to couple work hours to task duration with 8 hours per day.
The assigned resources. A resource can be a work resource type or a material resource type, as indicated in the Resources sheet. The quantity per resource is stated after each resource between [ ], except for 1 which can be omitted. Examples:
|R||corresponds to 100% availability (work) or 1 unit (material)|
|R||corresponds to 100% availability or 1 unit|
|R[0.5]||corresponds to 50% availability or 0.5 units|
Assigning multiple resources is indicated by separating them by a semicolon. Example:
Assigning work resources will not change the required work hours, but in he Pro version the task duration will follow the assigned availability of the work resources.
A resource is automatically added to the Resources sheet or adjusted if already present.
The cost per task, calculated from the assigned resource quantities and the cost per resource, and in case of a work resource, the required work hours. The work hours per task are divided amongst the assigned work resources based on availability.
Cost per work resource = assigned availability * Rate per resource * Work hours / total assigned availability
Cost per material resource = assigned resource quantity * Rate per resource
User defined (e.g. Workdays)
A user defined column can contain any information. Also cell formulas can be applied for dedicated calculations on e.g. dates and costs.
Resource columns (Pro)
The resource columns are in the resource sheet.
Descriptive name of the resource. A resource will disappear entirely if its name is deleted.
A resource can be a work resource (default) or a material resource. Work resources are counted per work hour, material resources are counted per unit.
Rate per resource
Cost for a resource per hour or unit.
All work hours or units assigned to a single resource across all tasks.
All cost of a single resource across all tasks.
Some functions require that one or more tasks are selected first before choosing a function in the sidebar. Selection can be done per row at the left margin of the sheet or by selecting any cell in the task row. Only adjacent rows can be selected, not multiple selections.
The sidebar can be closed with X in its black title bar. The sidebar can be opened again in the add-ons menu of the spreadsheet.
Edit Work Breakdown Structure
< WBS WBS >
This function decreases / increases the level of the WBS code and the amount of indent of the task descriptions of the selected tasks.
Hide / Show tasks
collapse selected task(s) / expand selected task(s)
collapse all summary tasks / show all tasks
hide 100% completed tasks
Shift start date of tasks
Shift non-summary tasks in time, adjusting their start and finish dates.
In order to see the shifted tasks in the Gantt chart, it may be needed to change the start date of the time scale.
In the Pro version chaining comes before shifting, and shifting always results in a working day.
Set Gantt chart timescale
Adjust start date of time scale
The start date of the timescale can be set to :
The start date will be the first start day of the work week before the resulting date.
Set weeks per period
The time scale consists of 10 periods. A period can last from 1 to 13 weeks.
Start day of work week
The first day of the work week can be set to Sunday or Monday. The start day of a period in the Gantt timescale will be adjusted. In the Pro version the weekend calculation will be done in line with this setting (Friday-Saturday or Saturday-Sunday). Monday is the default setting.
Progress with color indication / Gantt bar with progress color
When selected, the background color of the Progress cell indicates progress status.
Assign resources (Pro)
This function in the Sidebar assigns resources to the selected task(s).
Adjust working time (Pro)
Link Days and Work hours
Task duration and Work hours can be set independent or follow each other with the set working hours per day. The option takes effect on changes and on new tasks only.
Set the start day of the work week, the number of working days per week and the number of working hours per work day.
A new tab will open with this page.
Start a 10-days trial with the Pro features or activate this version after your PayPal purchase. In the black title bar Pro will appear after ProjectSheet and in case of the trial between brackets the remaining number of days.
This function in the Sidebar recalculates the WBS codes and the Gantt chart. In some cases they are not automatically updated after changes.