Google Script: Remove Duplicate using Standard Script without losing Hyperlink

Question

Situation:

I have an spreadsheet and the same is modify by many users around 20, every min adding and following incident.

I have an script that add an Hyperlink into the column A.

Column (A) =HYPERLINK("https://www.example.com/id=12345";"12345")

Note: The Hyperlink appear for every row in the column A when I ingress the Ticket ID.

Problem:

1. When this script run and delete the information for the duplicate data, also clear the Hyperlink and then the Column A only have the data without the hyperlink.
   

2. Script delete the contents and not delete rows. Sometime delete the row and other only delete de data into the row.

For example:

row 10: data is Test1
row 11: data is Test1
row 12: data is Test3

When the script run sometime delete rows and the row 12 up to row 11. But other sometime only delete the contents in Row 11 and row 12 not move.

I needs this script do the following:

1. Remove Duplicate rows.
   
2. Is posible indicate in with Cell in a Boxmsg is the duplicate data?
   
3. Not lost the hyperlink.
   

SCRIPT:

function removeDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var newData = new Array();
  for(i in data){
    var row = data[i];
    var duplicate = false;
    for(j in newData){
      if(row[0] == newData[j][0]){
        duplicate = true;
      }
    }
    if(!duplicate){
      newData.push(row);
    }
  }
  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

Thanks So Much.

Answer 1

The first problem (and the one mentioned in your question title) is a normal consequence of the script you are using.

The script reads and writes values in the spreadsheet and your hyperlink is a formula so when the script writes back all the values (bulk writing) to the sheet after having cleared it you won't have any formula anymore... just values.

This will be a bit tricky to avoid :

You should begin with reading the sheet and getting all the formulas it contains, store that information somewhere for each row with a reference to the row it belongs to and then, after having removed the duplicate rows, rewrite the formulas at their places...

... but since the row numbers will not be the same anymore you 'll have to know which row was deleted and retrieve the right position in the sheet using the original position minus the deleted rows and skipping formulas that were in a deleted row...

All this seems very complicated so I guess it would be better to completely change your duplicate search script and let it process the sheet differently, looking for duplicate values AND formulas all together.

You should try to write such a script and ask for help if you meet difficulties making it work.