Google脚本删除重复的行,保留最新的 [英] Google script delete duplicate row, leave most recent
问题描述
我正在使用脚本中的GmailApp从gmail抓取票证数据.但是,当票证状态发生变化时,我会收到一封新电子邮件,并会在新行中附加最新的时间戳.
I am scraping ticket data from gmail using the GmailApp in scripts. However, when a ticket status changes, I get a new email and we get a new row appended with a more recent time stamp.
我要搜索重复的票证编号(A),并删除较早的时间戳(J).
I want to search for the duplicate ticket number (A) and delete the older timestamp (J).
我遇到的问题是oldTime实际上是最新的条目,因此什么也没发生.
The issue I am having is that oldTime is actually the latest entry so nothing happens.
13007 | B | C | D | E | F | G | H | I | 2/25/2019
13007 | B | C | D | E | F | G | H | I | 2/26/2019
A是票号.B-I根据票证信息而变化.J从电子邮件日期中拉出.
A is the ticket number. B-I change depending on the ticket information. J is pulled from the email date.
function removeDuplicates() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var newData = [];
for(i in data){
var row = data[i];
var duplicate = false;
for(j in newData){
//If Column A in the old entry matches Column A in the new entry
if(row[0] == newData[j][0]){
//Pull New Timestamp and Old Timestamp
var newTime = Date.parse(newData[j][9]);
var oldTime = Date.parse(row[9]);
if (newTime>oldTime) duplicate=true; // number is milliseconds in 24 hours
}
}
if(!duplicate){
newData.push(row);
}
}
sheet.clearContents();
sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}
推荐答案
用较新的行替换较旧的行
好的,这正在处理我的数据,让我们看看它是否对您的数据有效.
Replacing Older Rows with newer Rows
Okay this is working on my data let's see if it works on yours.
function outWithTheOldInWithTheNew() {
var sh=SpreadsheetApp.getActiveSheet();
var eA=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();
var tA=sh.getRange(2,1,sh.getLastRow()-1,1).getValues();
var dA=sh.getRange(2,10,sh.getLastRow()-1,10).getValues();
var uA=[];//ticket numbers
var uB=[];//dates
var uC=[];//entire rows
for(var i=0;i<tA.length;i++) {
var idx=uA.indexOf(tA[i][0]);
if(idx==-1) {
uA.push(tA[i][0]);
uB.push(dA[i][0]);
uC.push(eA[i]);
}else if(new Date(dA[i][0]).valueOf() > new Date(uB[idx]).valueOf()) {
uB.splice(idx,1,dA[i][0]);//replace newer dates
uC.splice(idx,1,eA[i]);//replace newer rows
}
}
sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).clearContent();
sh.getRange(2,1,uC.length,uC[0].length).setValues(uC);//newest rows
}
之前的电子表格:
以下电子表格:
这篇关于Google脚本删除重复的行,保留最新的的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!