|
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 utility data from Urjanet® for sustainability reporting

Download utility data from Urjanet® for sustainability reporting

  • Last updated on

urjanet_color_logo.svgUrjanet Utility Cloud® enables companies to act on their climate impact with accurate, on-demand data from utility providers around the world. 

With Workiva, you can build chains to automatically download utility data from Urjanet into a table, based on a time period specified in a custom Urjanet control sheet spreadsheet. You can then use this table as a source for a query or spreadsheet connection to include its data in Environmental, Social, and Governance (ESG) or sustainability 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 Urjanet and update the spreadsheet.
Note: The Urjanet control sheet spreadsheet used by these chains is provided by Workiva. If you don't have it but are interested, contact your Customer Success Manager (CSM) 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 utility 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. Urjanet and a description to help identify that the chain logs utility data uploads.
  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. Urjanet 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.
    urjanet-create-file.png
  5. Enter the command's properties, and click Save:
    Property Value
    Connector Select the File Utilities connector to use.
    Text Enter Site usage 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 Urjanet control sheet spreadsheet's connected table with the data downloaded from Urjanet. 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 Urjanet

Finally, build a chain to download and import utility data from Urjanet.

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. Urjanet and a description to help further identify the chain.
    urjanet-chain-variables.png
  3. In Variables, add variables for your Urjanet credentials and the IDs of the Urjanet control sheet spreadsheet and its Control sheet section.
    Name Value
    Urjanetusername Enter the username to use to access Urjanet services.
    Urjanetpassword Enter the password to use to access Urjanet services.
    SpreadsheetID Enter the ID of the Urjanet control sheet spreadsheet.
    SheetID Enter the ID of the Control sheet section of the 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 Urjanet control sheet spreadsheet:

  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:
    urjanet-get-sheet-data.png
    Property Value
    Connector Select the same Workiva connector as the earlier chain.
    Spreadsheet ID Select the Spreadsheet ID chain variable.
    Sheet ID/name Select the Sheet ID chain variable.
    Region Enter A11:E12.
    Value style Select Calculated.
    Revision To ensure the latest version, enter -1.

Step 3. Add commands to prepare time period for Urjanet

To identify the time period for Urjanet, 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:
    urjanet-advanced-query.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-01'
when `Start Month` = 'February' then '02-01'
when `Start Month` = 'March' then '03-01'
when `Start Month` = 'April' then '04-01'
when `Start Month` = 'May' then '05-01'
when `Start Month` = 'June' then '06-01'
when `Start Month` = 'July' then '07-01'
when `Start Month` = 'August' then '08-01'
when `Start Month` = 'September' then '09-01'
when `Start Month` = 'October' then '10-01'
when `Start Month` = 'November' then '11-01'
else '12-01' end as 'sm',
case when `End Month` = 'January' then '01-31'
when `End Month` = 'February' then '02-28'
when `End Month` = 'March' then '03-31'
when `End Month` = 'April' then '04-30'
when `End Month` = 'May' then '05-31'
when `End Month` = 'June' then '06-30'
when `End Month` = 'July' then '07-31'
when `End Month` = 'August' then '08-31'
when `End Month` = 'September' then '09-30'
when `End Month` = 'October' then '10-31'
when `End Month` = 'November' then '11-30'
else '12-31' 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.
    urjanet-csv-to-json.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 a POST command to send a bearer token to Urjanet

To provide Urjanet with the bearer token for authentication, add an HTTP POST command:

  1. From Available connectors, select HTTP, and move POST to the canvas.
  2. Drag a link from CSV to JSON to POST.
  3. Select the POST 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:
    urjanet-post.png
    Property Value
    Connector Select the HTTP connector to use.
    Show response Select this checkbox.
    URL Enter https://api.urjanet.com/auth/login.
    Content type Enter application/json.
    Body text Enter the string to build the bearer token:
    1. Enter {"password":".
    2. Select the Urjanetpassword variable from Chain.
    3. Enter ","username":"
    4. Select the Urjanetusername variable from Chain.
    5. Enter "}.
    Output schema Enter this sample response: 
    {"status":200,"message":"Authentication Successful","type":"Bearer","token":"abc","expires":"1694017012"}

Step 5. Add commands to get sites from Urjanet

To retrieve and parse the sites from your Urjanet account, add an HTTP GET and Handlebars Render text template commands:

  1. From Available connectors, select HTTP, and move GET to the canvas.
  2. Drag a link from POST 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:
    urjanet-get.png
    Property Value
    Connector Select the same HTTP connector as earlier.
    Show response Select this checkbox.
    URL Enter https://api.urjanet.com/utility/sites.
    Headers Add this header:
    • In Key, enter authorization.
    • In Value, select Type and then Token from the Response output of the POST command.
    Content type Enter application/json.
    Output schema Enter this sample response: 
    { 
  "_embedded" : { 
    "sites" : [ { 
      "createdBy" : "jack.doe", 
      "createdDate" : "2023-08-29T18:49:32.000+00:00", 
      "entityId" : "1ee469cc-a606-d007-9415-eeeb8714a7ae", 
      "facilityType" : "Office", 
      "lastModifiedBy" : "jack.doe", 
      "lastModifiedDate" : "2023-08-29T18:49:32.000+00:00", 
      "region" : null, 
      "serviceAddress" : { 
        "addressType" : "RAW", 
        "city" : "CHICAGO", 
        "country" : "USA", 
        "fullAddress" : "123 EVERGREEN TERRACE, CHICAGO, IL, 60603, USA", 
        "postalCode" : "60603", 
        "recipient" : null, 
        "state" : "IL", 
        "streetLine1" : "123 EVERGREEN TERRACE", 
        "streetLine2" : null 
      }, 
      "siteCode" : "DIO", 
      "siteName" : "Doe, Inc.", 
      "siteNumber" : "1", 
      "subRegion" : null, 
      "_links" : { 
        "self" : { 
          "href" : "https://api.urjanet.com/utility/sites/1ee469cc-a606-d007-9415-eeeb8714a7ae" 
        }, 
        "accounts" : { 
          "href" : "https://api.urjanet.com/utility/sites/1ee469cc-a606-d007-9415-eeeb8714a7ae/accounts" 
        }, 
        "credentials" : { 
          "href" : "https://api.urjanet.com/utility/sites/1ee469cc-a606-d007-9415-eeeb8714a7ae/credentials" 
        }, 
        "events" : { 
          "href" : "https://api.urjanet.com/utility/sites/1ee469cc-a606-d007-9415-eeeb8714a7ae/events" 
        }, 
        "meters" : { 
          "href" : "https://api.urjanet.com/utility/sites/1ee469cc-a606-d007-9415-eeeb8714a7ae/meters" 
        }, 
        "statements" : { 
          "href" : "https://api.urjanet.com/utility/sites/1ee469cc-a606-d007-9415-eeeb8714a7ae/statements" 
        } 
      } 
    } ] 
  }, 
  "_links" : { 
    "self" : { 
      "href" : "https://api.urjanet.com/utility/sites?page=0&size=20" 
    } 
  }, 
  "page" : { 
    "size" : 20, 
    "totalElements" : 1, 
    "totalPages" : 1, 
    "number" : 0 
  } 
}
  6. From Available connectors, select Handlebars, and move Render text template to the canvas.
  7. Drag a link from GET to Render text template.
  8. Select the Render text template command, and click Edit.
  9. In Basic info, enter a name and description to help identify the command.
  10. Enter the command's properties, and click Save:
    urjanet-render-text-template.png
    Property Value
    Connector Select the Handlebars connector to use.
    Template Enter this text template: 
    [{{#each a}} "{{entityId}}" {{#unless @last}},{{/unless}}{{/each}}]
    JSON variables Add this variable:
    • In Name, enter a.
    • In Value, select sites from _embedded of the Response output of the GET command.

Step 6. Add commands to request site data from Urjanet

To request your company's site data from Urjanet, add another HTTP POST command, followed by a Pause chain event to allow time for the request to process:

  1. From Available connectors, select HTTP, and move POST to the canvas.
  2. Drag a link from Render text template to POST.
  3. Select the POST 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:
    urjanet-post-2.png
    Property Value
    Connector Select the same HTTP connector as earlier.
    Show response Select this checkbox.
    URL Enter https://api.urjanet.com/utility/sites/downloads/usages.
    Content type Enter application/json.
    Body text Enter the string to build the bearer token:
    1. Enter {"siteIds":.
    2. Select the Rendered text output of the Render text template command.
    3. Enter ,"startDate":"
    4. Select the JSON file output of the CSV to JSON command, and apply an Get value from JSON variable transformation:
      • In Output, select Text.
      • In Value, enter 0 and then ry.
    5. Enter -.
    6. Select the JSON file output of the CSV to JSON command, and apply an Get value from JSON variable transformation:
      • In Output, select Text.
      • In Value, enter 0 and then sm.
    7. Enter ","endDate":".
    8. Select the JSON file output of the CSV to JSON command, and apply an Get value from JSON variable transformation:
      • In Output, select Text.
      • In Value, enter 0 and then ry.
    9. Enter -.
    10. Select the JSON file output of the CSV to JSON command, and apply an Get value from JSON variable transformation:
      • In Output, select Text.
      • In Value, enter 0 and then em.
    11. Enter ","outputFormat":"CSV"}.
    Headers Add two headers:
    1. An authorization header:
      • In Key, enter authorization.
      • In Value, select Type and then Token from the Response output of the first POST command.
    2. An accept header:
      • In Key, enter accept.
      • In Value, enter application/json.
    Output schema Enter this sample response: 
    {  "createdDate" : "2023-09-19T15:11:09.944+00:00",  "entityId" : "1ee56fec-3a9c-d96e-a849-a6c381b08d2b",  "_links" : {    "download" : {      "href" : "https://api.urjanet.com/utility/downloads/1ee56fec-3a9c-d96e-a849-a6c381b08d2btpnu"    }  }}
  6. From Chain events, move Pause chain to the canvas.
  7. Drag a link from POST to Pause chain.
  8. Select the Pause chain event, and click Edit.
    urjanet-pause-chain.png
  9. In Basic info, enter a name and description to help identify the command.
  10. Enter the event's details, and click Save:
    Detail Value
    Wait time Enter 6.
    Wait time unit Select Seconds.

Step 7. Add commands to download the site data as a CSV

To download the site data from Urjanet as a CSV file, add another HTTP GET command and a Tabular Transformation Map headers command:

  1. From Available connectors, select HTTP, and move GET to the canvas.
  2. Drag a link from POST 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:
    urjanet-get-2.png
    Property Value
    Connector Select the same HTTP Request connector as earlier.
    Show response Select this checkbox.
    URL Select href from _links of the Response output of the second POST command.
    Headers Add this header:
    • In Key, enter authorization.
    • In Value, select Type and then Token from the Response output of the first POST command.
    Content type Enter application/json.
  6. From Available connectors, select Tabular Transformation, and move Map headers to the canvas.
  7. Drag a link from GET to Map headers.
  8. Select the Map headers command, and click Edit.
  9. In Basic info, enter a name and description to help identify the command.
  10. Enter the command's properties, and click Save:
    urjanet-map-headers.png
    Property Value
    Connector Select the same Tabular Transformation connector as earlier.
    Input file Select the Response output of the second GET command.
    Output file Enter urjanet.csv.
    Preview results Select this checkbox.
    Delimiter Select Comma.
    Input headers Add the headers of the CSV file downloaded from Urjanet, in this order:
    • Statement Id
    • Interval Start
    • Interval End
    • Service Type
    • Meter Id
    • Meter Number
    • Meter Status
    • Raw Account Number
    • Normalized Account Number
    • Provider Name
    • Service Address
    • Service City
    • Service State
    • Service Postal Code
    • Service Country
    • Site Code
    • Site Name
    • Site Number
    • Site Region
    • Site Sub Region
    • Site Facility Type
    • Site Address
    • Site City
    • Site State
    • Site Postal Code
    • Site Country
    • Tariff Name
    • Consumption
    • Consumption Unit Of Measure
    • Additional Provider Name
    • Additional Provider Account Number
    • Provider Classification
    • Total Charges
    • Total Charges Currency Code
    Output headers Add the corresponding headers for the table to contain the data, in this order:
    • statement_id
    • interval_start
    • interval_end
    • service_type
    • meter_id
    • meter_number
    • meter_status
    • raw_account_number
    • normalized_account_number
    • provider_name
    • service_address
    • service_city
    • service_state
    • service_postal_code
    • service_country
    • site_code
    • site_name
    • site_number
    • site_region
    • site_sub_region
    • site_facility_type
    • site_address
    • site_city
    • site_state
    • site_postal_code
    • site_country
    • tariff_name
    • consumption
    • consumption_unit_of_measure
    • additional_provider_name
    • additional_provider_account_number
    • provider_classification
    • total_charges
    • total_charges_currency_code

Step 8. Add Run chain events for the other chains

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 Map headers 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.
  6. Enter the chain runtime inputs, and click Save:
    urjanet-run-chain.png
    Input Value
    Table ID To provide the ID of the Wdata table to upload the data to, select the JSON file output of the CSV to JSON command, and apply a Get variable from JSON variable transformation:
    • In Output, select Text.
    • In Value, enter 0 and tid.
    File name To define the name of the dataset to upload, based on the data's time period:
    1. Enter Site Usage_.
    2. For the start month, select the JSON file output of the CSV to JSON command, enter a dash (-), and select the JSON file output of the CSV to JSON command again.
    3. Enter an underscore (_).
    4. For the end month, select the JSON file output of the CSV to JSON command, enter a dash (-), and select the JSON file output of the CSV to JSON command again.
    5. Enter .csv.
    6. 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.
    7. 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 em.
    Data file Select the Map headers output of the Map headers 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 Urjanet control sheet spreadsheet.
  11. In Chain to run, select the 3. Urjanet chain created earlier.
    urjanet-run-chain-2.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 table with the latest utility data:

  1. From the Control sheet section of the Urjanet control sheet spreadsheet, select the time period — months and year — of the data to download from Urjanet.
    urjanet-control-sheet.png
  2. From Chains, select the 1. Urjanet chain, and click Execute and Run chain.
    urjanet-execute.png
    Note: When you run this chain, it automatically runs the two other chains to update the Wdata table and Urjanet control sheet spreadsheet with the data.
Was this article helpful?
0 out of 0 found this helpful
Urjanet Utility Cloud® enables companies to act on their climate impact with accurate, on-demand data from utility pr...