How do I hide rows in a Google spreadsheet using a

Question

I'm using the below script to hide all rows with a value of "1" in column B.

function myfunction () {
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("CSSMaster");
  var maxRows = sheet.getMaxRows();

  //show all the rows
  sheet.showRows(1, maxRows);

  //get data from clumn B
  var data = sheet.getRange('B:B').getValues();

  //iterate over all rows
  for(var i=0; i< data.length; i++){
    //compare first character, if 1, then hide row
    if(data[i][0].charAt(0) == '1'){
      sheet.hideRows(i+1);
    }
  }
}
}

I go to my spreadsheet and enter the following: =myfunction(B:B) , I see "thinking" but nothing happens. What am I doing wrong? (I'm VERY new to Google scripts and am guessing this is a very basic error.)

Answer 1

Your instincts are right - it's a very basic error. First, make sure that you're actually running the code you intend to.

In this example, you've got a function inside a function. When you invoke myFunction() from the spreadsheet, execution will begin but won't find any code to run. Because onOpen() is enclosed in myFunction(), but there is no call in myFunction() to onOpen(), there's nothing to do.

Sort that out - you probably just need to get rid of the declaration and closing brace for onOpen().

Next, you're passing a 2-dimensional array of values when you invoke myFunction(B:B), but you have no parameter handing in the function. (You retrieve the values explicitly with getRange('B:B').getValues().)

General advice: learn some JavaScript (I recommend CodeAcademy), and try your hand at some of the Google Apps Script tutorials. With the competence and confidence from those, you should have no problem getting this working on your own.