Hide/Unhide Columns with appsScript

49 views
Skip to first unread message

G Develop

unread,
Oct 2, 2022, 12:31:20 AMOct 2
to Google Apps Script Community
Hello,
I'm a beginer in js & google apps script and trying to get this done to no avail...
I've divided my google sheet in 3 working zones [Range1(E:I), Range2(J:N), Range3(O:S)]
so, I'm trying to write a script to hide and unhide each range in the way that
if Range1 is showing/unhidden, Range2 & Range3 should be hidden and so should it be for the other 2 ranges...
The script I tried to write(see below) for Range1 returns no error; but won't do anything either

Please your contribution and help will be apprciated...

=============

function hideRange1(){
    var wb= SpreadsheetApp.getActiveSpreadsheet();
    var sh= wb.getActiveSheet();
    var range1= homesh.getActiveRange(E:I);

  if (range1.getA1Notation() !="E1:I") return;

  if(range1=="True"){
    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().hideColumns(10, 19);

  }
  else if (range1 == "FALSE") {
    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().showColumns(10, 19);
  }
}

Martin Molloy

unread,
Oct 2, 2022, 4:48:56 PMOct 2
to google-apps-sc...@googlegroups.com
It's a bit of a chore to work out if a column (or range of columns) is currently displayed, or hidden.

You could try the script below which displays each of these ranges, in turn, and hides the other two.

Hope that helps

function hideRange1(){
    var wb= SpreadsheetApp.getActiveSpreadsheet();
    var sh= wb.getActiveSheet();

    var props = PropertiesService.getUserProperties()
    var rangeToDisplay = props.getProperty('displayRange')
    if(!rangeToDisplay){rangeToDisplay='1'}
    var temp = ''
   
    sh.hideColumns(5, 15)  // hides all columns in all 3 ranges
   
    if(rangeToDisplay == '1'){  //show the columns in range 1
      sh.showColumns(5, 5)
      temp = '2'
      }
     
    if(rangeToDisplay == '2'){ //show the columns in range 2
      sh.showColumns(10, 5)
      temp = '3'
      }
     
    if(rangeToDisplay == '3'){ //show the columns in range 3
      sh.showColumns(15, 5)
      temp = '1'
      }


  props.setProperty('displayRange', temp) // set the property to the next range
  SpreadsheetApp.flush()
}

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/3c7d4fab-db7d-479b-812a-a0b7bb3061f0n%40googlegroups.com.

G Develop

unread,
Oct 4, 2022, 10:43:24 AMOct 4
to google-apps-sc...@googlegroups.com
Thanks very much;
It's quite a helpful start compare to what I had; I try to change this into what I need and will definitely keep you updated;

Again, thx for your help

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/QsZJRihC8SI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAO8s2PjVGBySR6tyt-A_t%3DKDwy8cL_3Cy%2BEbgkOE1UkXdsFgYw%40mail.gmail.com.
Reply all
Reply to author
Forward
0 new messages