How to prevent any editor from adding new sheet in google sheets workbook-CodePudding

How can I prevent any anonymous editor from adding a new sheet inside the workbook. I wish to allow them to edit just one single sheet, but some editors mistakenly mess up the workbook by inserting unwanted sheets. thanks in advance. i tried the script in this link below but it does not seem to work.

Prevent users from creating new sheets in a shared google spreadsheet

// 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])
    }
  }
}

I created this spreadsheet https://docs.google.com/spreadsheets/d/1EdF8I0tyfQagfw1cxHCWgsr2umXi6-vbu7GvM8SJQSM/edit#gid=0 with the code above and set the triggers, but anyone could still create a new sheet.

CodePudding user response：

I believe your current situation and goal are as follows.

There is a sheet in Google Spreadsheet.
You don't want to make the anonymous users insert a new sheet.

Issue and workaround:

In the current stage, unfortunately, there is no method for clearly detecting the edit of an anonymous user. In your script, I thought that the script can be used for the special permitted users. In this case, the anonymous users cannot be included. I thought that this might be the reason for your issue. So in order to achieve your goal, it is required to prepare a workaround. In this answer, I would like to propose a workaround.

When the sheet insert is detected, OnChange trigger can be used. But, the identification of the user is a bit difficult. When a function installedOnChange(e) is executed by the OnChange trigger, the following condition can be obtained. And, in order to check the active user, Session.getActiveUser().getEmail() and Session.getEffectiveUser().getEmail() are used.

When the owner inserts a new sheet,
- e of installedOnChange(e) has the property of "user":{"email":"### owner's email ###","nickname":"### owner name ###"}.
- Session.getActiveUser().getEmail() and Session.getEffectiveUser().getEmail() return the owner's email.
When the special permitted user inserts a new sheet,
- e of installedOnChange(e) has the property of "user":{"email":"","nickname":""}. In this case, no email address and no name are returned.
- Session.getActiveUser().getEmail() returns empty, and Session.getEffectiveUser().getEmail() returns the owner's email.
When the anonymous user inserts a new sheet,
- e of installedOnChange(e) has the property of "user":{"email":"### owner's email ###","nickname":"### owner name ###"}.
- Session.getActiveUser().getEmail() and Session.getEffectiveUser().getEmail() return the owner's email.
- In this case, the situation is the same with the owner. But, in order to identify this, here, the simple trigger is used. Because the anonymous user cannot use the simple trigger. For example, when the anonymous user edits a cell, the simple trigger is not run. This situation is used.

When these conditions are reflected in a sample script, it becomes as follows.

Usage:

1. Prepare sample script.

Please copy and paste the following script to the script editor of Spreadsheet. And, please directly run onOpen function with the script editor. By this, the initial sheet name is stored in PropertiesService. And also, when the Spreadsheet is opened, onOpen is run. By this, the initial condition can be saved.

function onOpen() {
  PropertiesService.getScriptProperties().setProperty("sheetName", JSON.stringify(SpreadsheetApp.getActiveSpreadsheet().getSheets().map(s => s.getSheetName())));
}

function onSelectionChange(e) {
  CacheService.getScriptCache().put("simpleTrigger", JSON.stringify(e), 30);
}

function deleteSheet(e) {
  if (e.changeType != "INSERT_GRID") return;
  const sheetNames = JSON.parse(PropertiesService.getScriptProperties().getProperty("sheetName"));
  e.source.getSheets().forEach(s => {
    if (!sheetNames.includes(s.getSheetName())) e.source.deleteSheet(s);
  });
}

function installedOnChange(e) {
  const lock = LockService.getDocumentLock();
  if (lock.tryLock(350000)) {
    try {
      Utilities.sleep(3000); // Please increase this wait time when the identification is not correct.
      const c = CacheService.getScriptCache();
      const simpleTrigger = c.get("simpleTrigger");
      const activeUser = Session.getActiveUser().getEmail();
      const effectiveUser = Session.getEffectiveUser().getEmail();
      if (activeUser && effectiveUser && simpleTrigger) {
        // Operation by owner.

        // do something.
        PropertiesService.getScriptProperties().setProperty("sheetName", JSON.stringify(SpreadsheetApp.getActiveSpreadsheet().getSheets().map(s => s.getSheetName())));

      } else if (!activeUser && effectiveUser && simpleTrigger) {
        // Operation by permitted user.

        // do something.
        deleteSheet(e); // If you want to make the permitted user not insert new sheet, please use this line.

      } else {
        // Operation by anonymous user.

        // do something.
        deleteSheet(e);
      }
      c.remove("simpleTrigger");
    } catch (e) {
      throw new Error(JSON.stringify(e));
    } finally {
      lock.releaseLock();
    }
  } else {
    throw new Error("timeout");
  }
}

In this sample script, the owner, the special permitted user, and the anonymous user are identified. And, the owner can insert a new sheet. But, when the special permitted user and the anonymous user insert a new sheet, the inserted sheet is deleted. By this, as a workaround, your goal might be able to be achieved.
In this script, onSelectionChange is used as a simple trigger for detecting the anonymous user.

3. Install OnChange trigger as an installable trigger.

Please install a trigger to the function installedOnChange as the OnChange installable trigger. Ref

3. Testing.

In order to test this script, please insert a new sheet by the special permitted user and the anonymous users. By this, the inserted sheet is deleted. And, when the owner inserts a new sheet, the inserted sheet is not deleted.

Note:

In this sample script, in order to check whether the simple trigger is executed, Utilities.sleep(3000) is used. So, the time for identifying the user is a bit long. I thought that this might be a limitation.

For example, if you are not required to identify the users who insert a new sheet, you can also use the following simple script. Before you use this script, please install OnChange trigger to installedOnChange and run onOpen with the script editor. In this sample script, all users cannot insert a new sheet.