In Google Sheets, I have a tab titled "Scheduled" that has all our client's import dates. I need to automatically hide (NOT delete) rows on that tab if the import date (column D) is less than or equal to the current date. Basically, automate right-clicking on the row and selecting "Hide Row" based on the parameters listed above.
Completely new to Google Apps Script so any help is greatly appreciated.
CodePudding user response：
=$D1<edate(today(),6)
5 Steps:
- Select the cell, cell
D1 - Go to Format, Conditional Formatting
- Under Format Rules, scroll to the bottom of the drop-down list to find "Custom Formula is"
- In the text input box, copy and paste the code above
- Change the text and background colours to white. To change the colour, use the Formatting options
Please note: when changing cell, change the code D1 to the current cell.
CodePudding user response：
Hide old rows
function hideOldRows() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Sheet0');
const sr = 2; //data start row
const dv = new Date().valueOf();
sh.getRange(sr,4,sh.getLastRow() - sr 1).getValues().flat().map((d,i) => [new Date(d).valueOf(),i sr]).filter(r => r[0] < dv).forEach(r => sh.hideRows(r[1]));
}
Sheet0 before:
Sheet0 After:
CodePudding user response：
Try this in your script editor:
function hideRows() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName("Scheduled"); //get Scheduled sheet
var dateRange = sh.getRange(2, 4, sh.getLastRow()-1, 1); //get column D range
var dates = dateRange.getDisplayValues(); //get the values of column D using the range fetched above
var currentDate = new Date(); //get current date
for(var i = 0; i < dates.length; i ){
var date = new Date(dates[i][0].replace(/-/g, '\/').replace(/T. /, '')); //convert column D values to date
if(date.valueOf() <= currentDate.valueOf()){ // check if the date is less than or equal to current date
sh.hideRows(i 2); // hide row
}
}
}
Test Data:
Output:
