|
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

Chain template: Run query with a parameter value from a spreadsheet

Chain template: Run query with a parameter value from a spreadsheet

  • Last updated on

With this chain, you can retrieve a query's parameter value from a spreadsheet—such as to focus on a specific entity or time period—and then run the query and export its results to another sheet.

run-query-parameter-sheet_05.png

Ingredients

To create this chain, you'll need:

Step 1. Create the chain

  1. In Chain Builder, from the Chains  device_hub tab, click Create chain.
  2. Under Setup, enter a unique name and description to help identify the chain and its intent.
  3. Under Variables, add variables for the spreadsheets and query to work with.
    Name Value
    Query ID Enter the ID of the query to run.
    Tip: In the query's URL, the ID appears after queries/.
    Spreadsheet ID Enter the ID of the spreadsheet with the value to retrieve for the query's parameter.
    Tip: In the spreadsheet's URL, its ID appears after spreadsheet/.
    Sheet ID Enter the ID of the sheet with the spreadsheet with the value to retrieve for the query's parameter.
    Tip: In the spreadsheet's URL, the sheet's ID appears after sheet/.
    Spreadsheet URL To export the query results to an existing spreadsheet, enter its full URL.
    Note: If the chain will always create new spreadsheets based on the query's results, this variable is unnecessary.
  4. Click Save.

Step 2. Start with the Get sheet data command

To retrieve the value for the query parameter from the cell of a spreadsheet, start the chain with a Get sheet data command.

  1. Under Available Connectors, select Workiva, and move Get sheet data to Start.
  2. Click Edit for the command, and enter its name and description under Basic info.
  3. Under Command properties, select the Workiva connector to perform the command.
    run-query-parameter-sheet_01.png
  4. For Spreadsheet ID, select the chain variable with the spreadsheet ID for the parameter value.
  5. For Sheet Name/ID, select the chain variable with the sheet ID for the parameter value.
  6. For Region, enter the cell to retrieve the value from, in the A1-representation of [column][row].
  7. For Value Style, select whether to retrieve the cell's raw or calculated value. For example, if the cell contains 1 + 1, select Raw value for the explicit formula, or Calculated for the sum of 2.
  8. For Revision, enter the revision of the spreadsheet to retrieve the value from. For the current version, enter -1.
  9. Click Save.

Step 3. Add an Extract value command

To use the value retrieved from the spreadsheet with the query parameter, add an Extract value command.

  1. Under Available Connectors, select Tabular Transformation, and move Extract value to the canvas.
  2. Drag a link from Start to Extract value, and click Edit for the command.
  3. Under Basic info, enter a name and description to help identify the command.
  4. Under Command properties, select the Tabular Transformation connector to perform the command.
  5. For Input file, select the Data output of the Get sheet data command.
    run-query-parameter-sheet-chain_02.png
  6. Since the Get sheet data command retrieves only the one cell's value, leave Column index, Delimiter, and Row index with their respective default values of 0, Comma, and 1.
  7. Click Save.

Step 4. Add a Run query command

To apply the retrieved value as a parameter and then run the query, add a Run query command.

  1. Under Available Connectors, select Workiva, and move Run query to the canvas.
  2. Drag a link from Extract value to Run query, and click Edit for the latter.
  3. Under Basic info, enter a name and description to help identify the command.
  4. Under Command properties, select the same Workiva connector as Get sheet data.run-query-parameter-sheet-chain_03.png
  5. For Query ID, select the chain variable for the query ID.
  6. Under Parameters, click Add, and apply the value from the spreadsheet to the query's parameter:
    • In Key, enter the name of the parameter to use the value.
    • In Value, select the Value output of the Extract value command.
  7. Click Save.

Step 5. Add an Export query result command

To export the query's results to Spreadsheets, add an Export query result command.

  1. Under Available Connectors, select Workiva, and move Run query to the canvas.
  2. Drag a link from Run query to Export query result, and click Edit for the latter.
  3. Under Basic info, enter a name and description to help identify the command.
  4. Under Command properties, select the same Workiva connector as Get sheet data.
    run-query-parameter-sheet_04.png
  5. For Query result ID, select ID from the Query result output of the Run query command.
  6. For Spreadsheet URL, specify whether to export the results to a new or existing spreadsheet:
    • To export to a new spreadsheet, leave Spreadsheet URL blank.
    • To export to an existing spreadsheet, select the chain variable for the spreadsheet's URL.
  7. Click Save and Publish.
Was this article helpful?
1 out of 4 found this helpful
With this chain, you can retrieve a query's parameter value from a spreadsheet—such as to focus on a specific entity ...