CODEWITHSUNDEEP
javascriptgoogle-sheets
Julie Swedberg
Julie Swedberg

Reputation: 33

Having trouble with loops

I am completely new to coding (accountant by training) and I'm trying to develop a loop in sheets that based on an answer, hides or shows a range of data existing in another sheet. I've got it built manually right now, but it is sooooo slow, so I'm hoping I can get some advice on how to build the same function with a loop. Any help would be greatly appreciated. Sample code below...

//provides code on how to hide or show rows depending on a cell value

function HideRow() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var areas = ss.getSheetByName("areas");
  var Budget = ss.getSheetByName("Budget by Month");

  //REVENUE SECTION

  //provides code on hiding tuition section
  var tuition = areas.getRange('B7').getValue();
  if (tuition == "no"){
    Budget.hideRows(4,6);}
  else {
    Budget.showRows(4,6);
  }
  //provides code on hiding financial aid section
  var aid =areas.getRange('B8').getValue();
  if (aid == "no"){
    Budget.hideRows(10,8);}
  else {
    Budget.showRows(10,8);
  }
  //provides code on hiding student fees section
  var fees =areas.getRange('B9').getValue();
  if (fees == "no"){
    Budget.hideRows(18,16);
  }
  else {
    Budget.showRows(18,16);
  }
}

Upvotes: 3

Views: 68

Answers (1)

buræquete
buræquete

Reputation: 14698

It is not easy to create a loop, since values for rowStart & rowEnd do not follow a structure it seems. But I managed to made it more compact and readable;

function HideRow() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var areas = ss.getSheetByName("areas");
  var Budget = ss.getSheetByName("Budget by Month");

  //REVENUE SECTION
  var json = JSON.parse(''); //use my json here

  json.forEach(function(item) {
     if (areas.getRange(item.range).getValue() == "no"){
        Budget.hideRows(item.startRow, item.endRow);
     }
     else {
        Budget.showRows(item.startRow, item.endRow);
     }
  });
}

You can create a JSON object to store these variables, this way it will be a pure loop logic;

[{ "range" : "B7", "startRow" : 4, "endRow" : 6 }, { "range" : "B8", "startRow" : 10, "endRow" : 8 }, { "range" : "B9", "startRow" : 18, "endRow" : 16 }]

Upvotes: 1

Related Questions