Google表格 - 删除日期过期行的脚本 [英] Google Sheets - Script to delete date expired rows

问题描述

我试图获得一个脚本，自动删除3天以上的日期（4天）

I am trying to get a script going that will automatically delete rows with a date over 3 days old (4 days on)

我发现这个脚本，我是希望能够适应...
函数DeleteOldEntries（）{

I found this script that I was hoping to be able to adapt... function DeleteOldEntries() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
//give your sheet name below instead of Sheet1
var sheet = ss.getSheetByName("Foglio1");

var datarange = sheet.getDataRange();
var lastrow = datarange.getLastRow();


var currentDate = new Date();
var oneweekago = new Date();
oneweekago.setDate(currentDate.getDate() - 7);

for (i=lastrow;i>=2;i--) {
var tempdate = sheet.getRange(i, 1).getValue();

if(tempdate < oneweekago)
{
  sheet.deleteRow(i);
}
}
}

但似乎有一个脚本本身的错误，我需要弄清楚，以适应4天，而不是7（这部分很容易）

But there seems to be an error in the script itself that I need to figure out before adapting it to 4 days instead of 7 (that part is easy)

我的工作表有3列，日期在列C，如果该信息有帮助

My sheet has 3 columns with the date in column C, if that info is helpful

推荐答案

日期在列C，如果该信息是有帮助的

"date in column C, if that info is helpful"

确实是这样！而不是试图在列A中获得一个日期，就像在这行中所做的那样：

It is indeed ! instead of trying to get a date in column A as it it done in this line :

var tempdate = sheet.getRange(i, 1).getValue();

您应该查看列C中的值，如下所示：

you should look at the value in column C like this :

var tempdate = sheet.getRange(i, 3).getValue();

但为了更有效率，您应该在数组级别进行这些比较，尝试如下，它将运行得更快...

but to be more efficient you should do these comparisons at array level, try it like below and it will run much faster ...

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Foglio1");
var datarange = sheet.getDataRange();
var lastrow = datarange.getLastRow();
var values = datarange.getValues();// get all data in a 2D array

var currentDate = new Date();
var oneweekago = new Date();
oneweekago.setDate(currentDate.getDate() - 7);

for (i=lastrow;i>=2;i--) {
var tempdate = values[i-1][2];// arrays are 0 indexed so row1 = values[0] and col3 = [2]

if(tempdate < oneweekago)  
{
  sheet.deleteRow(i);
}
}
}

这篇关于Google表格 - 删除日期过期行的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！