You want to find duplicate values in Column B and delete the record if the corresponding Date and State are identical. If a duplicate exists but either the date and/or state has changed then you want to update the original record for those changed values. In the event of a duplicate with an Internal State of, say, "abc", you also want to delete the Internal State from the original record.
You have a script that "Removes duplicate rows from the current sheet" however this uses the join
method which is designed to detect exact duplicates of rows. This is inappropriate for your requirement since each of your records has a unique ID. However there are elements of this script - specifically the looping of row
and newData
, and newData.push(row)
that can be retained.
In this answer, the deduped/updated output is copied to a new sheet; the source sheet remains intact.
/**
* Finds duplicate values in Column B
* deletes duplicate if date and state are identical
* updates date and state if either has changed plus also
* deletes Internal state if value equals watch value
*/
function wa15127902() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourcesheetname = "Source"
var sourcesheet = ss.getSheetByName(sourcesheetname)
var targetsheetname = "Target"
var targetsheet = ss.getSheetByName(targetsheetname)
var watchInternalState = "abc"
var data = sourcesheet.getDataRange().getValues();
var newData = [];
for (var i in data) {
var row = data[i];
var duplicate = false;
for (var j in newData) {
if (row[1] == newData[j][1]) {
// duplicate on Column B
duplicate = true;
// now test date and/or state for change
// format dates for comparison
var rowdate = Utilities.formatDate(row[2], "GMT","yyyy-MM-dd'T'HH:mm:ss'Z'");
var newdate = Utilities.formatDate(newData[j][2], "GMT","yyyy-MM-dd'T'HH:mm:ss'Z'");
if (rowdate === newdate && row[3] == newData[j][3]){
// both the date or the state are identical
// so this is an exact duplicate
// do nothing
}
else{
// either the date or state has changed
// so update both
newData[j][3] = row[3];
newData[j][2] = row[2];
if(newData[j][5] == watchInternalState){
// the internal state has the specific value
// remove the internal state
newData[j][5] = "";
}
}
}
}
if (!duplicate) {
newData.push(row);
}
}
targetsheet.clearContents();
targetsheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}
Source data

Updated data
