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
- General / Credentials
- Field Mapping
- Project Field Mapping
- Allocation Field Mapping
- Financial Event Field Mapping
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.
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 project list 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.