Workiva Support Center

Step 1: Edit Existing Chain

• Navigate to Chain Builder and locate the Chain CLP | Reading from a Control Sheet
• Click the pencil button to edit the Chain
• Click Chain Settings in the top right 
  • Rename the Chain: CLP | Using Values from a Control Sheet
• Save the Chain

Step 2: Convert the Control Sheet to JSON

To effectively utilize the values in a control sheet, we will convert the output of the Get Sheet Data command from CSV format to JSON. This conversion establishes key-value pairs between the headers in the control sheet and the corresponding values in the rows below each header, enabling efficient iteration.

• Add a CSV to JSON Command from the JSON Connector to the Chain canvas
• Connect the Get Sheet Data Command to the CSV to JSON Command
• Double click the Command to configure it
  • Name the Command: Convert Control Sheet to JSON
  • Click on the Input File field
    • Within the Select a Variable panel, click the down arrow to expand Get Sheet Data
    • Select the Data output
  • Leave the delimiter field as Comma (,)
  • Save the Command
    

Step 3: Add a Command Group

Add a Command Group to the Chain to enable iteration over each row in the control sheet. This allows us to push each row of the control sheet through the series of Commands for the group.

• Add a Command Group to the Chain canvas
• Connect the Convert Control Sheet to JSON Command to the In section of the Command Group
• Double click the Command group to configure it
  • Name the Group: Control Sheet Iterator
  • Navigate to the Iterators tab
    • Enable the Iterations toggle and click on the Iterations field
      
      • Within the Select a Variable panel, click the down arrow to expand Convert Control Sheet to JSON
      • Select the JSON output 
  • Save the Command

Now that the Command has been configured, it will iterate through each row of the control sheet and provide an output for each line.

Step 4: Add a Conditional Command

Within a control sheet, users can determine if they’d like a certain row to be executed within a workflow. This is leveraged via the ActiveFlag column. We will use a Conditional Command to determine if the workflow needs to continue for each row in the control sheet.

• Add a Conditional Command Chain Event to the Chain canvas
• Connect the Group Start within the Control Sheet Iterator Group to the Conditional Command
  
• Double click the Command to configure it
  • Name the Command: Active Flag?
  • Leave the condition as AND and click the + RULE button
  • Double check the data type is set to String and change the operation to test from Is Blank to =
  • Click into the open field on the left side of the command (the value to test)
    • From the Select a Variable panel, click the down arrow to expand Group Iterator 
    • Select <> JSON File Iteration
    • Click the green JSON File Iteration pill to transform the Variable
      • Click Select a Transformation and choose Get Value from JSON
      • Press the green + sign
      • In the value field type the ActiveFlag header exactly as it is within the control sheet and hit enter
      • Select another transformation and choose Lowercase
      • Press the green + sign
      • Click Accept

• • Back in the Conditional Command, on the right side (the value to compare), type the word true
    • Ensure that this is in lower case, since the variable transformation applied earlier makes the Active Flag value all lower case. 
• Save the Command

Step 5: Feed the Outputs to a Command

At this point, the control sheet iterator is setup to provide values to any Command that exists in the Group Start of the Command Group. We will leverage a Handlebars Command as a placeholder to display the retrieved values, which can also be used iteratively in downstream Commands.

• Add a Render Text Template Command from the Handlebars Connector to the Chain canvas
• Connect the Active Flag? Command to the Render Text Template Command
• Double click the Command to configure it
  
  • Name the Command: Capture Control Sheet Values
  • In the Template field, type out the following information:
    • Top Row: Control Sheet Information
    • Second Row: File Name: <JSON File Iteration>
      • Replace the <JSON File Iteration> from the Select a Variable panel, click the down arrow to expand Group Iterator 
      • Select <> JSON File Iteration 

• • • • Click the green JSON File Iteration pill to transform the Variable
        • Click Select a Transformation and choose Get Value from JSON
          
          
        • Press the green + sign
        • In the value field type FileName exactly as it is within the control sheet and hit enter
        • Click Accept

• • Repeat the steps for rows third, fourth and fifth
    • Replace the <JSON File Iteration> placeholder with the variable from the control sheet.
      • Table ID: <JSON File Iteration>
        •  Follow the same steps as before, but use TableID instead of FileName.
      • Spreadsheet ID: <JSON File Iteration>
        •  Follow the same steps as before, but use SpreadsheetID instead of FileName.
      • File Location: <JSON File Iteration>
        •  Follow the same steps as before, but use FileLocation instead of FileName.
• Save the Command

Note: Follow the outlined steps to extract any header and retrieve the corresponding row values from a control sheet.

Step 6: Test the Exercise

Now that the Chain is complete, test the result.

• Publish the Chain
• Click Execute and then select Run Chain
• Once the Chain has completed, click the Capture Control Sheet Values Command to verify success
  • Let the Chain fully complete before clicking the Command
• There will be a field that states Value 1
  • If you click this field, it will show that the Command processed three different values, these values are the three rows of data from the control sheet
• Click the Log field for each individual value and verify that it matches the values within the control sheet

Value 1:

Value 2:

Value 3:

Learn more about using control sheets by completing the next path: Running Processes and Logging Results within a Control Sheet!