With a Workiva connected solution, there are a number of ways to update data in a Workiva Spreadsheet. When using Wdata, an incoming connection can be created to refresh data in a spreadsheet from a Wdata Query. In this article, we explore the use of Chains to update data in a Spreadsheet using the Write Sheet Data and Overwrite Sheet Data Commands.
Common Command Inputs
The Write Sheet Data and Overwrite Sheet Data Commands have several inputs that are the same across both Commands.
| Input | Purpose |
| Spreadsheet ID | The ID of the Workiva Spreadsheet to which data will be written. |
| Sheet ID/Name |
The name or ID of the sheet within the Spreadsheet to which data is written. Names are CASE-SENSITIVE. |
| Data File |
The file containing delimited data that should be written to the sheet. CSV is the most common file format but other delimiters are supported. JSON data must be converted to a delimited format in order to use this Command. |
| Delimiter |
The file delimiter. Comma (,) is the most common but any delimiter is supported including semi-colon (;), pipe (|), or tab (\t). Be sure that fields whose data that contain the specified delimiter are enclosed in doubel quotes. For example, in a comma delimited file: Workiva,"Ames, IA, USA",WK |
| Region (Write Sheet Data) / Start Cell (Overwrite Sheet Data) |
The starting cell address where data will be written. Additional information about valid values are found in each command's specific section below. |
| Use Async |
When checked, the Command will perform an asynchronous update of the sheet. This means that the Chain will proceed to its next operation while the sheet update operation is still being performed within the platform. This option is recommended for large datasets but only if subsequent Chain operations are not reliant on the sheet update. |
| Use Platform API |
When checked, the Command uses the Platform API to perform the update rather than the legacy Spreadsheet API. The Platform API is recommended as this is the newer of the APIs and continues to be enhanced with additional features. |
Write Sheet Data
The Write Sheet Data Command is best used when needing to update specific regions in a sheet while preserving data in other regions of the sheet. The following table outlines the clearing behavior for each Region format specified.
| Format | Example | Effect |
| Single cell address | B2 |
Update a single cell within the sheet, no other cell values are impacted. This format is not recommended unless writing a single cell value. If the data file contains more than a single data point then the Command will fail when using this format. |
| Starting cell address, no ending cell or column | B2: |
Update the sheet beginning at the starting cell address. Sheet values to the left (column) and above (row) the starting cell address are preserved; all other values are cleared even if the data file does not contain cell values in the row or column. |
| Starting cell address, ending column with no ending row | B2:D |
Update the sheet beginning at the starting cell address. Sheet values to the left (column) and above (row) the starting cell address are preserved; all other values are cleared even if the data file does not contain cell values in the row or column. |
| Starting cell address, ending cell address | B2:D5 |
Replaces all cells within the specified range. Note: If the range specified in the Command is smaller (less rows or columns) than the data file being loaded then the Command will fail. |
Overwrite Sheet Data
The Overwrite Sheet Data Command is best used when needing to clear an entire block within a sheet. The following table outlines the clearing behavior for each Starting Cell format specified.
| Format | Example | Effect |
| Single cell address | B2 |
Sheet values to the left (column) and above (row) the starting cell address are preserved; all other values in the sheet are cleared and the data in the data file is written starting at the cell address specified. |
| Starting cell address, no ending cell or column | B2: |
Sheet values to the left (column) and above (row) the starting cell address are preserved; all other values in the sheet are cleared and the data in the data file is written starting at the cell address specified. |
| Starting cell address, ending column with no ending row | B2:D |
This format is not supported and will result in a Command execution error. |
| Starting cell address, ending cell address | B2:D5 |
Sheet values to the left (column) and above (row) the starting cell address are preserved; all other values in the sheet are cleared and the data in the data file is written starting at the cell address specified. The width (columns) and height (rows) of the data file are irrelevant. |
Try It Out
A Chain Template and testing Spreadsheet have been created that allow you to see the results of each of the above Start Cell/Region formats. To test:
- Download the testing spreadsheet from this link (25 KB) and import it into your Workspace.
- Create a testing Chain Utilizing the Use Chains to update a Workiva Spreadsheet Template found within 4. Quick Start Guides -> Enablement & Usage Examples Template folder.
- It is recommended to map the Spreadsheet ID Template Variable as a Chain Variable.
- Update the Spreadsheet ID Chain Variable before running the Chain in your Workspace.
- Ensure that the user in the Workiva Chains connection has the file:write scope and that the user has been assigned to the imported spreadsheet.
- Run the Chain and review the results. Expected results are outlined on the Expected Results sheet of the imported spreadsheet.