如果列的日期早于一个月，则删除行 [英] Delete row if column has date older than one month

问题描述

我从 https://stackoverflow.com/users/6656050/jeremy-kahan 找到了这段代码，编辑了一下.当L列中的条目超过一个月时，我需要我的工作表删除任何行，但是如果L列中的单元格为空，则保留该行，我需要一些指导.根据要求，我可以提供工作表的副本并显示示例.在其他人看来，这似乎很容易，但是我觉得只有16岁并且只学习Java是一个劣势.

I found this code from https://stackoverflow.com/users/6656050/jeremy-kahan and edited it a little. I need my sheet to delete any row when the entry in column L is older than one month, but keep the row if the cell in column L is empty i'll need some guidance. On request I can provide a copy of my sheet and show examples. It might seem easy it others but I feel at a disadvantage only being 16 and just learning java.

function DeleteOldEntries() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");//assumes Sheet 1 is the name of the sheet
var datarange = sheet.getDataRange();
var lastrow = datarange.getLastRow();
var values = datarange.getValues();// get all data in a 2D array

var currentDate = new Date();//today
var monthOld = Date.now() + -30*24*3600*1000; 
for (i=lastrow;i>=3;i--) {
var tempDate = values[i-1][11];// arrays are 0 indexed so row1 = values[0] and col12 = [11]
if  (tempDate <= (monthOld))
{
  sheet.deleteRow(i);
}

}

推荐答案

我将提供一些帮助，即使我会为此受到指责.将来，请进行一些研究，尝试理解代码并使代码按您的目的工作.如果您不能这样做，而Google没有帮助，那么那就来询问所有详细信息的CLEAR问题.如果所有这些都没有完成，那么这里的大多数顶级狗甚至都不会看您的问题.另外，请务必在问题上使用正确的标签.没有他们，它甚至不会露面给正确的人.

I'm going to provide some help, even though I'm going to get chastised for it. In the future, do some research, try to understand the code and make the code work for your purpose. If you can't, and Google doesn't help, THEN come on and ask a CLEAR question with all of the details. Most of the top dogs on here won't even look at your question if all of that isn't done. Also, be sure to use the correct tags on your questions. It won't even show up to the correct people without them.

现在您的答案:

如果L列中的日期是过去30天以上，则要删除一行.您提供的代码在C列中查找日期.因此，这是第一件事需要更改.其次，条件 if((tempDate！= NaN)&&(tempDate< = currentDate))正在检查单元格是否为空( NaN )，或者如果日期是< = TODAY，今天是否-30.因此，您需要一种方法来计算今天-30天是: var monthOld = Date.now()+-30 * 24 * 3600 * 1000; ，那么您可以将其与L列中的日期进行比较.

You want to delete a row if the date in column L is > 30 days in the past. The code you provided is looking at column C for the date. So that is the first thing that needs to be changed. Second, the conditional if ((tempDate!=NaN) && (tempDate <= currentDate)) is checking to see if the cell is empty (NaN), or if the date is <= TODAY, NOT today - 30. So, you need a way to calculate what today - 30 days is: var monthOld = Date.now() + -30*24*3600*1000;, then you can compare that to the date in column L.

如果进行了这两项更改，您将得到:

If you make those two changes, you get:

function DeleteOldEntries() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Live Events");//assumes Live Events is the name of the sheet
var datarange = sheet.getDataRange();
var lastrow = datarange.getLastRow();
var values = datarange.getValues();// get all data in a 2D array

var currentDate = new Date();//today
var monthOld = Date.now() + -30*24*3600*1000; 
for (i=lastrow;i>=3;i--) {
var tempDate = values[i-1][11];// arrays are 0 indexed so row1 = 
values[0] and col12 = [11]
if ((tempDate!=NaN) && (tempDate <= (monthOld)))
{
  sheet.deleteRow(i);
}
}
}

这应该做您想要的.如果您还有其他问题，请告诉我.我很乐意提供帮助.

This should do what you want. If you have any further questions, let me know. I'm happy to help.

这篇关于如果列的日期早于一个月，则删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！