CLP - Creating and Reading a Control Sheet – Support Center

Using controls sheets is the most efficient way to store distinct variables used for a single workflow. Control sheets are valuable for allowing end users to specify inputs for a Chain without needing to edit the Chain itself. To use a control sheet, we first need to understand how to structure a control sheet and how to read data from the control sheet. In this Connected Learning Path, we will set up a control sheet and use a Workiva Command to retrieve data from the control sheet.

Primary Business Use Case	Orchestrating reporting workflows from a centralized location Simplifying workflow management for end users
Primary Learning Objective	Learn how to structure a control sheet and retrieve data from a control sheet using Chains
Prerequisites	Configure Workiva Connector Complete the CLP \| Uploading Data to a Table Path Complete the CLP \| Refresh List of Connections Provide permissions to the Spreadsheet for the associated Workiva API Grant User
Supporting Template	CLP \| Reading from a Control Sheet

Step 1: Create a Control Sheet

We will be creating a control sheet to store variables used to upload data to a Wdata Table and then refresh the connections for that data. Rather than specifying these variables as Runtime Inputs each time the Chain runs, we'll add our variables to a control sheet that the Chain will read and use throughout its run.

While this control sheet demonstrates uploading multiple files to a single Wdata Table and refreshing connections on a single Spreadsheet, the process can expanded to include multiple Tables and Spreadsheets by simply adding the unique ID of the Table or Spreadsheet to the control sheet.

Navigate to the Workiva Platform to create a new Spreadsheet
Click Create and select Spreadsheet
Name the Spreadsheet: CLP Control Sheet
- Name the first Sheet: Primary Control Sheet
In the Primary Control Sheet, create the following headers
- ActiveFlag
  - This column specifies if the workflow needs to run for that line in the control sheet
- FileName
  - This column indicates the name of the dataset as it will appear in the Wdata Table
- TableID
  - ID of the Table in Wdata where the data will be uploaded
- SpreadsheetID
  - ID of the Spreadsheet where the data will be refreshed
- FileLocation
  - Location of the file that will be downloaded
Populate the Sheet with the following values, filling in the <TableID> column with the ID of the CLP Employee Detail Table and the <SpreadsheetID> column with the ID of the CLP Employee Overview Spreadsheet, these IDs were created in previous Connected Learning Paths, refer to the prerequisites to see the required Connected Learning Paths.
- Check out this document for help finding the IDs

ActiveFlag,FileName,TableID,FileLocation
TRUE,EmployeeDetailsP01,<TableID>,<SpreadsheetID>,https://cs-sftp-training-bucket.s3.amazonaws.com/cs-training/transformation-qs/employee_detail_P01.csv
TRUE,EmployeeDetailsP02,<TableID>,<SpreadsheetID>,https://cs-sftp-training-bucket.s3.amazonaws.com/cs-training/transformation-qs/employee_detail_P02.csv
TRUE,EmployeeDetailsP03,<TableID>,<SpreadsheetID>,https://cs-sftp-training-bucket.s3.amazonaws.com/cs-training/transformation-qs/employee_detail_P03.csv

Step 2: Update Spreadsheet Permissions

To ensure effective communication between a Workiva Command and a Spreadsheet, the API Grant generated must be associated with a user who possesses owner or editor permissions on the Spreadsheet.

From the CLP Control Sheet Spreadsheet, click File in the ribbon on the top
Click Permissions, then Spreadsheet
- Provide access to whichever Workiva user is associated with the configured API Grant within the Workiva Connection

image (7).png

Step 3: Create a Chain

To use the Variables we created in the control sheet, we'll create a Chain that reads from it. Since we'll always use the same spreadsheet and sheet, we'll set up Chain Variables to store these details for consistent access.

Navigate to Chain Builder
Create a new Chain
- Name the Chain: CLP | Reading from a Control Sheet
Under Chain Variables, click the Plus sign twice to create two Chain Variables and configure them as the below:
- Variable 1:
  - Type: Chain Variable (cv)
  - Name: cv-Control Spreadsheet ID
  - Value: input the ID of the CLP Control Spreadsheet
    - To find the Spreadsheet ID, navigate to the CLP Control Sheet Spreadsheet
      - In the URL, select the Spreadsheet ID, which is found after “spreadsheet/” and before “/sheet”
      - For example, the Spreadsheet ID is bolded here: app.wdesk.com/a/QWNjb3VudB8yMDkwNzExMDQ0/spreadsheet/6c4a23966c3c4a10a3178ae5791db7fd/-1/sheet/3161ee0402ec4b9fa4e937e6f0f63b13
- Variable 2:
  - Type: Chain Variable (cv)
  - Name: cv-Control Sheet Name
  - Value: Primary Control Sheet
Save the Chain

Step 4: Get Sheet Data

We use the Get Sheet Data Command to kick off the Chain. This allows us to retrieve information from the control sheet to feed to the Chain.

Add a Get Sheet Data Command from the Workiva Connector to the Chain start area
Double click the Command to configure it
- Click on the Spreadsheet ID field
  - Within the Select a Variable panel, click the down arrow to expand Chain
  - Select the cv-Control Spreadsheet ID Chain Variable
- Click on the Sheet ID/Name field
  - Within the Select a Variable panel, click the down arrow to expand Chain
  - Select the cv-Control Sheet Name Chain Variable
- Region: feel free to specify a region. If left as “:” the command will retrieve data in the entire Spreadsheet
Save the Command

Step 5: 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 Get Sheet Data node to verify success
- The command should provide a .csv output of the data within the specified Sheet within the Spreadsheet, you see the .csv by clicking on the eyeball icon

Learn more about using control sheets by completing the next path: Using Values from a Control Sheet!

Workiva Support Center