Skip to content
The Chat Archive
o1-mini-2024-09-12· OpenAI WildChat-4.8M (AI2)

Make a function that deletes values of column E of tab CoinList from current sheet, then…

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

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

    1. Access Apps Script:

      • Open your Google Sheet.
      • Navigate to Extensions > Apps Script.
    2. Add the Scripts:

      • Paste the above functions into the script editor.
      • Save the script with an appropriate name.
    3. 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 onOpen function to ensure the menu appears when the spreadsheet is opened.
    4. 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 Sub
    

    2. 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 Sub
    

    Usage Instructions

    1. Access VBA Editor:

      • Open your Excel workbook.
      • Press ALT + F11 to open the VBA editor.
    2. Insert a Module:

      • In the VBA editor, go to Insert > Module.
      • Paste the above VBA macros into the module.
    3. 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.
    4. 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

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

    2. Transparency: Users are informed about the backup and restoration processes through alerts and messages, enhancing user awareness.

    3. Maintainability: Storing backups in a dedicated location (like Script Properties or a hidden sheet) keeps the backup process organized and manageable.

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