Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

Download carbon footprint data from Watershed® for sustainability reporting

Download carbon footprint data from Watershed® for sustainability reporting

  • Last updated on

watershed.jpegWatershed's enterprise climate platform enables you to measure, report, and reduce your organization's carbon footprint.

With the Workiva Platform, you can build chains to automatically download carbon footprint data from Watershed — such as for disclosure in your Environment, Social, and Governance (ESG) or sustainability reporting — into a table, based on a time period specified in a custom Watershed spreadsheet. You can then use the table as a source for a query or spreadsheet connection, such as to include the data in reporting outputs.

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 Watershed and update the spreadsheet.
Note: The Watershed 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, you'll need these connectors:

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

To successfully run the chains, also identify:

Build a chain to log data downloads

To start, build a chain to update the Control sheet section to track when carbon footprint data uploads to Workiva.

watershed-chain_06.png

Step 1. Create the chain

  1. From Chains, click Create, and select Create chain.
    watershed-chain_02.png
  2. In Setup, enter a name of 3. Watershed and a description to help identify that the chain logs Watershed data uploads.
    watershed-chain_01.png
  3. Click Save.

Step 2. Start with a Runtime Inputs event

  1. From Chain events, move Runtime inputs to Start.
  2. Select the Runtime inputs event, and click Edit.
  3. In Basic info, enter a name and description to help identify the event.
    watershed-chain_03.png
  4. In Variables, add TextField inputs with these display names:
    • Range
    • SpreadsheetID
    • SheetID
    Tip: Leave these variables' default values blank; they'll be generated when you run the 1. Watershed chain created later.
  5. Select Required for all three variables, and click Save.

Step 3. Add a File Utilities Create File command

To create the log file for the Control Sheet section, add a File Utilities Create file command:

  1. From Available connectors, select File Utilities, and move Create file to the canvas.
  2. Drag a link from Runtime inputs to Create file.
  3. Select the Create file command, and click Edit.
  4. In Basic info, enter a name and description to help identify the command.
    watershed-chain_04.png
  5. Enter the command's properties, and click Save:
    Property Value
    Connector Select the File Utilities connector to use.
    Text Enter Footprint data last refreshed on, and select the System.DateTime runtime variable.

Step 4. Add a Workiva Write Sheet Data command

To update the Control Sheet section with the log file, add a Workiva Write sheet data command:

  1. From Available connectors, select Workiva, and move Write sheet data to the canvas.
  2. Drag a link from Create file to Write sheet data.
  3. Select the Write sheet data command, and click Edit.
  4. In Basic info, enter a name and description to help identify the command.
  5. Enter the command's properties, and click Save:
    watershed-chain_05.png
    Property Value
    Connector Select the Workiva connector to use.
    Spreadsheet ID Select the SpreadsheetID runtime input from Trigger.
    Sheet ID/name Select the SheetID runtime input from Trigger.
    Data file Select the Created file output of the Create file command.
    Delimiter Enter a comma (,).
    Region Select the Range runtime input from Trigger.
  6. 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 Watershed spreadsheet's connected table with the data downloaded from Watershed. The Load Data to Wdata template creates chains that work together to determine whether the dataset already exists in the table and 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.

watershed-chain_30.png

Build a chain to download data from Watershed

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

watershed-chain_14.png

Note: Create the other two chains before this one, for its Run chain events.

Step 1. Create the chain

  1. From Chains, click Create, and select Create chain.
  2. In Setup, enter a name of 1. Watershed and a description to help further identify the chain.
    watershed-chain_07.png
  3. In Variables, add variables for the Watershed API key and the IDs of the Watershed spreadsheet and its control sheet.
    Name Value
    APIKey Enter the Watershed API key.
    SpreadsheetID Enter the ID of the Watershed spreadsheet.
    SheetID Enter the ID of the Control sheet section of the Watershed spreadsheet.
  4. Click Save.

Step 2. Start with a Workiva Get Sheet Data command

Start with a Workiva Get sheet data command to identify the data to download, based on the time period in the Watershed spreadsheet's Control sheet:

  1. From Available connectors, select Workiva, and move Get sheet data to Start.
  2. Select the Get sheet data command, and click Edit.
  3. In Basic info, enter a name and description to help identify the command.
  4. Enter the command's properties, and click Save:
    watershed-chain_08.png
    Property Value
    Connector Select the Workiva connector to use.
    Spreadsheet ID Select the Spreadsheet ID chain variable.
    Sheet ID/name Select the Sheet ID chain variable.
    Region Enter A11:.
    Value style Select Calculated.
    Revision To ensure the latest version, enter -1.

Step 3. Add commands to prepare time period for Watershed

To identify the time period for Watershed, add Advanced query and CSV to JSON commands:

  1. From Available connectors, select Tabular Transformation, and move Advanced query to the canvas.
  2. Drag a link from Get sheet data to Advanced query.
  3. Select the Advanced query command, and click Edit.
  4. In Basic info, enter a name and description to help identify the command.
  5. Enter the command's properties, and click Save:
    watershed-chain_09.png
    Property Value
    Connector Select the Tabular Transformation connector to use.
    Tables Add the table for the time period data:
    • In File, select the Data output of the Get sheet data command.
    • In Table name, enter a.
    Query Enter the query to run: 
    select
case when `Start Month` = 'January' then '01'
when `Start Month` = 'February' then '02'
when `Start Month` = 'March' then '03'
when `Start Month` = 'April' then '04'
when `Start Month` = 'May' then '05'
when `Start Month` = 'June' then '06'
when `Start Month` = 'July' then '07'
when `Start Month` = 'August' then '08'
when `Start Month` = 'September' then '09'
when `Start Month` = 'October' then '10'
when `Start Month` = 'November' then '11'
else '12' end as 'sm',
case when `End Month` = 'January' then '01'
when `End Month` = 'February' then '02'
when `End Month` = 'March' then '03'
when `End Month` = 'April' then '04'
when `End Month` = 'May' then '05'
when `End Month` = 'June' then '06'
when `End Month` = 'July' then '07'
when `End Month` = 'August' then '08'
when `End Month` = 'September' then '09'
when `End Month` = 'October' then '10'
when `End Month` = 'November' then '11'
else '12' end as 'em',
`Reporting Year` as 'ry',
TableId as 'tid'
from a
    Input delimiter Select Comma.
    Output delimiter Select Comma.
    Preview results Select this checkbox.
  6. From Available connectors, select JSON, and move CSV to JSON to the canvas.
  7. Drag a link from Advanced query to CSV to JSON.
  8. Select the CSV to JSON command, and click Edit.
  9. In Basic info, enter a name and description to help identify the command.
    watershed-chain_10.png
  10. Enter the command's properties, and click Save:
    Property Value
    Connector Select the JSON connector to use.
    Input file Select the Result output of the Advanced query command.
    Delimiter Select Comma (,).

Step 4. Add an HTTP Request GET command

To download carbon footprint data for the selected time period, add an HTTP Request GET command:

  1. From Available connectors, select HTTP Request, and move GET to the canvas.
  2. Drag a link from CSV to JSON to GET.
  3. Select the GET command, and click Edit.
  4. In Basic info, enter a name and description to help identify the command.
  5. Enter the command's properties, and click Save:
    watershed-chain_11.png
    Property Value
    Connector Select the HTTP Request connector to use.
    Show response Select this checkbox.
    URL Enter https://api.watershedclimate.com/v1/report/footprint_detail.
    Query string Enter the query string to indicate the start and end months of the data to download:
    1. For the start month, enter startMonth=, and select the JSON file output of the CSV to JSON command twice.
    2. Then, for the end month, enter &endMonth=, and select the JSON file output of the CSV to JSON command two more times.
    3. For both months, click the first JSON file output, and apply a Get variable from JSON transformation:
      • In Output, select Text.
      • In Value, enter 0 and ry.
    4. For both months, click the second JSON file output, and apply another Get variable from JSON transformation:
      • In Output, select Text.
      • In Value, enter 0 and sm.
    Headers Add a header:
    • In Key, enter Authorization.
    • In Value, enter Bearer , then select the APIKey chain variable.
    Content type Enter application/json.

Step 5. Add Run chain events to perform and log the data upload

To upload the data into Wdata and log the upload in the Control sheet section, add Run chain events for the chains created earlier:

  1. From Chain events, move Run chain to the canvas.
  2. Drag a link from GET to Run chain.
  3. Select the Run chain event, and click Edit.
  4. In Basic info, enter a name and description to help identify that the event uploads the data into Wdata.
  5. In Chain to run, select Load Data to Wdata | Primary chain created earlier from the Load Data to Wdata template.
    watershed-chain_29.png
  6. Enter the chain runtime inputs, and click Save:
    Input Value
    Table ID To provide the ID of the Wdata table to upload the data to:
    1. Select the JSON file output of the CSV to JSON command.
    2. Click the JSON file output, and apply a Get variable from JSON transformation:
      • In Output, select Text.
      • In Value, enter 0 and tid.
    File prefix To define the prefix of the dataset to upload, based on the data's time period:
    1. For the start month, select the JSON file output of the CSV to JSON command twice.
    2. Enter an underscore (_).
    3. For the end month, select the JSON file output of the CSV to JSON command two more times.
    4. Enter .csv.
    5. For both months, click the first JSON file output, and apply a Get variable from JSON transformation:
      • In Output, select Text.
      • In Value, enter 0 and ry.
    6. For both months, click the second JSON file output, and apply another Get variable from JSON transformation:
      • In Output, select Text.
      • In Value, enter 0 and sm.
    Data file Select the Response output of the GET command.
    Load method Select Replace dataset.
    Rollback Select this checkbox.
  7. From Chain events, move another Run chain to the canvas.
  8. Drag a link from the first Run chain to the second.
  9. Select the second Run chain event, and click Edit.
  10. In Basic info, enter a name and description to help identify that the event logs the data upload in the Watershed spreadsheet.
  11. In Chain to run, select the 3. Watershed chain created earlier.
    watershed-chain_13.png
  12. Enter the chain runtime inputs, and click Save:
    Input Value
    Range Enter F12:F12.
    Spreadsheet ID Select the SpreadsheetID chain variable.
    Sheet ID Select the SheetID chain variable.
  13. Click Publish, enter any notes about its publication, and click Publish.

Run the chains

To refresh the Watershed spreadsheet with the latest carbon footprint data:

  1. From the Control sheet section of the Watershed spreadsheet, select the time period — months and year — of the data to download from Watershed.
    watershed-chain_15.png
  2. From Wdata Chains, select the 1. Watershed chain, and click Execute and Run chain.
    watershed-chain_16.png
    Note: When you run this chain, it automatically runs the two other chains to update the Wdata table and Watershed spreadsheet with the data.
Was this article helpful?
1 out of 1 found this helpful
Watershed's enterprise climate platform enables you to measure, report, and reduce your organization's carbon footpri...