This article describes how you integrate Smartsheet and Meisterplan.
- 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
- 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.
Watch our video tutorial for step-by-step instructions detailing how to set up and use the integration (full-screen playback available):
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|
|Resource Sheet (optional)||
Next, we will show you how to set up the two types of sheets for the import.
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)
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.
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.
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 Field Configuration 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.)
- 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:
Meisterplan asks you if you want to (re-)import Smartsheet data after setting up or changing a Smartsheet 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 in the Data Sources tab of the Meisterplan Settings.
Setting up an Allocation Report
Via the report manager, add a new report of the Pivot & Charts: Allocations type:
- Open the Reports menu and select Manage Reports.
Open the Reports menu and select Manage Reports.
- Click Add Report and select Pivot & Charts: Allocations.
Click Add Report and select Pivot & Charts: Allocations.
- Configure the report as desired:
- Enter the users who should be able to access the report under Report Rights.
- Select the desired Allocation Unit: FTE, Days or Hours.
- Select the desired Time Slice Period: Months or Weeks. Please note that months will be grouped by year and quarter, while weeks will be listed flat without grouping.
- Enter the number of past and future Time Periods to include in your report.
- Click OK to confirm your settings.
Configure the report as desired.
- Click the star icon to make the new report a favorite. This will add it to the Reports menu.
- Double-click the new report to open it.
- The report will initially not display any data. Click the Field Chooser button to select fields.
- In the Field Chooser overlay window, select the desired data and grouping fields:
- Move the Allocation field to the Data Fields area.
- Move the Allocation Date field to the Column Fields area.
- Move the Project Name and/or Resource Name fields to the Row Fields area. If you select both, you may determine the grouping order (by project or by resource first).
In the Field Chooser overlay window, select the desired data and grouping fields.
- Click the X at the top right to close the Field Chooser.
- If you are using two row grouping fields, 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 won't be exported if they aren't expanded.
Right-click a row header and select Expand All from the associated context menu to expand the rows.
- If you have selected Months as slice period, expand the columns the same way.
- Click the Save Configuration toolbar button to save your settings. If you don't, the report will be empty again when you next open it.
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 in the Data Sources tab of the Meisterplan Settings.
After importing the Smartsheet data, click the Home button in the upper left-hand corner to return to the main screen. Your updated data is now available in Meisterplan.
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:
- Open the Reports menu and select your report.
Open the Reports menu and select your report.
- 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.
Name unnamed columns, if desired, then click Import to start the import.
- Your allocation data will now be available in Smartsheet.
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 right-clicking a project in the project list and selecting Open in app.smartsheet.com.
The context menu entry for opening imported Meisterplan projects in Smartsheet
The links are also available in the Edit Project window, under Data Source Link:
The links are also available in the Edit Project window.
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: