Google-sheets – Hiding and Unhiding columns based on prompt

google sheetsgoogle-apps-script

I'm trying to make a template that hides and unhides columns based the value in G2. When a user will value from 1-5 in G2, certain columns will be shown, and certain columns will be hidden. Specifically, I'm looking to show only columns 1-3 when G2 is "1", show only columns 1-6 when G2 is "2", show only columns 1-9 when G2 is "3" and so on.

Here's my attempt. It doesn't produce an error, but nothing happens when I run it.

function showColumns() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var COLUMN = sheet.getRange('G2').getValue();
if (COLUMN=='1') {
  sheet.showColumns(1,3);  
  sheet.hideColumns(4,12);
  }
if (COLUMN=='2') {
  sheet.showColumns(1,6);  
  sheet.hideColumns(7,9);
  }
if (COLUMN=='3') {
  sheet.showColumns(1,9);  
  sheet.hideColumns(10,6);
  }
if (COLUMN=='4') {
  sheet.showColumns(1,12);  
  sheet.hideColumns(13,3);
  }
if (COLUMN=='5') {
  sheet.showColumns(1,15);   
  }

 }

I only have 5 sets of 3 columns, with the number of sets showing up depending on G2, hence why I tried the simplistic approach of showing/hiding specific rows depending on the input, although I'm positive there's an easier way to do this.
I greatly appreciate any advice!

Best Answer

It's very likely that the problem is due that the code is comparing the value of COLUMN with Strings rather than Numbers, replace '1' by 1, '2' by 2, and so on.

By the other hand it could be a good idea to use a switch statement of use if..else if instead of several independent if, also it could be a good idea to check that G2 value has the correct data type.

Related Solutions

Google-sheets – Add a ‘creation date’ value when a new line is edited

You could add a trigger that runs when new rows are added to the spreadsheet:

function onModification(event) {
  var sheet = event.source.getActiveSheet();
  if (event.changeType == "EDIT") { // A cell is edited. Other values include "INSERT_ROW" (when a new row is inserted)
    fillEmptyRows(sheet);
  }
}

function fillEmptyRows(sheet) {
    var range = sheet.getRange(1, 1, sheet.getMaxRows(), 1);
    var firstColumnValues = range.getValues();
    for (var i = 0; i < firstColumnValues.length; i++) {
      if (firstColumnValues[i][0] == "") { // Column 0 is A
         firstColumnValues[i][0] = new Date();
      }
    }
    range.setValues(firstColumnValues);
}

You need to register this trigger manually: Click Resources -> Triggers -> Add new trigger, select the onModification method and select on modification from the right-most dropdown menu.

When a cell is edited, the script will fill all blank cells in column A with the current timestamp.

I have set up an example spreadsheet, feel free to play with it.

Google-sheets – Make dynamic array blank based on adjacent values in Google Spreadsheets

Direct answer

The following formula return the expected results for the three cases exposed in the question. It should be added to cell A2 and cells in A3:A13 should be empty.

=ArrayFormula(
  IF(
    NOT(
      (ROW(B2:B13)-1=1)
      *
      (B2:B13="$")
    )
    *
    (
      (B2:B13=">")
      +
      (B2:B13="$")
      +
      (ROW(B2:B13)-1>=Vlookup("$",{B2:B12,ROW(B2:B12)},2,0))
    )
    *
    NOT((B1:B12=">")
    *(B2:B13="$")),
    ".",
    " "
  )
)

Explanation

In this case the use of ArrayFormula function together with references to B2:B13 will return an array of 12 rows and 1 columns with the expected values.

The comparison of a range or array with a scalar value returns an array of the same size of the range or array. All the tests should return an array of the same size.

AND and OR functions could not work with arrays, but Google Sheets interpret TRUE as 1 and FALSE as 0, so, instead of using AND function use * and instead of OR use +.

As + and * take precedence over comparison operators each test should be enclosed in parentheses.