0

I am working on an app to remove old content from a spreadsheet. I have found several posts that hide or delete sheets based on specific data, but it is always in a single cell. I need something that checks dates in three cells per row.

The sheet is populated by a form that asks users to enter three dates they want a bulletin to run in our video and print bulletin at the HS where I teach. I just want to remove old data, and found countless (well, maybe 6 or 8) examples of scripts that did something similar, so I grabbed one and tried to expand the logic statement to check columns C, D, and E (using index, of course).

It works, but removes any row with a date that is not today rather than dates in all three columns. I also need to have it count cells that are empty as qualifying as old.

So I tried the script suggested below by Cooper, which is a big advancement. It is still skipping rows with empty values, and in one case removes a row that I don't want to remove In the image shown a form accepts input from teachers who submit bulletin content for a maximum of three dates. I've formatted the sheet to show old dates in red, today in green, and future dates in yellow. I want to delete rows that have all red or empty cells. A bandaid, however, would be to force them to pick a date for all three rather than allow non entries. But it would be nice to have the code recognize blank cells as qualifying. [![Data Sheet for daily bulletin][1]][1]

Here's what eventually worked. I'm sure there are some smoother ways of doing this, but I actually grew a bit in my programming ability by working things out through the log first, then moving on once I had evidence that what I was doing worked.

/*
Here is the thinking:
I need to have a script that is run from Add-ons that checks each cell of a range
for dates. It can be done through conditional formatting bg colors, but that is 
redundant. 

The first thing is to create the functions that find and return row numbers that have all 
old dates. In the initial build these can be returned to the log (Logger.log()) inside 
of a for(loop).
'*/

function readRows() {
  var sh=SpreadsheetApp.getActiveSheet();
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var rowsDeleted=0;
  var today=new Date().valueOf();
  //cycle through rows. In each row check column values for date. if date is old, add 1 to oldCount
  for(var i=vA.length-1;i>=0;i--) {
    var oldCount=0;
    if(new Date(vA[i][2]).valueOf()<today) {
    oldCount++;
    }
    if(new Date(vA[i][3]).valueOf()<today) {
    oldCount++;
    }
    if(new Date(vA[i][4]).valueOf()<today) {
    oldCount++;
    }
    if(oldCount>2) {
      sh.deleteRow(i+1);
      rowsDeleted++;
    }
  }

  Logger.log(rowsDeleted);
}

Next I want to figure out how to make something that will output the daily bulletin to a Doc or pdf.

1 Answer 1

1

Try this:

function readRows() {
  var sh=SpreadsheetApp.getActiveSheet();
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var rowsDeleted=0;
  var today=new Date().valueOf();
  for(var i=vA.length-1;i>=0;i--) {
    if ((new Date(vA[i][2]).valueOf()<today)||(new Date(vA[i][3]).valueOf()<today)||(new Date(vA[i][4]).valueOf()<today) ){
      sh.deleteRow(i+1);
      rowsDeleted++;
    }
  }
  Logger.log(rowsDeleted);
}

I'm not completely sure what you want but this might help. When your deleting rows you should start from the bottom.

Sign up to request clarification or add additional context in comments.

3 Comments

Thank you. This removes any row that has a previous date in it. I've done some ActionScript programming, so Javascript looks pretty familiar. I understand the looping should start from the bottom--I have no idea why the example I found goes the other way.
But: the if statement you use includes (vA[i][2]).valueOf(). I know that the i is the current value of the loop, referring to a row. The [2] is what I understand to be the third column, indexing from zero. If a cell is blank, I think that results in less than today.
It occurred to me to have each cell that passes the <today (or empty if that is different) test add 1 (++) to a variable eC (empty cell). If the row gets the variable up to 3, then it gets deleted. I'm not sure if I'm going about it the hard way. I guess I am complicating it if || means or. What I might try instead is &&.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.