Google Script to hide rows in Google Sheets

1
votes

I have Google sheet with Product Type column (with following dropdown values Type 1, Type and Type) and rows as shown in picture. I am trying to achieve using google script to show only the rows based on Product Type value (if I select Type 1, I want to hide the rows which have Type 2 and Type 3 values in the same column). Here I cannot use a filter because there are some titles in some rows. Any help would be greatly appreciated.

Screenshot

enter image description here

1
javascriptgoogle-apps-scriptgoogle-sheetsscripting
Welcome. Would you please share a copy of your spreadsheet, Would also explain your comment about filter "Here I cannot use filter because there are some titles in some rows." – Tedinoz
I agreed with Tedinoz. Could you share some code and give us more details about your question? Here a link to learn how to do a good question stackoverflow.com/help/how-to-ask – Pankwood
Please see if this answers your query - yagisanatode.com/2018/05/26/… – Sourabh Choraria

1 Answers

0
votes

You want to hide or show rows, depending on the selected type in cell A1

Here is a simple script that runs automatically onEdit, i.e. every time a cell in the spreadsheet is edited. It uses the methods showRows() and hideRows() and compares the entries in column A against the value in cell A1. It hides all the rows where the entry contains the word "Type", but is not the selected type.

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1"); // adapt to your needs
  var toShow=sheet.getRange("A1").getValue();
  var startRow=10 //adapt to your needs
  var lastRow = sheet.getLastRow();
  Logger.log(lastRow);
  for (i = startRow; i <= 320; i++) {
    var type = sheet.getRange("A" + i).getValue();
    if (type == toShow||toShow=="Product Type") {
      sheet.showRows(i);
      Logger.log('row '+i+' shown');
    }
  }
  for (i = startRow; i <= lastRow; i++) {
    var type = sheet.getRange("A" + i).getValue();
    if (type != toShow && type.indexOf('Type')!=-1) {      
      Logger.log(i+'i hidden');
      sheet.hideRows(i);
    }
  }
}

You need to bind the script to your spreadsheet and run it manually once - to trigger authorization flow.