Replace Duplicates with Null Value in Google Script

Question

I've got a list of folder names corresponding to file names within each of these folders.

I'd like to delete the duplicate folder names. This is what I need it to end up as:

My thought was that I would do a reverse loop comparing each entry to the one before it. If it matches add a blank '' to the top of an array. If it doesn't match then add the value of the array entry to the top of the array.

Below is my attempt. For some reason all of the comparisons are false. What am I doing wrong?

  //Delete Duplicate folder names
  var destRange = summarySht.getRange(startRow + 1,2,detLastRN - startRow,1);
  //var folderNamesArr = destRange.getValues();
  var folderNamesArr = [['Folder1'],['Folder1'],['Folder2'],['Folder2'],['Folder3'],['Folder4'],['Folder5'],['Folder6'],['Folder6']];
  var updatedFolderNamesArr = [];
  for (i = folderNamesArr.length - 1; i >= 0; i--) {
    if(folderNamesArr[i] != folderNamesArr[i - 1]){
      updatedFolderNamesArr.unshift(folderNamesArr[i])
    }else{
      updatedFolderNamesArr.unshift([''])
    }
    Logger.log(folderNamesArr[i] + ' vs ' + folderNamesArr[i - 1] + ' = ' + (folderNamesArr[i] === folderNamesArr[i - 1]))
  }
  Logger.log(updatedFolderNamesArr)
  destRange.clearContent();
  destRange.setValues(updatedFolderNamesArr);

I don't have many rows. I'm thinking worst case scenario we'll have 100 maybe 200 rows to process.

Update: I added .toString() and it seems to work. Here is what I did and my issues with it.

  //Delete Duplicate folder names
  var destRange = summarySht.getRange(startRow + 1,2,detLastRN - startRow,1);
  //var folderNamesArr = destRange.getValues();
  var folderNamesArr = [['Folder1'],['Folder1'],['Folder2'],['Folder2'],['Folder3'],['Folder4'],['Folder5'],['Folder6'],['Folder6']];
  var updatedFolderNamesArr = [];
  for (i = folderNamesArr.length - 1; i >= 1; i--) {
    if(folderNamesArr[i].toString() !== folderNamesArr[i - 1].toString()){
      updatedFolderNamesArr.unshift(folderNamesArr[i])
    }else{
      updatedFolderNamesArr.unshift([''])
    }
    Logger.log(folderNamesArr[i] + ' vs ' + folderNamesArr[i - 1] + ' = ' + (folderNamesArr[i] === folderNamesArr[i - 1]))
  }
  //Add back the first folder
  updatedFolderNamesArr.unshift(folderNamesArr[0]);      
  Logger.log(updatedFolderNamesArr)
  destRange.clearContent();
  destRange.setValues(updatedFolderNamesArr);

The first issue I had was that folderNamesArr[i - 1] doesn't work when it gets to the last element. So I had to change the limit on the loop to i >= 1. That then caused the issue of there being 1 less element. The first element was missing so I had to add it back to the end.

This seems so hacky to me. Is this acceptable or are there better ways/methods?

Tanaike · Accepted Answer · 2020-06-18 23:49:11Z

2

How about this answer?

Modification points:

In your script, folderNamesArr is 2 dimensional array which is retrieved with getValue. But, at if(folderNamesArr[i] != folderNamesArr[i - 1]){, the arrays are compared. By this, this if statement is always true. So updatedFolderNamesArr becomes the same with folderNamesArr.
- Also, I think that the reason that if(folderNamesArr[i].toString() !== folderNamesArr[i - 1].toString()){ works is this.
- But, when if(folderNamesArr[i] != folderNamesArr[i - 1]){ is modified to if(folderNamesArr[i][0] != folderNamesArr[i - 1][0]){, when i is 0, an error occurs.
- So, it is required to modify for (i = folderNamesArr.length - 1; i >= 0; i--) { to for (i = folderNamesArr.length - 1; i > 0; i--) {.
- But, in this case, because of (i = folderNamesArr.length - 1; i > 0; i--), the result becomes [[""],["Folder2"],[""],["Folder3"],["Folder4"],["Folder5"],["Folder6"],[""]]. The 1st element is not included. In this flow, when i is 1, folderNamesArr[i] is put.

When your script is modified how about the following modification?

Modified script:

var folderNamesArr = [['Folder1'],['Folder1'],['Folder2'],['Folder2'],['Folder3'],['Folder4'],['Folder5'],['Folder6'],['Folder6']];
var updatedFolderNamesArr = [];
for (i = folderNamesArr.length - 1; i > 0; i--) {  // Modified
  if (folderNamesArr[i][0] != folderNamesArr[i - 1][0]) {  // Modified
    updatedFolderNamesArr.unshift(folderNamesArr[i]);
  } else {
    updatedFolderNamesArr.unshift(['']);
  }
  if (i == 1) updatedFolderNamesArr.unshift(folderNamesArr[i]);  // Added
}
Logger.log(updatedFolderNamesArr)

Other pattern:

As other pattern, how about the following script? In this script, updatedFolderNamesArr is flatten and created an array as 1 dimensional array, and then, the array is converted to 2 dimensional array. I think that there are several sample script for your situation.

var folderNamesArr = [['Folder1'],['Folder1'],['Folder2'],['Folder2'],['Folder3'],['Folder4'],['Folder5'],['Folder6'],['Folder6']];
var updatedFolderNamesArr = folderNamesArr
  .flat()
  .reduce((ar, e) => ar.concat(ar.includes(e) ? '' : e), [])
  .map(e => [e]);
Logger.log(updatedFolderNamesArr)

Reference:

getValues()

answered Jun 18, 2020 at 23:49

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DanCue Over a year ago

The modified script is the same as what I added, a little cleaner. Thank you. As for the "Other pattern" option... I created the folderNamesArr the way you see it for simplicity but it is coming from a column on my sheet and will need to be pasted in the same column. Once the array is flattened, don't I have to unflatten it to setValues?

Tanaike Over a year ago

@DanCue Thank you for replying. Yes. So the values from reduce is converted to 2 dimensional array using map(e => [e]). In this case, the result of above modified script is the same with the result from "Other pattern".

Collectives™ on Stack Overflow

Replace Duplicates with Null Value in Google Script

1 Answer 1

Modification points:

Modified script:

Other pattern:

Reference:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Modified script:

Other pattern:

Reference:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related