Google表格-根据日期删除过期的行 [英] Google Sheets - Delete Expired Rows Based On Date

问题描述

我当前正在尝试制作脚本或任何能够在C列中给定日期之后删除一行的文字.

I'm currently trying to make a script or literally anything that will be able to delete a row after the given date in Column C.

该站点是免费站点，因此一旦在C列上指定的日期过去，我就需要删除行/条目.

The site is a giveaway site so I need the rows/entries to delete themselves once the date specified on Column C is passed.

例如:如果一个赠品的到期日期为20/13/2016，则一旦该日期达到此日期20/13/2016，它将删除该行.我以dd/mm/yy的公制为注释.

Eg: If one giveaway had an expiration date @ 20/13/2016, once the date reaches this date of 20/13/2016 it will delete the row. I am following the metric system of dd/mm/yy as a note.

我在 Google表格-脚本中看到了与此类似的问题删除日期已过期的行，但该代码无法满足我的需求.

I saw a question similar to this at Google Sheets - Script to delete date expired rows but the code won't work for my needs.

这是另一个问题中使用的代码.

Here is the code that was used in the other question.

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

如果您可以更改它以满足我的上述需求，将不胜感激！

If you could change it to work for my above needs it will be greatly appreciated!

推荐答案

假设您的日期位于所述的C列中，则应该这样做.调整只是针对我们比较的日期并处理缺失的日期.为了使可读性更好，我也弄混了一些名字.

Assuming your dates are in column C as stated, this should do it. The adjustment is just to the date to which we compare and to handle missing dates. I am also messing with the case on some names for readability.

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

for (i=lastrow;i>=3;i--) {
var tempDate = values[i-1][2];// arrays are 0 indexed so row1 = values[0] and col3 = [2]
if ((tempDate!=NaN) && (tempDate <= currentDate))
{
  sheet.deleteRow(i);
}//closes if
}//closes for loop
}//closes function

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