Apps Script: need to append unique records which have hyperlinks in them from one tab to another tab

Question

I have a script to append unique records. But, when I run the script it turns the file names with hyperlinks into a simple text (removes the hyperlinks). How do I retain the hyperlinked file names while appending the records from one tab to another tab?

Append Unique Records Apps Script:

function appendUniqueRows() {
  var ss = SpreadsheetApp.getActive();
  var sourceSheet = ss.getSheetByName('Tab 1');
  var destSheet = ss.getSheetByName('Tab 2');

  var sourceData = sourceSheet.getRange('W5:AC5').getValues();
  var destData = destSheet.getDataRange().getValues();

  // Check whether destination sheet is empty
  if (destData.length === 1 && "" === destData[0].join('')) {
    // Empty, so ignore the phantom row
    destData = [];
  }

  // Generate hash for comparisons
  var destHash = {};
  destData.forEach(function(row) {
    destHash[row.join('')] = true; // could be anything
  });

  // Concatentate source rows to dest rows if they satisfy a uniqueness filter
  var mergedData = destData.concat(sourceData.filter(function (row) {
    var hashedRow = row.join('');
    if (!destHash.hasOwnProperty(hashedRow)) {
      // This row is unique
      destHash[hashedRow] = true;   // Add to hash for future comparisons
      return true;                  // filter -> true
    }
    return false;                   // not unique, filter -> false
  }));

  // Check whether two data sets were the same width
  var sourceWidth = (sourceData.length > 0) ? sourceData[0].length : 0;
  var destWidth = (destData.length > 0) ? destData[0].length : 0;
  if (sourceWidth !== destWidth) {
    // Pad out all columns for the new row
    var mergedWidth = Math.max(sourceWidth,destWidth);
    for (var row=0; row<mergedData.length; row++) {
      for (var col=mergedData[row].length; col<mergedWidth; col++)
        mergedData[row].push('');
    }
  }

  // Write merged data to destination sheet
  destSheet.getRange(1, 1, mergedData.length, mergedData[0].length)
           .setValues(mergedData);
}

[How to create a Minimal, Reproducible Example....](https://stackoverflow.com/help/minimal-reproducible-example) — Marios, Dec 06 '20 at 23:51

score 2 · Accepted Answer · answered Dec 07 '20 at 11:37

You can use the Rich Text values

For example:

function copyTextWithLink() {
  file = SpreadsheetApp.getActive();
  sheet1 = file.getSheetByName("Sheet1");
  sheet2 = file.getSheetByName("Sheet2");

  range1 = sheet1.getDataRange();
  values1 = range1.getRichTextValues();

  let output = values1.map((row) => {
    return row.map((cell) => {
      let newCell = SpreadsheetApp.newRichTextValue()
        .setText(cell.getText())
        .setLinkUrl(cell.getLinkUrl())
        .build();
      return newCell;
    });
  });

  let height = output.length
  let width = output[0].length
  
  let outputRange = sheet2.getRange(1,1,height,width)

  outputRange.setRichTextValues(output)
}

Explanation

This script is just an illustration of how to use the Rich Text functionality in Apps Script.
It copies values from one sheet to another keeping the Hyperlink. All the values in the source sheet are hyperlinked.
The first step is getting the range in which the hyperlinked values are, and then calling getRichTextValues on them. Which functions the same as getValues except it is returning a RichTextValue object.
Next up is to transform the 2D array of rich text values into another array with rich text values. This is redundant here as you could just skip this and directly use setRichTextValues to copy all the data, including hyperlink to the destination. This step is included as an illustration as to how you can individually get and set texts or links from the rich text value array.
Building the rich text values involves using the RichTextValueBuilder:

let newCell = SpreadsheetApp.newRichTextValue()
        .setText(cell.getText())
        .setLinkUrl(cell.getLinkUrl())
        .build();

References

score 1 · Answer 2 · answered Dec 07 '20 at 00:21

Explanation:

I can't reproduce your example since it is not feasible to do that.

However, I think your goal is to copy the format from the first tab to the other so you preserve the hyperlink formatting.

Solution:

Use copyTo() to copy the format only from a selected to range to another range of the same dimensions. Put this code at the end of your script.

  var sourceRange = sourceSheet.getRange(1, 1, mergedData.length, mergedData[0].length);
  var destRange = destSheet.getRange(1, 1, mergedData.length, mergedData[0].length);
  sourceRange.copyTo(destRange, {formatOnly:true})

Please note!

sourceRange is the desired range you want to copy the format from and destRange is the range you want to paste the format to. These ranges need to have exactly the same dimensions. Choose yourself the arguments in this line as you are the only one who can see the file and test the code:

var sourceRange = sourceSheet.getRange(1, 1, mergedData.length, mergedData[0].length);

You might need to change the second and the fourth argument to the desired starting and ending column where you want to copy the format of the first tab.

The example, starts from the first column. But if you want the second column, then change the line to:

var sourceRange = sourceSheet.getRange(1, 2, mergedData.length, mergedData[0].length+1);

Please see:

Sheet.getRange(1,1,1,12) what does the numbers in bracket specify?

Apps Script: need to append unique records which have hyperlinks in them from one tab to another tab

2 Answers2

You can use the Rich Text values

Explanation

References