1

I have a Google Sheets worksheet that contains certain values that should ideally only be revealed to people who have Edit capability. For example, there is a "next row to be processed" cell that's present for restarts. There is also a sheet where some maintenance items may be placed (for example, renames of items in the main sheet), and so on. It would be preferable to not reveal that information (nor even its existence). So I tried this code:

function onOpen() {
  var menuItems ;
  var spreadsheet = SpreadsheetApp.getActive();
    if (spreadsheet.getDataRange().canEdit()) {
        menuItems = [
               {name: 'Process all rows from the top' , functionName: 'processAllStreams'}
             , {name: 'Resume processing at row #'    , functionName: 'processStreams'}
             // other stuff
                 ];
        spreadsheet.addMenu('Process it', menuItems);
        if (replacementsSheet) {
            replacementsSheet.showSheet();
            }
        for (var c=0; c < MAINSHEET_NBR_MAINTENANCE_COLUMNS; c++) {
            mainSheet.unhideColumn(MAINSHEET_MAINTENANCE_START_COLUMN + c) ;
            }
        }
    else {
        mainSheet.hideColumns(MAINSHEET_MAINTENANCE_START_COLUMN, MAINSHEET_NBR_MAINTENANCE_COLUMNS) ;
        if (replacementsSheet) {
            replacementsSheet.hideSheet();
            }
        }
    }

But of course, those who cannot edit the spreadsheet cannot perform those functions in the else clause. How do others handle the situation where you want the presentation to be different for the public versus the operational aspects?

Can operate without these niceties, but it looks a little unprofessional as is.

Dennis
  • 1,071
  • 2
  • 17
  • 38

1 Answers1

0

I'm seeing other posts related to this and they all point out that the best way to limit the data based on users is by restricting them based on the email.

Code:

function onOpen() {
  var validUsers = ['ex1@ex.com', 'ex2@ex.com'];
  if (validUsers.indexof(Session.getEffectiveUser().getEmail()) >= 0) {
    // let them access the data
  }
  else {
    // hide the data
  }
}

If it is an option for you and you can list down all the allowed users to view the data, then you can have this kind of approach instead.

Note:

  • This can't be done when both type (edit and view) of users are accessing the data at a time.
  • If data is critical, then this solution should not be used as it will be revealed once conflicting types of users accesses the data at the same time.

StackOverflow similar questions:

NightEye
  • 10,634
  • 2
  • 5
  • 24
  • 1
    Thanks very much for your answer. I understand and agree with the solution (and I've accepted the answer). In this case, I'm not willing to give edit access to the public (which it looks like I'd need to do in order to take advantage of this solution), so I guess I'll just live with the extraneous values on the worksheet. I really appreciate your time and insight. And sorry I didn't find this answer myself. I did look, didn't find. – Dennis Feb 15 '21 at 17:53
  • It's fine @Dennis. I too did find these questions after searching for a considerable amount of time. Also, thanks for accepting the answer too. I do apologize for not being able to provide you the answer you were hoping for. I did try some workarounds but this is by far the closest thing I can do, and has very limited use cases. Hopefully you can have an alternative to what you want to do. Good luck. – NightEye Feb 15 '21 at 17:57