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());
s.getRange('D:D')
.getValues()
.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.
I am a WordPress product developer and creator of Image Attributes Pro. I am passionate about solving problems and travelling the world.
Hey Arun,
Thanks for your valuable post. will implement same.
Hi!
It works, but … it doesn’t if there are more than 300 rows. Any sugestions?
Hey Arun,
Thank you needed this for a work related task 🙂
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());
s.getRange(‘D:D’)
.getValues()
.forEach( function (r, i) {
if (r[0] == ‘Completed’)
s.hideRows(i + 1);
});
}
I got the same, any suggestions on how to fix this?
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.
I’ve edited the three variables to correctly refelct my needs, yet I still receive the same bug as others. What am I missing?
The issue should be that `var s` is null. Do you have a sheet named `Todo`?
Hi there, Thanks so much for this. Everything works great, except it won’t perform the function automatically. I have to manually go into Script Editor each time there is an update to run the function. I noticed you mentioned above that it should update each time we load the sheet. I assume that means each time we refresh or re-open it. Is there some way to make this genuinely automatic?
Thanks so much!
I got this to work on one sheet (thank you so much!). How would you adjust the code to run through 2 specific sheets in a workbook (but not the entire workbook)?
This script only hides once I close the spreadsheet and open again. If I change it to onEdit then it unhides the lines that were hidden each time there is an edit to any cell. Is there a way to stop this.
Hello,
Thank You for this script!
This works great for me when I applied to to 1 sheet but I was wondering how would I change this script to apply to my entire spreadsheet?
Hi, thank you for this. I’m trying to hide rows that have numbers less than 5. I can replace ‘Done’ with a single number which does that but how can I, for instance, write <5 for the script to hide all lines containing <5?