How to efficiently move values from a column to a different column without overwriting values?

user_11034971 0
Viewed 93

I'm trying to move some dates from one column to the next neighbouring column. I want it to only move cells before a specific date and until it reaches an empty one.

I've tried using the filter method but I don't think it's working for me.

Here's an image of what I'm trying to achieve.

test spreadsheet
I selected the cell values that the script should move; the rest should remain where they are.
Here's my code:

function moveOldEntryDates()
{
  // Get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Sheet1");

  var oneDayAgo = sheet1.getRange(2, 6, 1, 1);
  Logger.log("First Date: " + oneDayAgo.getValue());

  var sevenDaysAgo = subtractDaysFromDate(oneDayAgo.getValue(), 7);
  Logger.log("Seven days ago Date: " + sevenDaysAgo);

  // Dates start on second row
  var dateOffset = 2;

  // Not working properly
  var FColumn = sheet1.getRange("F6:F").getValues(); // Date Column
  var LastRowInFColumn = FColumn.filter(String).length;
  //var g = e.filter( function(elem, pos){ return (f.indexOf(elem[1]) == pos) })

  Logger.log("Last row in column F is "+ LastRowInFColumn);
  var dateColumn = sheet1.getRange(dateOffset, 6, LastRowInFColumn, 1);

  var dateValues = dateColumn.getValues();
  var currentRow = 0;

   // This while loop finds the start of the outdated dates
  while(dateValues[currentRow][0] != "")
  {
    var dateBuilt = new Date(dateValues[currentRow][0]);
    var less = dateBuilt < sevenDaysAgo;
    Logger.log(dateBuilt + " less than " + sevenDaysAgo + "?: " + less + " at row: " + currentRow + dateOffset);
    // Check if less than 7 days ago
    if(less)
    {
      startOfOutdatedCells = dateValues[currentRow][0]; // if date is less than oneWeekAgo, it's outdated
      // everything after is already outdated.
      break;
    }
    else if(!less)
    {
      currentRow++;
    }    
  }
  var outdatedDateRow = currentRow + dateOffset;
  Logger.log("First outdated date starts in row: " + outdatedDateRow);

  // Create a new range to move
  var outdatedCellRange = sheet1.getRange(outdatedDateRow,6, LastRowInFColumn, 1);
  var outdatedCellRangeValues = outdatedCellRange.getValues();
  var oudatedCellCopy = outdatedCellRange.moveTo(sheet1.getRange(outdatedDateRow, 7)); // currentRow = the current row is at startOfOutdatedCells

}

function subtractDaysFromDate(date, days)
{
  var result = new Date(date.getTime()-days*(24*3600*1000));
  return result
}

My guess is that var LastRowInFColumn = FColumn.filter(String).length; is not filtering / working properly. Instead of returning an array that only contains String values, it still returns than the entire column, including the empty cells. As a result, this is what happens when I run the script:
test spreadsheet script
You can also notice that it's moving the empty cells and placing them, overwriting the values in the other column. If I run it a third time, it just clears the column because it's moving the empty ones as well. test spreadsheet script
Sorry for the long post! Any help would be greatly appreciated. Thanks!

google-apps-script google-sheets javascript
1 Answers

Since the .filter method was not working the way I wanted, I wrote my own method. Here's the code for anyone who might find this helpful. 

function getLastRowByColumn()
{
  // Get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Sheet1"); // <======= Change this
  var lastRow = ss.getLastRow();
  var lastColumn = ss.getLastColumn();

  var dates = sheet1.getRange(2,6,lastRow, 1); // <====== Change range values
  var values = dates.getValues();
  var currentRow = 0;
  var rowCounter = 0;
  while(values[currentRow][0] != "")
  {
    rowCounter++;
    currentRow++;
  }
  return rowCounter;
}
user_110349710
Related