This article describes the Smartsheet data source settings for importing projects, milestones, resources, allocations and financial events using the integration of Smartsheet and Meisterplan.
Adding a Smartsheet Data Source
Proceed as follows to add a Smartsheet data source in the Data Sources view under Manage:
- in the left hand sidebar, under Manage, open the Data Sources view.
- Click the Add Data Source button to open the selection of available data sources.
- Select Smartsheet to add a Smartsheet data source.
General / Credentials
Name your Smartsheet data source and enter a valid access token to select a workspace, a project report and, if desired, a resource sheet.
Name: the default name for Smartsheet data sources is "Smartsheet". Rename the data source if you are using multiple Smartsheet data sources.
Access Token: the connection to Smartsheet will be set up via an API access token. Create this token in Smartsheet as described in the Creating an API Access Token for Smartsheet article, copy it and paste it here to establish the connection to Smartsheet, making the workspace selection available.
Workspace: select here the desired Smartsheet workspace to import data from. Once you have done this, the selections of project reports and resource sheets will become available.
Project Report: select here the desired Smartsheet project report from which to import projects, milestones, resources, allocations and financial events.
See the How Smartsheet and Meisterplan Work Together article for details about the required structure of the individual project sheets and the combined project report in Smartsheet.
Resource Sheet (Optional): resources will be imported if you map either or both of the Project Manager field under Project and the Assigned Resource field under Allocation in the Field Mapping tab or if you select a resource sheet. You may, but don't have to, use the same Smartsheet column for assigning project managers to projects and resources to tasks.
Without a resource sheet, only the name and, if available, the e-mail address and Smartsheet user ID of each resource assigned to an imported project as a project manager or via a task in Smartsheet will be imported.
If you wish to import additional resource fields, additional resources or roles and role allocations, create a resource sheet in Smartsheet or import our Excel template. Then select your resource sheet here.
See the Importing Resources, Roles and Allocations Smartsheet article for details about importing resources, roles and allocations.
Field Mapping
Select the Field Mapping tab to map Smartsheet project, allocation and financial event columns from the selected Project Report to the desired Meisterplan fields.
Project Field Mapping
Under Project, select the desired Smartsheet columns for the Meisterplan project fields listed here. The following fields are available. Mandatory fields marked with an asterisk must be mapped. All other fields are optional.
Please note that the four mandatory fields will not only be used for importing project data but also for importing allocations, milestones and financial events. See the following field list for details.
- Unique Project Id*: mapped to Smartsheet SheetId column by default; please note that this Smartsheet column may not be mapped to any other Meisterplan field.
- Title*: this should be mapped to the Smartsheet Primary column. If you have defined your project sheets and project summary report as explained in the How Smartsheet and Meisterplan Work Together article, this will be used to import the project name, milestone names and descriptions for financial events.
- Start Date*: project/allocation start date, milestone date, financial event due date
- Finish Date*: project/allocation finish date
- Project Manager: when this field is mapped, entries in the associated Smartsheet column will be matched against the resources that already exist in Meisterplan and against the resources imported via the Smartsheet data source. If an imported project manager is not available as a resource in Meisterplan or in the resources to import, a resource will be created for that project manager in Meisterplan during the import.
- Project Status
- Business Goal
- Cost Type
- Priority: map this to a Smartsheet number column to rank imported projects by priority. Lower priority value means higher rank. Please note that the prioritization of projects during the import may be deactivated in the Import Rules tab. By default, the associated option Sort projects by priority of imported projects is selected.
- Custom Fields: in addition to the default project fields listed here, any custom project field defined in Meisterplan may be mapped to any Smartsheet column with a compatible type.
Allocation Field Mapping
Under Allocation, you may select an assignment column and an allocation column to import allocations.
- Assigned Resource: map this to a Smartsheet column to activate the import of assigned resources and their allocations from Smartsheet. You may, but don't have to, use the same Smartsheet column for assigning project managers to projects and resources to tasks.
- Allocation (%): by default, the resource utilization is 100% for each task, using the default 40 hour a week Smartsheet calendar that will automatically be created and assigned to imported resources during the import. Map this field to a column specifying the amount of allocation in Smartsheet to import an amount of allocation other than 100%. The mapped Smartsheet column should be formatted as a percentage in Smartsheet to make sure its values are imported correctly.
Financial Event Field Mapping
Under Financial Event, you may select columns for importing financial events.
- Type: this should be mapped to a column with the following values:
- OPEX (Operational Expenditure)
- CAPEX (Capital Expenditure)
- BENEFIT (Benefit)
- Amount: this should be mapped to a column that specifies the actual amount.
In Smartsheet project sheets, you may specify financial events in any but the first row (the project row):
- The value in the Task Name column will be imported as Description.
- The value in the Start column will be imported as Due Date.
Please note that the Duration in Smartsheet must be greater than 0 or a milestone will also be imported. By default, the duration will be set to 1 day when entering a start date in an empty row, such that no milestone will be imported.
If you specify a financial event in the row of an allocation or a milestone, both the financial event and the allocation or milestone will be imported. Financial events imported together with allocations or milestones won't be connected to them in Meisterplan.
Import Rules
Define under Import Rules how existing data in Meisterplan will be dealt with when importing from Smartsheet.
Check the box next to Sort projects by priority of the imported projects if you would like to keep the prioritization from Smartsheet. If you do not check the box, the priority in Meisterplan will be kept and new projects will be added above the Cut-Off Line.
Next, define for each data type (called Entity above) which data in Meisterplan will be updated and which will be deleted, depending on whether Smartsheet is the leading system for all or only some types of data.
Update:
- All: All data of this type will be updated, regardless of whether the last change to this data was made in Smartsheet or directly in Meisterplan.
- Own: Only data that was last changed by Smartsheet will be updated on import.
- None: No data will be updated, regardless of whether the last change to this data was made in Smartsheet or directly in Meisterplan.
Delete:
- All: All data of this type will be deleted, regardless of whether the last change to this data was made in Smartsheet or directly in Meisterplan.
- Own: Only data that was last changed by Smartsheet will be deleted on import.
- None: No data will be deleted, regardless of whether the last change to this data was made in Smartsheet or directly in Meisterplan.
The default settings for all of these is Own (referring to all projects that were last changed by Smartsheet and therefore have Smartsheet as data source). This way you do now delete or overwrite data by accident that was last updated directly in meisterplan.
If Smartsheet is always the leading data source, we recommend to set all fields below Update to All. This ensures that your Meisterplan data always has the same status as Smartsheet.
Relation Rules
Under Relation Rules you can define your own field relations between project and resource IDs instead of using the default matching of IDs. This might be necessary, for example, if the ID in Smartsheet is not the same as in Meisterplan, because you have configured more than one data source.
Activate this option by checking the box next to Deviating matching mode. Then you can define which field in Smartsheet corresponds to which field in Meisterplan.
- For example, if you have configured a custom project field in Meisterplan for the project number, you can match this field with the Smartsheet project ID field.
- For example, if you have added all resources in Meisterplan with their email addresses, you can match your resources in Smartsheet with the field E-Mail.
Use Comparing with to define whether the content of the matched fields has to be identical or if the field in Meisterplan only has to contain the content of the Smartsheet field
- equals: The Smartsheet and the Meisterplan field are only matched if their content is identical.
- contains: For example, if the project ID field in Smartsheet has "123-456" as content, and the project number field in Meisterplan is "ID=123-456", they will only be matched if selected contains.