Prevent some sheet actions by users

I have given "Edit" rights to some users. I am the owner.

I prevent Add or Delete or hide or resize Column (by user) by protecting Row1 (only editable by owner).

But, I have seen some dummy sheets getting added by the user. I can delete these sheets by script which runs once a day. Is there a better way to prevent a user from adding a sheet?

If a user (who is an editor) deletes a sheet, the system will collapse. How to prevent an editor from deleting a sheet? This is very important for me.

Some users press the "Add 1000 rows" button at the bottom and add unnecessary rows. Is there a way to prevent it? Now, I have made an option in the menu which deletes the empty rows.

Solution

Answer:

You can't prevent a user with edit access from creating new Sheets or rows, but you can run an onChange() trigger which deletes them as soon as they're created.

More Information:

The onChange() trigger of Google Sheets is an installable trigger which runs when a change to the structure of a Sheet is made.

The event object for the chenged event contains information about the type of change - specifically INSERT_GRID and INSERT_ROW - these refer to a Sheet structure change of a new Sheet added and a new row or range of rows inserted.

From here you can then handle the immediate removal of them.

Code:

function onChange(e) {
  
  if (e.changeType == "INSERT_GRID") {
    // list your sheet names in here:
    var sheetNames = ["Sheet1", "Sheet2", "Sheet3"];
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    
    if (sheets.length != sheetNames.length) {
      for (var i = 0; i < sheets.length; i++) {
        if (sheetNames.includes(sheets[i].getName())) {
          continue;
        }
        else {
          SpreadsheetApp.getActiveSpreadsheet().deleteSheet(sheets[i])
        }
      }          
    }
  } 
  
  else if (e.changeType == "INSERT_ROW") {
    // define how many rows your sheet has here:
    var noOfRows = 200;    
    var sheet = SpreadsheetApp.getActive();    
    
    sheet.getRange((noOfRows + 1) + ':' + (noOfRows + 1)).activate();
    
    var currentCell = sheet.getCurrentCell();
    
    sheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
    
    currentCell.activateAsCurrentCell();
    sheet.getActiveSheet().deleteRows(sheet.getActiveRange().getRow(), sheet.getActiveRange().getNumRows());
  }
}

Setting up the Trigger:

Save the script with the save icon, press the run button (►), and confirm the authentication of running the script.

From here, following the Edit > Current project's triggers menu item, you will have a new page open in the G Suite Developer Hub. Click the + Add Trigger button in the bottom right and set up the trigger settings as follows:

Choose which function to run: onChange
Choose which deployment should run: Head
Select event source: From spreadsheet
Select event type: On change

And press save.

Deleted Sheets:

This is the tricky part; unfortunately, there is no way of recovering deleted sheets by catching them with an onChange().

If this is a huge importance, however, the best thing I can recommend is taking a look at file revisions of Drive files and the Drive API's file revisions: list and revisions: get methods, and do some integration of this on e.changeType == REMOVE_GRID, but in reality you should just try and back the sheet up as often as you can so that if important structure is lost it can be easily recovered.