Delete filtered Views and hyperlinks from google sheets

Question

This is an extension of question

I have sheet a sheet of names(sheet 1 in Columns A, B and C). I want Have sales information of people in Sheet 2 , sheet 3 and Sheet 4.

if possible, please add a function to delete the filter views and hyperlinks of Columns A, B and C from Sheet1. I want the option to delete filter views and Hyperlinks of names in each column. Instead of deleting all filterviews I mean. Like input will be names from each column and output will be deleted hyperlinks and filterviews for those names(This way I will have the option for deleting just Column A and B's views and hyperlinks, instead of deleting ALL).

Example pictures:

I have names in sheet1. All the columns have hyperlinks. Now I want a delete filter function to delete filter views and hyper links from column C (Names List1) only.

Sheet1 with ALL hyperlinks:

Sheet1 when delete function is applied only for the names in Names List1:

In this case, when the function is run, you want to delete the specific filter view. In this case, can you provide the sample input values you expect for the function? For example, you want to delete the filter views using the input values of the column letter? Because from your question, I couldn't understand the method for running the script you expect. I apologize for this. — Tanaike, Jan 09 '22 at 05:37
Thank you for replying. In your goal, you want to delete all filter views and hyperlinks in each column. Is my understanding correct? — Tanaike, Jan 09 '22 at 08:08
@Tanaike: Can you please take a look at https://stackoverflow.com/questions/71027348/how-to-schedule-a-bigquery-run-through-apps-script — user17243359, Feb 09 '22 at 17:51
@Tanaike Can you please take a look at this question: https://stackoverflow.com/questions/71027348/how-to-schedule-a-bigquery-run-through-apps-script?noredirect=1#comment125560263_71027348 — user17243359, Feb 26 '22 at 02:06

Yuri Khristich · Answer 1 · 2022-01-09T19:14:32.043

2

Just in case, here is the example how to remove all the links from column 'C':

function remove_links_from_col_C() {
  var range = SpreadsheetApp.getActiveSheet().getRange('C5:C');
  var values = range.getDisplayValues();
  range.clear().setValues(values);
}

If you have colored backgrounds and you need to keep them intact it can be done this way:

function remove_links_from_col_C() {
  var range = SpreadsheetApp.getActiveSheet().getRange('C5:C');
  var backgrounds = range.getBackgrounds(); // <--- save backgrounds
  var values = range.getDisplayValues();
  range.clear().setValues(values);
  range.setBackgrounds(backgrounds); // <--- restore backgrounds
}

edited Jan 09 '22 at 19:14

answered Jan 09 '22 at 12:33

Yuri Khristich

13,448
2
8
23

Thank you so much. I wanted to delete the filter view URL's as well in addition to removing links. Your answer was helpful, thank you. I can delete the URL's in the method answered by @Tanaike – user17243359 Jan 09 '22 at 13:40

Tanaike · Accepted Answer · 2022-01-09T13:29:19.700

I believe your goal is as follows.

By giving the range, you want to delete the filter views and want to reset "Sheet1".

In this case, how about the following sample script?

Sample script:

Before you use this script, please enable Sheets API at Advanced Google services.

function myFunction() {
  // Please set the range you want to delete the filter views.
  var obj = { src: "Sheet1", dst: ["C4:C", "G4:G"] };

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var sheet = ss.getSheetByName(obj.src);
  var ids = obj.dst.flatMap(e => {
    var range = sheet.getRange(e);
    var richTextValues = range.getRichTextValues();
    var fids = richTextValues.flatMap(([r], i) => {
      var url = r.getLinkUrl();
      return url ? [url.split("=").pop()] : [];
    });
    var bk = range.getBackgrounds();
    range.clearFormat().setBackgrounds(bk).setRichTextValues(richTextValues.map(([r]) => [r.copy().setLinkUrl(null).build()]));
    return fids.map(f => ({ deleteFilterView: { filterId: f } }));
  });
  if (ids.length > 0) Sheets.Spreadsheets.batchUpdate({ requests: ids }, ssId);
}

In this sample script, by var obj = { src: "Sheet1", dst: ["C4:C", "G4:G"] }, your column "C" and "G" are reset.

References:

I want the hyperlink to be cleared, not the format. Right now the background color of the cell becomes white. How can I just clear hyperlink without changing cell color? — user17243359, Jan 09 '22 at 13:22
@user17243359 Thank you for replying. I apologize for my poor English skill. From your sample image, I had thought that the background color of cells is the default color. From your replying, I updated my sample script. Could you please confirm it? If there are still your situations I couldn't notice, I apologize again. — Tanaike, Jan 09 '22 at 13:29

Delete filtered Views and hyperlinks from google sheets

2 Answers2

Sample script:

References:

Linked