0
174
ViewsDelete 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 when delete function is applied only for the names in Names List1:
2 answers
Answer question0
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
}
0
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.