When creating integrations that utilize Spreadsheets, it is important to consider their limits during the architecture phase of your build. In this post, I will focus on the max cell limit. Spreadsheets have a maximum cell limit of 2,000,000 cells across all Sheets within a Spreadsheet. It is easy to quickly bump up to this limit if you're sending across large sets of data or regularly updating data in a Spreadsheet via Chains or directly via the Platform Spreadsheet API. Each Spreadsheet is allocated its own 2,000,000 cell count. With that in mind, it's important to have a roll forward process implemented to have a successful long term integration. Below is an example process which reduces and eliminates manual interaction depending on the steps you choose to take:
Roll Forward Process Option 1
The first option is to manually create new Spreadsheets and Sheets as needed and update your Chains/integrations to use the new Spreadsheet and Sheet IDs. While this will work in all cases, there are additional options that can reduce the need for manual interaction.
Roll Forward Process Option 2
This second option builds in some automation to start reducing manual intervention in the Spreadsheet roll forward process. The first element we'll need is a "Master" Spreadsheet. This master Spreadsheet will have the sole purpose of housing the IDs of the current Spreadsheet(s) and Sheet(s) that are in use by the Chain/integration (Alternatively, a Wdata Table could be utilized instead of a Spreadsheet). Below is an example screenshot:
In this example, the master Spreadsheet contains the necessary IDs of three Spreadsheet/Sheet combinations. Cells A2, A3, and A4 are locked because "Balance Sheet" must remain on row 2, "Income Statement" must remain on row 3, and "Cash Flow Statement" must remain on row 4 for next steps. I have also adjusted permissions to this master Spreadsheet so only the people who will be involved with the roll forward process have access to make edits. The username associated to the OAuth2 Grant also has Viewer permissions at minimum (Editor permissions at minimum for option 3 below).
During the roll forward process, after a new Spreadsheet and Sheet is created, their IDs will need to replace the old IDs in the master Spreadsheet. For example, if I need to roll forward the Spreadsheet for "Cash Flow Statement", I'd create a new Spreadsheet and Sheet, note the values of the new IDs, then copy and paste them into row 4 column B and C of my master Spreadsheet. This can be repeated as needed for any other Spreadsheets. When completed, the master Spreadsheet will have all of the new Spreadsheet/Sheet IDs from the roll forward process.
The final step is to modify the Chain (or integration) to read the values in the master Spreadsheet so the Chain will know which Spreadsheet/Sheet to interact with. In this example, I will demonstrate how to do this in Chains but the principles are the same if using the API directly. First, I will use the Workiva command Get Sheet Data to get the IDs in from the master Spreadsheet. Get Sheet Data requires both the "Spreadsheet ID" and "Sheet ID/Name" of the master Spreadsheet. I will also specify the region as "B2:C4" since I only am interested in the IDs and not the headers or descriptions. Below is a screenshot of the configured node:
When Get Sheet Data is ran, it will return the following data in a csv:
The next step is to extract the above values from the csv. Earlier it was mentioned that cells A2, A3, and A4 were locked. Since they are locked, we know that the first row of returned values correspond to the Balance Sheet, the second row corresponds to Income Statement and so on. For this example, I want my Chain to get the IDs of the current Spreadsheet/Sheet for the Income Statement. I will use two instances of the Tabular Transformation Command Extract Value. To avoid confusion, rename the Extract Value commands to something meaningful. I will rename mine to "Income Statement - Spreadsheet ID" and "Income Statement - Sheet ID". Next, the Extract Value commands require an "Input file" which is the data retrieved from the Get Sheet Data node. Finally, I must specify the location of the IDs in the csv data that Get Sheet Data returned. I know the the second row of data contains the values I need. So for the Extract Value node that will retrieve the Spreadsheet ID, I will set "Column Index" to 1 and "Row Index" to 2. Similarly for the Extract Value node that will retrieve the Sheet ID, I will set "Column Index" to 2 and "Row Index to 2. A simple formula to know which Row Index to set is to look at the row in the master Spreadsheet that you're trying to get the values from and subtract 1. Below are screenshots of the two Extract Value nodes configured to get the IDs of the Income Statement Spreadsheet and Sheet:
Now we have the IDs stored in variables associated to the Extract Value nodes. I can then use those variables throughout my Chain to interact with the Income Statement Spreadsheet and Sheet. Later, during the roll forward process as I update the master Spreadsheet with new ID, my Chain will automatically retrieve the new IDs and start using the new Spreadsheet/Sheet. Below is a screenshot of the nodes feeding an Overwrite Sheet command as an example:
Optional: The above method could be adjusted to store the full URL path in the master Spreadsheet and parse that for the IDs.
Roll Forward Process Option 3
If your goal is to reduce or eliminate manual intervention more than Option 2 with the Spreadsheet roll forward process, option 3 provides additional automation. First step is to implement and complete Option 2. In this option, we'll work to automate the creation of the new Spreadsheet and Sheets and automatically update the master Spreadsheet with their IDs.
I will begin by creating a new Chain that will build new Spreadsheets and Sheets for me during the roll forward process. In the new Chain, begin by using the Workiva Command Create Spreadsheet. This node requires a unique name for the Spreadsheet. Because my roll forward process needs to happen at the first of each month, I will ensure the name is unique by dynamically adding the current month and year to the name using the Runtime variable "System.DateTime". Below is a screenshot of the configuration:
After the Create Spreadsheet command completes, it will return metadata about the new Spreadsheet including it's ID. Next, using the Workiva Command Create Sheet, I will create new Sheets for the Balance Sheet and a new Sheet for Income Statement Sheet since I have them in the same Spreadsheet in the example from option 2. Below is the node configurations:
When the two Create Sheet commands finish, they will return the metadata of the new Sheets including the new IDs. We now have the IDs of the new Spreadsheet and Sheets to update the master Spreadsheet with. We can put them together in the expected format that the master Spreadsheet will want. The format is "Spreadsheet ID,Sheet ID". This can be done with the File Utilities Command Create File. In the Text field, build the structure of the comma separated IDs. In this example I created a new Sheet for Balance Sheet and Income Statement which live inside the same Spreadsheet. So the format is as follows:
New Spreadsheet ID,New Balance Sheet ID
New Spreadsheet ID,New Income Statement Sheet ID
Below is a screenshot of the configured Create File node highlighting the ID variables in red boxes:
Finally, the output from Create File can be used to overwrite the master Spreadsheet to update it with the new IDs. This can be done using the Workiva Command Overwrite Sheet Data. To configure this node, provide both the Spreadsheet ID and Sheet ID of the master Spreadsheet. Select the Created File output from the Create File node for the "Data File" field and chose where within the Sheet to start writing the new data to. In this case, the Balance Sheet related IDs start at cell "B2" in my master Spreadsheet. Below is a screenshot of this node configured:
Lastly, I want to schedule this chain to run automatically on the first day of each month in the early morning hours so it is ready to go when the work day begins.
Below is the completed Chain. Additional steps could be taken to include error handling and notification emails etc.
Please sign in to leave a comment.