0
277
ViewsAdd filter view hyperlinks and rearrange the column values
I have Sheet1 with existing filter view hyperlinks. I want to find any names that dont have filterviews, then create filterviews with hyperlinks to these names and add them in alphabetical order to the list that already has hyperlinks.
Example: In Sheet 1, Col C,E,G, new names: Tracy Jack,Maria Jose , Samuel Philips,Karan Tucker, Vincent Lee, Wes Lee do not have a filter view and hyperlink, I want to add filter view and hyperlink to these names and then add them alphabetically to the list of names with hyperlinks in the rows above
After adding hyper link to
Tracy Jack (Col C),
Maria Jose , Samuel Philips (Col E),
Karan Tucker, Vincent Lee, Wes Lee(Col G),
I want to add insert these names with links in alphabetical order in the rows above. Please see pic below for final output
1 answers
Answer question0
Here is the simply script that adds the custom menu 'Scripts' and add two commands: 'Sort column' and 'Sort columns C, E, G':
function onOpen() {
SpreadsheetApp.getUi().createMenu('Scripts')
.addItem('Sort current column', 'sort_active_col')
.addItem('Sort columns C, E, G', 'sort_cols_CEG')
.addToUi();
}
function sort_active_col() {
var letter = SpreadsheetApp.getActiveRange().getA1Notation().replace(/^([A-Z]+).+/,'$1');
sort_col(letter);
}
function sort_cols_CEG() {
['C', 'E', 'G'].forEach(letter => sort_col(letter));
}
function sort_col(letter) {
var range = SpreadsheetApp.getActiveSheet().getRange(`${letter}5:${letter}`);
range.sort(range.getColumn());
}
They sort a current column of columns 'C', 'E', 'G' respectively (start from row 5).