How to Filter Url linked text in Google sheets using Formula/Google Apps Script?

user_19456634 0
Viewed 76

I want to filter Google sheet data that contains URL-linked text in one column. Here is the image of the raw Data

I want this data to be filtered based on Column A value. I used the following formula to filter this data:

 =FILTER(Template!A2:D,Template!A2:A = "A")

The formula states that get all the data from the Template sheet which contains the value 'A' in the first column (Column A). The filtered data looked like this.

As you can see, the data is filtered, but the hyperlink attached in raw Data does not come through in the filtered Data.

I tried to use the following script to counter this issue but this created the same result:

  function FilteredData() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var rawSheet = ss.getSheetByName("Template");
  var lr = rawSheet.getLastRow();
  var filteredSheet = ss.getSheetByName("FilteredData");
  
  var WholeData = rawSheet.getRange(2, 2, lr, 4).getValues();

  var Criteria = 'A';
  var Filtered = WholeData.filter(function (e){return e[0]==Criteria });
  
  
  filteredSheet.getRange(2, 1, Filtered.length, Filtered[0].length).setValues(Filtered);

This is the sheet link with the data. Thank you for your guidance.

google-sheets-formula google-apps-script google-sheets filter
2 Answers

One approach could be to make use of "Filter Views" instead of using Filter function.

The use of Filtered views has another advantage, that other users on the same sheet do not get impacted, unlike the use of normal filter functionality in Google sheets where moment one user applies the filter on the sheet, other users on the same sheet get filtered data and not the complete sheet.

You can reach the filter view functionality on your Google sheet using below path

Data => Filter Views => Create New Filter View.

Thanks

user_2059830

So, I found a way to keep the URL intact with the text by updating the above script, so instead of using getValue(), you can use getRichTextValue() to get the text along with its url :

function FilteredData() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var rawSheet = ss.getSheetByName("Template");
  var lr = rawSheet.getLastRow();
  var filteredSheet = ss.getSheetByName("FilteredData");
  var FilteredUrl =[];
  var WholeData = rawSheet.getRange(2, 2, lr, 4).getValues();

  var Criteria = 'A';
  var Filtered = WholeData.filter(function (e){
  if(e[0]==Criteria)
 {
  FilteredUrl.push([whole.getRange(i+2, 4).getRichTextValue()]);
  return e });
  }
  
  filteredSheet.getRange(2,1,Filtered.length,Filtered[0].length).setValues(Filtered); 
  filteredSheet.getRange(2, 4, FilteredUrl.length,FilteredUrl[0].length).setRichTextValues(FilteredUrl);
  FilteredUrl = [];

}
user_194566340
Related