CODEWITHSUNDEEP
google-sheets
davecut85
davecut85

Reputation: 263

Google Sheets: Hiding Rows Based on Cell Value

I am very new to Google scripts and have reviewed similar questions to the one I am asking now, but cannon seem to retro fit prior advice into what I'm looking to do. I have a simple "Yes/No" drop down in cell A2 of my sheet. If "Yes" is selected, I would like to hide row 5 and 7. I have been working on the below: 

 Prior Year my onEdit(e) {
  Logger.log('e.value: ' + e.value);

 var cellEdited = e.range.getA1Notation();
 Logger.log('cellEdited: ' + cellEdited);

if (cellEdited === "A2" && e.value === "yes") {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("TX MD 2017");
var maxRows = sheet.getMaxRows();

theSheet.hideRows(5,7);
 };
}

Any assistance or nudges would be very helpful.

Upvotes: 1

Views: 2841

Answers (1)

Joshua Dawson
Joshua Dawson

Reputation: 629

Code is picky about case

In your project description you talk about the options being 'Yes'/'No', with the 'Y' being capitalized. If this is indeed the case, then e.value === 'yes' won't work as expected. Be sure to make the strings the same case when comparing them:

`e.value.toLowercase() === 'yes'`

Wrong function: Sheet.hideRows

The API for hideRows states that the first parameter is the row to start from, and the second parameter is how many rows to hide after the starting row. This makes your code hide the 7 rows below and including the 5th. If you want to hide row 5 and 7, you should do:

theSheet.hideRow(5);
theSheet.hideRow(7);

Avoid adding unnecessary permissions

When you use SpreadsheetApp.getActiveSpreadsheet you make it so you'll need to allow the script to access and modify your spreadsheets. This breaks the security/protection principle of least privilege​; normally you want to give a user the least access possible to complete their task. Since you can access the spreadsheet within the event object using e.source, it would a good idea to just use that instead:

var ss = e.source;
var sheet = ss.getSheetByName("TX MD 2017");

// You don't use maxRows, so get rid of it
sheet.hideRow(5); // theSheet is not defined
sheet.hideRow7);

Prior Year my?

This is not valid JavaScript. It should be:

function onEdit(e) {
    // code
}

Upvotes: 1

Related Questions