I'm not a scripting expert by any means, but I put together some code that I needed for Google Sheets. The good news is that it actually works!
However, it's noticeably slow to run (about 15 seconds). I'm sure it's not optimized to deal with arrays and other fun things that I don't fully understand...
function ShowHideClosedCards() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var lastRow = sheet.getLastRow();
strVisible = sheet.getRange('N1').getValue(); //Get the value of N1
if (strVisible == '** Closed accounts are visible **') { //If the closed accounts are visible...
for (i = 6; i <= lastRow; i++) { // Start with row 6 and continue through the last row: i <= lastRow
var status = sheet.getRange("F" + i).getValue(); //Get the value of the cell.
if (status !== "") { // If there's something in the "Date Closed" cell, then that should mean it's closed
sheet.hideRows(i); // Hide the row
}
}
sheet.getRange('N1').setValue('** Closed accounts are hidden **');
} else { // Otherwise, assume the closed accounts are hidden...
for (i = 6; i <= lastRow; i++) { // Start with row 6 and continue through the last row: i <= lastRow
var status = sheet.getRange("F" + i).getValue(); //Get the value of the cell.
if (status !== "") { // If there's something in the "Date Closed" cell, then that should mean it's closed
sheet.showRows(i); // Show the row
}
}
sheet.getRange('N1').setValue('** Closed accounts are visible **');
}
}
The gist of it is that it should go through each row (from 6 through 500) and if there's anything in column F of that row, hide the row. Then it marks one cell (N1) to let the user know that the closed accounts are hidden.
If the user runs the code again, it should do the same thing, but unhide those rows (and only those rows). Then it marks N1 again to let the user know that the closed accounts are visible.
Any suggestions on how to get it to run faster?
-- Jim
Aucun commentaire:
Enregistrer un commentaire