--- Introduce Yourself
--- Answer the Question of the Month
--- Connect with your Peers
Preserving Human-Driven Modifications in Smartsheet
Hi Community.
Reflecting on the solution to kbloch's question regarding unwanted automation changes in Smartsheet, I devised a method to track human-driven modifications. I want to share this approach with the community, hoping it can enhance how we manage and restore data integrity.
Step-by-Step Process to Preserve Modification Records
Creating a backup and history of changes in Smartsheet, particularly for the "Modified" and "Modified By" columns, is crucial for maintaining accurate records, especially when dealing with unintended modifications due to automation. Here’s a breakdown of how to implement a strategy to preserve these details:
Modified, Modified By Report
Copy Rows Workflow Automation
- Create a Workflow with Trigger Conditions:
- Objective: Trigger a workflow when any row is modified.
- Trigger Conditions: Set up the workflow to trigger on changes in any cell within a row. Add a condition to ensure it does not trigger if the modification is made by the automation account (e.g., automation@smartsheet.com), thus capturing only human-driven changes.
- Copy Rows to a Backup or Record Sheet:
- Objective: When a row meets the trigger condition, copy it to another sheet dedicated to tracking changes.
- Details: This preserves the row's state at the time of modification. Ensure the row ID or any unique ID is maintained for easy reference.
Use System and Helper Columns
Original Sheet
- Modified By Text(Original Sheet): A helper column in the original sheet can be used with a formula like
=[Modified By]@row
to capture the user who made the change. This value is then copied to the backup sheet, maintaining the integrity of the "Modified By" data. - Created Column (Record Sheet): Automatically captures the date and time when the row was copied to the backup sheet.
Record Sheet
- Highlight the Latest Changes: (Optional)
- Latest Column: In the backup sheet, implement a formula to identify the most recent change for each row. For example,
=IF(Created@row = MAX(COLLECT(Created:Created, [Row ID]:[Row ID], [Row ID]@row)), 1)
. This formula checks if the "Created" date of the row is the most recent among all entries with the same Row ID.
- Latest Column: In the backup sheet, implement a formula to identify the most recent change for each row. For example,
Modified, Modified By Report
- Create a Report:(Top Image)
- Objective: Use both the original and backup sheets to compile a report.
- Report Configuration: Group data by Row ID and include relevant columns from both sheets—particularly the "Modified By Text" and "Created" columns from the backup sheet and any applicable data from the original sheet.
Advantages of This Method
- Accuracy: Ensures that human modifications are recorded precisely, without interference from automated processes.
- Recovery: Facilitates easy recovery of previous data states if an unintended modification occurs.
- Audit Trail: Provides a clear historical record for audit purposes, which can be critical for tracking changes in sensitive or critical data environments.
By implementing these steps, you can ensure that your Smartsheet data remains accurate and that all changes are traced back to their origins, providing transparency and accountability in data management.
Limitation
Even if you change the Record Sheet's Created system column name, the report shows it as "Created", so you need to interpret the Created as the Modified (Date) of the Original Sheet.
Comments
-
Thanks for sharing. This is a great idea, especially when there are multiple users with edit permissions on a sheet.
Isis Taylor
Business Analyst Senior
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives