Hide Rows In Google Sheets Based On Value Of Cell

I am a fan of Google sheets for collaborative work.

To manage an ongoing Todo for the development of Super Progressive Web Apps WordPress Plugin, me and my partner uses Google sheets.

We use data validation to set the status of an assigned task. Once the task is done, it is marked as Done. The following script cleans up our Todo each time we load the sheet by hiding the completed tasks.

function onOpen() {
  var s = SpreadsheetApp.getActive().getSheetByName('Todo');
  s.showRows(1, s.getMaxRows());
    .forEach( function (r, i) {
    if (r[0] == 'Done') 
      s.hideRows(i + 1);

Go to Google Sheets > Tools > Script Editor and add the above code. Replace highlighted parts with the column you have the conditional data and the value to check for.

Of course, hidden tasks can be easily brought back, so the history is safely recorded.

Hope you find it useful.

Hello, I am Arun Basil Lal. Thank you for reading!

I am a freelance WordPress developer with over a decade of experience. I am passionate about making things and traveling the world.

Divi WordPress Theme - My Review

Divi WordPress Theme
Divi is a WordPress theme that web designers do not want you to know. It comes with a drag-and-drop theme builder. You can build beautiful looking unique websites without touching a line of code. Just choose from one of the many pre-made layouts, or pick elements and arrange them any way you like.

Divi is every WordPress developer's wet dream. Surprise your clients with neat responsive websites and have fun building them.

Divi comes from Elegant Themes. If you enjoy building websites, you *need* an Elegant Themes membership. 87 beautiful themes and 5 plugins for the cost of less than a candy-bar each!

Note: I am an avid user of Divi myself and this is a honest review. I wouldn't recommend something that I do not personally find amazing.


  1. Praveen says:

    Hey Arun,

    Thanks for your valuable post. will implement same.

  2. Rakun says:


    It works, but … it doesn’t if there are more than 300 rows. Any sugestions?

  3. Jas Aujla says:

    Hey Arun,

    Thank you needed this for a work related task 🙂

  4. talha says:

    TypeError: Cannot call method “showRows” of null. (line 3, file “Code”)

    Getting the above mentioned error for the following code

    function onOpen() {
    var s = SpreadsheetApp.getActive().getSheetByName(‘Todo’);
    s.showRows(1, s.getMaxRows());

    .forEach( function (r, i) {
    if (r[0] == ‘Completed’)
    s.hideRows(i + 1);

  5. BY0LOG1C says:

    In simple words, the error explains that it couldn’t find a sheet named “Todo”. Either rename your sheet to “Todo” or edit the code so that it look for the actual name of your sheet.

    Similarly, the script is looking for data in the column “D” and only look for the “Completed” keyword to hide rows. You should edit these three(3) variables to reflect your own needs.

    Thanks to the author, this worked perfectly.

  6. Brooklyn says:

    I’ve edited the three variables to correctly refelct my needs, yet I still receive the same bug as others. What am I missing?

Leave a Reply

Your email address will not be published. Required fields are marked *


Share With Friends