CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
John
John

Reputation: 29

GoogleSheets Script get logged in user to hide/show columns

Obviously I am clueless at scripting. Just trying to fire a manual script by the sheet's current user - obtains their Goggle User Email and then will hide or show certain columns. I am lost and have no idea what I am doing, as you can tell. Any help would be so very appreciated. Firing script manually by logged in user via script embedded image on Sheet that is clicked after opening sheet.

function onChoice(e){
var email = (Session.getEffectiveUser().getEmail());
var sheet = e.source.getActiveSheet();
if (e.user() !== 'email') return;
switch (e.value) {
          case '[email protected]':
            sheet.showColumns(1, sheet.getMaxColumns() - 1)
            break;
          case '[email protected]':
            sheet.showColumns(5, 2)
                sheet.hideColumns(7, 6)
            break;
          case '[email protected]':
            sheet.showColumns(7, 6)
            sheet.hideColumns(5, 2)
            break;
}
}

Upvotes: 1

Views: 7166

Answers (1)

HDCerberus
HDCerberus

Reputation: 2143

So, here's a very simple adjustment that does what you describe, but I wasn't going to mess about with the 'Clicking and image' thing, you'll have to find how it's going to work with your existing code.

The following will open/close columns based on your email using a simple 'onOpen' trigger:

function onOpen(e){
  var email = (Session.getEffectiveUser().getEmail());
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  switch (email){
      case '[email protected]':
            sheet.showColumns(1, sheet.getMaxColumns() - 1);
            sheet.hideColumns(5, 2);
            break;  
      case '[email protected]':
            sheet.showColumns(5, 2);
            sheet.hideColumns(7, 6);
            break;
      case '[email protected]':
            sheet.showColumns(7, 6);
            sheet.hideColumns(5, 2);
            break;
  };
}

It currently works for me. The issues you were having were centered around the 'if (e.user() !== 'email') return;' as I expected, but I've not tested anything related to 'var sheet = e.source.getActiveSheet();' as I'm not sure what data is being passed in via 'e; (I don't believe 'onChoice' is a Google Trigger).

This should resolve the code issue you're having.

Upvotes: 3

Related Questions