Roll forward ESG data collection spreadsheets with Chains

From Chain Builder, you can use these chain templates, which together simplify the roll forward of Environmental, Social, and Governance (ESG) data:

1a. ESG roll forward | Primary | Start here
1b. ESG roll forward | Refresh connections & status
1c. ESG roll forward | Refresh connection | Execution

Tip: While you create three chains from these templates, you'll execute only the first chain created, which automatically runs all of them.

Note: These chains roll forward only data collection spreadsheets — not an ESG Program. To roll forward ESG Program to a new reporting year, contact your Workiva Customer Success Manager.

When you run chains created from these templates, they work together to automatically archive and import the previous year's ESG dataset into a table. When you run the chains, you can also select to:

Refresh the source Spreadsheet's connections before importing the dataset, such as to ensure the latest values
Clear values in the source Spreadsheet (except those linked to sources or destinations), such as to prepare for the next year's data collection

Note: These chains automatically replace any dataset from the same period in the connected Wdata table. If an error occurs when importing the new dataset, the table reverts to the previous dataset.

Prerequisites

Chains created from these templates require specific connectors to perform its commands.

Before you create chains from these templates, set up these connectors in Chain Builder:

Note: All connectors used in these templates' chains use the default CloudRunner. No GroundRunners are needed.

Note: To perform its commands, the HTTP Request connector require the client ID and secret of the OAuth grant created for the Workiva connector's integration user. Ensure the OAuth grant has the appropriate scope.

Step 1. Set up the Workiva files

Chains created from these templates require a specific setup of Workiva files for ESG data collection. Before you run these templates' chains, set up the Workiva files to collect and roll forward ESG data:

In Spreadsheets, identify the data collection sheet used for ESG reporting.
In Wdata, create a table that uses the Spreadsheet as a connected dataset, and record the table's ID for when you run the chains.
In the Spreadsheet, add a sheet to track the refresh status of the outgoing connection to the Wdata table, and record the sheet's name for when you run the chains.
Create a query to run when the chains archive data:
- Use the Wdata table connected to the data collection spreadsheet as a data source.
- In the query, include a multi-select parameter for Year, with the same data type as the table's Year column.
- Record the query's ID for when you run the chains.

Step 2. Determine values for variables and runtime inputs

To enable the chains to work with multiple workspaces, they use variables to identify the integration user and environments for the Workiva connector. In Chain Builder, create these workspace variables:

Variable	Value
wsv-ClientID	The client ID of the OAuth2 grant created for the Workiva integration user
wsv-ClientSecret	The client secret generated for the OAuth2 grant created for the integration user
wsv-BaseURL-Wdata	The URL host base and base path for the Wdata commands, based on the environment's region: US: `https://h.app.wdesk.com/s/cerebral` EU: `https://h.eu.wdesk.com/s/cerebral` APAC: `https://h.apac.wdesk.com/s/cerebral`
wsv-BaseURL-IAM	The URL host base and base path for the Identity and Access Management commands, based on the environment's region: US: `https://api.app.wdesk.com/iam/v1/oauth2/token` EU: `https://api.eu.wdesk.com/iam/v1/oauth2/token` APAC: `https://api.apac.wdesk.com/iam/v1/oauth2/token`
wsv-BaseURL-OC	The URL host base and base path for the Chains commands, based on the environment's region: US: `https://h.app.wdesk.com/s/wdata/oc/api/` EU: `https://h.eu.wdesk.com/s/wdata/oc/api/` APAC: `https://h.apac.wdesk.com/s/wdata/oc/api/`

When you run the chain created from the 1a. ESG roll forward | Primary | Start here template, it prompts for runtime inputs to identify the Workiva files to interact with and how to identify the dataset:

Runtime input	Value
Data collection—Roll forward table ID	The ID of the Wdata table — connected to the ESG data collection Spreadsheet — to import the ESG dataset into
Data collection—Roll forward query ID	The ID of the Wdata query to run to archive data
PY year	The year of the data to be archived
Tag value	The name for the archived dataset Note: The chains use the Tag value input with the PY year input to create the dataset's file name, as `[Tag value]_[PY year].csv`. For example, a PY year of 2021 and Tag value of Archived creates the filename `Archived_2021.csv`.
Status—SheetName	The name of the sheet to track the refresh status of the Spreadsheet's outgoing connection to the Wdata table

Step 3. Create the chains

To create the chains needed to roll forward ESG data collection, use the 1a. ESG roll forward | Primary | Start here template:

From Templates in Chain Builder, open the 1a. ESG roll forward | Primary | Start here template, and click New chain.
Enter a name to help identify the chain.
Select the environment to use the chain, and click Next.
For the dcv-clearSuccess variable, enter the name dvc-clearSuccess, and click Next.
For each connection, select the connector to use, and click Next chain.
Note: For each connector, use the default CloudRunner.

Set up each variable, and click Next.

Variable	Variable type	New variable
wsv-ClientID	Workspace	Select wsv-ClientID.
cv-TimeToCheck	Chain	`cv-TimeToCheck`
wsv-ClientSecret	Workspace	Select wsv-ClientSecret.
dcv-RefreshStatus	Chain dynamic	`dcv-RefreshStatus`
wsv-BaseURL-IAM	Workspace	Select wsv-BaseURL-IAM.
wsv-BaseURL-OC	Workspace	Select wsv-BaseURL-OC.

For each connection, select the connector to use, and click Next chain.
Note: For each connector, use the default CloudRunner.

Set up each variable, and click Next.

Variable	Variable type	New variable
dcv-srcStatus	Chain dynamic	`dcv-srcStatus`
wsv-ClientID	Workspace	Select wsv-ClientID.
dcv-destStatus	Chain dynamic	`dcv-destStatus`
wsv-ClientSecret	Workspace	Select wsv-ClientSecret.
wsv-BaseURL-Wdata	Workspace	Select wsv-BaseURL-Wdata.
wsv-BaseURL-IAM	Workspace	Select wsv-BaseURL-IAM.

For each connection, select the connector to use, and click Submit.
Note: For each connector, use the default CloudRunner.

Step 4. Publish the chains

Note: To ease installation, the Refresh connections & status template has a Run chain event. Before you publish the chains created from the templates, delete this extraneous Run chain event.

From Chains, for the chain created from the Refresh connections & status template, select Edit.
Select the Run chain - Delete event, and click Delete and Delete.
Click Publish and Publish.
From Chains, for the chain created from the Primary template, select Edit.
Click Publish and Publish.
From Chains, for the chain created from the Execution template, select Edit.
Click Publish and Publish.

Run the chains

To archive your ESG data and prepare for the next year's data collection:

In Chain Builder, from Chains, select Execute for the chain created from the 1a.ESG roll forward | Primary | Start here template.

Click Run with inputs, and enter the runtime inputs:

Runtime input	Value
Data collection—Roll forward table ID	Enter the ID of the Wdata table to import the dataset into.
Data collection—Roll forward query ID	Enter the ID of the query to run to archive data.
PY year	Enter the year of the data to be archived.
Tag value	Enter the name for the archived dataset. Note: With the PY year input, this input creates the dataset's file name as `[Tag value]_[PY year].csv`. For example, a PY year of 2021 and Tag value of Archived creates the filename `Archived_2021.csv`.
Refresh sheet connections before data archival	Select whether to refresh the Spreadsheet's outgoing connections to the Wdata table before the roll forward query runs, such as to update the dataset before archiving.
Status—SheetName	Enter the name of the sheet that tracks that refresh status of the outgoing connections from the Spreadsheet to the Wdata table.
RefreshExecution—ChainName	Enter the name of the chain created from the ESG Rollforward \| Refresh connection \| Execution template.
Clear Spreadsheet Value column	Select whether to delete input values from the Spreadsheet as part of the roll forward. Note: No values with a source or destination link will be deleted.

To ensure the latest values before importing the dataset, select Refresh connections before data archival.

If you select this option, the chains refresh the Spreadsheet's outgoing connections and track their refresh status in a control sheet:
- In Status—SheetName, enter the name of the sheet in the source Spreadsheet where the chains can save the refresh status of the outgoing connections.
- In RefreshExecution—ChainName, enter the name of the chain created from the ESG Rollforward | Refresh connection | Execution template.
Tip: The refresh may take up to a few minutes, depending on the number of the Spreadsheet's outgoing connections. To adjust the wait time, edit the Pause chain event of chain created from the 1b. ESG roll forward | Refresh connections & status template.
To delete input values from the source Spreadsheet as part of the archival, such as to prepare for the next year's data collection, select Clear spreadsheet value column.

Note: The chain retains any values linked to a source or destination. You can't recover any values deleted from the Spreadsheet. We recommend you select this option only for the final rollforward of the year, to prepare the Spreadsheet for the next year's data collection.

Tip: By default, the chain deletes values starting with row 2 of the Spreadsheet's sheets. To adjust this, edit the Region property of the Clear sheet data command.
Click Start.

Help

Community

Learning Hub

Support

Tip: While you create three chains from these templates, you'll execute only the first chain created, which automatically runs all of them.

Note: These chains roll forward only data collection spreadsheets — not an ESG Program. To roll forward ESG Program to a new reporting year, contact your Workiva Customer Success Manager.

Note: These chains automatically replace any dataset from the same period in the connected Wdata table. If an error occurs when importing the new dataset, the table reverts to the previous dataset.

Prerequisites

Note: All connectors used in these templates' chains use the default CloudRunner. No GroundRunners are needed.

Note: To perform its commands, the HTTP Request connector require the client ID and secret of the OAuth grant created for the Workiva connector's integration user. Ensure the OAuth grant has the appropriate scope.

Step 1. Set up the Workiva files

Step 2. Determine values for variables and runtime inputs

Note: The chains use the Tag value input with the PY year input to create the dataset's file name, as `[Tag value]_[PY year].csv`. For example, a PY year of 2021 and Tag value of Archived creates the filename `Archived_2021.csv`.

Step 3. Create the chains

Note: For each connector, use the default CloudRunner.

Note: For each connector, use the default CloudRunner.

Note: For each connector, use the default CloudRunner.

Step 4. Publish the chains

Note: To ease installation, the Refresh connections & status template has a Run chain event. Before you publish the chains created from the templates, delete this extraneous Run chain event.

Run the chains

Note: With the PY year input, this input creates the dataset's file name as `[Tag value]_[PY year].csv`. For example, a PY year of 2021 and Tag value of Archived creates the filename `Archived_2021.csv`.

Note: No values with a source or destination link will be deleted.

Tip: The refresh may take up to a few minutes, depending on the number of the Spreadsheet's outgoing connections. To adjust the wait time, edit the Pause chain event of chain created from the 1b. ESG roll forward | Refresh connections & status template.

Note: The chain retains any values linked to a source or destination. You can't recover any values deleted from the Spreadsheet. We recommend you select this option only for the final rollforward of the year, to prepare the Spreadsheet for the next year's data collection.

Tip: By default, the chain deletes values starting with row 2 of the Spreadsheet's sheets. To adjust this, edit the Region property of the Clear sheet data command.

Tip: While you create three chains from these templates, you'll execute only the first chain created, which automatically runs all of them.

Note: These chains roll forward only data collection spreadsheets — not an ESG Program. To roll forward ESG Program to a new reporting year, contact your Workiva Customer Success Manager.

Note: These chains automatically replace any dataset from the same period in the connected Wdata table. If an error occurs when importing the new dataset, the table reverts to the previous dataset.

Prerequisites

Note: All connectors used in these templates' chains use the default CloudRunner. No GroundRunners are needed.

Note: To perform its commands, the HTTP Request connector require the client ID and secret of the OAuth grant created for the Workiva connector's integration user. Ensure the OAuth grant has the appropriate scope.

Step 1. Set up the Workiva files

Step 2. Determine values for variables and runtime inputs

Note: The chains use the Tag value input with the PY year input to create the dataset's file name, as [Tag value]_[PY year].csv. For example, a PY year of 2021 and Tag value of Archived creates the filename Archived_2021.csv.

Step 3. Create the chains

Note: For each connector, use the default CloudRunner.

Note: For each connector, use the default CloudRunner.

Note: For each connector, use the default CloudRunner.

Step 4. Publish the chains

Note: To ease installation, the Refresh connections & status template has a Run chain event. Before you publish the chains created from the templates, delete this extraneous Run chain event.

Run the chains

Note: With the PY year input, this input creates the dataset's file name as [Tag value]_[PY year].csv. For example, a PY year of 2021 and Tag value of Archived creates the filename Archived_2021.csv.

Note: No values with a source or destination link will be deleted.

Tip: The refresh may take up to a few minutes, depending on the number of the Spreadsheet's outgoing connections. To adjust the wait time, edit the Pause chain event of chain created from the 1b. ESG roll forward | Refresh connections & status template.

Note: The chain retains any values linked to a source or destination. You can't recover any values deleted from the Spreadsheet. We recommend you select this option only for the final rollforward of the year, to prepare the Spreadsheet for the next year's data collection.

Tip: By default, the chain deletes values starting with row 2 of the Spreadsheet's sheets. To adjust this, edit the Region property of the Clear sheet data command.

Note: The chains use the Tag value input with the PY year input to create the dataset's file name, as `[Tag value]_[PY year].csv`. For example, a PY year of 2021 and Tag value of Archived creates the filename `Archived_2021.csv`.

Note: With the PY year input, this input creates the dataset's file name as `[Tag value]_[PY year].csv`. For example, a PY year of 2021 and Tag value of Archived creates the filename `Archived_2021.csv`.