CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
Steven Froast
Steven Froast

Reputation: 61

Sorting duplicate submissions by date

I have a Google form that deposits data to a google spreadsheet. All the data from the first form filled out will be deposited on row 1, then the next form filled out will be deposited on row 2, etc. A problem that I have been having is that sometimes people lose their link to edit their submitted form and they submit a whole new entry creating a duplicate row on google sheets with updated information.

I am trying to make sheet2 that will filter out the duplicate entries but still keep the order that they first appeared. I have considered using UNIQUE(range) function however the problem is that the unique function takes the first time the name appears. What I want it to do is keep the position that the first name appears in but take the data of the latest time the name appears because that will have the updated information. I cannot figure out a way to do this on my own. Is there anyone who is more knowledgeable that can help?

Below is an example of what I am hoping to achieve. The first time John appears is overwritten by the latest time it appears.

enter image description here

Upvotes: 0

Views: 47

Answers (1)

Cooper
Cooper

Reputation: 64100

I think this should do it for you.

function findCopyDeleteDupes()
{
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sht=ss.getActiveSheet();
  var rng=sht.getDataRange();
  var rngA=rng.getValues();
  var nodupes=[];
  var dupeRows=[];
  for(var i=0;i<rngA.length;i++)
  {
    var idx=nodupes.indexOf(rngA[i][1]);
    if(idx==-1)//if not in nodupes then put it in nodupes
    {
      nodupes.push(rngA[i][1]);
    }
    else
    {
      for(var j=0;j<rngA[i].length;j++)
      {
        rngA[idx][j]=rngA[i][j];//Copy data from the latest time to the first position
      }
      dupeRows.push(i+1);//add this row to the delete rows array
    }
  }
  rng.setValues(rngA);
  for(var i=dupeRows.length-1;i>-1;i--)//delete rows from the bottom first
  {
     sht.deleteRow(dupeRows[i])
  }
}

Upvotes: 1

Related Questions