Solution Manager for Smartsheet | Getting Started Help Guide
- Dan Morales
- May 7, 2024
- 2 min read
Updated: Aug 7, 2024
What is Solution Manager?
Solution Manager is an application developed by SoftwareX for Smartsheet Solution Administrators and Creators. It provides a suite of tools to apply bulk updates, permissions, and actions on sheets and assets with similar structures across multiple assets at once. More details are available here
Who Can Access Solution Manager?
Solution Manager is available to all Smartsheet users in the US "Region". Interested users can begin a free Solution Manager plan via this link—no payment details are necessary. After logging in with your Smartsheet credentials, select a tool to deploy updates or navigate to the pricing page on the left-hand side rail to view the paid plan details. Solution Manager users can deploy changes to assets owned by users in their plan.
Is Solution Manager Secure?
Solution Manager handles and transforms Smartsheet data, but neither SoftwareX nor the Solution Manager app logs or stores customer data. To ensure user data is protected, we have implemented an OAuth Flow that requires users to sign in using their Smartsheet credentials. This process guarantees that users log in the same way they would to Smartsheet, inheriting any 2-factor authentication or SSO policies put in place by their System Administrators.
SoftwareX keeps fundamental customer details such as name and email in a secure database that is not exposed to the internet. Confidential data like API keys are encrypted, accessed as needed, and refreshed frequently. It's important to note that we do not retain any Smartsheet content, ensuring the privacy of our users' data.
Jump to Tool Overview Section
Bulk Column Manager Tool Overview
What is it?
Make bulk column updates a breeze with a tool that allows you to select multiple sheets and create, update, or delete the same column across all of them.
When Should you use it?
You need to perform any of the following actions across many sheets and don't want to go each individually to make the changes:
Add a column
Delete a column
Update column properties
Change column titles
Lock or hide column
Change column position
Change column description
Set a column formula
Change column type
How do you use it?
Click on the tool from the Solution Manager home screen
Select all the sheets you want to update from the workspace panel in the left-hand side.
Configure the column settings on the right-hand side.
Select Test Update, choose a sample sheet, and select Run Test.
Open the test and either continue with the deployment or refine your configuration settings if the test doesn't come out as expected. Note that the Solution Manager will take a copy of your sample sheet and deploy the update to the copy, so your live data won't be impacted until you approve the test.
Confirm the test passes and deploy your changes!
Bulk Edit Tool Overview
What is it?
Bulk Edit allows you to find and replace text in cells, columns, sheet names, and more across multiple assets in one click. Changes in internal terminology have never been easier to handle!
When Should you use it?
Anytime you need to update names of assets, lookup values, formulas, or anywhere text is outdated. Here is the list of all areas you can find and replace with the Bulk Edit tool:
Update Asset Names - Rename Sheets, Reports, Dashboards
Update formulas in cells or in sheet summary fields
Update Sheet Summary field titles and values
Update hyperlink names and URL's
Update column titles
Of course, to update text in a cell, you can use good old-fashioned find and replace.
How do you use it?
Click on the tool from the Solution Manager home screen
Select all the assets you want to update from the workspace panel. Note, If you select a workspace or folder, only that workspace/folder will be selected; all assets within that workspace will not be evaluated for bulk edit. If you select a folder only the folder will be selected as an asset. Content inside of dashboards and reports will not be updated.
Enter the text you want to find and replace on the right-hand side of the tool.
Toggle on each area of Smartsheet you want the Bulk Edit tool to find and replace.
Select Test Update, choose a sample sheet, and Run Test.
Open the test and either continue with the deployment or refine your configuration settings if the test doesn't come out as expected. Note that the Solution Manager will take a copy of your sample sheet and deploy the update to the copy, so your live data won't be impacted until you approve the test.
Confirm the test passes and deploy your changes!
Clone Cross-Sheet Reference "CSR" Tool Overview
What is it?
The "Clone Cross-Sheet References" tool efficiently adds or updates cross-sheet references on multiple sheets simultaneously. In contrast to the Smartsheet Sheet Reference Manager tool, which is ideal for creating and viewing cross-sheet references on one sheet, Clone CSR allows you to replicate the same cross-sheet reference on several sheets at once.
When Should you use it?
There are 2 main reasons you may need to use the Clone CSR tool:
Reason/Mode 1 - Clone the same range from a single source lookup sheet to many destination sheets
Let's say you have a main sheet that contains important information which is accessed by several other sheets through formulas. You may receive a request to add new information to these secondary sheets, but this can only be done by creating a new reference range for each sheet to retrieve data from the main sheet. With Clone CSR, you can replicate the same reference range across multiple destination sheets so that they look up the same data from the central sheet.
Reason/Mode 2 - Clone the same range relationship across "like" source and destination sheets
Imagine that you have a Smartsheet solution environment with tens to hundreds of folders, each representing a project, and all contain the same standardized sheet structures. If you are a Smartsheet Control Center user than you know this structure very well.
Within each folder, there is a metrics sheet that looks up data from another sheet within the same folder to run some metrics. Now, suppose you receive a request to collect a new metric across all of these projects...this technically means adding a new metric to this sheet that needs to reference data from the other sheet. This would require adding a formula, but it would also mean adding a new cross-sheet reference to point the range to the unique lookup sheet that exists within every formula, across the hundreds of folders.
Clone CSR is a useful tool that enables you to create cross-sheet references between source and destination sheets across multiple folders with similar structures. Additionally, by using Clone CSR in conjunction with Bulk Column Manager, you can add a new cross-sheet formula to all your projects with ease.
How do you use it?
Determine if you are going to Clone CSR from a Single Source to many sheets (Mode 1) or Clone CSR across "like" folders (Mode 2).
Click on the tool from the Solution Manager home screen.
Define your Mode by selecting either "Single Source" or "Folders/Workspaces" in the configuration settings on the right hand side of the tool.
Single Source Mode
Select 1 source sheet.
Select as many destination sheets to which the cross-sheet reference should be added to.
Name the cross-sheet reference.
Define the bounds of the cross-sheet reference by choosing the from/to column and row ranges.
Select Create Reference to deploy the range.
Folders/Workspaces Mode
Select "Projects" by selecting every folder or workspace that contains the standardized folder structure.
Example: Your Smartsheet solution is a project portfolio consisting of 50 folders, each representing a project and having a Metrics sheet and a Project Schedule sheet. In this step, you would select all 50 folders.
Select Sheets by defining the partial name of the source and destination sheets.
Example: Continuing on the previous example, all of the Metrics sheets have the word "Metric" and Project Schedule sheets have the word "project" but with a suffix of the project name, e.g., Metrics—Project AA11 and Project Schedule—Project AA11. Solution Manager doesn't need to know every sheet name only the consistent naming convention that is used as it uses this to identify the source and destination sheet in each folder.
Name the cross-sheet reference.
Define the bounds of the cross-sheet reference by choosing the from/to column and row ranges.
Select Create Reference to deploy the range
Command Access Tool Overview
What is it?
Quickly update the sharing permissions on multiple assets at once. Great for when you need to update the permissions on a large number of assets for a new member of your team or when transferring a solution to another team.
When Should you use it?
Use Command Access when you quickly need to share or unshare a user to multiple assets (sheets, reports, dashboards, or workspaces) when it's not effective or easy to do so from the core Smartsheet workspace panel.
How do you use it?
Click on the tool from the Solution Manager home screen.
Scroll through the workspace panel and select any sheet, report, or dashboard that you want to command access for.
When only 1 asset is selected you may view the existing collaborators on the asset from the Sharing panel on the right and you can
share additional collaborators via the + button
Modify permission levels for each collaborator
Transfer ownership (if you are an Owner of the asset) to another user via the permission pick list next to the collaborator's name.
When selecting multiple assets in the workspace panel the Sharing menu in the right-hand side of the tool will change and you can:
See a count of the assets you have selected
Enter the email address of the user that you want to grant access or unshare to these objects
Enter the desired permission level (or Unshare)
Click Submit to command your access settings and the updates will apply in Smartsheet
Review the summary of your settings and select Continue
Freeze Formula Tool Overview
What is it?
Create automations that will update the value of a cell to record the result of a formula. Great for 'freezing' the value of a formula at a specific point in time like end-of-month numbers or for large sheets where live formulas can cause performance issues.
When Should you use it?
Freeze Formula is a workflow-based automation tool. Use this tool to create multiple workflows to freeze formulas based on triggers or on a recurring schedule.
In either of the modes below you can create as many workflows as needed to freeze formulas across multiple columns and sheets.
Mode 1 - Create a workflow to freeze a formula on a specific sheet
Mode 2 - Integrate with a Smartsheet Control Center Blueprint to automatically create a new workflow for every newly provisioned sheet.
How do you use it?
Determine if you are going to create a workflow to freeze a formula on a specific sheet (Mode 1) or integrate with a Smartsheet Control Center Blueprint (Mode 2).
Click on the tool from the Solution Manager home screen.
Click Create New Workflow
Mode 1 - Specific Sheet
Select Click Here next to the Sheet setting. Disregard the SCC Integration Click Here button for this mode
Select the sheet from the workspace window pop-up and click ok. The name of the sheet you selected will appear next to the Click Here button.
Click Select Column next to the Formula Column setting. Then, select the specific column from the sheet in which you want the formula recorded.
Write or paste the formula in the open text box by the Formula setting.
Optional - Toggle on the Write Formula Instead of Value toggle if you want the formula written to the cell and not the recorded result. This can be useful for an experience similar to a column formula with dynamic or absolute references. It is recommended to leave it off unless you need the formulas to update instantly and are unable to use a column formula.
Move to the Configure the workflow schedule and/or Triggers section. You can add up to one schedule and as many triggers as necessary.
Click the Add Schedule button to configure the formula result (or formula) to be written on an hourly, daily, weekly, or monthly schedule. Recommended to create the formula in the sheet first and copy/paste it into the text box after you confirm it is working.
Click the Add Trigger button to configure the formula result (or formula) to be written. Repeat for as many triggers as needed.
Click Save
The workflow will now appear in the list of the Freeze Formula Workflows tool screen.
Mode 2 - Integrate with a Smartsheet Control Center Blueprint
Select Click Here next to the SCC Integration (Optional) setting.
Review the "Before You Begin" popup details, as there are specific configuration settings necessary in your SCC blueprint, and cell link direction.
Click Next and follow the remaining prompts to define the Summary or Intake sheet, including the column containing cell links, Blueprint folder, and cell link direction.
Click Confirm SCC Integration. "Configured" will appear next to the Click Here button if successfully configured.
Select Click Here next to the Sheet setting and select the specific blueprint template sheet in which the workflow should be configured to.
Click Select Column next to the Formula Column setting. Then, select the specific column from the sheet in which you want the formula recorded.
Write or paste the formula in the open text box by the Formula setting. Recommended to create the formula in the sheet first and copy/paste it into the text box after you confirm it is working.
Optional - Toggle on the Write Formula Instead of Value toggle if you want the formula written to the cell and not the recorded result. This can be useful for an experience similar to a column formula with dynamic or absolute references. It is recommended to leave it off unless you need the formulas to update instantly and are unable to use a column formula.
Move to the Configure the workflow schedule and/or Triggers section. You can add up to one schedule and as many triggers as necessary.
Click the Add Schedule button to configure the formula result (or formula) to be written on an hourly, daily, weekly, or monthly schedule.
Click the Add Trigger button to configure the formula result (or formula) to be written. Repeat for as many triggers as needed.
Click Save
The workflow will now appear in the list of the Freeze Formula Workflows tool screen.
Dynamic Dropdown Value Tool Overview
What is it?
Select a column within one of your sheets, then select as many destination sheets & columns as you would like. Now, you can update the column on your source sheet to update the dropdown values across all of your sheets in real-time. Great for company contact lists, customer lists, status-driven consistency, and unique identifier standardization.
When Should you use it?
Dynamic Dropdown Values is a workflow-based automation tool. Use this tool to create multiple workflows to update dropdown values from a central sheet based on triggers or on a recurring schedule.
In either of the modes below you can create as many workflows as needed to update dropdown values across multiple columns and sheets from a source sheet.
Mode 1 - Create a workflow to update dynamic dropdown values on a specific sheet.
Mode 2 - Integrate with a Smartsheet Control Center Blueprint to update dynamic dropdown values with newly provisioned sheets.
How do you use it?
Determine if you are going to create a workflow to update the dropdown values on a specific sheet (Mode 1) or integrate with a Smartsheet Control Center Blueprint (Mode 2).
Click on the tool from the Solution Manager home screen.
Click Create New Workflow
Mode 1 - Specific Sheet
Select Click Here next to the Source Sheet setting.
Select the sheet containing your source dropdown values from the workspace window pop-up and click "ok." The name of the sheet you selected will appear next to the Click Here button.
Click Select Column next to the Source Column setting. Then, select the specific column that contains the dropdown values that you want updated in other sheets.
Move to the Destination Columns section. From here on, you can add as many destination columns and sheets as needed.
Click the Add Destination button and then Select Sheet to select the destination sheet you want dropdowns dynamically updated in.
Click Select Column to specify the dropdown column on your destination sheet.
Repeat Steps 5 and 6 for as many destination columns and sheets as needed.
Click Save Workflow
The workflow will now appear in the list of the Freeze Formula Workflows tool screen.
Dropdowns will dynamically update on your destination sheet whenever the source is updated!
Mode 2 - Integrate with a Smartsheet Control Center Blueprint
Select Click Here next to the Source Sheet setting.
Select the sheet containing your source dropdown values from the workspace window pop-up and click "ok." The name of the sheet you selected will appear next to the Click Here button.
Click Select Column next to the Source Column setting. Then, select the specific column that contains the dropdown values that you want updated in other sheets.
Move to the Destination Columns section.
Select Click Here next to the SCC Integration (Optional) setting.
Review the "Before You Begin" popup details, as there are specific configuration settings necessary in your SCC blueprint, and cell link direction.
Click Next and follow the remaining prompts to define the Summary or Intake sheet, including the column containing cell links, Blueprint folder, and cell link direction.
Click Confirm SCC Integration. "Configured" will appear next to the Click Here button if successfully configured.
Click the Add Destination button and then Select Sheet to select the blueprint template sheet you want dropdowns dynamically updated in when SCC provisions the project.
Click Select Column to specify the dropdown column on your destination sheet.
Make sure to repeat Steps 9 and 10 for any other sheet within the template set. The dropdowns will be dynamically updated based on your source sheet.
Click Save Workflow
The workflow will now appear in the list of the Workflows tool screen.
A workflow will be created for newly provisioned projects, and dropdowns on your destination sheet will dynamically update whenever the source is updated!
Comments