javascript - 如何在不覆盖值的情况下有效地将值从列移动到不同的列？

Question

我正在尝试将一些日期从一列移动到下一列。我希望它只在特定日期之前移动单元格，直到它到达一个空的单元格。

我试过使用过滤器方法，但我认为它不适合我。

这是我要实现的目标的图像。

我选择了脚本应该移动的单元格值；其余的应该留在原地。
这是我的代码：

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
}

我的猜测是var LastRowInFColumn = FColumn.filter(String).length;没有过滤/正常工作。它不是返回仅包含字符串值的数组，而是返回整个列，包括空单元格。结果，这就是我运行脚本时发生的情况：

您还可以注意到它正在移动空单元格并放置它们，覆盖另一列中的值。如果我第三次运行它，它只会清除该列，因为它也在移动空的列。
对不起，很长的帖子！任何帮助将不胜感激。谢谢！

标签： javascriptgoogle-apps-scriptgoogle-sheets

Answer 1

由于 .filter 方法没有按我想要的方式工作，所以我编写了自己的方法。这是任何可能觉得这很有帮助的人的代码。

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;
}