Make a function that deletes values of column E of tab CoinList from current sheet, then…
- User
Make a function that deletes values of column E of tab CoinList from current sheet, then restores the values of column E by doing a control Z. If this is unsafe you will propose me your secured method.
- Assistant
Performing an undo (Control + Z) operation programmatically is not feasible in both Google Sheets and Microsoft Excel. Scripts in these environments don't have the capability to trigger or interact with the native undo stack. Attempting to manipulate the undo history can lead to unpredictable results and potential data loss.
Instead of relying on an undo operation, a safer and more reliable approach is to create a backup of the data before making any changes. This way, you can restore the original values whenever needed without risking data integrity.
Below are methods for both Google Sheets (using Apps Script) and Microsoft Excel (using VBA) to achieve this.
For Google Sheets: Using Apps Script
1. Backup and Delete Column E
This function will:
- Backup the values from column E in the "CoinList" sheet.
- Clear the contents of column E.
function backupAndDeleteColumnE() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName("CoinList"); const lastRow = sheet.getLastRow(); const range = sheet.getRange(1, 5, lastRow, 1); // Column E const values = range.getValues(); // Store the backup in Script Properties const scriptProperties = PropertiesService.getScriptProperties(); scriptProperties.setProperty("Backup_ColumnE", JSON.stringify(values)); // Clear the contents of column E range.clearContent(); SpreadsheetApp.getUi().alert("Column E has been cleared and backed up."); }2. Restore Column E from Backup
This function will:
- Retrieve the backup from Script Properties.
- Restore the values back to column E.
function restoreColumnE() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName("CoinList"); const lastRow = sheet.getLastRow(); const range = sheet.getRange(1, 5, lastRow, 1); // Column E // Retrieve the backup const scriptProperties = PropertiesService.getScriptProperties(); const backup = scriptProperties.getProperty("Backup_ColumnE"); if (backup) { const values = JSON.parse(backup); range.setValues(values); // Optionally, remove the backup after restoration scriptProperties.deleteProperty("Backup_ColumnE"); SpreadsheetApp.getUi().alert("Column E has been restored from backup."); } else { SpreadsheetApp.getUi().alert("No backup found to restore."); } }Usage Instructions
-
Access Apps Script:
- Open your Google Sheet.
- Navigate to
Extensions>Apps Script.
-
Add the Scripts:
- Paste the above functions into the script editor.
- Save the script with an appropriate name.
-
Create Custom Menus (Optional):
- For easier access, you can create custom menu items to trigger these functions.
function onOpen() { const ui = SpreadsheetApp.getUi(); ui.createMenu('Custom Actions') .addItem('Backup & Delete Column E', 'backupAndDeleteColumnE') .addItem('Restore Column E', 'restoreColumnE') .addToUi(); }- Add the
onOpenfunction to ensure the menu appears when the spreadsheet is opened.
-
Authorize the Script:
- The first time you run the script, you'll need to authorize it to access your spreadsheet data.
For Microsoft Excel: Using VBA
1. Backup and Delete Column E
This VBA macro will:
- Backup the values from column E in the "CoinList" sheet into a hidden sheet named "Backup".
- Clear the contents of column E.
Sub BackupAndDeleteColumnE() Dim ws As Worksheet Dim backupWs As Worksheet Dim lastRow As Long Set ws = ThisWorkbook.Sheets("CoinList") lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row ' Check if Backup sheet exists; if not, create it On Error Resume Next Set backupWs = ThisWorkbook.Sheets("Backup") On Error GoTo 0 If backupWs Is Nothing Then Set backupWs = ThisWorkbook.Sheets.Add(After:=ws) backupWs.Name = "Backup" backupWs.Visible = xlSheetVeryHidden ' Makes the sheet hidden End If ' Backup the data backupWs.Range("A1:A" & lastRow).Value = ws.Range("E1:E" & lastRow).Value ' Clear Column E ws.Range("E1:E" & lastRow).ClearContents MsgBox "Column E has been cleared and backed up." End Sub2. Restore Column E from Backup
This VBA macro will:
- Retrieve the backup from the "Backup" sheet.
- Restore the values back to column E in the "CoinList" sheet.
Sub RestoreColumnE() Dim ws As Worksheet Dim backupWs As Worksheet Dim lastRow As Long Set ws = ThisWorkbook.Sheets("CoinList") ' Check if Backup sheet exists On Error Resume Next Set backupWs = ThisWorkbook.Sheets("Backup") On Error GoTo 0 If backupWs Is Nothing Then MsgBox "No backup sheet found.", vbExclamation Exit Sub End If lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row ' Restore the data ws.Range("E1:E" & lastRow).Value = backupWs.Range("A1:A" & lastRow).Value MsgBox "Column E has been restored from backup." End SubUsage Instructions
-
Access VBA Editor:
- Open your Excel workbook.
- Press
ALT + F11to open the VBA editor.
-
Insert a Module:
- In the VBA editor, go to
Insert>Module. - Paste the above VBA macros into the module.
- In the VBA editor, go to
-
Run the Macros:
- You can run the macros directly from the VBA editor or assign them to buttons within your Excel sheet for easier access.
-
Security Note:
- Ensure that macros are enabled in your Excel settings.
- Be cautious with VBA macros as they can pose security risks if sourced from untrusted locations.
Key Benefits of This Approach
-
Data Safety: By creating a backup before making changes, you ensure that the original data can always be restored, mitigating the risk of accidental data loss.
-
Transparency: Users are informed about the backup and restoration processes through alerts and messages, enhancing user awareness.
-
Maintainability: Storing backups in a dedicated location (like Script Properties or a hidden sheet) keeps the backup process organized and manageable.
-
Reusability: The provided scripts and macros can be easily adapted or expanded to handle backups and restorations for other columns or sheets as needed.
Conclusion:
While the idea of performing an undo operation programmatically is appealing, it's not supported in spreadsheet scripting environments. Instead, implementing a robust backup and restore mechanism ensures data integrity and provides a reliable way to manage changes to your spreadsheet data.