1

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.

sample range

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!

1

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.

  • 1
    Hi Ruben, your suggestion worked perfectly! It was solved when I replaced '1' with 1 and so on. – Jon Feb 7 at 3:16

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.