Hide columns in one sheet based on whether checkbox in another sheet is FALSE

Question

I am trying to run a script on my spreadsheet that hides columns on one sheet based on whether a checkbox in another sheet is ticked.

I have created the below code but this doesn't seem to work:

function onEdit(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = SpreadsheetApp.getActive().getSheetByName('BALANCE SHEET');
  var sheet2 = SpreadsheetApp.getActive().getSheetByName('OVERVIEW')
  var cell = sheet2.getRange(7, 5);

  if(cell == "TRUE"){
    sheet1.hideColumns(21,2)};
  if(cell == "FALSE"){
    sheet1.showColumns(21,2)};
}

The tickbox is on the sheet OVERVIEW in cell E7 and I want this to hide columns V & W on the BALANCE SHEET.

When the tickbox is TRUE, I want the columns to be displayed and when the tickbox is FALSE the columns will be hidden.

Can anyone help on this please?

Cooper Cooper · Accepted Answer · 2020-02-15T18:07:52

This works:

Note: you can't run this from the script editor as it requires having the onedit trigger object. I have my checkbox at 'OVERVIEW!E7';

function onEdit(e){
  //e.source.toast('Test');
  var sh=e.range.getSheet();
  if(sh.getName()!='OVERVIEW')return;
  if(e.range.columnStart==5 && e.range.rowStart==7) {
    //e.source.toast('Test1');
    var sheet1=e.source.getSheetByName('BALANCE SHEET');
    if(e.value=="TRUE"){sheet1.hideColumns(21,2)};
    if(e.value=="FALSE"){sheet1.showColumns(21,2)};
  }
}

Hide columns in one sheet based on whether checkbox in another sheet is FALSE

1 Answers