CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsmenu
Damien
Damien

Reputation: 373

Create a custom menu in Google Sheets / Apps Scripts to hide and unhide column based on who is browsing the sheet

I have a massive Google Sheet report that contains a lot of metrics. A lot of people in my company have access to this report and they all want to see different metrics.

So, I'm trying to create a button at the top of the report that hide/unhide columns depending on the person who is browsing the report.

    function onOpen(){
         const ui = SpreadsheetApp.getUi();
             ui.createMenu('Column Creation')
               .addItem('Add Column', 'insertColumn')
               .addToUi();
             ui.createMenu('View 👀')
               .addItem('Patrick View', 'HideColumns')
               .addItem('Karen View', 'HideColumns')
               .addItem('Umesh View', 'HideColumns')
               .addItem('Unhide everything', 'HideColumns')
               .addToUi();  function insertColumn()   
var ss = SpreadsheetApp.getActiveSpreadsheet();   
var sheet = ss.getSheetByName('KW'); [...] //this is another function to insert a column

I know I need to use sheet.hideColumns(1) and sheet.showColumns (2), but I'm not sure how to aggregate all of this. Should I create different variables for each view? Also right now, my code is running both actions at the same time (column creation + hide/unhide columns) how can I separate this?

To summarise I want:

  1. One custom menu "View 👀" with 2 items:
  1. One custom menu "Column Creation" with 1 item:

I hope my explanations are not too messy, thanks in advance for any help !

Upvotes: 0

Views: 444

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15308

Try

var sh = SpreadsheetApp.getActiveSheet()
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Column Creation')
    .addItem('Add Column', 'insertColumn')
    .addToUi();
  ui.createMenu('View 👀')
    .addItem('Patrick View', 'hideColumnsP')
    .addItem('Karen View', 'hideColumnsK')
    .addItem('Unhide everything', 'showColumns')
    .addToUi();
}
function showColumns() {
  sh.unhideColumn(sh.getRange(1, 1, 1, sh.getLastColumn()))
}
function hideColumnsP(){
  showColumns()
  sh.hideColumns(1,2)
}
function hideColumnsK(){
  showColumns()
  sh.hideColumns(4,3)
}

Note that only one person will be active at once!

Upvotes: 1

ziganotschka
ziganotschka

Reputation: 26796

It is not possible to hide / unhide columns only for certain users

Once a user unhides a hidden column, this column will become unhidden for all the others viewers as well (after a propagation time of few seconds).

A solution would be for each user to have their personal spreadsheet that imports data of interest from the master spreadsheet via ImportRange. That would be optimal for viewing. However, if the users are also meant to modify the data, things will become more complicated since the modified data has to be synched back to the master spreadsheet and conflicts can arise if multiple users modify data simultaneously in their local spreadsheets.

Upvotes: 1

Related Questions