Download carbon footprint data from CO2 AI® for sustainability reporting

CO2 AI enables you to measure your company's environmental impact and decarbonize at scale, with the power of artificial intelligence (AI).

With the Workiva Platform, you can build chains to automatically download carbon footprint data from CO2 AI into a table, based on a time period specified in a custom CO2 AI 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 CO2 AI and update the spreadsheet.

Note: The CO2 AI 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:

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

To enable integration with CO2 AI, request the token and domain for your company's account from your CO2 AI administrator.

Lastly, note the IDs for the CO2 AI spreadsheet, its sheets, and its connected table.

Build a chain to log data downloads

To start, build a chain to update the CO2 AI spreadsheet to track when carbon footprint data uploads to Workiva.

Step 1. Create the chain

From Chains, click Create, and select Create chain.
In Setup, enter a name of 3. CO2 AI log and a description to help explain that the chain logs when data uploads from CO2 AI.
Click Save.

Step 2. Start with a Runtime Inputs event

From Trigger event, 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:
- Message
- Range
- Spreadsheet ID
- Sheet name
Tip: Leave the variables' default values blank; they'll be generated when you run the 1. CO2 AI refresh chain created later.
Select Required for all variables, and click Save.

Step 3. Add commands to log the download

To add the download to the Status sheet of the CO2 AI spreadsheet :

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 Select the Message runtime input from Trigger.
From Available connectors, select Workiva, and move Overwrite sheet data to the canvas.
Drag a link from Create file to Overwrite sheet data.
Select the Overwrite 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 Sheet name runtime input from Trigger.
Data file	Select the Created file output from Create file.
Delimiter	Enter a comma (`,`).
Start cell	Enter `A2:C2`.
Use async	Clear this checkbox.
Use Platform API	Select this checkbox.

Step 4. Add commands to reset the Control sheet

To reset the Control sheet selection for the next run:

From Available connectors, select File utilities, and move another Create file to the canvas.
Drag another link from Runtime inputs to the new 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 same File utilities connector as earlier.

Text Enter ,,.
From Available connectors, select Workiva, and move another Overwrite sheet data to the canvas.
Drag a link from the second Create file to the new Overwrite sheet data.
Select the Overwrite 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	Enter Control Sheet.
Data file	Select the Created file output from the second Create file.
Delimiter	Enter a comma (`,`).
Start cell	Select the Range runtime input from Trigger.
Use async	Clear this checkbox.
Use Platform API	Select this checkbox.

Click Publish, enter a note about the chain's 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 CO2 AI spreadsheet's connected table with the data downloaded from CO2 AI. 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 CO2 AI

Finally, build a chain to download and import carbon footprint data from CO2 AI.

Step 1. Create the chain

From Chains, click Create, and select Create chain.
In Setup, enter a name of 1. CO2 AI refresh and a description to help explain that the chain downloads data from CO2 AI and uploads it to the table.

Add these variables, and click Save:

Name	Value
CO2 AI token	Enter the token for your company's account with CO2 AI.
CO2 AI domain	Enter the domain of your company's account with CO2 AI.
Spreadsheet ID	Enter the ID for the CO2 AI spreadsheet.
Control sheet ID	Enter the ID for the Control sheet section of the CO2 AI spreadsheet.
Log sheet	Enter Status.
Table ID	Enter the ID for the table connected to the CO2 AI spreadsheet.

Step 2. Add command to get Year from Control sheet

To identify the year of the data to download from your CO2 AI account:

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 the earlier chain.
Spreadsheet ID	Select the Spreadsheet ID variable from Chain.
Sheet ID/name	Select the Control sheet ID variable from Chain.
Region	Enter `A5`.
Value style	Select Calculated.
Revision	Enter `-1`.

Step 3. Add commands to see if CO2 AI has data

To verify whether carbon footprint data is available to download:

From Available connectors, select HTTP, and move Get to the canvas.
Drag a link from Get sheet data to Get.
Select the Get command, and click Edit.
In Basic info, enter a name and description to help identify that the command checks CO2 AI for Cycles.

Enter the command's properties, and click Save:

Property	Value
Connector	Select the HTTP connector to use.
Show response	Select this checkbox.
URL	Create the URL for the request: Enter `https://`. Select the CO2 AI domain variable from Chain. Enter `.co2ai.com/api/0.1/cycles?in_past=100&in_future=100`.
Headers	Add this header: In Key, enter `Authorization`. In Value, enter `Bearer` , then select the CO2 AI token variable from Chain.
Content type	Enter `application/json`.
Output	Enter this sample response: [ { "id": 1, "name": "2020", "is_enabled": true, "start_at": "2020-01-01T00:00:00+00:00", "end_at": "2020-12-31T00:00:00+00:00", "is_current": false, "has_any_footprint": true } ]

From Available connectors, select JSON, and move Array to CSV to the canvas.
Drag a link from Get to Array to CSV.
Select the Array to CSV command, and click Edit.
In Basic info, enter a name and description to help identify the command.

Enter the command's properties:

Property	Value
Connector	Select the JSON connector to use.
JSON data	Select the Response output from Get.
Multi-value delimiter	Enter a comma (,).
Preview result	Select this checkbox.
Delimiter	Select Comma.

In Columns, add these columns, then click Save:

Column name JSON path

id .id

name .name

is_enabled .is_enabled

start_date .start_at

end_date .end_at

current .is_current

footprint .has_any_footprint
From Available connectors, select Tabular transformation, and move Extract value to the canvas.
Drag a link from Array to CSV to Extract value.
Select the Extract value command, and click Edit.
In Basic info, enter a name and description to help identify that the command retrieves the Year from the Control sheet.

Enter the command's properties, and click Save:

Property	Value
Connector	Select the Tabular Transformation connector to use.
Input file	Select this Data output from Get sheet data.
Column index	Enter `2`.
Delimiter	Select Comma.
Row index	Enter `2`.

From Available connectors, select Tabular transformation, and move Advanced query to the canvas.
Drag a link from Extract value to Advanced query.
Select the Advanced query command, and click Edit.
In Basic info, enter a name and description to help identify that the command retrieves the Year from the Control sheet.

Enter the command's properties, and click Save:

Property	Value
Connector	Select the same Tabular Transformation connector as Extract value.
Tables	Add this table: In File, select the Converted file output from Array to CSV. In Table name, enter `b`.
Query	Enter the query to perform: Enter `Select * from b where name='`. Select the Value output from Extract value. Enter `' and footprint = 'true'`.
Input delimiter	Select Comma.
Output delimiter	Select Comma.
Preview results	Select this checkbox.

Step 4. Add commands to download any available data from CO2 AI

To download available carbon footprint data from CO2 AI:

From Chain events, move Conditional to the canvas.
Drag a link from Advanced query to Conditional.
Select the Conditional event, and click Edit.
In Basic info, enter a name and description to help identify that the event verifies the query returned results.
In Condition, add the rule to test, then click Save:
Data Operation
In Data type to test, select Integer.

Select the Record count output from Advanced query.
In Operation to test, select Greater than (>).

Enter 0.
To stop the chain if there is no data to download, add a Run chain event to the canvas.
Drag a link from Conditional to Run chain.
Double-click the link, and select Error.
Select the Run chain event, and click Edit.
In Basic info, enter a name and description to help identify the event.
In Chain to run, select the 3. CO2 AI log chain created earlier.

Enter the chain's runtime inputs, and click Save:

Runtime input	Value
Message	Create the message to post to the Status sheet: Select the SystemDateTime variable from Runtime. Click the SystemDateTime variable, then add a Parse date/time transformation. Select ISO Extended (Platform Standard). In Output date format, enter `%Y-%m-%d`. Select the input and output time zones, then click Apply. Enter `,Successful with errors,` . Select the Value output from the Extract value command for the year. Enter `footprint doesn't exist. Contact your CO2 AI administrator.`
Range	B6:C6
Spreadsheet ID	Select the Spreadsheet ID variable from Chain.
Sheet name	Select the Log sheet variable from Chain.

From Available connectors, select Tabular transformation, and move Extract value to the canvas.
Drag a link from Conditional to Extract value.
Select the Extract value command, and click Edit.
In Basic info, enter a name and description to help identify that the command retrieves the Cycle ID.

Enter the command's properties, and click Save:

Property	Value
Connector	Select the same Tabular Transformation connector as earlier.
Input file	Select this Result output from Advanced query.
Column index	Enter `1`.
Delimiter	Select Comma.
Row index	Enter `2`.

From Available connectors, select HTTP, and move Get to the canvas.
Drag a link from Extract value to Get.
Select the Get command, and click Edit.
In Basic info, enter a name and description to help identify that the command retrieves the list of available footprint data.

Enter the command's properties, and click Save:

Property	Value
Connector	Select the same HTTP connector as earlier.
Show response	Select this checkbox.
URL	Create the URL for the request: Enter `https://`. Select the CO2 AI domain variable from Chain. Enter `.co2ai.com/api/0.1/footprints/cycles/`. Select the Value output from the preceding Extract value.
Headers	Add this header: In Key, enter `Authorization`. In Value, enter `Bearer` , then select the CO2 AI token variable from Chain.
Content type	Enter `application/json`.
Output	Enter this sample response: [{"name":"spirit-cycle-3-(api)","description":"Footprint created via the CO2AI API","id":3,"created_by":{"id":2,"sub":"samlp\|co2ai-okta\|dima@co2ai.com","username":"dima@co2ai.com"},"created_at":"2024-05-20T08:50:27.263752+00:00","updated_at":"2024-05-20T08:50:29.335418+00:00","state":"processed","cycle_id":3,"is_cycle_baseline":true,"is_projected":false,"start_at":"2024-05-20T08:50:27.258324+00:00","end_at":null,"data_ingestion_method":"prepared-files"}]

From Available connectors, select HTTP, and move another Get to the canvas.
Drag a link between this and the previous Get commands.
Select the new Get command, and click Edit.
In Basic info, enter a name and description to help identify that the command retrieves the available footprint data.

Enter the command's properties, and click Save:

Property	Value
Connector	Select the same HTTP connector as earlier.
Show response	Select this checkbox.
URL	Create the URL for the request: Enter `https://`. Select the CO2 AI domain variable from Chain. Enter `.co2ai.com/api/0.1/reports/metrics/`. Select the Response output from the preceding Get.
Headers	Add this header: In Key, enter `Authorization`. In Value, enter `Bearer` , then select the CO2 AI token variable from Chain.
Content type	Enter `application/json`.
Output	Enter this sample response: {"source":"CO2 AI","created_at":"2024-07-15T21:34:43.449364+00:00","footprint":{"name":"spirit-cycle-3-(api)","description":"Footprint created via the CO2AI API","id":3,"created_by":{"id":2,"sub":"samlp\|co2ai-okta\|dima@co2ai.com","username":"dima@co2ai.com"},"created_at":"2024-05-20T08:50:27.263752+00:00","updated_at":"2024-05-20T08:50:29.335418+00:00","state":"processed","cycle_id":3,"is_cycle_baseline":true,"is_projected":false,"start_at":"2024-05-20T08:50:27.258324+00:00","end_at":null,"data_ingestion_method":"prepared-files"},"metrics":[{"name":"Gross Scopes 1, 2, 3 - GHG emissions per scope [table]","meta":{"ESRS":"E1","DR":"E1-6"},"unit":"kgCO2eq","group":["Scope"],"values":[{"Scope":2.0,"value":411629000.0},{"Scope":3.0,"value":4800422470.269996},{"Scope":1.0,"value":253374999.99999952}]},{"name":"Total GHG emissions","meta":{"ESRS":"E1","DR":"E1-6"},"unit":"kgCO2eq","group":[],"values":[{"value":5465426470.269995}]},{"name":"Total GHG emissions per scope and ghg category","meta":{"ESRS":"E1","DR":"E1-6"},"unit":"kgCO2eq","group":["Scope","GHG category"],"values":[{"GHG category":"EnergyAndElectricityConsumption","Scope":2.0,"value":411629000.0},{"GHG category":"DirectEmissions","Scope":1.0,"value":253374999.99999952},{"GHG category":"EndOfLifeTreatmentOfSoldProducts","Scope":3.0,"value":2371999.999999996},{"GHG category":"Franchises","Scope":3.0,"value":299.99999999999955},{"GHG category":"Investments","Scope":3.0,"value":350999.9999999993},{"GHG category":"EmployeeCommuting","Scope":3.0,"value":95039.99999999977},{"GHG category":"PurchasedGoodsAndServices","Scope":3.0,"value":2747921733.269994},{"GHG category":"BusinessTravel","Scope":3.0,"value":2523904.9999999898},{"GHG category":"ProcessingOfSoldProducts","Scope":3.0,"value":0.0},{"GHG category":"UpstreamTransportationAndDistribution","Scope":3.0,"value":1198368000.0},{"GHG category":"WasteGeneratedInOperations","Scope":3.0,"value":642060500.0},{"GHG category":"UpstreamLeasedAssets","Scope":3.0,"value":150196991.99999982},{"GHG category":"CapitalGoods","Scope":3.0,"value":56442999.99999991},{"GHG category":"DownstreamLeasedAssets","Scope":3.0,"value":89999.9999999999},{"GHG category":"UseOfSoldProduct","Scope":3.0,"value":0.0}]},{"name":"Total energy consumption related to own operations","meta":{"ESRS":"E1","DR":"E1-5"},"unit":"kWh","group":[],"values":[{"value":522000000.0}]}]}

From Available connectors, select Handlebars, and move Render text template to the canvas.
Drag a link from the second Get to Render text template.
Select the Render text template command, and click Edit.
In Basic info, enter a name and description to help identify that the command parses the unique headers of the footprint data.

Enter the command's properties, and click Save:

Property	Value
Connector	Select the Handlebars connector to use.
Template	Enter the template to render: {{#each d}} {{#each this.group}} {{.}} {{/each}} {{/each}}
JSON variables	Add this variable: In Name, enter `d`. In Value, select the Response output from the Get command for the list of footprint data. Select Is list?

Step 5. Add commands to parse data for unique columns

To parse the unique columns' data from the rendered template:

From Available connectors, select File utilities, and move Create file to the canvas.
Drag a link from Render text template 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 parses the unique columns' data.

Enter the command's properties, and click Save:

Property	Value
Connector	Select the same File Utilities connector as earlier.
Text	Enter the text to add to the created file: Enter `col`, then press Enter. Select the Rendered text output from Render text template.

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 help identify the command.

Enter the command's properties, and click Save:

Property	Value
Connector	Select the same Tabular Transformation connector as earlier.
Tables	Add this table: In File, select the Created file output from Create file. In Table name, enter `f`.
Query	Enter this query: select distinct(cols) from f
Input delimiter	Select Comma.
Output delimiter	Select Comma.
Preview results	Select this checkbox.

From Available connectors, select JSON, and move CSV to JSON to the canvas.
Drag a link from Advanced query to CSV to JSON.
Select the CSV to JSON 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 JSON connector as earlier.

Input file Select the Result output of the preceding Advanced query command.

Delimiter Select Comma (,).

Step 6. Add commands to also parse all columns' data

To also parse data from all columns of the rendered template:

From Available connectors, select File utilities, and move another Create file to the canvas.
Drag another link from Render text template to the new Create file.
Select the Create file command, and click Edit.
In Basic info, enter a name and description to help identify that the command parses the all the columns' data.

Enter the command's properties, and click Save:

Property	Value
Connector	Select the same File Utilities connector as earlier.
Text	Enter the text to add to the created file: Enter `colm`, then press Enter. Enter `name`, then press Enter. Enter `framework`, then press Enter. Enter `disclosure`, then press Enter. Enter `unit`, then press Enter. Select the Rendered text output from Render text template. Enter `values`.

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 help identify the command.

Enter the command's properties, and click Save:

Property	Value
Connector	Select the same Tabular Transformation connector as earlier.
Tables	Add this table: In File, select the Created file output from the preceding Create file command. In Table name, enter `f`.
Query	Enter this query: select lower(replace(colm,' ','_')) as "colm" from (select distinct(colm) from f)
Input delimiter	Select Comma.
Output delimiter	Select Comma.
Preview results	Select this checkbox.

From Available connectors, select JSON, and move CSV to JSON to the canvas.
Drag a link from Advanced query to CSV to JSON.
Select the CSV to JSON 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 JSON connector as earlier.

Input file Select the Result output of the preceding Advanced query command.

Delimiter Select Comma (,).

Step 7. Add commands to download and import data

To parse and import the footprint data downloaded from CO2 AI:

From Available connectors, select Handlebars, and move Render text template to the canvas.
Drag links from both CSV to JSON commands to Render text template.
Select the Render text template command, and click Edit.
In Basic info, enter a name and description to help identify that the command parses the downloaded footprint data.

Enter the command's properties:

Property

Value

Connector

Select the Handlebars connector to use.

Template

Enter the template to render:

{{#each c}}{{colm}}{{#unless @last}},{{/unless}}{{/each}}
{{#each a}}
{{#each this.values}}
"{{../name}}","{{../meta.ESRS}}","{{../meta.DR}}","{{../unit}}",{{#each ../../b}}"{{lookup ../this cols}}"{{#unless @last}},{{/unless}}{{/each}},{{value}}
{{/each}}
{{/each}}

In JSON variables, add these variables, then click Save.

Name	Value	Is list?
a	Select Metrics from the Response output of the Get command for the footprint data.	Select this checkbox.
b	Select the JSON file output from the first CSV to JSON command.	Select this checkbox.
c	Select the JSON file output from the second CSV to JSON command.	Select this checkbox.

From Available connectors, select File utilities, and move Create file to the canvas.
Drag a link from Render text template to Create file.
Select the Create file 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 File Utilities connector as earlier.

Text Select the Rendered text output from the preceding Render text template.
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 help identify the command.

Enter the command's properties, and click Save:

Property	Value
Connector	Select the same Tabular Transformation connector as earlier.
Tables	Add this table: In File, select the Created file output from the preceding Create file command. In Table name, enter `h`.
Query	Enter the query to perform: Enter `select ,`. Select the Value* output from the Extract value command for the Year. Enter `as "period" from h` .
Input delimiter	Select Comma.
Output delimiter	Select Comma.
Preview results	Select this checkbox.

From Chain events, move Run chain to the canvas.
Drag a link from Advanced query to Run chain.
Select the Run chain event, and click Edit.
In Basic info, enter a name to help identify that the event imports the data into a Wdata table.
In Chain to run, select the primary chain created from the Load Data into Wdata template.

In Chain runtime inputs, enter these inputs, then click Save:

Input	Value
Table ID	Select the Table ID variable from Chain.
File name	Create the file name: Enter `footprint_data_`. Select the Value output from the Extract value command for the Year. Enter `.csv`.
Data file	Select the Result output from the preceding Advanced query command.
Load method	Select Replace dataset.
Rollback	Select this checkbox.

From Chain events, move another Run chain to the canvas.
Drag a link between both Run chain events.
Select the new Run chain event, and click Edit.
In Basic info, enter a name to help identify that the event logs the download in the Status sheet of the CO2 AI spreadsheet.
In Chain to run, select the 3. CO2 AI log created earlier.

In Chain runtime inputs, enter these inputs, then click Save:

Input	Value
Message	Create the message to add to the Status sheet: Select the SystemDateTime variable from Runtime. Click the SystemDateTime 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 timezones, and click Accept. Enter `,Successful,`. Select the Value output from the Extract value command for the Year. Enter `footprint data successfully uploaded`.
Range	Enter `B6:C6`.
Spreadsheet ID	Select the Spreadsheet ID variable from Chain.
Sheet name	Select the Log sheet variable from Chain.

Click Publish, enter a note about the chain's publication, and click Publish.

Run the chains

To refresh the CO2 AI spreadsheet and table with the latest carbon footprint data:

From the Control sheet of the CO2 AI spreadsheet, select the year of the data to download.
From Chains, select the 1. CO2 AI 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 CO2 AI spreadsheet with the latest data.

Support

Community

Workiva Support Center