This article describes how you integrate Smartsheet and Meisterplan.
- Overview
- Video Tutorial
- Setting up the Integration in Smartsheet
- Structuring Your Project and Resource Data
- Project Sheets
- Importing Milestones
- Importing Allocations
- Importing Financial Events
- Importing Custom Project Fields
- Using a Resource Sheet (Optional)
- Creating a Project Summary Report
- Creating an API Access Token
- Setting up the Integration in Meisterplan
- Using the Integration of Smartsheet and Meisterplan
- (Re-)Importing Smartsheet Data to Meisterplan
- Exporting the New Portfolio to Smartsheet Using the Allocation Report
- Opening a Meisterplan Project in Smartsheet
- Download our Templates
Overview
- Use your Smartsheet project and resource data as the basis for planning your portfolio in Meisterplan, including simulating what-if scenarios.
- After finalizing your planning, generate a Pivot & Charts: Allocations report and export it to Smartsheet to let project managers know what has changed.
Video Tutorial
Watch our video tutorial for step-by-step instructions detailing how to set up and use the integration.
Setting up the Integration in Smartsheet
For setting up the integration in Smartsheet, you must first structure your project and resource data to make it work in Meisterplan.
Then you can generate a project summary report and an API access token, both of which are required for importing your data to Meisterplan.
Structuring Your Project and Resource Data
For ensuring smooth data transfer later on, it's important that your Smartsheet data is structured as shown in this section.
Depending on your needs, you can work with either just project sheets, or with project sheets and a separate resource sheet. Use a resource sheet to include additional resource fields, resources without task allocations as well as roles and role allocations.
Here's what you can import to Meisterplan, depending on the sheets you use:
| Smartsheet Sheets | Data available for import to Meisterplan |
|---|---|
| Project Sheets |
|
| Resource Sheet (optional) |
|
Next, we will show you how to set up the two types of sheets for the import.
Project Sheets
Import our template project sheet to Smartsheet and just copy and paste your project data. Download here: Mobile Sales (Meisterplan Project Template)
After importing our Excel file in Smartsheet, open it, right-click a column header, select Edit Project Settings and set Dependencies Enabled to have durations calculated. Then, set the durations of milestones in the imported project sheet to 0.
Smartsheet project sheets typically contain project tasks, milestones and freely configurable columns.
For importing to Meisterplan, your Smartsheet project sheets must contain at least the following columns:
- Task Name
- Duration (calculated automatically from start and finish dates)
- Start
- Finish
The project name, tasks, and milestones should all be included in the Task Name column.
Please note that if you also wish to import financial events (see below), associated Task Name column values will be imported as descriptions.
A Smartsheet project sheet properly formatted for exporting data to Meisterplan
For an import to Meisterplan to work correctly, make sure that the entries in the Task Name column are formatted as follows:
- The first row, called “project row”, must contain the project name.
- All following rows of the column must be indented:
Indenting cells in the Task Name column in Smartsheet
The order in which you enter allocations and milestones here doesn’t matter. Meisterplan will display them in the Gantt chart sorted by date.
A note on Smartsheet programs: It is not possible to connect Meisterplan programs to Smartsheet data, or import Smartsheet data and connect it with a Meisterplan program.
When importing projects using the project rank from Smartsheet, it will always import the projects according to their rank in Smartsheet. An existing program will be moved just above the cut-off line with no project linked to it.
When importing projects not using the project rank from Smartsheet, the program will remain with the same rank but no project linked to it.
Importing Milestones
To import a milestone:
- Enter the milestone name in the Task Name column.
- Enter the milestone date in the Start column.
- Enter 0 in the Duration column.
Importing Allocations
In addition to the basic project and milestone data, you can assign resources to a project using the following columns:
- Assigned To: assign resources to your tasks.
- Allocation %: enter resource allocation for this task, given in percent of her overall capacity.
Editing resource allocations in a Smartsheet project sheet
By default, all resources assigned to an imported Smartsheet project, either as a project manager or via a task, will be imported as Meisterplan resources. For resources assigned to tasks, the associated allocations will also be imported. Please note that this requires mapping of the fields used to assign the project manager to a project or a resource to a task in Smartsheet.
For more information on how resources are imported with or without a resource sheet, see the Importing Resources, Roles and Allocations from Smartsheet article.
Importing Financial Events
You may also import financial events specified via the following columns:
- 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.
Importing Custom Project Fields
Generally, you can add as many details to a project as needed. Just add a column for each custom field you'd like to use, e.g., Stage Gate.
To import Smartsheet columns to Meisterplan, you need corresponding target fields there.
- See the Manage Project Fields article for details about creating custom fields in Meisterplan.
- See the Smartsheet Data Source Settings article for details about mapping Smartsheet columns to Meisterplan fields.
Additional custom fields in a Smartsheet project sheet
Using a Resource Sheet (Optional)
Import our template resource sheet to Smartsheet and just copy and paste your project data. Download here: Meisterplan Resources Template
If you wish to use a resource sheet for importing additional resource fields, resources without task allocations as well as roles and role allocations, see the Importing Resources, Roles and Allocations from Smartsheet article for in-depth information.
Creating a Project Summary Report
A Smartsheet project summary report will combine your various project sheets to prepare your data for exporting them to Meisterplan.
- To create a new report, right-click the white space of your Smartsheet workspace and select Report.
- Name your report and click OK to confirm.
- Click the newly created report in your workspace to open it.
- The Report Builder will now open. If you wish to open it later, click the report icon in the toolbar. Here, you can configure your new report.
- To begin with, the Where? section lets you choose from all workspaces and sheets you have available in your Smartsheet account. All projects you select here will be imported to Meisterplan.
The Report Builder in Smartsheet
- Next, click the Columns button to open the column selection. This offers all available columns of the selected project sheets. Select the columns you wish to export to Meisterplan and close the window when you're done.
The Columns button as part of the Report Builder in Smartsheet
- Select at least the following columns. The Task Name column is also required, but you don't have to select it. It will be selected automatically as Primary.
- Sheet Name (important: the report must be sorted by this column for the import to work.)
- Start
- Finish
- Click the Run button to finish.
- Close the Report Builder.
Creating an API Access Token
To import your Smartsheet data to Meisterplan, you first need to create an API access token in Smartsheet. We've detailed the necessary steps in the Creating an API Access Token for Smartsheet article.
Setting up the Integration in Meisterplan
For setting up the integration in Meisterplan, you need to create a Smartsheet data source to import your project data from Smartsheet and a Pivot & Charts: Allocations report to export your finalized portfolio plan back to Smartsheet.
Setting up a Smartsheet Data Source
Data will be imported from Smartsheet to Meisterplan via a Smartsheet data source, which has to be added and configured in Meisterplan. We've detailed the necessary steps for setting up the data source in the Smartsheet Data Source Settings article.
Once you are done configuring your Smartsheet data source, click OK in the Add "Smartsheet" Data Source window.
This will save your settings and open a new dialog asking you to refresh the plan of record by importing data from your newly configured data source:
- Click Yes to start the import.
- Click No to skip this step at this point and not import Smartsheet data to Meisterplan yet.
The settings you entered for the new data source will be saved in any case.
From now on, you can import your Smartsheet data by clicking the Refresh button for this data source under Manage > Data Sources.
Setting up an Allocation Report
In the Reports view, add a new report of type Pivot & Charts: Projects, Allocations and Capacities:
- In the left hand sidebar, open the Reports view.
- Click Add Report and select Pivot & Charts: Projects, Allocations and Capacities. Enter a name, then under Report Rights enter all users who should have access to the report.
- Double-click the new report to open it. The report will not initially display any data.
- Select the desired Allocation Unit: FTE, Days or Hours.
- Select the desired Time Unit: Years-Quarters-Months, Months or Weeks.
- Click the Field Chooser button to select the desired data and grouping fields in the overlay window:
- Under Capacity & Allocation, select Allocation and Date
- Under Project, select Project by Name, or
- Under Resource, select Resource Name.
If you select both, you may determine the grouping order (by project or resource first). - Drag the fields to the area where they should be located (Row Fields or Column Fields)
- Click the X on the top right to close the Field Chooser.
- If you are using two row grouping fields, either click the arrow in front of the column name or right-click a row header and select Expand All from the associated context menu to expand the rows. This is required in order to export row group details, which will not be exported if they are not expanded.
- If you have selected Years-Quarters-Months as the slice period, expand the columns the same way.
- Click the Save Configuration toolbar button to save your settings.
Using the Integration of Smartsheet and Meisterplan
When everything is set up, you can use the integration between Smartsheet and Meisterplan to import project data from Smartsheet to Meisterplan at any time. You can also display how your new plan affects your projects in Smartsheet, and open imported projects in Smartsheet from Meisterplan.
(Re-)Importing Smartsheet Data to Meisterplan
As described above, you will be asked whether you wish to import data from your newly created or updated data source when clicking OK in the data source settings.
Additionally, you may also start the import later by clicking the Refresh button for that data source under Manage > Data Sources.
After importing the Smartsheet data, switch back to the Portfolio Designer view via the left hand side sidebar display your updated data.
Exporting the New Portfolio to Smartsheet Using the Allocation Report
Via the Pivot & Charts: Allocations report you created in Meisterplan, you can export your allocation data to Microsoft Excel, then import them from the Excel export file into Smartsheet:
- In the left hand side sidebar, open the Reports view.
- Open the Pivot & Charts: Allocations report you created previously.
- In the report, click the Export toolbar button, then click OK to export the Pivot as Excel.
- In Smartsheet, import the Excel file you have just exported. Name unnamed columns, if desired, then click Import to start the import.
Your allocation data will now be available in Smartsheet.
If you want other Smartsheet users to access the report, you need to create Meisterplan accounts for them and allow them to access the report by setting report rights accordingly.
Opening a Meisterplan Project in Smartsheet
While using Meisterplan, you can always open projects that were imported from Smartsheet directly in Smartsheet by clicking thr three dots behind a project's name in the Portfolio Designer and selecting Open in app.smartsheet.com.
Download our Templates
Import our template project sheets to Smartsheet and just copy and paste your data. The sheets are then ready for importing them to Meisterplan: