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

Answer 1

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]+$/.

Answer 2

Without seeing your existing spreadsheet, Seedmanc, Google's "Filter views" option under the "Data" menu of your Sheet could be helpful, because it lets owners/editors create and save filters that are accessible to view-only users too.

What's more, view-only users can create filter views unique to them. Once they enter a filter range, menu arrows appear...

...allowing them to filter either by condition (including custom formulas!) or by values. So, depending on how elaborate your filter is and how spreadsheet-savvy your users are, filter views can provide a more flexible filter while still protecting your sheets.

(Read more about Google Sheets filter views here.)