CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
J.Mapz
J.Mapz

Reputation: 511

Making onEdit() faster and more efficient

After several googling, I arrived with a code that looks into a main sheet and shows or hides sheets (named A to N) depending on what checkbox is ticked/unticked (main requirement).

However, it seems to be not that efficient, and takes a couple of seconds to truly show/hide the sheets after ticking/unticking their respective checkboxes. I've recently learned that onEdit() always runs whenever there's an Edit anywhere in the spreadsheet.

I would like to seek advise if it is possible to only run onEdit() when edits are done only on specific ranges, specifically B6:B20 of Main sheet (which contains my checkboxes). Likewise, instead of going over all checkboxes and showing/hiding sheets based on what is ticked/unticked, is it possible for onEdit() to detect what was immediately ticked/unticked from among the checkboxes and show/hide the appropriate sheet? We maybe adding several other sheets in the future, and it will just get more inefficient if we try to scale the current code.

Here's the code:

function onEdit(e) {

var sheet = e.source.getSheetByName("Main");
var ss = SpreadsheetApp.getActiveSpreadsheet();
var A = ss.getSheetByName("A");
var B = ss.getSheetByName("B");
var C = ss.getSheetByName("C");
var D = ss.getSheetByName("D");
var E = ss.getSheetByName("E");
var F = ss.getSheetByName("F");  
var G = ss.getSheetByName("G");
var H = ss.getSheetByName("H");
var I = ss.getSheetByName("I");
var J = ss.getSheetByName("J");
var K = ss.getSheetByName("K");
var L = ss.getSheetByName("L");
var M = ss.getSheetByName("M");
var N = ss.getSheetByName("N");  

if(sheet.getRange('B6').getValue() === true){A.showSheet();}else{A.hideSheet();}
if(sheet.getRange('B7').getValue() === true){B.showSheet();}else{B.hideSheet();}
if(sheet.getRange('B8').getValue() === true){C.showSheet();}else{C.hideSheet();}
if(sheet.getRange('B9').getValue() === true){D.showSheet();}else{D.hideSheet();}
if(sheet.getRange('B10').getValue() === true){E.showSheet();}else{E.hideSheet();}
if(sheet.getRange('B11').getValue() === true){F.showSheet();}else{F.hideSheet();}
if(sheet.getRange('B12').getValue() === true){G.showSheet();}else{G.hideSheet();}
if(sheet.getRange('B13').getValue() === true){H.showSheet();}else{H.hideSheet();}
if(sheet.getRange('B14').getValue() === true){I.showSheet();}else{I.hideSheet();}
if(sheet.getRange('B15').getValue() === true){J.showSheet();}else{J.hideSheet();}
if(sheet.getRange('B17').getValue() === true){K.showSheet();}else{K.hideSheet();}
if(sheet.getRange('B18').getValue() === true){L.showSheet();}else{L.hideSheet();}
if(sheet.getRange('B19').getValue() === true){M.showSheet();}else{M.hideSheet();}
if(sheet.getRange('B20').getValue() === true){N.showSheet();}else{N.hideSheet();}
}

Likewise, here's a preview of our Main sheet. Based on the image, sheets B, E, J and K should be showing while the others should be hidden:

enter image description here

Upvotes: 0

Views: 623

Answers (1)

Cameron Roberts
Cameron Roberts

Reputation: 7377

onEdit will fire anytime the sheet is edited, however, you can reference the event object (e) to determine which cell was modified, and only take action if the edit is on a cell you actually care about.

I would do this by creating a map of cells to sheets, so you don't need a bunch of if statements, but rather just check if the cell is mapped and take action if it is.

var actionable_cell_map = {'B6':'A',
                           'B7':'B',
                           ...
                           'B20':'N'
                          };
function onEdit(e){
    if(e.range.getSheet().getName() == "Main"){
       var edited_cell = e.range.getA1Notation();
       if(actionable_cell_map[edited_cell]){
         var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(actionable_cell_map[edited_cell]);
         if(e.range.getValue() === true){
            sheet.showSheet();
         }else{
            sheet.hideSheet();
         }
       }
    }
}

Upvotes: 2

Related Questions