CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Scott Scriver
Scott Scriver

Reputation: 3

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

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

      }

    }

  //}

}

Upvotes: 0

Views: 1409

Answers (1)

Daniel Brose
Daniel Brose

Reputation: 1403

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

Upvotes: 1

Related Questions