Re-Insert deleted rows Google Sheet-CodePudding

I would like to prevent the deletion of rows on a google sheet, but this is not possible to set it through permissions especially if it is the same user who can modify the sheet, can accidentally delete one or more rows and/or insert new ones. For inserting of a new row I have a script that works correctly which simply get the "INSERT_ROW" event onChange (), alerts the user and deletes the newly inserted row. For deletion instead,the user can delete a single row or even more rows at the same time ... and I don't know how to restore them in the same range from which they were deleted (and therefore also recover the values).

Here is the first working point:

 /** RESTORE ROW OR COLUMN */
function onChange(e){

var ss = SpreadsheetApp.getActiveSpreadsheet();

 
 
    var sheet = ss.getActiveSheet();
    var range = SpreadsheetApp.getActiveRange();
    if (e.changeType === 'INSERT_ROW'){
      var row = range.getRow();
      var lastRow = range.getLastRow();
      var numRows = 1 lastRow-row;
      sheet.deleteRows(row, numRows)
      SpreadsheetApp.getUi().alert("Warning for Row");
    }else if (e.changeType === 'INSERT_COLUMN'){
      var col = range.getColumn();
      var lastCol = range.getLastColumn();
      var numCols = 1 lastCol-col;
      sheet.deleteColumns(col, numCols);
      SpreadsheetApp.getUi().alert("Warning for Column");
    }
     
       
   

  GmailApp.sendEmail([email protected],
                          'Warning:' e.changeType " row:"  row, 
                           "",
                            {
                            name :'WARNING'
                            });

}

CodePudding user response：

There is no built-in / direct way to prevent that editors delete rows in Google Sheets while allowing them to user other features like filtering, sorting etc.

Under certain scenarios it might work to protect the sheet and setting some protections exceptions to allow some users to edit some cells.

If the above doesn't work for you, you might have to implement a way to backup the rows but that might not scalable and very reliable.

The most reliable approach might be to have a "backend database" (it could be another spreadsheet or another service i.e. BigQuery) then import the data into the "frontend" spreadsheet and push the changes to the backend database after they were validated.

CodePudding user response：

Yes I have a backend database. In the sense that I have a goolge form that fills in the answer sheet. Then I have 4 more sheets which simply read the sheet values from the answers(but on which, other users can take actions). So for example in sheet2 I have something like (= 'Form Answers'! A: A) and next to it, I have a cell with a checkbox like "OPEN" / "CLOSED" . The point is that if the user in Sheet2 inserts/delete a row, the answers are misaligned from the checkboxes...so the values of the checkboxes no longer correspond to the information taken from the sheet "'Form Answers'!".I wanted to avoid having him insert/delete a row by mistake. So with the above procedure, I can do an undo of a new row ... it would have been nice to be able to restore any deleted row.

it would take something like "If you delete row3 of sheet2 then reinsert row3 of sheet 'Form Answer', exactly in the position of row 3 and not in append "(it would be the top to recover the "old value" of the checkbox that was lost due to deletion of the entire row