CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Josh Jackson
Josh Jackson

Reputation: 1

Is there a way to write an app script that will automatically hide rows in multiple tabs (3) based on a date?

I am a teacher and I need to figure out how to autohide a row/rows that are 12 days old from from the current date. The "6 Incident", "7 Incident", and "8 Incident" tabs are all mirrors of each other. Meaning, the columns replicate each other, they are just used in different grades. So, Column D in "6 Homework" is the same as Column D in "7 Homework".

The reason this script would be so helpful is it will allow us to see the up-to-date incidents and "recycle" by hiding the out of date incidents.

Ultimately, I'd love for the script to automatically hide the rows when the date an interaction is posted (Column D) is 12 days old. This could be time-based, so at 2 a.m. each morning, the script could run and automatically hide any older incident in all three tabs.

The tabs are formatted to turn white once an incident is older than 12 days. So any white rows would automatically be hidden so the spreadsheet isn't ever 100s of rows down, and current information can be found quicker.

Hopefully all this makes sense. I really appreciate anyone who can help. This would help many teachers out for this upcoming school year.

Josh

Link to spreadsheet: https://docs.google.com/spreadsheets/d/1f66nEFl0xhIEVumiwx1Dsjv0gbLIy1SnR-h64tKpQtk/edit?usp=sharing

Upvotes: 0

Views: 308

Answers (1)

TheWizEd
TheWizEd

Reputation: 8598

Here is an example of how to hide the rows older than 12 days.

Code.gs

function timerHideRows() {
  try {
    // Install the timer to run every day at 2:00 AM
    let sheets = ["6 Incident","7 Incident","8 Incident"];
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let today = new Date();
    today = new Date(today.getFullYear(),today.getMonth(),today.getDate()-12);
    sheets.forEach( name => {
        let sheet = spread.getSheetByName(name);
        let dates = sheet.getRange(3,4,sheet.getLastRow()-2,1).getValues();
        dates.some( (value,index) => {
            let day = value[0];
            if( day == "" ) return true;
            day = new Date(day.getFullYear(),day.getMonth(),day.getDate());
            if( day.valueOf() < today.valueOf() ) {
              if( !sheet.isRowHiddenByUser(index+3)) {
                sheet.hideRow(sheet.getRange(index+3,1));
              }
              return false;
            }
          }
        );
      }
    );
  }
  catch(err) {
    Logger.log(err);
  }
}

References

Upvotes: 0

Related Questions