Smartsheet Sheet and Template Requirements

Whether you’re initializing your Smartsheet sheets from a template or using an existing sheet, there are a few required fields you need to include for Projector and Smartsheet to properly share data.

Get Starter Templates from Projector

If you’d like to get a head start on creating Smartsheet templates and sheets, there are two options.

1. Share the Smartsheet workspace you reference above with ppsa-supportuser-smartsheet@projectorpsa.com

2. Send the email address associated with your Smartsheet User and ask Projector support to temporarily share a Projector Templates workspace with you.

Creating Your Own Templates

In the event that you want or need to create new templates, there are a few things you’ll want to know ahead of time.

1. Projector is very particular about how the templates/sheets are created in Smartsheet, so you’ll want to either start from a template/sheet layout already in use or create a new template

2. If you’re creating a template from scratch, create it as a “Project” not as a “Sheet”, nor as a Gannt”. When creating as a “Project”, some internal project settings that Projector expects are automatically taken care of. This includes properly connected duration, start date, end date, predecessor among other things.

3. In addition to starting from a “Project”, Projector also has a list of required fields (below) that you’ll want to be sure to include and properly configure.

4. There are also optional fields (below) that may be of value to your task planning workflow.

Required Fields for Smartsheet Sheets and Templates

Optional Integrated Projector Fields for Smartsheet Sheets and Templates

In addition to the required fields above, Projector will also synchronize data from Projector to Smartsheet for these additional integrated-enabled fields when they are included in a linked sheet. You will notice that each of the following fieldnames ends with “(PPSA)”. During a synchronization event, these special (PPSA) designated fields will populate with data calculated by Projector. If you add these fields to your templates or linked sheets, it is recommended that you set them as read-only in the sheet.

The “Operation (PPSA)” Field

If you include this special field and populate it with some very specific values, you can use the task plan to:

• Complete tasks

• Uncomplete milestone tasks and the milestones linked to them

• Update the remaining time for a task based on the value in another field (e.g. “Last PM Update for Hours Remaining” or the “Remaining Minutes (PPSA)” field if it’s not defined as read-only)

Open

Note that the “Operation (PPSA)” field and the other in the above image are all optional. In fact, the values (each menu item) in the “Operation (PPSA)” drop list is also optional. If you chose to include only the “Complete()” value or to set the cell value to “Complete()” based on a checkbox field somewhere else on your sheets, your are welcome to. You might even set the Operation (PPSA) field as a hidden field and use formulas to generate the cell value for a particular task (e.g. populate the cell based on other cells and drive the operations behind the scenes).

Basic approaches to using the “Operation (PPSA)” field

The options for working with Smartsheet functionality are endless. We’ve outlined what we feel is a set of basic approaches to executing the three operations noted above.

Completing a task

Setting a task to “Completed” from the task plan can be accomplished by:

1. Set the value of the Operation (PPSA) field to “Complete()” for the task in question

2. Save the task plan

After you’ve saved and subsequently refreshed the task plan frame by using the built in Smartsheet refresh behind the vertical ellipse, refreshed the entire page, or manually “Sync”ing the task plan, you will see the task marked as completed in time entry, and set to 100% complete in the task plan if you are showing the “% Complete (PPSA)” field.

Completing a milestone task

“Achieving” a milestone by updating the task plan can be accomplished by:

1. Set the value of the Operation (PPSA) field to “Complete()” for the task in question

2. Save the task plan

After you’ve saved and subsequently refreshed the task plan frame by using the built in Smartsheet refresh behind the vertical ellipse, refreshed the entire page, or manually “Sync”ing the task plan, you will see the linked milestone task marked as “Achieved” with an achieved date of today if you navigate to the Project’s parent Contract Line Item (CLI) > Setup > Milestone page.

Uncompleting a milestone task

Setting a task to “Uncompleted” can only be done for tasks without assigned roles. In practice this means that tasks explicitly linked to contract line item milestones are really the only tasks you can uncomplete.

1. Set the value of the Operation (PPSA) field to “Uncomplete()” for the task in question

2. Save the task plan

After you’ve saved and subsequently refreshed the task plan frame by using the built in Smartsheet refresh behind the vertical ellipse, refreshed the entire page, or manually “Sync”ing the task plan, you will see the linked milestone task marked as “Pending” if you navigate to the Project’s parent Contract Line Item (CLI) > Setup > Milestone page.

Set remaining minutes to

Projector thinks of task effort in terms of minutes. Most people think of task effort as hours, days, weeks, or even months. You can use the “Operation (PPSA)” functionality to update time remaining on a task whether you think in minutes or not. You simply need to set things up such that formulas feed Projector the minutes it’s expecting.

Updating the remaining minutes on a task can be accomplished by:

1. Creating a Smartsheet field to capture the task minutes remaining. If you prefer to think in hours, days, weeks, or some other unit, create columns and formulas the result in the minutes remaining column being populated with minutes.

2. Add a value to the “Operation (PPSA)” field that connects the command with the minutes remaining field

   1. Command = “SetRemainingMinutesTo()”

   2. example minutes remaining field name = “Last PM Update for Minutes Remaining”

   3. Operation drop list value that links the command to the field “SetRemainingMinutesTo(Last PM Update for Minutes Remaining)”

3. Consider creating a field to capture hours remaining, that are then converted to minutes in the “Last PM Update for Minutes Remaining” field by a formula that divides the hours by 60 to get minutes

   1. Hours field = “Assigned Hours Remaining” - this is the field where data entry for remaining time will actually happen

   2. Minutes field = “Last PM Update for Minutes Remaining” as noted above

   3. An example formula you could use within Minutes field to connect it to the hours field is:

      1. =IF([Last PM Update for Hours Remaining]@row > 0, ROUND([Last PM Update for Hours Remaining]@row * 60, 0), "")

4. Enter the remaining hours on the task in the Hours field

   1. minutes field will calculate the number of minutes and round to the nearest minute (Projector doesn’t expect partial minutes for task time remaining)

5. Save the task plan

6. After you’ve saved and subsequently refreshed the task plan frame by using the built in Smartsheet refresh behind the vertical ellipse, refreshed the entire page, or manually “Sync”ing the task plan, you will see updated values across Projector as well as in the task plan

   1. time entry, time remaining is updated

   2. time entry, percent complete is updated

   3. the “% Complete (PPSA)” optional task plan field will be updated

   4. the “Remaining Minutes (PPSA)” optional task plan field will be updated

Before & After Operation Image

The following image shows the before and after states associated with the execution of a few operations within the embedded task plan. The image includes a representation of the before and after state for the task plan in question, as well as the related timesheet and milestone pages.

Open