Microsoft Sustainability Manager enables you to monitor and manage your organization's environmental impact.
With the Workiva Platform, you can build chains to automatically download emissions data from Microsoft Sustainability Manager into a table, based on a time period specified in a custom Microsoft Sustainability Manager spreadsheet. You can then use the table as the source for a query or spreadsheet connection, such as to include the data in Environmental, Social, and Governance (ESG) or sustainability reporting.
Note: While you build multiple chains in these instructions, you'll run only one chain — which then automatically runs the others — to download data from Microsoft and update the spreadsheet.
Note: The Microsoft Sustainability Manager spreadsheet used by these chains is provided by Workiva. If you don't have it but are interested, contact your Customer Success Manager for details.
Prerequisites
To build these chains, first set up these connectors in Chains:
- Workiva connector
- Microsoft Dynamics® CRM connector
- Tabular Transformation connector
- JSON connector
- Handlebars connector
- File Utilities connector
Tip: All of the chains' commands use the default CloudRunner. No GroundRunners are needed.
To enable integration with Microsoft Dataverse, register an app with a Microsoft Entra ID, and note the following for the Microsoft Dynamics CRM connector:
- The URL and tenant ID of the Microsoft Sustainability Manager instance to download from
- The client ID and secret of the Microsoft Entra ID
Lastly, note the IDs for the Microsoft Sustainability Manager spreadsheet, its sheets, and its connected table.
Build a chain to log data downloads
To start, build a chain to update the Microsoft Sustainability Manager spreadsheet to track when emissions data uploads to Workiva.
Step 1. Create the chain
- From Chains, click Create, and select Create chain.
- In Setup, enter a name of
3. MSM logand a description to help explain that the chain logs when data uploads from Microsoft Sustainability Manager. - Click Save.
Step 2. Start with a Runtime Inputs event
- From Chain events, move Runtime inputs to Start.
- Select the Runtime inputs event, and click Edit.
- In Basic info, enter a name and description to help identify the event.
- In Variables, add TextField inputs with these display names:
RangeSpreadsheet IDControl sheet IDChain run log IDRefresh startRefresh endRefresh year
Tip: Leave the variables' default values blank; they'll be generated when you run the 1. MSM refresh chain created later.
- Select Required for all variables, and click Save.
Step 3. Add commands to get time period from Control sheet
To identify the time period of the emissions data to download from Microsoft:
- From Available connectors, select File utilities, and move Create file to the canvas.
- Drag a link from Runtime inputs to Create file.
- Select the Create file command, and click Edit.
- In Basic info, enter a name and description to identify the command.
- Enter the command's properties, and click Save:
Property Value Connector Select the File utilities connector to use. Text Enter the string to update in Control sheet: - Enter
,,,Emissions data last updated on. - Select the System.DateTime variable from Runtime.
- Enter
- From Available connectors, select Workiva, and move Write sheet data to the canvas.
- Drag a link from Create file to Write sheet data.
- Select the Write sheet data command, and click Edit.
- In Basic info, enter a name and description to help identify the command.
- Enter the command's properties, and click Save:
Property Value Connector Select the Workiva connector to use. Spreadsheet ID Select the Spreadsheet ID runtime input from Trigger. Sheet ID/name Select the Control sheet ID runtime input from Trigger. Data file Select the Created file output from Create file. Delimiter Enter a comma ( ,).Range Select the Range runtime input from Trigger. Use Platform API Select this checkbox.
Step 4. Add commands to update the Chain run log
To update the Chain run log sheet when data downloads from Microsoft:
- From Available connectors, select Workiva, and move Get sheet data to the canvas.
- Drag a link from Runtime inputs to Get sheet data.
- Select the Get sheet data command, and click Edit.
- In Basic info, enter a name and description to help identify the command.
- Enter the command's properties, and click Save:
Property Value Connector Select the same Workiva connector as earlier. Spreadsheet ID Select the Spreadsheet ID runtime input from Trigger. Sheet ID/name Select the Chain run log ID runtime input from Trigger. Region Enter A1:.Value style Select Calculated. Revision Enter -1. - From Available connectors, select File utilities, and move Create file to the canvas.
- Drag a link from Get sheet data to Create file.
- Select the Create file command, and click Edit.
- In Basic info, enter a name and description to help identify that the command creates the log entry.
- Enter the command's properties, and click Save:
Property Value Connector Select the same File utilities connector as earlier. Text Enter the string to update the columns of the Chain run log sheet: - Enter the column headers:
Date,Details, and press Enter. - Select the System.DateTime variable from Runtime.
- Click the System.DateTime variable, and add a Parse Date/Time transformation:
- Select ISO Extended (Platform Standard).
- In Output date format, enter
%Y-%m-%d. - Select the input and output date timezones.
- Enter
,Emission data for. - Select the Refresh year runtime input from Trigger.
- Enter
-. - Select the Refresh start runtime input from Trigger.
- Enter
to. - Select the Refresh year runtime input from Trigger.
- Enter
-. - Select the Refresh end runtime input from Trigger.
- Enter
was successfully refreshed.
- Enter the column headers:
- From Available connectors, select Tabular transformation, and move Advanced query to the canvas.
- Drag a link from Create file to Advanced query.
- Select the Advanced query command, and click Edit.
- In Basic info, enter a name and description to identify the command.
- Enter the command's properties, and click Save:
Property Value Connector Select the Tabular transformation connector to use. Tables Add two tables to use in the query: - For the first table:
- In File, select the Data output from Get sheet data.
- In Table name, enter
a.
- For the second table:
- In File, select the Created file output from the preceding Create file.
- In Table name, enter
b.
Query Enter the query to run: select * from a union all select * from b
Input delimiter Select Comma. Output delimiter Select Comma. Preview results Select this checkbox. - For the first table:
- From Available connectors, select Workiva, and move Overwrite sheet data to the canvas.
- Drag a link from Advanced query to Overwrite sheet data.
- Select the Overwrite sheet data command, and click Edit.
- In Basic info, enter a name and description to identify the command.
- Enter the command's properties, and click Save:
Property Value Connector Select the same Workiva connector as earlier. Spreadsheet ID Select the Spreadsheet ID runtime input from Trigger. Sheet ID/name Select the Chain run log ID runtime input from Trigger. Data file Select the Result output from Advanced query. Delimiter Enter a comma ( ,).Start cell Enter A1.Async Select this checkbox. - Click Publish, enter a note about its publication, and click Publish.
Build chains from the Load Data to Wdata template
Next, use the Load Data to Wdata template to build chains to update the Microsoft Sustainability Manager spreadsheet's connected table with the data downloaded from Microsoft. The Load Data to Wdata template creates chains that work together to:
- Determine whether the dataset already exists in the table
- Update the table with the latest dataset
To create the chains from Templates, open the Load Data to Wdata | Primary chain template, and click New chain.
Build a chain to download data from Microsoft
Finally, build a chain to download and import emissions data from Microsoft Sustainability Manager.
Step 1. Create the chain
- From Chains, click Create, and select Create chain.
- In Setup, enter a name of
1. MSM refreshand a description to help explain that the chain downloads data from Microsoft Sustainability Manager and uploads it to the table. - Add the variables:
Name Value Spreadsheet ID Enter the ID for the Microsoft Sustainability Manager spreadsheet. Control sheet ID Enter the ID for the Control sheet section of the Microsoft Sustainability Manager spreadsheet. Chain run log ID Enter the ID for the Chain run log section of the Microsoft Sustainability Manager spreadsheet. Table ID Enter the ID for the table connected to the Microsoft Sustainability Manager spreadsheet. - Add a dynamic variable with a Name of
nextLink, and click Save.
Step 2. Start with commands to identify the time period of data to download
To determine the time period of the emissions data to download, based on the Microsoft Sustainability Manager spreadsheet:
- From Available connectors, select Workiva, and move Get sheet data to Start.
- Select the Get sheet data command, and click Edit.
- In Basic info, enter a name and description to help identify the command.
- Enter the command's properties, and click Save:
Property Value Connector Select the same Workiva connector as earlier. Spreadsheet ID Select the Spreadsheet ID variable from Chain. Sheet ID/name Select the Control sheet ID variable from Chain. Region Enter A4:.Value style Select Calculated. Revision Enter -1. - From Available connectors, select Tabular transformation, and move Advanced query to the canvas.
- Drag a link from Start to Advanced query.
- Select the Advanced query command, and click Edit.
- In Basic info, enter a name and description to identify the command.
- Enter the command's properties, and click Save:
Property Value Connector Select the same Tabular transformation connector as earlier. Tables Add the table to use in the query: - In File, select the Data output from Get sheet data.
- In Table name, enter
a.
Query Enter the query to run: select case when `Transaction Start Month` = 'January' then '01-01' when `Transaction Start Month` = 'February' then '02-01' when `Transaction Start Month` = 'March' then '03-01' when `Transaction Start Month` = 'April' then '04-01' when `Transaction Start Month` = 'May' then '05-01' when `Transaction Start Month` = 'June' then '06-01' when `Transaction Start Month` = 'July' then '07-01' when `Transaction Start Month` = 'August' then '08-01' when `Transaction Start Month` = 'September' then '09-01' when `Transaction Start Month` = 'October' then '10-01' when `Transaction Start Month` = 'November' then '11-01' when `Transaction Start Month` = 'December' then '12-01' end as "stmth", case when `Transaction End Month` = 'January' then '01-31' when `Transaction End Month` = 'February' then '02-28' when `Transaction End Month` = 'March' then '03-31' when `Transaction End Month` = 'April' then '04-30' when `Transaction End Month` = 'May' then '05-31' when `Transaction End Month` = 'June' then '06-30' when `Transaction End Month` = 'July' then '07-31' when `Transaction End Month` = 'August' then '08-31' when `Transaction End Month` = 'September' then '09-30' when `Transaction End Month` = 'October' then '10-31' when `Transaction End Month` = 'November' then '11-30' when `Transaction End Month` = 'December' then '12-31' end as "enmt", `Transaction Year` as "yr" from a
Input delimiter Select Comma. Output delimiter Select Comma. Preview results Select this checkbox. - From Available connectors, select Tabular transformation, and move Extract value to the canvas.
- Drag a link from Advanced query to Extract value.
- Select the Extract value command, and click Edit.
- In Basic info, enter a name and description to identify that the command determines the start date of the data to download.
- Enter the command's properties, and click Save:
Property Value Connector Select the same Tabular transformation connector as earlier. Input file Select the Result output from Advanced query. Column index Enter 1.Delimiter Select Comma. Row index Enter 2. - From Available connectors, select Tabular transformation, and move another Extract value to the canvas.
- Drag another link from Advanced query to the second Extract value.
- Select the Extract value command, and click Edit.
- In Basic info, enter a name and description to identify that the command determines the end date of the data to download.
- Enter the command's properties, and click Save:
Property Value Connector Select the same Tabular transformation connector as earlier. Input file Select the Result output from Advanced query. Column index Enter 2.Delimiter Select Comma. Row index Enter 2. - From Available connectors, select Tabular transformation, and move a third Extract value to the canvas.
- Drag another link from Advanced query to the third Extract value.
- Select the Extract value command, and click Edit.
- In Basic info, enter a name and description to identify that the command determines the year of the data to download.
- Enter the command's properties, and click Save:
Property Value Connector Select the same Tabular transformation connector as earlier. Input file Select the Result output from Advanced query. Column index Enter 3.Delimiter Select Comma. Row index Enter 2.
Step 3. Add commands to download emissions data from Microsoft
To download the data from Microsoft:
- From Available connectors, select Microsoft Dynamics CRM, and move Retrieve to the canvas.
- Drag a link from all three Extract value commands to Retrieve.
- Select the Retrieve command, and click Edit.
- In Basic info, enter a name and description to identify that the command retrieves emissions data from Microsoft Dataverse.
- Enter the command's properties, and click Save:
Property Value Connector Select the Microsoft Dynamics CRM connector to use. Path Enter msdyn_emissions.Query params Build the OData query parameters to apply: - Enter
$select=msdyn_name,msdyn_activityname,msdyn_scope,msdyn_calculationdate,msdyn_consumptionstartdate,msdyn_consumptionenddate,msdyn_transactiondate,msdyn_co2e,msdyn_co2emt,msdyn_isbiogenic,msdyn_ismarketbased&$expand=msdyn_co2eunit($select=msdyn_name),msdyn_countryregioncode($select=msdyn_name),msdyn_datadefinition($select=msdyn_name),msdyn_emissionfactor($select=msdyn_name),msdyn_facilityid($select=msdyn_name),msdyn_organizationalunitid($select=msdyn_name),msdyn_emissionsourcev2($select=msdyn_name)&$filter= msdyn_transactiondate ge '
- Select the Value output of the Extract value command for the year.
- Enter a hyphen (
-). - Select the Value output of the Extract value command for the start date.
- Enter
T00:00:00Z' and msdyn_transactiondate le '. - Select the Value output of the Extract value command for the year.
- Enter a hyphen (
-). - Select the Value output of the Extract value command for the end date.
- Enter
T00:00:00Z'.
Custom headers Add a custom header: - In Key, enter
Prefer. - In Value, enter
odata.maxpagesize=100,odata.include-annotations="OData.Community.Display.V1.FormattedValue".
Max pages Enter 1.Output schema Enter this example response: {"@odata.context": "string","value": [{}],"@odata.nextLink": "string"} - Enter
- From Available connectors, select JSON, and move Array to CSV to the canvas.
- Drag a link from Retrieve to Array to CSV.
- Select the Array to CSV command, and click Edit.
- In Basic info, enter a name and description to identify the command.
- Enter the command's properties:
Property Value Connector Select the JSON connector to use. JSON data Select Value from the Retrieved data output of the Retrieve command. Multi-value delimiter Enter a comma ( .).Preview result Select this checkbox. Delimiter Select Comma. - Add the columns and JSONPaths to create, and click Save:
Column name JSONPath name .msdyn_name activity_type .msdyn_activityname calculation_date .msdyn_calculationdate consumption_start_date .msdyn_consumptionstartdate consumption_end_date .msdyn_consumptionenddate transaction_date .msdyn_transactiondate emission_factor .msdyn_emissionfactor facility_name .msdyn_facilityid.msydn_name organizational_unit .msdyn_organizationalunitid.msdyn_name country_region .msdyn_countryregioncode.msdyn_name emission_source .msdyn_datadefinition.msdyn_name co2e .msdyn_co2e co2e_unit .msdyn_con2eunit.msdyn_name is_biogenic .msdyn_isbiogenic is_marketbased .msdyn_ismarketbased source .msdyn_emissionsourcev2.msdyn_name scope ."msdyn_scope@OData.Community.Display.V1.FormattedValue"
Step 4. Add commands to import all datasets into table
To ensure the chain imports all data, add logic to either upload or compile data based on any additional pagination downloaded from Microsoft:
- From Chain events, move Conditional to the canvas.
- Drag a link from Array to CSV to Conditional.
- Select the Conditional event, and click Edit.
- In Conditions, add a rule, then click Save:
Data type Data Operation String Select the nextLink variable from Chain, then click the variable and add a Trim transformation. Is not blank - From Chain events, move Run chain to the canvas.
- Drag a link from Conditional to Run chain.
- To ensure the chain runs only when the dowloaded data has no pagination, double-click the link, and select Error in Edit link condition.
- Select the Run chain event, and click Edit.
- In Basic info, enter a name and description to identify that the event runs chains created from the Load Data to Wdata template.
- In Chain to run, select the primary chain created from the Load Data to Wdata template.
- Enter the chain runtime inputs, and click Save:
Runtime input Value Table ID Select the Table ID variable from Chain. File name Build the filename of the dataset to import to the table: - Enter
MSM_EmissionsData_. - Select the Value output of the Extract value command for the year.
- Enter a hyphen (
-). - Select the Value output of the Extract value command for the start date.
- Enter an underscore (
_). - Select the Value output of the Extract value command for the year.
- Enter a hyphen (
-). - Select the Value output of the Extract value command for the end date.
- Enter
.csv.
Data file Select the Converted file output from Array to CSV. Load method Select Replace dataset. - Enter
Step 5. Add command group to iterate through paginated results
To compile emission data downloaded as paginated results:
- From Chain events, move Set dynamic chain variable to the canvas.
- Drag a link from Conditional to Set dynamic chain variable.
- Select the Set dynamic chain variable event, and click Edit.
- In Basic info, enter a name and description to help identify the event.
- In Action, set up the dynamic chain variable, and click Save:
Property Value Dynamic variable Select nextLink. Value Select @Odata.NextLink from the Retrieved data output of the Retrieve command. - Move Command group to the canvas, and drag a link between it and Set dynamic chain variable.
- Select the group, and click Edit.
- From Iterators, enable iterations.
- In Select modifier type, select Repeat until.
- Add the iteration rule, and click Save:
Data type Data Operation String Select the nextLink variable from Chain. Is blank - From Available connectors, select Microsoft Dynamics CRM, and move Retrieve to the canvas.
- Drag a link from Group start to Retrieve.
- Select the Retrieve command, and click Edit.
- In Basic info, enter a name and description to identify that the command retrieves paginated results from Microsoft Dataverse.
- Enter the command's properties, and click Save:
Property Value Connector Select the Microsoft Dynamics CRM connector to use. Path Enter msdyn_emissions.Query params Select the nextLink variable from Chain. Custom headers Add a custom header: - In Key, enter
Prefer. - In Value, enter
odata.maxpagesize=100,odata.include-annotations="OData.Community.Display.V1.FormattedValue".
Max pages Enter 1.Output schema Enter this example response: {"@odata.context": "string","value": [{}],"@odata.nextLink": "string"} - In Key, enter
- From Available connectors, select JSON, and move Array to CSV to the canvas.
- Drag a link from Retrieve to Array to CSV.
- Select the Array to CSV command, and click Edit.
- In Basic info, enter a name and description to identify the command.
- Enter the command's properties:
Property Value Connector Select the same JSON connector as earlier. JSON data Select Value from the Retrieved data output of the preceding Retrieve command. Multi-value delimiter Enter a comma ( .).Preview result Select this checkbox. Delimiter Select Comma. - Add the columns and JSONPaths to create, and click Save:
Column name JSONPath name .msdyn_name activity_type .msdyn_activityname calculation_date .msdyn_calculationdate consumption_start_date .msdyn_consumptionstartdate consumption_end_date .msdyn_consumptionenddate transaction_date .msdyn_transactiondate emission_factor .msdyn_emissionfactor facility_name .msdyn_facilityid.msydn_name organizational_unit .msdyn_organizationalunitid.msdyn_name country_region .msdyn_countryregioncode.msdyn_name emission_source .msdyn_datadefinition.msdyn_name co2e .msdyn_co2e co2e_unit .msdyn_con2eunit.msdyn_name is_biogenic .msdyn_isbiogenic is_marketbased .msdyn_ismarketbased source .msdyn_emissionsourcev2.msdyn_name scope ."msdyn_scope@OData.Community.Display.V1.FormattedValue" - From Available connectors, select Tabular transformation, and move Stack files to the canvas.
- Drag a link from Array to CSV to Stack files.
- Select the Stack files command, and click Edit.
- In Basic info, enter a name and description to identify the command.
- Enter the command's properties, and click Save:
Property Value Connector Select the same Tabular transformation connector as earlier. Delimiter Select Comma. Input files Build the comma-separated list of the files to stack: - Select Previous stack files output from the Stack files command.
- Enter a comma (
,). - Select the Converted file output of the preceding Array to CSV command.
Preview result Select this checkbox. - From Chain events, move Conditional to the canvas.
- Drag a link from Array to CSV to Conditional.
- Select the Conditional event, and click Edit.
- In Conditions, add a rule, then click Save:
Data type Data Operation String Select @Odata.NextLink from the Retrieved data output of the Retrieve command. Is not blank - From Chain events, move Set dynamic chain variable to the canvas.
- Drag a link from Conditional to Set dynamic chain variable.
- Select the Set dynamic chain variable event, and click Edit.
- In Basic info, enter a name and description to help identify the event.
- In Action, set up the dynamic chain variable, and click Save:
Property Value Dynamic variable Select nextLink. Value Select @Odata.NextLink from the Retrieved data output of the Retrieve command. - From Chain events, move Exit group to the canvas.
- Drag a link from Conditional to Exit group.
- To exit the group only when there are no more paginated results, double-click the link, select Error in Edit link condition, and click Close.
- Select the Exit group event, click Edit, then click Save.
Step 6. Add commands to import compiled data into table
To upload the paginated results into the table after the group completes:
- From Available connectors, select Tabular transformation, and move Stack files to the canvas.
- Drag a link from the command group's Out to Stack files.
- Select the Stack files command, and click Edit.
- In Basic info, enter a name and description to identify the command.
- Enter the command's properties, and click Save:
Property Value Connector Select the same Tabular transformation connector as earlier. Delimiter Select Comma. Input files Build the comma-separated list of the files to stack: - Select the Converted file output of the group's Array to CSV command.
- Enter a comma (
,). - Select Stacked files output from the group's Stack files command.
Preview result Select this checkbox. - From Chain events, move Run chain to the canvas.
- Drag a link from Stack files to Run chain.
- Select the Run chain event, and click Edit.
- In Basic info, enter a name and description to identify that the event runs chains created from the Load Data to Wdata template.
- In Chain to run, select the primary chain created from the Load Data to Wdata template.
- Enter the chain runtime inputs, and click Save:
Runtime input Value Table ID Select the Table ID variable from Chain. File name Build the filename of the dataset to import to the table: - Enter
MSM_EmissionsData_. - Select the Value output of the Extract value command for the year.
- Enter
-. - Select the Value output of the Extract value command for the start date.
- Enter
_. - Select the Value output of the Extract value command for the year.
- Enter
-. - Select the Value output of the Extract value command for the end date.
- Enter
.csv.
Data file Select Stacked files output from the preceding Stack files command. Load method Select Replace dataset. - Enter
- From Chain events, move another Run chain to the canvas.
- Drag links from the two previous Run chain events to this Run chain event.
- Select the new Run chain event, and click Edit.
- In Basic info, enter a name and description to identify that the event runs chains created from the Load Data to Wdata template.
- In Chain to run, select the 3. MSM log chain created earlier.
- Enter the chain runtime inputs, and click Save:
Runtime input Value Range Enter B5:E5.Spreadsheet ID Select the Spreadsheet ID variable from Chain. Control sheet ID Select the Control sheet ID variable from Chain. Chain run log ID Select the Chain run log ID variable from Chain. Refresh start Select the Value output of the Extract value command for the start date. Refresh end Select the Value output of the Extract value command for the end date. Refresh year Select the Value output of the Extract value command for the year. - Click Publish, enter a note about the chain's publication, and click Publish.
Run the chains
To refresh the Microsoft Sustainability Manager spreadsheet and table with the latest emissions data:
- From the Control sheet of the Microsoft Sustainability Manager spreadsheet, select the time period of the data to download.
- From Chains, select the 1. MSM refresh chain, and click Execute and Run chain.
Tip: When you run this chain, it automatically runs the other chains to update the table and Microsoft Sustainability Manager spreadsheet with the latest data.