Question

0

126

Vistas

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:

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

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 !

7 months ago · Santiago Gelvez

2 Respuestas

Responde la pregunta

0

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.

7 months ago · Santiago Gelvez Denunciar

Responde la pregunta

Encuentra empleos remotos

Accepted Answer · 2022-06-30T23:57:11.002Z

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!