0

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:
  • "Patrick View": Unhide all columns but hide column 1 & 2
  • "Karen View": Unhide all columns but hide column 4 and 6
  1. One custom menu "Column Creation" with 1 item:
  • "Add column" (this script is already working but running at the same time as the first custom menu so when I click, it both hide column and create a new one)

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

Damien
  • 143
  • 1
  • 10
  • Related: https://stackoverflow.com/questions/56554858/hide-show-specific-sheets-tabs-for-specific-users-on-shared-file/56559511#56559511 – TheMaster Apr 12 '22 at 15:27
  • You can try filterviews, as those are not global. Or you can build a custom webapp based on the sheet – TheMaster Apr 12 '22 at 15:29

2 Answers2

1

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!

Mike Steelson
  • 14,650
  • 2
  • 5
  • 20
  • Thanks a lot, that's exactly what I needed. Usually there is only one user browsing the sheet at once so there wont be any problem! – Damien Apr 12 '22 at 16:49
1

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.

ziganotschka
  • 25,866
  • 2
  • 16
  • 33