Protect Sheet with specific condition

Question

I would like to prevent a user from deleting/adding rows and columns on my Google Sheet, however, when I protect the sheet, I can't specifically ask it to just prevent those, it blocks data entry, hiding/unhiding rows/columns etc. What I thought about is to put a checkbox on a cell, so the user can still hide/unhide certain rows if needed, however, my script doesn't work when the sheet is protected.

function onEdit(e) {

var sheetName = 'Fund Request';

var sh = SpreadsheetApp.getActive().getSheetByName(sheetName),
    status = sh.getRange('A20').getValues();

function showHideRow(v, i) {
    // [[row, numberRows]]
    var rows = [[21,180]];

    return v[0]
           ? sh.showRows.apply(sh, rows[i])
           : sh.hideRows.apply(sh, rows[i])
};

status.forEach(showHideRow);
}

Any ideas? Would be nice if a user can still use the sheet normally except for adding/deleting rows and columns even though it's protected.

Sorry I didn't reply the question in my previous answer. I can't see how you can have user role to allow hide/unhide rows, but block add/remove rows — Waxim Corp
I cannot understand about Would be nice if a user can still use the sheet normally except for adding/deleting rows and columns even though it's protected.. I apologize for this. Can I ask you about the detail of your goal? — Tanaike
@tanaike,My goal is for the users to be able to fill out the sheet, hide/unhide rows as they need it, but not be able to add/delete rows or columns. The form that I created already have enough rows to accommodate what they need to input data, however, it varies. Sometimes they only need 1 row, sometimes they need 100s! Since I already know the max number of rows, I set it already on the sheet. All they need to do is hide/unhide it. They don't need to add/delete any. Hence, my question. I need the sheet protected, but allow them to hide/unhide, but not allow them to add/delete. — Kelvs
Thank you for replying. You want to protect a sheet from users and you want to show and hide rows in the protected sheet by the users. From your replying, I understood like this. If my understanding is correct, in the current stage, I think that your goal cannot be achieved because of the specification of Google side. I apologize for this. If I misunderstood your goal, please tell me. — Tanaike
@Tanaike Yes, your understanding is correct. So for now, there's really no way to protect a sheet and at the same time, allow users to hide/unhide rows? If that's the case, is there no work-around for this? Maybe, another solution you can think of? Thank you for responding as well. — Kelvs

ale13 ale13 · Accepted Answer · 2021-03-22T10:43:37

Sadly, what you want cannot be achieved as protecting a sheet results in no changes in the content being possible. This goes for adding/removing rows/columns as well and unfortunately Google does not allow in the current stage to select which types of actions to limit in your spreadsheet.

You may benefit from using an onChange trigger and if the above actions are detected, display an alert.

function onChange(e) {
  if (e.changeType == 'REMOVE_ROW')
    SpreadsheetApp.getUi().alert('Please do not remove any rows!');
}

Protect Sheet with specific condition

1 Answers

Reference