Create new spreadsheets for connected reporting as a chain

Each spreadsheet can contain up to 2,000,000 cells. To avoid exceeding this limit while maintaining processes to automate connected reporting in Spreadsheets, use these chains. For example, if you use Spreadsheets to track financial data—such as balance sheets and income statements—for connected reporting or roll-forward processes, run these chains periodically to:

• Create new spreadsheets to continue data before you meet the cell limit.
• Extract and maintain the spreadsheets' IDs to ensure connected reporting through other chain commands, such as an Overwrite Sheet Data command to update the spreadsheet's data.

Ingredients

To create this chain, you'll need:

• Access to spreadsheets with the type of data to track with new sheets; for example, to maintain financial data, your balance sheet, income statement, and cashflow statement
• A Workiva connector
• A Tabular Transformation connector
• A File Utilities connector

Create a spreadsheet to track spreadsheet IDs

In Spreadsheets, create a spreadsheet to track the IDs of spreadsheets used with your financial accounting, and their respective sheets' IDs:

1. In the spreadsheet, include columns for Description, Spreadsheet ID, and Sheet ID, and rows for each accounting, such as your balance sheet and your income and cashflow statements. For example:

   Description	Spreadsheet ID	Sheet ID
   Balance sheet
   Income statement
   Cashflow statement

2. To ensure consistent mapping for the chain, lock the cells in the first column.
3. Copy the IDs for the spreadsheets and sheets where you track your balance sheet, income statement, and cashflow statement, and paste them in their respective cells.

   Note: In a spreadsheet's URL, its ID follows /spreadsheet/, and the sheet's ID follows /sheet/.

4. Set the spreadsheet's permissions so collaborators who need the IDs—such as those involved in the roll-forward process—are Editors or Owners.
5. Note the IDs of this spreadsheet and sheet.

Create a chain to automatically create new spreadsheets

For each spreadsheet you use to track financial data, create a chain to periodically create its new spreadsheet.

For example, if you track the balance sheet and income statement as separate sheets within the same spreadsheet:

Step 1. Create the chain

1. In Chain Builder, from Build  device_hub, Chains, click Create Chain.
   
2. Under Setup, enter a unique name—such as Create New Financial Spreadsheets—and a description to help identify the chain and its intent.
3. To enable multiple instances of the chain to run at once, such as to create several new spreadsheets at the same time, select Allow concurrent runs.
4. Under Variables, add variables for the IDs of the spreadsheet and sheet used to track IDs, and enter their IDs.
5. Click Save.

 

Step 2. Start with a Create Spreadsheet command

1. Under Available BizApps, click Workiva, move Create Spreadsheet to Start, and click Edit  create.
   
2. Under Basic Info, enter a unique name and description to help identify the command.
3. Under Command Properties, enter a unique name to help identify the spreadsheet's data, such as "Balance sheet and Income statement". To ensure a unique name each time the chain runs, append a System.DateTime runtime variable.
4. Click Save.

 

Step 3. Add Create Sheet commands

For each sheet within the spreadsheet, add a Create Sheet command. For example, if the spreadsheet contains separate sheets for the balance sheet and the income statement, add a Create Sheet command for each.

1. Under Available BizApps, click Workiva, and move Create Sheet to the canvas.
2. Drag a link from Create Spreadsheet to Create Sheet, and click Edit  create.
   
3. Under Basic Info, enter a unique name and description to help identify the command.
4. In Spreadsheet ID, select the Id property of the Spreadsheet Metadata output of the Create Spreadsheet command.
5. In Sheet Name, enter a unique name to help identify the sheet's data, such as "Balance Sheet" or "Income Statement".
6. In Sheet Index, enter the sheet's position within the spreadsheet, starting with 0. 
7. Click Save.

 

Step 4. Add commands to track the new spreadsheet IDs

To update the spreadsheet used to track IDs with the IDs of the new spreadsheet and its sheets:

1. Under Available BizApps, click File Utilities, and move Create File to the canvas.
2. Drag a link from each Create Sheet to Create File, and click Edit  create.
   
3. Under Basic Info, enter a unique name and description to help identify the command.
4. In Text, select the Id properties of the Spreadsheet Metadata and Sheet Metadata outputs from the Create Spreadsheet and Create Sheet commands, separated by a comma.

   Note: If the chain creates multiple sheets, enter each comma-separated pairing of IDs on a separate line, in the same order as their data's rows in the spreadsheet used to track spreadsheet IDs.

5. Click Save.
6. Under Available BizApps, click Workiva, and move Overwrite Sheet Data to the canvas.
7. Drag a link from Create File to Overwrite Sheet Data, and click Edit  create.
   
8. Under Basic Info, enter a unique name and description to help identify the command.
9. In Spreadsheet ID and Sheet ID, select the Chain variables for the IDs of the spreadsheet and sheet used to track spreadsheets' IDs, respectively.
10. In Data File, select the Created File output of the Create File command.
11. In Delimiter, select a comma (,).
12. In Start Cell, enter the cell of the spreadsheet used to track spreadsheets' ID to start to update. In our example, to start with the IDs for the balance sheet, enter B2.
13. Click Save and Publish.

 

Step 5. Schedule the chain

To run the chain automatically at a periodic interval for reporting or based on the rate at which you meet spreadsheet's cell limit:

1. Click Chain Settings.
2. Under Schedules, click Add, and set up when and how often to run the chain.
3. Click Save.

After this chain runs, set up the new spreadsheet with its applicable data. For example, if this chain supports the quarterly roll-forward of financial data, update the spreadsheet with the current period's balance and income data. To help automate this process, you can use this chain with a Run Chain event in another chain to update the new spreadsheet, such as with an Overwrite Sheet Data command.

Create a chain to extract new spreadsheets' IDs as variables

To enable automated connected reporting, create a chain to automatically maintain the IDs of spreadsheets used to track the data used in the reporting. This chain enables you to easily create variables to use as spreadsheet and sheet IDs.

Use this chain with a Run Chain event in another chain to use these variables with commands later in a chain.

Step 1. Create the chain

1. From Chains , click Build  device_hub, Chains, and Create Chain.
2. Under Setup, enter a unique name—such as Extract Spreadsheet IDs—and a description to help identify the chain and its intent.
3. To enable the chain to run in multiple Run Chain events, select Allow concurrent runs.
4. Under Variables, add variables for the IDs of the spreadsheet and sheet used to track IDs, and enter their IDs.
5. Click Save.

 

Step 2. Start with a Get Sheet Data command

To retrieve the spreadsheet and sheet IDs for data as comma-separated values (CSV):

1. Under Available BizApps, click Workiva, move Get Sheet Data to Start, and click Edit  create.
   
2. Under Basic Info, enter a unique name and description to help identify the command.
3. In Spreadsheet ID and Sheet ID/Name, select the Chain variables for the spreadsheet ID and sheet ID, respectively. 
4. In Region, enter the range of the cells with the spreadsheet and sheet IDs, but not the headers or descriptions. In our example, to retrieve the IDs for the budget sheet and income statement, B2:C4.
5. Click Save.

The Get Sheet Data command's Data CSV output includes only the spreadsheet and sheet IDs, in lines that correspond with the rows of the spreadsheet used to track IDs. In our example, the first line includes IDs for the balance sheet; the second line, the income statement's IDs.

Step 3. Add commands to extract the spreadsheet and sheet IDs

For each pairing of IDs to extract, add two Tabular Transformation Extract Value commands, one each for the ID of its spreadsheet and sheet. For example, to extract the IDs for the income statement data:

1. Under Available BizApps, click Tabular Transformation, and move Extract Value to the canvas.
2. Drag a link from Start to Extract Value, and click Edit  create.
   
3. Under Basic Info, enter a unique name and description to help identify the command, such as Extract Income Statement Spreadsheet ID.
4. In Input, select the Data output of the Get Sheet Data command.
5. In Column Index, enter 1 for the spreadsheet ID.
6. In Delimiter, select Comma.
7. In Row Index, enter the line number that corresponds with the applicable data, such as 2 for the Income statement row.
8. Click Save.
9. Under Available BizApps, click Tabular Transformation, and move another Extract Value to the canvas.
10. Drag another link from Start to the second Extract Value, and click Edit  create.
    
11. Under Basic Info, enter a unique name and description to help identify the command, such as Extract Income Statement Sheet ID.
12. In Input, select the Data output of the Get Sheet Data command.
13. In Column Index, enter 2 for the sheet ID.
14. In Delimiter, select Comma.
15. In Row Index, enter the line number that corresponds with the applicable data, such as 2 for the Income statement row.
16. Click Save and Publish.

 

Step 4. Use the chain in a Run Chain event

After you publish the chain, you can use it with a Run Chain event within another chain, such as to use the variable within a later command. In the parent chain:

1. Click Chain Settings, add a variable for the table ID, and click Save.
2. Click Events insert_invitation, move Run Chain device_hub to the canvas, drag a link from its preceding node, and click Edit create.
3. Under Basic Info, enter a unique name and description to help identify the event.
4. Under Chain to Run, select the chain created to extract IDs.
5. Click Save.