CODEWITHSUNDEEP
google-apps-scriptgoogle-workspace
arul selvan
arul selvan

Reputation: 624

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.

Upvotes: 0

Views: 2140

Answers (2)

Steve
Steve

Reputation: 66

I don't think there is a way to easily "protect" your spreadsheet in this way. However, you can add an onChange trigger to your spreadsheet (read more here) which will run a function whenever somebody changes the format or structure of your spreadsheet. From the script editor, click edit > current project's triggers which will open a new window. There you can add a new trigger (select a function to execute and the spreadsheet event onChange). Here's the function that you could link to the trigger:

function onChange(e) {
  /* e is the event object, see documentation */
  if (e.changeType == "INSERT_ROW") {
    /* delete unneeded empty rows at the end */
    removeEmptyRows();
  } else if (e.changeType == "INSERT_GRID") {
    /* delete unwanted sheets */
    removeExtraSheets();
  }
}

As for preventing users from deleting a sheet, it seems like there is no option to do that. You can catch the deletion of a sheet in the onChange function, the changeType will be "REMOVE_GRID" but the event object will not allow you to access the deleted data.

So, if you have important data in the sheets (and don't trust the editors to not delete the sheets), you should probably backup your data regularly (using a time-based trigger and a second backup spreadsheet that only you can access).

Upvotes: 2

Rafa Guillermo
Rafa Guillermo

Reputation: 15375

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.

References:

Upvotes: 3

Related Questions