Download emissions data for the Persefoni® Template Spreadsheet

With the Persefoni Template Spreadsheet, you can collect emissions data from Persefoni®, such as to disclose in Environmental, Social, and Governance (ESG) reporting. From Wdata Chain Builder, you can build multiple chains that work together to automatically:

• Start to download carbon accounting data from Persefoni
• Update values in the Persefoni Template Spreadsheet
• Log when data downloads from Persefoni

Note: While you build three chains in these instructions, you'll run only one chain — which then automatically runs the others — to download data from Persefoni and update the Persefoni Template Spreadsheet.

You can download different types of data from Persefoni:

• Emissions, for the greenhouse gas (GHG) emissions associated with transactions
• Activities, for all investment and lending activity used to calculate your carbon footprint
• Facilities, for your company's locations associated with transactions

Note: The Persefoni Template Spreadsheet is provided by Workiva. If you don't have this Spreadsheet but are interested, contact your Customer Success Manager for details.

Prerequisites

To build these chains, you'll need these connectors:

• Persefoni connector
• Workiva connector
• JSON connector
• Tabular Transformation connector

Note: All of the chains' commands use the default CloudRunner. No GroundRunners are needed.

Before you run the chains, identify:

• The IDs for the Persefoni resource group and language of the data to download
• The IDs of the Persefoni Template Spreadsheet and its Control Sheet section

Build a chain to log data downloads

To start, build the chain to track when carbon accounting data was last refreshed in the Control Sheet section of the Persefoni Template Spreadsheet.

Step 1. Create the chain

1. In Chain Builder, from Chains, click Create and select Create chain.
   
2. Under Setup, enter a name and description to help identify the chain.
   
3. Under Variables, add variables for the IDs of the Persefoni Template Spreadsheet and its Control Sheet section.
4. Click Save.
   
   

Step 2. Start with a Runtime Inputs event

To indicate which cell of the Control Sheet section to update when the chain runs, start with a Runtime inputs event:

1. Move Runtime inputs from under Trigger events to Start.
2. Select the Runtime inputs event, and click Edit.
3. Click Add input, and select TextField.
   
4. In Display name, enter Range.
5. Select Required, and click Save.
   
   

Step 3. Add command to create log file

To create the log file of when the chains download data from Persefoni, add a Create file command:

1. Under BizApps, select File Utilities, and drag Create file to the canvas.
2. Drag a link from Start to the Create file command.
3. Select the Create file command, and click Edit.
4. Under Basic info, enter a name and description to help identify the intent to create a log file.
   
5. Enter the command's properties, and click Save:
   

   Property	Value
   Connector	Select the File Utilities connector to use.
   Text	Enter Data last refreshed on , and then select the System.DateTime runtime variable.

Step 4. Add command to update Control Sheet section

To update the Control Sheet section of the Persefoni Template Spreadsheet with the log file, add an Overwrite sheet data command:

1. Under BizApps, select Workiva, and drag Overwrite sheet data to the canvas.
2. Drag a link from the Create file command to the Overwrite sheet data command.
3. Select the Overwrite sheet data command, and click Edit.
4. Under Basic info, enter a name and description to help identify the intent to overwrite the Control Sheet section.
   
5. Enter the command's properties, and click Save:
   

   Property	Value
   Connector	Select the Workiva connector to use.
   Spreadsheet ID	Select the chain variable for the Persefoni Template Spreadsheet.
   Section ID/name	Select the chain variable for the Control Sheet section.
   Data file	Select the Created file output of the Create file command.

6. Click Publish, enter any comment about publication, and click Publish.

Build a chain to download and import data from Persefoni

Next, build the chain to download emissions, activities, and facilities data from Persefoni.

Step 1. Create the chain

1. In Chain Builder, from Chains, click Create and select Create chain.
2. Under Setup, enter a name and description to help identify the chain.
   
3. Select Allow concurrent runs, and click Save.
   
   

Step 2. Add logic to download data based on Control Sheet section

From the Control Sheet section of the Persefoni Template Spreadsheet, you can select whether to refresh values based on emissions, activities, or facilities data. To download data based on these selections when the chain runs, start with a Runtime inputs event, and add conditions and command groups for each data type:

1. Move Runtime inputs from under Trigger Events to Start.
2. Select the Runtime inputs event, and click Edit.
   
3. Add variables for the data type to report — based on the selections in the Control Sheet section — and the ID of the Wdata table to upload data into based on type:
   

   Input type	Display name
   TextField	Report
   TextField	Table ID

4. Select Required for both variables, and click Save.
5. Move Conditional from under Events to the canvas, and drag a link to it from Start.
6. Select the Conditional event, and click Edit.
7. Under Basic info, enter a name and description to help identify the context of Emissions data.
   
8. Under Conditions, add a rule based on the input selection of Emissions, and click Save:

   Data type	Variable	Operator	Value
   Select String.	Select the Report runtime input variable under Trigger.	Select =.	Enter Emissions.

9. Move Command group to the canvas, and drag a link to it from the Conditional event.
10. Select the command group, and click Edit.
11. Under Basic info, enter a name and description to identify its context of Emissions data, and click Save.
12. Move another Conditional from under Events to the canvas, and drag a link to it from Start.
13. Select this Conditional event, and click Edit.
14. Under Basic info, enter a name and description to help identify the context of Facilities data.
    
15. Under Conditions, add a rule based on the input selection of Facilities, and click Save:

    Data type	Variable	Operator	Value
    Select String.	Select the Report runtime input variable under Trigger.	Select =.	Enter Facilities.

16. Move two more command groups to the canvas, and drag a link to each from the Facilities Conditional event.
17. For each command group, click Edit, enter a name and description to identify the context of its data (Facilities or Activities, respectively), and click Save. 
18. To run the Activities group only when the runtime input is not Emissions or Facilities, double-click its link, select Error for Edit link condition, and click Close.
    

Step 3. Add commands to remove any datasets from the Wdata tables

To prepare each Wdata table for the latest data from Persefoni, build these commands three times— once for each command group of Emissions, Facilities, or Activities—to remove any existing datasets.

Tip: To ease chain creation, build these commands for one data type, click Copy to duplicate them for the other data types, and edit them as necessary.

1. Under BizApps, select Workiva, and move List files to the canvas.
2. Drag a link from Group start of the command group to the List files command.
3. Select the List files command, and click Edit.
4. Under Basic info, enter a name and description to help identify the intended data.
   
5. Enter the command's properties, and click Save:

   Property	Value
   Connector	Select the same Workiva connector as the previous chain.
   Table ID	Select the Table ID runtime variable under Trigger.

6. Under BizApps, select JSON, and move Array to CSV to the canvas.
7. Drag a link from the List files command to the Array to CSV command.
8. Select the Array to CSV command, and click Edit.
9. Under Basic info, enter a name and description to help identify the intent to convert the list of the table's datasets to a comma-separated values (CSV) format.
   
10. Enter the command's properties:

    Property	Value
    Connector	Select the JSON connector to use.
    JSON data	Select the Files list output of the List files command.
    Multi-value delimiter	Enter a comma (,).
    Preview result	Select this checkbox.

11. Add columns for IDs, names, and status:
    
    

    Column name	JSONpath
    id	.id
    name	.name
    status	.status

12. In Delimiter, select Comma, and click Save.
13. Under BizApps, select Tabular Transformation, and move Advanced query to the canvas.
14. Drag a link from the Array to CSV command to the Advanced query command.
15. Select the Advanced query command, and click Edit.
16. Under Basic info, enter a name and description to help identify the intent to query the table's datasets.
    
17. Select the Tabular Transformation connector to use.
18. Under Tables, add the file with the data to query:

    Property	Value
    File	Select the Converted file output of the Array to CSV command.
    Table name	Enter a.

19. Enter the command's properties, and click Save:

    Property	Value
    Query	Enter select * from a where status = 'IMPORTED'.
    Input delimiter	Select Comma.
    Output delimiter	Select Comma.
    Preview results	Select this checkbox.

20. Under BizApps, select Workiva, and move Un-import file from table to the canvas.
21. Drag a link from the Advanced query command to the Un-import file from table command.
22. Select the Un-import file from table command, and click Edit.
23. Under Basic info, enter a name and description to help identify the intent to un-import datasets.
    
24. Enter the command's properties:

    Property	Value
    Connector	Select the same Workiva connector as earlier.
    Table ID	Select the Table ID runtime input under Trigger.
    File ID	Select the Files list output of the List files command.

25. In the File ID field, click the Files list output, and apply a transformation:
    
    

    Transformation	Output	Value
    Select Get value from JSON.	Select Text.	Enter 0 and id.

26. Select the Skip tab.
27. In Skip condition, select Success.
28. Add a rule to skip this command if no data downloads from Persefoni, and click Save:
    
    

    Data type	Variable	Operator	Value
    Select Integer.	Select the Record count output of the Advanced query command.	Select =.	Enter 0.

29. Click Save.
30. Under BizApps, select Workiva, and move Delete file to the canvas.
31. Drag a link from the Un-import file from table command to the Delete file command.
32. Select the Delete file command, and click Edit.
33. Under Basic info, enter a name and description to help identify the intent to delete datasets.
    
34. Enter the command's properties, and click Save:

    Property	Value
    Connector	Select the same Workiva connector as earlier.
    File ID	Select ID from the File import output of the Un-import file from table command.

Step 4. Add commands to download and import data from Persefoni

To pull and upload the latest data from Persefoni, build these commands three times — once for each command group of Emissions, Facilities, or Activities. These commands download the respective data from Persefoni and update the values and log in the Persefoni Template Spreadsheet.

Tip: To ease chain creation, build these commands for one data type, click Copy to duplicate them for the other data types, and edit them as necessary.

1. Under BizApps, select Persefoni, and move Fetch transactions to the canvas.
2. Drag a link from Group start of the command group to the Fetch transactions command.

   Note: Since both the Fetch transactions and List files commands link to Group start, they'll start at the same time when the group runs.

3. Select the Fetch transactions command, and click Edit.
4. Under Basic info, enter a name and description to help identify the intent to download the respective data type.
   
5. Under BizApps, select Workiva, and move Create file to the canvas.
6. Drag links from both the Fetch transactions and Un-import file from table commands to the Create file command.
7. Select the Create file command, and click Edit.
8. Under Basic info, enter a name and description to help identify the intent to create a comma-separated values (CSV) file of the data.
   
9. Enter the command's properties, and click Save:
   

   Property	Value
   Connector	Select the same Workiva connector as earlier.
   Table ID	Select the Table ID runtime input under Triggers.
   File	Select the Fetched transactions output of the Fetch transactions command.
   Name	Enter the name for the created dataset, including the System.DateTime runtime variable. For example, for Emissions data, enter emissions-System.DateTime.csv.

10. Under BizApps, select Workiva, and move Import file into table to the canvas.
11. Drag a link from the Create file command to the Import file into table command.
12. Select the Import file to table command, and click Edit.
13. Under Basic info, enter a name and description to help identify the intent to import the dataset into the Wdata table.
    
14. Enter the command's properties, and click Save:
    

    Property	Value
    Connector	Select the same Workiva connector as earlier.
    Table ID	Select the Table ID runtime input under Triggers.
    File ID	Select ID from the Result output of the Create file command.

15. Move the Run chain event from under BizApps to the canvas.
16. Drag a link from the Import file into table command to the Run chain event.
17. Select the Run chain event, and click Edit.
18. Under Basic info, enter a name and description to identify the chain and its intent to log its respective data download.
    
19. Under Chain to run, select the chain built to log downloads from Persefoni.
20. In Range under Chain runtime inputs, enter the coordinates for the Control Sheet section cell to log the data's download:
    • For emissions, enter D13:D13.
    • For facilities, enter D15:D15.
    • For activities, enter D14:D14.
21. To delete the previous data's dataset from the connected Wdata table, select Workiva under BizApps, and move Delete file to the canvas.
22. Drag a link from the Import file from table command to the Delete file command.
23. Select the Delete file command, and click Edit.
24. Under Basic info, enter a name and description to help identify the intent to delete datasets.
    
25. Enter the command's properties, and click Save:

    Property	Value
    Connector	Select the same Workiva connector as earlier.
    File ID	Select ID from the File import output of the Un-import file from table command.

26. Select the Skip tab.
27. In Skip condition, select Success.
28. Add a rule to skip this command if no data downloads from Persefoni, and click Save:
    
    

    Data type	Variable	Operator	Value
    Select Integer.	Select the Record count output of the Advanced query command.	Select =.	Enter 0.

29. To re-import the previous dataset if the import fails, select the Import file into table command and click Copy.
30. Drag a link between the two Import file into table commands.
31. Select the new Import file to table command, and click Edit.
32. Under Basic info, enter a name and description to help identify the intent to re-import the previous dataset into the Wdata table.
    
33. In File ID, delete the value, then select the Files list output of the List files command.
34. Click the Files list output, and apply a transformation:
    

    Transformation	Output	Value
    Select Get value from JSON.	Select Text.	Enter 0 and id.

35. To run the command only when the first Import file into table command fails, double-click its link, select Error for Edit link condition, and click Close.
    
    

Step 5. Publish the chain

After you set up the commands for each data type, enable the chain to run:

1. Click Publish.
2. Enter any comment about its publication, and click Publish.

Build a chain to refresh the Persefoni Template Spreadsheet

Lastly, build the chain that checks the selections on the Control Sheet section of the Persefoni Template Spreadsheet and then runs the other chains to download, log, and refresh data from Persefoni. 

Note: This chain automatically runs the other chains and is the only chain you'll need to run to update the Persefoni Template Spreadsheet.

Step 1. Create the chain

1. In Chain Builder, from Chains, click Create and select Create chain.
2. Under Setup, enter a name and description to help identify the chain.
   
3. Add variables for the IDs of the Persefoni Template Spreadsheet and its Control Sheet section.
4. Click Save.
   
   

Step 2. Add commands to check Control Sheet section selections

To identify which data to download from Persefoni:

1. Under BizApps, select Workiva, and move Get sheet data to Start.
2. Select the Get sheet data command, and click Edit.
3. Under Basic info, enter a name and description to identify the chain and its intent to check the Control Sheet section.
   
4. Enter the command's properties, and click Save:
   

   Property	Value
   Connector	Select the same Workiva connector as the other chains.
   Spreadsheet ID	Select the chain variable for the Persefoni Template Spreadsheet's ID.
   Sheet ID/name	Select the chain variable for the ID of the Control Sheet section.
   Region	Enter A12:D15.
   Value style	Select Calculated.
   Revision	Enter -1.

5. Under BizApps, select Tabular Transformation, and move Advanced query to the canvas.
6. Drag a link from Start to the Advanced query command.
7. Select the Advanced query command, and click Edit.
8. Under Basic info, enter a name and description to identify the chain and its intent to identify data to download based on the Control Sheet section selections.
   
9. Select the same Tabular Transformation connector as the earlier chain.
10. Under Tables, add the file with the data to query:

    Property	Value
    File	Select the Data output of the Get sheet data command.
    Table name	Enter a.

11. Enter the command's properties, and click Save:
    

    Property	Value
    Query	Enter select * from a where `Refresh Data` = 'Yes'.
    Input delimiter	Select Comma.
    Output delimiter	Select Comma.
    Preview results	Select this box.

12. Under BizApps, select JSON, and move CSV to JSON to the canvas.
13. Drag a link from the Advanced query command to the CSV to JSON command. 
14. Select the CSV to JSON command, and click Edit.
15. Under Basic info, enter a name and description to identify the chain and its intent to convert the query results to JavaScript Object Notation (JSON).
    
16. Enter the command's properties, and click Save:
    

    Property	Value
    Connector	Select the same JSON connector as the previous chain.
    Input file	Select the Results output of the Advanced query command.
    Delimiter	Select Comma (,).

Step 3. Add command group to start downloads based on Control Sheet section

To start the download chain based on the selections from the Control Sheet section:

1. Under BizApps, move Command group the canvas.
2. Drag a link from the CSV to JSON command to In of the command group.
3. Select the command group, and click Edit.
4. On the Iterator tab, enable Iterator, and select the List modifier type.
   
5. Under Iterations, select the JSON file output of the CSV to JSON command.
6. Click Save.
7. Under BizApps, move the Run chain event to the canvas.
8. Drag a link from Group start to the Run chain event.
9. Select the Run chain event, and click Edit.
10. Under Basic info, enter a name and description to identify the intent to run the chain to download Persefoni data.
    
11. Under Chain to run, select the chain built to download and import data from Persefoni.
12. Under Chain runtime inputs, select the JSON file iteration variable under Group iterator for both Report and Table ID.
13. In Report, click the JSON file iteration variable, and apply a transformation:
    
    

    Transformation	Output	Value
    Select Get value from JSON.	Select Text.	Enter Name.

14. In Table ID, click the JSON file iteration variable, and apply a transformation:
    

    Transformation	Output	Value
    Select Get value from JSON.	Select Text.	Enter TableID.

15. Click Save.
16. To enable the chain to run, click Publish, enter any comment about its publication, and click Publish.
    
    

Run the chains

To automatically refresh values in the Persefoni Template Spreadsheet with the latest data from Persefoni:

Step 1. Select the data to refresh

In the Control Sheet section of the Persefoni Template Spreadsheet, select Yes in the Refresh data column for the types of data to update.

Step 2. Verify the Spreadsheet IDs

1. From Wdata Chain Builder, click Edit for the third chain built in these instructions.
2. Click Chain settings.
3. Under Variables, verify the IDs for the Persefoni Template Spreadsheet and its Control Sheet section.
4. Click Save.
5. If you update the variables' values, click Publish and Publish.
   
   

Step 3. Run the chains

From the third chain built in these instructions, click Execute and Run chain.

When the chain runs, it automatically runs the other two chains to download the selected data from Persefoni and update the corresponding values and log file in the Persefoni Template Spreadsheet.