Prevent users from creating new sheets in a shared google spreadsheet

Question

I have created a spreadsheet with all sheets protected from editing except for a single cell, where the user is supposed to enter a search value, which filters the sheet. I shared the spreadsheet with enabled editing to allow for that, but that also enables users to create new sheets, which I'd like to prevent.

Perhaps I'm using not the most optimal way of achieving what I want, so suggestions are welcome. I saw people advising to use forms in similar use cases, but I don't see how to apply their survey capabilities to my needs.

EDIt: here is the shared spreadsheet: https://docs.google.com/spreadsheets/d/1C4mrBWJxPLrFQ4bp82UA2ICOr1e6ER47wF7YuElyoZg/edit?usp=sharing

Kpym Kpym · Accepted Answer · 2020-09-24T17:12:48

You can use a script to delete every newly created (not by the owner) sheet. Here is an example of such a script :

// Deletes any tab named "SheetN" where N is a number 
function DeleteNewSheets() {
  var newSheetName = /^Sheet[\d]+$/
  var ssdoc = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ssdoc.getSheets();
  
  // is the change made by the owner ?
  if (Session.getActiveUser().getEmail() == ssdoc.getOwner().getEmail()) {
    return;
  }
  // if not the owner, delete all unauthorised sheets
  for (var i = 0; i < sheets.length; i++) {
    if (newSheetName.test(sheets[i].getName())) {
      ssdoc.deleteSheet(sheets[i])
    }
  }
}

Then set a trigger that runs this function DeleteNewSheets when a change (like sheet creation) happens.

Note : If english is not the document language you should change the regular expression /^Sheet[\d]+$/, for example for french it should be /^Feuille [\d]+$/.

Prevent users from creating new sheets in a shared google spreadsheet

2 Answers