Automatically Hide Rows in Google Spreadsheet Based on Multiple Conditions Based on a Cell Result
HTML-код
- Опубликовано: 11 сен 2024
- If you want to be a Full Fledge Member of our Team, please join the link below.
/ @integratedschoolforms
Help us keep our channel going!!!
Please show your support to our Channel by donating any small amount to keep our projects going, you can send your donations to:
Bank Transfer:
Landbank of the Philippines Account
Account Name: Wedzmer B Munjilul
Account Number: 0165 0409 29
Unionbank Account
Account Name: Wedzmer Briz Munjilul
Account Number: 1094 2956 4720
Coins.Ph: tinyurl.com/is...
Paypal: paypal.me/wedzmer
GCash: +63 916 192 5905
GoFundMe: bit.ly/HelpOur...
Even a P1.00 donation goes far.
===========================================================
Details:
You can view the codes used in this tutorial and its explanation from the link below. We also included a link for the file used in the demonstration.
eduknasyon.blo...
What if there are multiple tabs in the active spreadsheet? The code isn't running for me so I think that is the reason as everything else is exactly as you've written it.
it doesn't make any difference even if you have multiple tabs in the same spreadsheet for as long as the script is assigned on a button within a specified sheet. the script won't run within the app script window if you have multiple tabs since there's no active sheet specified in the app script window.
Nice! Right on time - it is like the instructions are done for me - I just needed them and I just implement it
Thank you so much!
Glad it helped!
Hello, please help me. This is the first time I am trying to use AppScript. I copied everything from you perfectly. Our tables are also similar. When I run the script it works without errors, but nothing happens except that all the area included in the script is selected. And - when I printing - script button is printable
That would be a bit difficult to determine since I haven’t seen your whole code. But I’ll guess, this might be with the counted columns, the script didn’t recognize the supposed helper column in your sheet.
thank you for your reply. Although I'm sure I rewrote everything perfectly, when I did a copy/paste of your code it worked. Amazing! I will definitely watch all the videos on your channel. I am really looking for help with one question: when you upload a picture to a Google sheet above the cells - after a while it looks broken and I have to upload it again. however, it makes it even more difficult if the image is active with a script - I need to bind it again to make it active and use it instead of the button.
uploading images on google spreadsheet is rather tricky. you need to make sure that the cells' row and column are of the same sizes when you place it there and make sure that there are no hidden rows above it. but sometimes, when you open the sheet and the images are broken or moved or missing, you just move to another sheet and go back to that sheet, it'd be back to normal. also, refreshing the sheet works sometimes.
@@IntegratedSchoolForms wow! never thought about it. Seems like a simple solution. My decaying pictures never fit into one cell. Hidden columns and rows are also always present
what I usually do is not hide rows if there would be images below it. I just resize the rows/columns to it's shortest size possible. Maybe 1 or 2, depending on the sheet. That way the rows/columns are still present and make sure the images are hovering over the rows that are visible above it.
Nice sir, but I get an error on the run time.
type error : Cannot read properties of undefined (reading '0')
double-check your appscript loop code. make sure that its characters are the same as what is provided in the video. alternatively, you can just go to the article published on our website, the link of which is available in the description box above.
I appreciate your script, and I'm trying to use it on my own spreadsheet. It's a simple spreadsheet, that has either "YES" or "NO" in column J. I want to hide the rows that have "NO" in them. It executes okay, so there's no syntax errors, but the results fails to hide all of the "NO" rows, and at the same time hides some of the "YES" rows. My script is below. Do you see where I might have gone wrong? Thank you in advance.
function AutoHidingNoStocks() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); //Pinpoints sheet working on
sheet.showRows(1, sheet.getLastRow()); // this line ensures that all rows in the sheet are visible.
var range = sheet.getRange("J3:J" + sheet.getLastRow()); //check all rows in col J
var values = range.getValues(); //Check all values in column J
var numRows = range.getNumRows(); // Determines number of rows in specified range
// Loop which when initiated reiterates through each row in the specified range.
for (var i = 0; i < numRows; i++) {
if (values[i][0] == "NO") {
sheet.hideRows(i+1); Checks if value in column J is equal to “NO” then hide it.
}
}
}
p.s.: On this line: if (values[i][0] == "NO") {
what is the bracketed 0 for?
Thank you!
The bracketed 0 refers to the index of the column within the range. In your case, since you're using it for column J, as it is the 10th column, it will be recognized as the 9th index since indexes starts at 0.
The issue in your code, if I'm not mistaken, is the appscripts counting the rows starting from 1. Unlike other programmable language, javascripts in google appscript starts at 0 or zero-based. So, when we're going to adjust the number of row in your formula, it should be:
sheet.hideRows(i + 3); // Adjusted to match row number in the sheet
Since i = 0 and you start the count by row 3 ( 0 + 3 = 3rd row).
@@IntegratedSchoolForms - Thank you sir, that was EXTREMELY helpful. My script works perfectly now! So, per your advice I changed the line in my code to read:
sheet.hideRows(i+3);
This is the entire script now:
function AutoHidingNoStocks() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.showRows(1, sheet.getLastRow()); // this line ensures that all rows in the sheet are visible.
var range = sheet.getRange("J3:J" + sheet.getLastRow());
var values = range.getValues();
var numRows = range.getNumRows();
// Loop which when initiated reiterates through each row in the specified range
for (var i = 0; i < numRows; i++) {
if (values[i][0] === "NO"){
sheet.hideRows(i+3); // Checks if the value in column J for a specified row would be equal to "NO" then it should hide it.
}
}
}
Since that code is within the "for loop", and executes every time it finds a 'NO" value, why doesn't it want to advance 3 more rows each and every time?
This line of code: sheet.hideRows(i+3);
I can understand the first time, since I start my count by row 3, but the "+3" logic every time would make me think it's going to skip 3 rows each time it runs. This is probably a dumb question, since the code works perfectly now, but that one line still baffles me a bit.
p.s.: I hate to admit this, but I'm a retired computer programmer! I've worked on everything including RPG, Cobol, PL/SQL and Oracle Forms development. Maybe it's just harder for a programmer to learn a new language, since we have to "unlearn" what we're accustomed to! lol
Anyhow, thank you so much for your help!!!!! Roger
I'm bad at SQL actually. 🤣 I'm still trying to learn all of that. Maybe javascript just isn't that much confusing since I was using it on html years back. Anyway, to answer your question... As mentioned previously, google spreadsheet counts the rows but the indexes would begin with 0. Since you decided to start the script come the 3rd row, we used i+3 which would mean 0+3 = 3rd row. This loop will continue with i = 1 where 1+3 = 4th row; i=2 where 2+3 = 5th row and so on. So, we basically told the script to start counting from 3.
@@IntegratedSchoolForms - Thanks, I got it now. Thank you for this explanation. I think it was probably the use of the index that was getting me confused. I haven't tried it, but since the index in Javascript always starts with zero, I wonder if the bracketed [0] depicting the index, could be omitted and Javascript would just default to zero. I know, I'm over-thinking this! 😂
The other line in my script that was confusing, was the "if" statement:
if (values[i][0] === "NO"
So values is set in the code above, where "values" is assigned the range starting in row 3. And my "if" statement looks at the first row in that range (or occurrence), since "i" at the first iteration is = 1 (which is the 3rd row); and gets incremented throughout the code.
It's really helped me, by both reading your explanations and me just talking it through. I've seen other RUclips videos on JavaScript for Google, but your's was way better by definitively explaining what you're coding and why.
Thank you again!!! If I was in your city, I'd come take one of your classes; if you teach in a classroom setting that is. I'm in Arizona in the U.S.
Glad I was able to help. Maybe providing annotations with your code could help address what confuses you in the if statement:
function AutoHidingNoStocks() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.showRows(1, sheet.getLastRow()); // Make sure all rows are visible
var range = sheet.getRange("J3:J" + sheet.getLastRow());
var values = range.getValues(); // Get all values in the range
var numRows = range.getNumRows(); // Get the number of rows
for (var i = 0; i < numRows; i++) {
if (values[i][0] === "NO") { // Check if the cell value is "NO"
sheet.hideRows(i + 3); // Hide the row (adjusting for row 3)
}
}
}