CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
cjvdg
cjvdg

Reputation: 533

Doesn't hide all the empty rows based on the certain cell

I'm currently working with this spreadsheet: LINK

So the problem is that in the number 20 in the male section (row 31), that row doesn't hide when the hide row button has been clicked. All empty cells in column B should be hidden when the hide rows button is clicked.

Picture

Here's the code that I'm currently using:

function doGet(e) {
  this[e.parameter.run](e.parameter.sheetName || null);
  return ContentService.createTextOutput();
}

function HideRows() {
  const activeSheet = SpreadsheetApp.getActiveSheet();
  const url = ScriptApp.getService().getUrl();
  UrlFetchApp.fetch(url + "?run=script_HideRows&sheetName=" + activeSheet.getSheetName(), {headers: {authorization: "Bearer " + ScriptApp.getOAuthToken()}});

// DriveApp.getFiles()  // This is used for automatically detecting the scope of "https://www.googleapis.com/auth/drive.readonly". This scope is used for the access token.
}

function showRows() {
  const url = ScriptApp.getService().getUrl();
  UrlFetchApp.fetch(url + "?run=script_showRows", {headers: {authorization: "Bearer " + ScriptApp.getOAuthToken()}});
}

var startRow = 11;
var colToCheck = 2;

// This script is the same with your "HideRows".
function script_HideRows() {
  var sheetNames = ["AP_Q1", "AP_Q2", "AP_Q3", "AP_Q4", "SUMMARY OF QUARTERLY GRADES"];  // Please set the sheet names here. In this case, 4 sheets are used.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheets().forEach(sheet => {
    var sheetName = sheet.getSheetName();
    if (sheetNames.includes(sheetName)) {
      if (sheetName == "SUMMARY OF QUARTERLY GRADES") {  // When the sheet is "SUMMARY", the start row is changed.
        startRow = 12;
      }
      var numRows = sheet.getLastRow();
      var elements = sheet.getRange(startRow, colToCheck, numRows).getValues();
     
      for (var i=0; i < elements.length; i++) {
        if (shouldHideRow(sheet, i, elements[i][0])) {
          sheet.hideRows(startRow + i);
        }
      }
      // Hide the rest of the rows
      var totalNumRows = sheet.getMaxRows();
      if (totalNumRows > numRows)
        sheet.hideRows(numRows+1, totalNumRows - numRows);
    }
  });
}

// This script is the same with your "showRows".
function script_showRows() {
  // set up spreadsheet and sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
//  var ss = SpreadsheetApp.getActiveSpreadsheet(),
  var sheets = ss.getSheets();

  for(var i = 0, iLen = sheets.length; i < iLen; i++) {
    // get sheet
    var sh = sheets[i];

    // unhide columns
    var rCols = sh.getRange("1:1");
    sh.unhideColumn(rCols);

    // unhide rows
    var rRows = sh.getRange("A:A");
    sh.unhideRow(rRows);
  }
};

function shouldHideRow(ss, rowIndex, rowValue) {
  if (rowValue != '') return false;
  if (ss.getRange(startRow + rowIndex, colToCheck, 1, 1).isPartOfMerge()) return false;
  if (ss.getRange(startRow + rowIndex + 1, colToCheck, 1, 1).isPartOfMerge()) return false;
  return true;
}

What seems to be the problem?

Upvotes: 0

Views: 79

Answers (1)

Tanaike
Tanaike

Reputation: 201338

When I saw both your Spreadsheet and your sample image, I thought that the values of "#REF!" and "" are required to be used. I think that the reason of your current issue might be due to this. So in your script, I think that it is required to add one more condition to the function shouldHideRow as follows.

Modified script:

function shouldHideRow(ss, rowIndex, rowValue) {
  if (rowValue == "" || rowValue == '#REF!') return true; // <--- Added
  if (rowValue != '') return false;
  • By this modification, when the cell value is "#REF!" and "", sheet.hideRows(startRow + i) is run.

Upvotes: 2

Related Questions