Preserving Human-Driven Modifications in Smartsheet

jmyzk_cloudsmart_jp

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

https://app.smartsheet.com/b/publish?EQBCT=171fe7ee9e6d45f9ba6aadc78e8c52ff

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

https://app.smartsheet.com/b/publish?EQBCT=1ce3bcaebf7f4bd6985b13fee56315f5

• 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

https://app.smartsheet.com/b/publish?EQBCT=45a8faa22eee4188847a39be43566226

• 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.

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.

Isis Taylor

Thanks for sharing. This is a great idea, especially when there are multiple users with edit permissions on a sheet.