How to not allow edit, but still allow hiding columns/rows

Question

I have a workbook with several sheets. I also have a custom menu that is suppose to enable any user to hide/show certain columns and rows. I do not want anyone (other than me) to have the ability to edit the sheet. I "Protected" the sheet so that only I have "Edit" access. The show/hide function works perfectly for me.

However, whenever a user tries it, it fails. I need the show/hide functionality to work for those that do not have edit access. I tried using the "Warning Only", but obviously, that still enables them to edit.

Any thoughts on how I can accomplish this?

My code is simple:

function showPercents() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet() //var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); //for (var j = 0; j < sheets.length ; j++ ) { // var sheet = sheets[j]; var sheetname = sheet.getSheetName(); if (sheetname != 'Charts' && sheetname != 'DataIn' && sheetname != 'Config') { var numofcolumns = sheet.getLastColumn(); var i = 3; while (i < numofcolumns - 1) { sheet.showColumns(i) sheet.showColumns(i+2) sheet.showColumns(i+4) sheet.showColumns(i+5) sheet.showColumns(i+7) sheet.showColumns(i+9) sheet.showColumns(i+10) i = i + 11 } } //} } function hidePercents() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet() //var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); //for (var j = 0; j < sheets.length ; j++ ) { // var sheet = sheets[j]; var sheetname = sheet.getSheetName(); if (sheetname != 'Charts' && sheetname != 'DataIn' && sheetname != 'Config') { var numofcolumns = sheet.getLastColumn(); var i = 3; while (i < numofcolumns - 1) { sheet.hideColumns(i) sheet.hideColumns(i+2) sheet.hideColumns(i+4) sheet.hideColumns(i+5) sheet.hideColumns(i+7) sheet.hideColumns(i+9) sheet.hideColumns(i+10) i = i + 11 } } //} }

Daniel Brose · Accepted Answer · 2020-05-08 06:18:44Z

Create a second sheet with whatever permissions you wanted, then import the needed data from your first.

It can even be an entire clone, in that each sheet is imported by placing the import formula in top left cell, however you can then lock just the import cells

That way users can show hide or whatever, without needing to save local copy, and you can lock the first (data entry) spreadsheet (which has its own url) so that they cant even see it.

=IMPORTRANGE("abcd123abcd123", "sheet1!A1:C10")

=IMPORTRANGE("http://docs.google.com/spreadsheets/d/URL/to/spreadsheet/edit" , "A22:E27")

See these great resources to help show how to set those permissions up if you wanted to lock that first sheet down entirely

https://www.howtogeek.com/442246/how-to-import-data-from-another-google-sheet/

https://support.google.com/a/users/answer/9308940?hl=en

Update

The google support site has great reources on this, short answer to how to lock a specific range of cells is to go Data > Protected Sheets and Ranges. Then you can "Set Permissions" to "Only You" or similar, regardless of the overall sheet protection level.

If still confused, just try it and have a look at the examples in google support site, pretty self explained though once you use it once

Hope that extra info helps

I did this and the import worked great. However, the user still has access to edit. If I change the user to 'Commentor" (which is what I really want), then they do not see the custom menu to be able to do the show/hide.
Changing back to "Contributor" enables the custom menu, but then they have access to edit. I changed it to "Show Warning", but it would be nice to not allow edit at all, but still allow hide/show columns/rows view custom menu for users that are below "Commentor" on the permissions.
@Scott, as i said, you should be able to lock specific cells entirely, ill add bit more description how, though as i said its covered in those links (google support link, look for permissions and protected sheets/ranges)

Collectives™ on Stack Overflow

How to not allow edit, but still allow hiding columns/rows

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related