javascriptgoogle-apps-scriptgoogle-sheetsgoogle-apps-script-api

Delete filtered Views and hyperlinks from google sheets

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

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

Sheet1 deleted

Solution

  • I believe your goal is as follows.

    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);
}

    References: