Google Sheets - Issues with building template (hiding/unhiding rows, columns and tabs)

Question

I have a Google Sheet in which users have to fill out a table with customer info by selecting values from dropdowns. Based on the info provided, different tabs, rows and columns will be hidden/unhidden so that the Google Sheet is relevant for that specific customer project.

This is an example of a customer info table, which is on Sheet 1: | Customer Name | Random Inc. | | --------------- | ------------- | | Customer Level? | Level 2 | | Geography? | North America | | Sales in Scope? | Yes | | Contract signed?| Yes |

I currently have 3 issues with my template I would like to address:

1. How to hide rows based on value in cell?

I am currently using row numbers to hide specific rows on different sheets, which is difficult to maintain as rows get added/removed monthly.

if (n2=="No"){sh1.hideRows(125,9), sh5.hideRows(112,2)}else{sh1.showRows(125,9), sh5.showRows(112,2)};

or

if (n4=="No"){sh1.hideRows(112,13)}else{sh1.showRows(112,13)};

I would much rather be able to hide/show rows based on the value in one of its columns. So for example:

If dropdown value is "North America", then hide all rows that do not have "North America" in column A or
If dropdown value is "No", then hide all rows that do not have "Sales" in column B

2. Hide/unhide columns on different tabs.

With my current code, I only seem able to hide the relevant column on one sheet. Is there a way to hide/show that column on multiple sheets using this code?

if (n1=="Level 2"){sh1.showColumns(25), sh5.showColumns(25), sh9.showColumns(3)}else{sh1.hideColumns(25). sh5.hideColumns(25), sh9.hideColumns(3)}; //Show or hide Level 2 services

3. Adding a clickable button to run my script once the user has filled out the table.

I am currently using the onEdit(e) function to run my script. So as users fill out the table my Google Sheet gets updated and rows/tabs are hidden/unhidden.

I would prefer to have a clickable button on Sheet 1 that users need to click after completing the customer info table. This would then run my script and users would get a pop-up box to say the template is built.

Is this possible?

This is my current code, which uses the onEdit function:

It is entirely possible that my code is complete rubbish, but I am very new to App Script so please bare with me :-)

function onEdit(e) {
  const n1=e.source.getRangeByName("CustomerLevel").getValue();
  const n2=e.source.getRangeByName("Geography").getValue();
  const n3=e.source.getRangeByName("SalesScope").getValue(); 
  const n4=e.source.getRangeByName("ContractSigned").getValue();
  const sh1=e.source.getSheetByName("Sheet 1");
  const sh2=e.source.getSheetByName("Sheet 2");
  const sh5=e.source.getSheetByName("Sheet 5");
  const sh6=e.source.getSheetByName("Sheet 6");
  const sh9=e.source.getSheetByName("Sheet 7");
  if (n1=="Level 2"){sh1.showColumns(25), sh5.showColumns(25), sh9.showColumns(3)}else{sh1.hideColumns(25). sh5.hideColumns(25), sh9.hideColumns(3)}; //Show or hide Level 2 services
  if (n2=="North America"){sh1.hideRows(125,9), sh5.hideRows(112,2)}else{sh1.showRows(125,9), sh5.showRows(112,2)}; //Show or hide North America rows>>
  if (n3=="No"){sh1.hideSheet(), sh2.hideSheet()}else{sh1.showSheet(), sh2.showSheet()}; //Show or hide Sales sheets
  if (n4=="No"){sh1.hideRows(112,13)}else{sh1.showRows(112,13)}; //Show or hide contract rows>>

That looks a bit complex for an onEdit. I don't see how you are limiting the script from all of the edits from not involved sheets. If you plan to use this for more than one person at a time I personally would not trust it. From the looks of your code I would guess that you might not have created a webapp yet and yet that is probably what I would be using in this sort of situation. — Cooper

Carlos M Carlos M · Accepted Answer · 2021-01-27T16:10:53

Solutions:

1. How to hide rows based on value in cell?

You can use for loop to scan a specified range row by row and show/hide rows based on value:

Sample Data:

Sample Code:

function hideShow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var dropdownValue = sheet.getRange("A2").getValue();
  var lr = sheet.getLastRow();
  var dataValues = sheet.getRange(5,1,lr-4).getValues();
  for (var i=0; i<dataValues.length; i++) {
    if (dataValues[i] != dropdownValue) {
      sheet.hideRows(i+5);
    }
    else { 
      sheet.showRows(i+5); 
    }
  }
}

2. Hide/unhide columns on different tabs.

Different code instructions need a semicolon delimiter:

if (n1 == "Level 2") {
  sh1.showColumns(25);
  sh5.showColumns(25); 
  sh9.showColumns(3); 
}
else {
  sh1.hideColumns(25); 
  sh5.hideColumns(25); 
  sh9.hideColumns(3);
}

3. Adding a clickable button to run my script once the user has filled out the table.

You can draw a button using Insert->Drawing menu, then right-click on the drawing, ... option inside the drawing and choose Assign Script. In my example in item #1:

Google Sheets - Issues with building template (hiding/unhiding rows, columns and tabs)

1 Answers

Solutions: