Hiding rows based on range length and client's name

user5Zic1 0
Viewed 25

I have this code, I'm trying to hide some rows based on the result of a filter. Here a sample sheet

Basically, I'm trying to build a bill based on a list of transactions/Clients. I use filter and query to have the transaction I want in the sheet "facture" which is my template for the bill.

In row 1003 I have the total of the bill + VAT I would like to hide rows 19 to 23 if the client's name is not "PUB" I would like to hide rows 1016 to 1042 if the client's name is not VSPE and finally I would like to hide every row between the last row of the bill from row 22 to row 999 that is empty.

function hideBill() {
 // Define your variables here
var ss = SpreadsheetApp.getActive();
var facture_sheet = ss.getSheetByName("Facture");
var input_sheet = ss.getSheetByName("Input");
var clientname=input_sheet.getRange("ClientName").getValue();

if(clientname=='PUB'){
      facture_sheet.showRows(19,1042);
      facture_sheet.hideRows(1018,24);
    }else if (clientname=='C'){
      facture_sheet.hideRows(19,3);
      facture_sheet.showRows(22,1022);
     }else {
      facture_sheet.hideRows(19,3);
      facture_sheet.showRows(22,1017);
      facture_sheet.hideRows(1018,24);
    }


facture_sheet.getRange("a22").activate();
facture_sheet.getRange("a"+(getLastDataRow(facture_sheet)+1)+":g999").activate();
facture_sheet.hideRows(ss.getActiveRange().getRow(), ss.getActiveRange().getNumRows());
facture_sheet.getRange("a22").activate();
}


function getLastDataRow(sheet) {
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange("e" + lastRow);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }              
}

It is not working correctly and I can't figure out where my mistake is. Can someone help me to find the problem? thanks a lot !! j.

google-sheets google-apps-script
1 Answers

Issue:

You want to do the following:

  • Hide or show rows 19-23 depending on whether clientname is PUB or not.
  • Hide or show rows 1016-1042 depending on whether clientname is VSPE or not.
  • Hide or show rows 22-999 depending on whether the row is empty or not.

Solution:

  • Use hideRows and showRows for each of these conditions. In each case, if two parameters are provided, the first one will correpond to the first row index, and the second one to the number of rows to hide/show.
  • For hide/show empty rows, loop through all values in your range and check whether the row is empty, for example using every.

Code sample:

function hideBill() {
  var ss = SpreadsheetApp.getActive();
  var facture_sheet = ss.getSheetByName("Facture");
  var input_sheet = ss.getSheetByName("Input");
  var clientname=input_sheet.getRange("ClientName").getValue();
  if (clientname === 'PUB') facture_sheet.showRows(19, 5); // Hide/show rows 19-23
  else facture_sheet.hideRows(19, 5);
  if (clientname !== 'VSPE') facture_sheet.showRows(1016, 27); // Hide/show rows 1016-1042
  else facture_sheet.hideRows(1016, 27);
  var firstRow = 22;
  var values = facture_sheet.getRange(`${firstRow}:999`).getValues();
  values.forEach((row,i) => { // Hide/show empty rows 22-999
    var isEmpty = row.every(cell => cell === "");
    if (isEmpty) facture_sheet.hideRows(firstRow + i);
    else facture_sheet.showRows(firstRow + i);
  });
}
Iamblichus0
Related