Search code examples
google-apps-scriptgoogle-sheetsbutton

how to hide a row based on date in a cell plus 1 month?


I have a spreadsheet I'm using to track television program used for fundraising. I have a button set up with a script to hide rows containing expired programs (column K) that looks at today's date. I would like to change it to hide rows who's programs are expired as of 30 days from today. I can't seem to get that part to work. I copied code from a sample I found online: I know I don't need all of it but I haven't started to cull things until I get it working the way I want.

EDIT: I forgot to link to the sample spreadsheet: https://docs.google.com/spreadsheets/d/1VIWNJqZJFgQ-JV1QrgL9etJiwb4S3p2QyUr78HC9CdE/edit?usp=sharing

function HideExpired() {
const ss = SpreadsheetApp.getActive();
const sheet = ss.getSheetByName('PledgePrograms');
const timezone = ss.getSpreadsheetTimeZone();
const thisMonth = Utilities.formatDate(new Date(), timezone, 'yyyy-MM');
const nextMonth = thisMonth+1;
sheet.getDataRange().getValues().forEach((Column, index) => {
    const date = Column[10];
    const dateIsBeforeCurrentMonth = Object.prototype.toString.call(date) === '[object Date]'
        && Utilities.formatDate(date, timezone, 'yyyy-MM') < nextMonth;
if (dateIsBeforeCurrentMonth || status.match(/^(Done|Rejected)$/i)) {sheet.hideRows(index + `your     text`1);
}
});
}`

Solution

  • While I liked the approach taken by both answers I've received neither of them worked quite right in my sheet and I couldn't follow the logic enough to correct them (likely because I forgot to post the link to my sample spreadsheet.) I did more google searches and came up with this script that worked for me.

    function HideExpired () {
       var sheet = SpreadsheetApp.getActive().getSheetByName("PledgePrograms");
      var data = sheet.getDataRange().getValues();
      for(var i = 7; i < data.length; i++) {
        //If column C (3rd column) is "Y" then hide the row.
        if(data[i][11] === "True") {
          sheet.hideRows(i + 1);
        }
      }
    }