move a row in google spreadsheet based on todays date

Question

I'm trying to move a row of data from one sheet to another in the same spreadsheet based on a value of today's date.

In column "A", I have a date. I want to move the row if the date entered in column "A" is older than today's date. (it's a flight schedule for aircraft and I want to move flights that have already occured onto a sheet called "Past Flights".) The name of the active sheet is "Flight Schedule".

After the row is moved, I want it to delete off the "Flight Schedule" sheet. I know where to add scripts, but have no idea how to make this happen.

Here is what I have tried. I think on line "If (data.link >1..." data.link isn't the right one to use. But I can't find something for indicating older than todays date.

function approveRequests() {
 var ss = SpreadsheetApp.getActiveSpreadsheet()
 sheet = ss.getActiveSheet(),
 sheetName = sheet.getName(),
 data = sheet.getDataRange().getValues();
 if (sheetName == "Flight Shedule") {
  var range = sheet.getActiveRange(),
  startRow = range.getRowIndex(),
  numRows = range.getNumRows(),
  numCols = range.getNumColumns()
  if (numCols == 9) {
   if (data.length > 1) {
    var values = range.getValues(),
    nextSheet = ss.getSheetByName("Past Flight"),
    lastRow = nextSheet.getLastRow();
nextSheet.getRange(lastRow+1,1,numRows,3).setValues(values);
sheet.deleteRows(startRow,numRows);
   } 
  } 
 } 
}

Any help would be huge!

Thanks!

Answer 1

Ok, I will go in with some general tips based on your current code first.

In your function you do a sheet = ss.getActiveSheet() which is redundant because you already have SpreadsheetApp.getActiveSpreadsheet().Also I would recommend to avoid this

var ss = SpreadsheetApp.getActiveSpreadsheet()
 sheet = ss.getActiveSheet(),
 sheetName = sheet.getName(),
 data = sheet.getDataRange().getValues();

in favour of this:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var sheetName = sheet.getName();
var data = sheet.getDataRange().getValues();

which is much more easy to read and change without making mistakes.

data.length has nothing to do with current date, it will simply be the length of the array. So if you select 1 row of data, it will be 1, if you select 2 rows it will be 2 etc. .getValues() will return an array where data[row][col]. What you are looking for is getting the value of the flight time, converting it into a date object (not a google specific thing, just general javascript). Then use var now = new Date() and compare the two.

I would also recommend to re-think your if statements. There are a lot of better ways to grab the row data than selecting the row manually and then running the function. You can save a lot of lines of code should you decide to actually make this run automatically, because as it is, it will run only when called manually.

Answer 2

This sample is working:

function approveRequests() {

  // Initialising
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var scheduleSheet = ss.getSheetByName("Flight Shedule");
  var pastSheet = ss.getSheetByName("Past Flight");
  var lastColumn = scheduleSheet.getLastColumn();

  // Check all values from your "Flight Schedule" sheet
  for(var i = scheduleSheet.getLastRow(); i > 0; i--){

    // Check if the value is a valid date
    var dateCell = scheduleSheet.getRange(i, 1).getValue();
    if(isValidDate(dateCell)){
      var today = new Date();
      var test = new Date(dateCell);

      // If the value is a valid date and is a past date, we remove it from the sheet to paste on the other sheet
      if(test < today){

        var rangeToMove = scheduleSheet.getRange(i, 1, 1, scheduleSheet.getLastColumn()).getValues();
        pastSheet.getRange(pastSheet.getLastRow() + 1, 1, 1, scheduleSheet.getLastColumn()).setValues(rangeToMove);
        scheduleSheet.deleteRow(i);

      }
    }
  }
}

// Check is a valid date
function isValidDate(value) {
  var dateWrapper = new Date(value);
  return !isNaN(dateWrapper.getDate());
}

So yes, It's not the optimized solution (cause of the use of several sheet.getRange() method), but it's working and allowing to have a clear code.