Archiving in Google Spreadsheets with Date Using Apps Script

Question

I have a spreadsheet with action items that have dates and priorities attached to them. I want to create a function that archives a row if the item is a Priority 2 and a week old or more.

This is my script, but it's acting out -- sometimes it moves the wrong priorities, sometimes it moves the wrong dates, and it never moves all the dates that it should. Worst, it adds tons of empty rows at the bottom of my "Action Items" sheet, and creates empty rows in the "Archive" sheet as well.

Obviously something is wrong, but I really don't see it, even after a day of intense debugging. Any help would be enlightening and appreciated!

function Archiver() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Action Items'); // get the sheet
  var targetsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Archive'); // get the target sheet
  var columnF = sheet.getRange(2, 6, sheet.getLastRow()-1, 1); // get all the rows with dates
  var fValues = columnF.getValues(); // get the values of dates
  var columnE = sheet.getRange(2, 5, sheet.getLastRow()-1, 1); // get all the rows with priorities
  var eValues = columnE.getValues(); // get the values of priorities
  var day = 24*3600*1000 // calculate ms in a day
  var today = parseInt((new Date().setHours(0,0,0,0))/day); // get date today
  for (var i = 0; i < fValues.length; i++) { // repeat loop
    var priority = eValues[i][0]; // set priority in loop
    var dataday = parseInt(fValues[i][0].getTime()/day); // convert date column into miliseconds
    Logger.log(dataday+" <= " + today-7 + " - " + priority) // my log isn't picking up day
    if (dataday <= today-7 && priority == "P2") { // if an item is more than 7 days old and Priority 2...
      targetsheet.insertRows(2,1)
      // move the entire source row to the second row of target sheet
    var rangeToMove = sheet.getRange(/*startRow*/ i + 2, /*startColumn*/ 1, /*numRows*/ 1, /*numColumns*/ sheet.getMaxColumns());
    rangeToMove.moveTo(targetsheet.getRange("A2"));
          // add date and time of when approved to target row in column E
    targetsheet.getRange("M2").setValue(Date());
    // delete row from source sheet
    sheet.deleteRows(i + 2,1);
    }
  }
        ss.toast("Move along.", "Archiving Complete.");
}

jad jad · Accepted Answer · 2013-01-17T07:23:55

I didn't debug your code, but my initial thought is that deleting rows while iterating disturbs the order of the data and creates confusion, so I adjusted it to copy the rows (instead of moving) and store the row numbers to be deleted in an array so we can delete them outside the loop. I did a quick test and this seems to work.

function Archiver() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Action Items'); // get the sheet
  var targetsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Archive'); // get the target sheet
  var columnF = sheet.getRange(2, 6, sheet.getLastRow()-1, 1); // get all the rows with dates
  var fValues = columnF.getValues(); // get the values of dates
  var columnE = sheet.getRange(2, 5, sheet.getLastRow()-1, 1); // get all the rows with priorities
  var eValues = columnE.getValues(); // get the values of priorities
  var day = 24*3600*1000 // calculate ms in a day
  var today = parseInt((new Date().setHours(0,0,0,0))/day); // get date today
  var rowsToDelete = [];
  for (var i = 0; i < fValues.length; i++) { // repeat loop
    var priority = eValues[i][0]; // set priority in loop
    var dataday = parseInt(fValues[i][0].getTime()/day); // convert date column into miliseconds
    Logger.log(dataday+" <= " + (today-7) + " - " + priority) // my log isn't picking up day
    if (dataday <= today-7 && priority == "P2") { // if an item is more than 7 days old and Priority 2...
      targetsheet.insertRows(2,1)
      // move the entire source row to the second row of target sheet
    var rangeToMove = sheet.getRange(/*startRow*/ i + 2, /*startColumn*/ 1, /*numRows*/ 1, /*numColumns*/ sheet.getMaxColumns());
    rangeToMove.copyTo(targetsheet.getRange("A2"));
          // add date and time of when approved to target row in column E
    targetsheet.getRange("M2").setValue(Date());
    // delete row from source sheet
    rowsToDelete.push(i+2);
    }
  }
  rowsToDelete.reverse();
  for (var j = 0; j < rowsToDelete.length; j++) { sheet.deleteRow(rowsToDelete[j]); }
  ss.toast("Move along.", "Archiving Complete.");
}

Archiving in Google Spreadsheets with Date Using Apps Script

1 Answers