Geek Answers Handbook

Google Sheets: hide columns based on cell value

In my Google A spreadsheet, I use a combination of the TRANSPOSE and IMPORTRANGE formulas to import data from Calendar B spreadsheet to populate the work schedule of events. Since there are 3 event slots on each date that do not always fill up, I get a lot of outdated columns.

Table:

row1: 01-01-2013 01-01-2013 01-01-2013 02-01-2013 row2: Event_ID Event_ID Event_ID Event_ID row3: Event_name Event_name Event_name Event_name

Lines 1 and 2 contain automatically generated dates and event_ID, so they will never be empty. Cell number. 3 appears blank when no event has been added to this slot, but in fact there is a CONTINUE formula to continue the importrange formula from cell A1.

I am looking for a script to automatically hide numbers in which cell nr. 3 does not contain imported data.

Not understanding anything about JavaScript (but wanting to learn), I tried to combine fragments from existing scripts, but at the moment I can not understand the meaning of these codes ...

+4
source share
2 answers

The following code does the trick:

 function onOpen() { // get active spreadsheet var ss = SpreadsheetApp.getActiveSpreadsheet(); // create menu var menu = [{name: "Hide columns", functionName: "hideColumn"}, {name: "Show all columns", functionName: "showColumn"}]; // add to menu ss.addMenu("Check", menu); } function hideColumn() { // get active spreadsheet var ss = SpreadsheetApp.getActiveSpreadsheet(); // get first sheet var sheet = ss.getSheets()[0]; // get data var data = sheet.getDataRange(); // get number of columns var lastCol = data.getLastColumn()+1; Logger.log(lastCol); // itterate through columns for(var i=1; i<lastCol; i++) { if(data.getCell(3, i).getValue() == '') { sheet.hideColumns(i); } } } function showColumn() { // get active spreadsheet var ss = SpreadsheetApp.getActiveSpreadsheet(); // get first sheet var sheet = ss.getSheets()[0]; // get data var data = sheet.getDataRange(); // get number of columns var lastCol = data.getLastColumn(); // show all columns sheet.showColumns(1, lastCol); }
  • Creates a menu when opening a file
  • The first parameter of the menu hides all columns in which row 3 is empty.
  • The second option shows all hidden columns

See the example file I prepared to see it in action: Hide columns based on cell value

+4
source

In addition to Jacob’s answer, you can write a filter in the formula itself to display only columns with data in row 3. For example, if your formula is ImportRange in A1:

=ImportRange("key";"A1:Z3")

you could use this:

=FILTER(ImportRange("key";"A1:Z3");LEN(ImportRange("key";"A3:Z3")))

or this one that uses only one ImportRange call:

=TRANSPOSE(QUERY(TRANSPOSE(ImportRange("key";"A1:Z3"));"select * where Col3 != ''"))

+1
source

More articles:


All Articles