Application examples of ProjectSheet
As a Google Sheets add-on, ProjectSheet can be used for a specific project environment by using the Google Sheets and Google Drive functionality.
An annual plan is an operational plan that indicates specific goals and objectives for the coming year. It often includes a detailed plan outlining which activities will be accomplished, by when and by whom.
With the Pro version you can create a waterfall schedule to see whether all activities are feasible in time. Events are set to a fixed date. Setting the Gantt timescale in the sidebar to week numbers, where a column is a week, gives a clear overview when activities need to be done.
Colors give tasks a special meaning. The background color of a task description is passed to the corresponding Gantt bar after using Refresh in the sidebar. Progress can also be tracked with colors if enabled in the settings.
The annual plan can be shared in Google Drive with stakeholders for viewing or editing. Each editor requires a Pro license, per account or for the whole (sub)domain. Viewers do not need to install ProjectSheet.
Date and time
ProjectSheet Pro combines start and finish dates with time, unlike date-only as in the free version. You can see the time of a combined date and time cell in the formula bar of the spreadsheet. The time is based on 24 hours for a full working day in order to be able to work with decimal task duration Days. In the example below we have a duration of 2.5 days, resulting in a finish time of 12:00.
To convert the time to your own working period in a day, you insert a column to contain the time per Start date and Finish date column. In these Start time and Finish time columns you add cell formulas to get the time part and do the time conversion:
= start_time / 24 + MOD(reference to cell in date-time column in same row, 1) * working_hours / 24
You best format the inserted columns as time and keep the original column formatted as date.
See the example below for start_time=9 and working_hours=8.
Google Sheets has a function to create a dedicated worksheet with a graphical timeline view. This timeline view can be useful to present the Gantt chart of ProjectSheet as a more graphical alternative to your project team or customer.
The Timeline worksheet is available in the Insert menu of Google Sheets in most (paid) Google Workspace editions. The timeline view needs to be linked to tasks in the Task Schedule worksheet. Tasks are represented as cards with the task description and task details in it, like progress. The cards are positioned along a timescale based on the start and finish date per task.
In the Timeline view you need to select the relevant columns, including the header. These columns can be used to customize the view. The cards can have the color of a column in the Task Schedule worksheet, like Task description or Progress. The cards can be grouped, based on a column. Changes in the Task Schedule worksheet are automatically updated in the Timeline.
Using parameters to calculate a schedule has many advantages. Rather than making an estimate for each deliverable, you describe similar deliverables with relevant parameters. Based on these parameters you calculate the required amount of work or material. You can optimize the parameters over time, based on experience.
An example where parametric planning can be used, is rollout planning. If the rollout consists of similar products in specific configurations, you can use the configuration parameters to calculate the estimated work.
In ProjectSheet Pro, parametric planning is realized by inserting a column for each parameter in the Task Schedule work sheet, see example below.
Cells in the Days column have a cell formula referring to the parameters A and B in front of it. After you enter values to these parameters, you need to perform a refresh in the sidebar to update the schedule and Gantt chart. Please note that a cell formula will be removed by Google Sheets when you enter a value in the cell.
As an alternative you can calculate the Work hours from the parameters. To link the Work hours to duration Days and assigned Resources, you set the Work hours setting to divide Work hours among Resources and link days and Work hours.