CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsmultiple-columnshideshow
Jgjc Me
Jgjc Me

Reputation: 1

Hide/show columns in google sheet based on cell value

I made a grade book with students names in column A , B for type of marks week 1 C,D,E,F week 2 G,H,I,J and so on till week 14

what I want is to hide all weeks except what am using by choosing from cell A1 the week that is shown this way I can show and hide columns dynamically.

this was the result:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var cell = e.range.getA1Notation();
  if (cell === "A1") {
    var cellValue = sheet.getRange(cell).getValue();
    var numColumns = sheet.getMaxColumns();
    for (var i = 1; i <= numColumns; i++) {
      var headerValue = sheet.getRange(1, i).getValue();
      if (headerValue === cellValue) {
        sheet.showColumns(i);
      } else {
        sheet.hideColumns(i);
      }
    }
  }
}

the problem is that it's very slow can someone help me make it faster please ?

Upvotes: 0

Views: 537

Answers (1)

Tanaike
Tanaike

Reputation: 201428

Modification points:

  • When getValue() is used in the loop, the process cost becomes high.
  • I thought that showColumns can be moved out of the loop.
  • sheet.getRange(cell) can be modified to e.range.
  • I thought that the event object can be used more.

When these points are reflected in your script, how about the following modification?

Modified script:

function onEdit(e) {
  var { range, source } = e;
  var { columnEnd, columnStart, rowEnd, rowStart } = range;
  if (columnEnd == 1 && columnStart == 1 && rowEnd == 1 && rowStart == 1) { // This is "A1". // or if ([columnEnd, columnStart, rowEnd, rowStart].every(f => f == 1)) {
    var sheet = source.getActiveSheet();
    var cellValue = range.getValue();
    var numColumns = sheet.getMaxColumns();
    var headerValues = range.offset(0, 0, 1, numColumns).getValues()[0];
    sheet.showColumns(1, numColumns);
    headerValues.forEach((e, i) => {
      if (e != cellValue) {
        sheet.hideColumns(i + 1);
      }
    });
  }
}
  • I guessed that by these modifications, the process cost might be able to be reduced a little.

Note:

  • In this modification, it supposes that your showing script works fine while the process cost is high.

References:

Upvotes: 0

Related Questions