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:
I hope my explanations are not too messy, thanks in advance for any help !
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!
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.