0

I'm in the midst of trying to custom code some script that will automatically hide some sheets from some users in a shared company spreadsheet. Yesterday it seemed to be working fine, but now it seems to not be working for anyone unless they authorize the script first. This is problematic for a variety of reasons. The code is as shows:

var adminUsers = ['exampleadmin1@gmail.com','exampleadmin2@gmail.com'];
var Users = ['exampleuser1@yahoo.ca','exampleuser2@gmail.com'];

function onOpen() {

SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sales Metrics').hideSheet()
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Quarterly Metrics').hideSheet()
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Monthly Metrics').hideSheet()

if (adminUsers.indexOf(Session.getEffectiveUser().getEmail()) >= 0) {
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sales Metrics').showSheet()
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Quarterly Metrics').showSheet()
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Monthly Metrics').showSheet()
}   

var ui = SpreadsheetApp.getUi();
ui.createMenu('Privacy')
  .addItem('Say Hello', 'helloWorld')
  .addItem('Privacy Lock', 'privacy')
  .addItem('Privacy Unlock', 'unprivacy')
  .addToUi();
}

function helloWorld() {
Browser.msgBox("Hello World!");
}
function privacy() {

SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sales Metrics').hideSheet()
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Quarterly Metrics').hideSheet()
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Monthly Metrics').hideSheet()

if (adminUsers.indexOf(Session.getEffectiveUser().getEmail()) >= 0) {
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sales Metrics').showSheet()
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Quarterly Metrics').showSheet()
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Monthly Metrics').showSheet()
}

}
function unprivacy() {

if (Users.indexOf(Session.getEffectiveUser().getEmail()) >= 0) {
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sales Metrics').showSheet()
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Quarterly Metrics').showSheet()
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Monthly Metrics').showSheet()
    }
}

The trigger fires on spreadsheet open, I know this because the 'Privacy' button appears in the menu of people who open it. But for some reason the code that hides the specific sheets isn't running automatically.

I don't know if there is a way to install a trigger onto the spreadsheet itself, or if there is some piece of code I'm using that is making it harder, but anyone that can help me understand this, or even some other way that this code could be improved, it would be greatly appreciated!

0

onOpen is an simple triggers and simple triggers can't run methods that require authorization to run. For details see https://developers.google.com/apps-script/guides/triggers and https://developers.google.com/gsuite/add-ons/concepts/triggers.

An alternative is to use an installable trigger instead of a simple one.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.